[Mondrian] Define multiple JDBC connections under a Mondrian schema

Julian Hyde julianhyde at gmail.com
Mon Mar 21 14:00:37 EDT 2016


I’ve long wanted to have multiple JDBC connections; see http://jira.pentaho.com/browse/MONDRIAN-1177 <http://jira.pentaho.com/browse/MONDRIAN-1177>.

The hard part is not defining connections; it’s executing joins between tables in different schemas, and aggregate the results of those joins, and push filters down if possible. With the various query patterns that can occur, pretty quickly you need a full SQL engine.

So, this was a use case that drove me to create Optiq (now Apache Calcite). Not coincidentally, Drill uses Calcite under the covers.

I wrote an extension to Mondrian 4 that allows you to define connections in the Mondrian schema file. Internally it instantiates Calcite, but it’s nicer because the Mondrian designer is just working with one schema file. See the “federate” branch: https://github.com/pentaho/mondrian/tree/federate <https://github.com/pentaho/mondrian/tree/federate> 

Julian



> On Mar 21, 2016, at 7:11 AM, Tom Barber <tom at analytical-labs.com> wrote:
> 
> I didn't say it was optimal, I just said you could do it :P
> 
> I've never tried it in production, I have messed around with Drill as a layer to merge NoSQL and SQL data sources in one mondrian schema as a POC, thats about as fun as it got.
> 
> Tom
> 
> --------------
> 
> Director Meteorite.bi - Saiku Analytics Founder
> Tel: +44(0)5603641316  
> 
> (Thanks to the Saiku community we reached our Kickstart <http://kickstarter.com/projects/2117053714/saiku-reporting-interactive-report-designer/> goal, but you can always help by sponsoring the project <http://www.meteorite.bi/products/saiku/sponsorship>)
> 
> On 21 March 2016 at 14:08, Ricardo Fradinho <ricardo.fradinho at webdetails.pt <mailto:ricardo.fradinho at webdetails.pt>> wrote:
> @Tom,
> 
> Please share/blog your findings using the virtual layer.
> 
> I’m skeptical that we’ll get a decent performance as Mondrian generates sql statements using fact and dimensions in the same statement, e.g., 
> 
> Select … from Fact F join Dimension D where …
> 
> which is hard to optimize by the virtual layer
> 
> However,  maybe there’s some opportunities there (like using sub selects), so getting feedback is welcome.
> 
> Best Regards,
> Ricardo.
> 
> From: <mondrian-bounces at pentaho.org <mailto:mondrian-bounces at pentaho.org>> on behalf of kevin melo <kevinmelo92 at gmail.com <mailto:kevinmelo92 at gmail.com>>
> Reply-To: Mondrian developer mailing list <mondrian at pentaho.org <mailto:mondrian at pentaho.org>>
> Date: Monday, March 21, 2016 at 1:57 PM
> To: Mondrian developer mailing list <mondrian at pentaho.org <mailto:mondrian at pentaho.org>>
> Subject: Re: [Mondrian] Define multiple JDBC connections under a Mondrian schema
> 
> Thanks
> 
> 2016-03-21 10:52 GMT-03:00 Tom Barber <tom at analytical-labs.com <mailto:tom at analytical-labs.com>>:
> Technically no, there is multi schema support but no multidatabase support. 
> 
> One technique we were discussing on the Saiku ML was using something like Apache Drill to add a layer over the top of the two target DB's to create a pseudo DB that will allow you to interact with them seamlessly.
> 
> --------------
> 
> Director Meteorite.bi - Saiku Analytics Founder
> Tel: +44(0)5603641316 <tel:%2B44%280%295603641316>  
> 
> (Thanks to the Saiku community we reached our Kickstart <http://kickstarter.com/projects/2117053714/saiku-reporting-interactive-report-designer/> goal, but you can always help by sponsoring the project <http://www.meteorite.bi/products/saiku/sponsorship>)
> 
> On 21 March 2016 at 13:49, kevin melo <kevinmelo92 at gmail.com <mailto:kevinmelo92 at gmail.com>> wrote:
> Is there any way to create connections with other banks by Mondrian?
> 
> I have a default connection, and i want to access a dimension in other database.
> 
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>
> http://lists.pentaho.org/mailman/listinfo/mondrian <http://lists.pentaho.org/mailman/listinfo/mondrian>
> 
> 
> 
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>
> http://lists.pentaho.org/mailman/listinfo/mondrian <http://lists.pentaho.org/mailman/listinfo/mondrian>
> 
> 
> _______________________________________________ Mondrian mailing list Mondrian at pentaho.org <mailto:Mondrian at pentaho.org> http://lists.pentaho.org/mailman/listinfo/mondrian <http://lists.pentaho.org/mailman/listinfo/mondrian>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>
> http://lists.pentaho.org/mailman/listinfo/mondrian <http://lists.pentaho.org/mailman/listinfo/mondrian>
> 
> 
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20160321/8592b218/attachment.html 


More information about the Mondrian mailing list