Javier, I will be sure to include you when the document has been completed. Thank you for your interest in MarkLogic.
Thank you, Scott Fowler Principal Sales Engineer, Alliances MarkLogic Corporation [email protected]<mailto:[email protected]> Mobile: 571-275-9690 Office: 703-854-8552 [Description: Description: Description: cid:[email protected]] [Description: Description: Description: MarkLogic Corporation] 7590 Jones Branch Drive Suite 200 McLean, VA 22102 http://www.marklogic.com/ [cid:83827696-CE51-42E0-9F05-705CB519B15F] This e-mail and any accompanying attachments are confidential. The information is intended solely for the use of the individual to whom it is addressed. Any review, disclosure, copying, distribution, or use of this e-mail communication by others is strictly prohibited. If you are not the intended recipient, please notify us immediately by returning this message to the sender and delete all copies. Thank you for your cooperation. From: Javier Lizarraga <[email protected]<mailto:[email protected]>> Date: Wednesday, April 29, 2015 at 1:51 PM To: MarkLogic Developer Discussion <[email protected]<mailto:[email protected]>>, "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Cc: Scott Fowler <[email protected]<mailto:[email protected]>> Subject: RE: [MarkLogic Dev General] SQL Query Performance and Tableau Hi Sara, Can you please provide a copy to me as well. We are currently evaluating using Tableau with MarkLogic, it would be very helpful. Thanks, Javier From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Sara Mazer Sent: Wednesday, April 29, 2015 10:49 AM To: [email protected]<mailto:[email protected]>; [email protected]<mailto:[email protected]> Cc: Scott Fowler Subject: Re: [MarkLogic Dev General] SQL Query Performance and Tableau Hi Freddy, We are working on a document that contains best practices for using MarkLogic and Tableau. My colleague, Scott Fowler, will send it over as soon as it is ready. What I meant by grouping in Tableau is to limit the “GROUP BY” in SQL when possible ( i.e. use DISTINCT instead of GROUP BY 1). Also, yes, a trade-off sometimes against performance in ML and limiting data returned to Tableau, but I’ve often found it faster to do grouping and ordering in Tableau. Also, I usually see a huge performance increase and ability to limit data returned to Tableau when using, and pushing, as much logic as possible to the right of a SQL MATCH keyword. Sara From:[email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Freddy Holwerda Sent: Tuesday, April 28, 2015 4:57 AM To: [email protected]<mailto:[email protected]> Subject: Re: [MarkLogic Dev General] SQL Query Performance and Tableau Dear Sara, Thank you for your elaborate reply on this topic. I am also investigating a scenario in which SQL would be used as entry point to ML. Is there any document available that actually describes the practices you refer to, eg. concerning the data model, which operations to do client/server side and how that relates to minimizing the data that comes back, translating an SQL scenario to a XQuery/Javascript scenario, etc.. Specific to your reply below: I do assume that doing the grouping at the side of Tableau instead of ML, would actually maximize, not minimize the data retrieved from ML? Thank you in advance, Freddy Holwerda > From: > [email protected]<mailto:[email protected]> > Subject: General Digest, Vol 130, Issue 106 > To: [email protected]<mailto:[email protected]> > Date: Mon, 27 Apr 2015 22:52:56 -0700 > > Send General mailing list submissions to > [email protected]<mailto:[email protected]> > > To subscribe or unsubscribe via the World Wide Web, visit > http://developer.marklogic.com/mailman/listinfo/general > or, via email, send a message with subject or body 'help' to > [email protected]<mailto:[email protected]> > > You can reach the person managing the list at > [email protected]<mailto:[email protected]> > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of General digest..." > > > Today's Topics: > > 1. SQL Query Performance and Tableau Connection (Cynthia Jiang) > 2. Re: SQL Query Performance and Tableau Connection (Sara Mazer) > 3. Is data also store in forest (Shashidhar Rao) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Mon, 27 Apr 2015 20:56:29 +0000 > From: Cynthia Jiang <[email protected]<mailto:[email protected]>> > Subject: [MarkLogic Dev General] SQL Query Performance and Tableau > Connection > To: "[email protected]<mailto:[email protected]>" > <[email protected]<mailto:[email protected]>> > Cc: Walt Rolle <[email protected]<mailto:[email protected]>> > Message-ID: > <[email protected]<mailto:[email protected]>> > Content-Type: text/plain; charset="us-ascii" > > Hello, > > We have been testing MarkLogic 8 downloaded from your website for a POC. > > Our environment is very straight forward. > > A single VM (windows 7 Enterprise - 64bit) with 16GM RAM and 2 Intel (E5-2690 > v2) 3GHz processors. > > We have around 4 SQL tables that we modeled in Json format so each row ends > up becoming a document, and here are some of the stats: > > Patient : 8 fields / 20,924 documents > PatientAddress: 5 fields / 36,193 documents > Claim: less than 30 fields / 335,349 documents > ClaimDiagnosis: 2 fields / 8,676 documents > > We created element range index for all the fields, and created 4 views that > each represent a SQL table. > > Then we were able to use SQL to query the data and get the result in the > query console. > > But the performance is not nearly as what we expected, we thought it would > work faster than SQL environment. > > A simple query like the one below took 30 seconds to run, and the same query > took 3 seconds to run with a larger data set in a SQL environment with 6GB > memory and 4 processors. > > select c.Patientid, ClaimNumber, FirstName, LastName, sum (PharmacyTotalCost) > as PharmacyTotalCost > from claim c inner join patient p on c.patientid = p.patientid left outer > join patientaddress a on p.patientid = a.patientid > where a.[State] = 'LA' > group by c.PatientId, ClaimNumber, FirstName, LastName order by > SUM(PharmacyTotalCost) desc limit 10 > > We also tested out the connection between MarkLogic and Tableau 9 trial > version through MarkLogic 64 bit ODBC connector on the same VM. > The connection is not stable, got lost very frequently, and sometimes it > won't stop running for 20 minutes after adding one dimension and one simple > measure the columns and rows shelves. > > We are under a very tight timeline and have to report our findings back to > our client within 24 hours. With the current performance, we will not be able > to recommend any MarkLogic product to our client. > > Please help us if there is anything that we could do to improve the SQL query > performance and data connectivity between Tableau and MarkLogic. > > Also, for the query as the simple example above, is there any XQuery or > Javascript Query that we can use that could produce the similar results? We > cannot find any documented examples. > > Any help will be really appreciated! > > Thank you very much, > > Cynthia Jiang > RDA Corpration > > > -------------- next part -------------- > An HTML attachment was scrubbed... > URL: > http://developer.marklogic.com/pipermail/general/attachments/20150427/6baf58a2/attachment-0001.html > > ------------------------------ > > Message: 2 > Date: Tue, 28 Apr 2015 00:29:37 +0000 > From: Sara Mazer <[email protected]<mailto:[email protected]>> > Subject: Re: [MarkLogic Dev General] SQL Query Performance and Tableau > Connection > To: MarkLogic Developer Discussion > <[email protected]<mailto:[email protected]>>, > "[email protected]<mailto:[email protected]>" > <[email protected]<mailto:[email protected]>> > Cc: Scott Fowler > <[email protected]<mailto:[email protected]>>, Walt Rolle > <[email protected]<mailto:[email protected]>> > Message-ID: > <7234b3233c1fb84ea9542f2daf2f5e491940c...@exchg10-be01.marklogic.com<mailto:7234b3233c1fb84ea9542f2daf2f5e491940c...@exchg10-be01.marklogic.com>> > Content-Type: text/plain; charset="us-ascii" > > Hi Cynthia, > My name is Sara Mazer, and I am a solutions consultant in the DC area. I've > worked a lot with Tableau and MarkLogic and would like to set up some time to > discuss your evaluation and give you some best practices so that you can get > the best performance possible. I was the person who worked with Tableau to > add MarkLogic as a named connection and supported their certification of > MarkLogic, and I think I can help you. > > I have a too many best practices to go over by email, so I'd like to speak > with you and walk you through setting up databases for successful analytics > through WebEx. Would that be possible? I have a meeting from 9:30-11:00 but > can move any other meeting I have tomorrow to suit your schedule, just let me > know a good time for you. > > In general, I plan on: > Recommending the best data model for MarkLogic of this type of data (hint: it > doesn't look like what would be in an RDBMs) > Showing you a demo of patient and claims data with Tableau and review how it > was done > Discussing the best practices of working with Tableau (minimize the data > coming back from MarkLogic by using custom SQL for charts, using MATCH > keyword, having Tableau do grouping/sorting instead of in SQL) > Reviewing common connection issues between MarkLogic and Tableau and how to > avoid them (most likely you are corrupting your SQL views:a best practice is > to have a unique schema database for each MarkLogic database) > Reviewing performance of XQuery/JavaScript vs. SQL in MarkLogic > > Best regards, > Sara Mazer > sara dot mazer "at" marklogic dot com > > This e-mail and any accompanying attachments are confidential. The > information is intended solely for the use of the individual to whom it is > addressed. Any review, disclosure, copying, distribution, or use of this > e-mail communication by others is strictly prohibited. If you are not the > intended recipient, please notify us immediately by returning this message to > the sender and delete all copies. Thank you for your cooperation. > > > > From: > [email protected]<mailto:[email protected]>[mailto:[email protected]]<mailto:[mailto:[email protected]]> > On Behalf Of Cynthia Jiang > Sent: Monday, April 27, 2015 4:56 PM > To: [email protected]<mailto:[email protected]> > Cc: Walt Rolle > Subject: [MarkLogic Dev General] SQL Query Performance and Tableau Connection > > Hello, > > We have been testing MarkLogic 8 downloaded from your website for a POC. > > Our environment is very straight forward. > > A single VM (windows 7 Enterprise - 64bit) with 16GM RAM and 2 Intel (E5-2690 > v2) 3GHz processors. > > We have around 4 SQL tables that we modeled in Json format so each row ends > up becoming a document, and here are some of the stats: > > Patient : 8 fields / 20,924 documents > PatientAddress: 5 fields / 36,193 documents > Claim: less than 30 fields / 335,349 documents > ClaimDiagnosis: 2 fields / 8,676 documents > > We created element range index for all the fields, and created 4 views that > each represent a SQL table. > > Then we were able to use SQL to query the data and get the result in the > query console. > > But the performance is not nearly as what we expected, we thought it would > work faster than SQL environment. > > A simple query like the one below took 30 seconds to run, and the same query > took 3 seconds to run with a larger data set in a SQL environment with 6GB > memory and 4 processors. > > select c.Patientid, ClaimNumber, FirstName, LastName, sum (PharmacyTotalCost) > as PharmacyTotalCost > from claim c inner join patient p on c.patientid = p.patientid left outer > join patientaddress a on p.patientid = a.patientid > where a.[State] = 'LA' > group by c.PatientId, ClaimNumber, FirstName, LastName order by > SUM(PharmacyTotalCost) desc limit 10 > > We also tested out the connection between MarkLogic and Tableau 9 trial > version through MarkLogic 64 bit ODBC connector on the same VM. > The connection is not stable, got lost very frequently, and sometimes it > won't stop running for 20 minutes after adding one dimension and one simple > measure the columns and rows shelves. > > We are under a very tight timeline and have to report our findings back to > our client within 24 hours. With the current performance, we will not be able > to recommend any MarkLogic product to our client. > > Please help us if there is anything that we could do to improve the SQL query > performance and data connectivity between Tableau and MarkLogic. > > Also, for the query as the simple example above, is there any XQuery or > Javascript Query that we can use that could produce the similar results? We > cannot find any documented examples. > > Any help will be really appreciated! > > Thank you very much, > > Cynthia Jiang > RDA Corpration > > > -------------- next part -------------- > An HTML attachment was scrubbed... > URL: > http://developer.marklogic.com/pipermail/general/attachments/20150428/c22d795f/attachment-0001.html > > ------------------------------ > > Message: 3 > Date: Tue, 28 Apr 2015 11:22:56 +0530 > From: Shashidhar Rao > <[email protected]<mailto:[email protected]>> > Subject: [MarkLogic Dev General] Is data also store in forest > To: [email protected]<mailto:[email protected]> > Message-ID: > <CAFY8jidOTxR7PnKAPQ8=u+powg2n6qjs_4rdo6945xonegd...@mail.gmail.com<mailto:CAFY8jidOTxR7PnKAPQ8=u+powg2n6qjs_4rdo6945xonegd...@mail.gmail.com>> > Content-Type: text/plain; charset="utf-8" > > Hi, > > We are running out of disk space due to the indexing . > > Could some answer a basic question. > > I have loaded data to Marklogic server from /home/data/data1 . Now the > loading is finished and re-indexing and merging started. > > Does Marklogic server still require data to be in /home/data/data1 or can > this data be deleted . > > I have always assumed that even after loading the data from the path above > Marklogic still requires data to be in this folder. > > Basically I want the data to be deleted once the loading is complete as > space is running out due to heavy re-indexing. > > Thanks > -------------- next part -------------- > An HTML attachment was scrubbed... > URL: > http://developer.marklogic.com/pipermail/general/attachments/20150428/67f143ed/attachment.html > > ------------------------------ > > _______________________________________________ > General mailing list > [email protected]<mailto:[email protected]> > Manage your subscription at: > http://developer.marklogic.com/mailman/listinfo/general > > > End of General Digest, Vol 130, Issue 106 > *****************************************
_______________________________________________ General mailing list [email protected] Manage your subscription at: http://developer.marklogic.com/mailman/listinfo/general
