BIRT Open Source - direct access to SQL Server 2008 Cube via MDX query language

Dear forum, 
 
I was wondering if Open Source BIRT Ver 2.5.x can directly access a SQL Server 2008 cube using an MDX query. I did find a thread in 2006 <a class='bbc_url' href='http://http://dev.eclipse.org/newslists/news.eclipse.birt/msg08124.html'>[news.eclipse.birt] MDX Support in BIRT </a> but I am hoping the the story has changed. 
 
If this is still a NO I would also like to understand why BIRT has implemented its own cubing technology rather than levering or at least allowing MDX queries. And lastly are there any tips or tricks to access a cube using MDX directly from within birt? 
 
 
Thanks in advance for any insight, 
 
Max

Find more posts tagged with

Comments

MMirabito

I had hope that by now someone would have answered so I will rephrase hoping that others might chime in.

I am using BIRT 2.5.x open source version, does anyone know how to connect to a SQL Server Analysis Services Server so that BIRT can access a cube using a MDX query and generate a BIRT Crosstab? Is this even possible, and/or is this functionality available with the Actuate version of the product.

Thanks again,

Max

Virgil Dodson

Hi Max, if you can get to your MDX query via JDBC (like this example at http://javatip.com/2010/06/frameworks/spring/mdx-from-java/) then you can use the standard BIRT JDBC datasource... otherwise, if you are getting your data into Java, then use BIRT's Scripted Data Source

MMirabito

<blockquote class='ipsBlockquote' data-author="'vdodson'" data-cid="69032" data-time="1286222964" data-date="04 October 2010 - 01:09 PM">
Hi Max, if you can get to your MDX query via JDBC (like this example at <a class='bbc_url' href='http://javatip.com/2010/06/frameworks/spring/mdx-from-java/'>http://javatip.com/2010/06/frameworks/spring/mdx-from-java/</a>) then you can use the standard BIRT JDBC datasource... otherwise, if you are getting your data into Java, then use BIRT's Scripted Data Source </blockquote>
 
Hi Virgil, 
 
Thanks for the explanation and link, I had stumbled upon it a while back but was still curious to know if BIRT had any plans to access MDX cubes directly but I guess the answer is using the OPENQUERY method. 
 
One of our requirements is to allow users to dynamically select the dimension (rows,cols) and measures and then display as a crosstab. In my experiments I have found that BIRT crosstabs and cubes are fast when the search criteria is granular but if the user decides to go against the entire data set (250,000+ rows) he/she will be waiting for several minutes. 
 
I have few more questions: 
 
<ul class='bbcol decimal'><li>I have not looked into it yet but as far as performance do you know which will be faster? BIRT Cubes or JDBC with open query? </li><li>This is pure ignorance on my side but if the crosstab is dynamically built every time based on the user?s selections (dimensions and measures) does this mean that the cube is also rebuilt? </li><li>Are there ways to cache it and does it even make sense?</li></ul>
 
Thanks again, 
 
Max