Leaf level queries in Gemini and SSAS 2008
Posted by thomasivarssonmalmo on September 18, 2009
SSAS cubes are good at answering queries that are designed for retrieving aggregated information. Sometimes end users also would like to use SSAS for queries that will flatten out the cube to the leaf level in dimensions and the fact table. Response time in these scenarios are often slow because SSAS have no aggregations and will have to create the resultset dynamically, from leaf level data.
Let us have a look at what Gemini can do in the leaf level scenario. I have imported data from the Adventure Works 2008 DW source database. It is the customer table, the product tables and the fact internet sales table. Gemini take care of the relations so after the import is finished you can start building queries in the Excel pivot tables. I am using Excel 2010 with Gemini and SQL Server 2008 sp1.
I will start with building the leaf level query with the Adventure Works demo cube on SSAS 2008. I will put the leaf level customer together with the leaf level product on rows and calendar year on columns. The measure is internet sales amount.
Response time is around 1 minute on my laptop, Intel Dual Core 1,66 GHz and 4 GB RAM. Not bad but can be a liitle to much for stressed end users. And if you change the order of customer and product in the rows box you will have to wait again. Remember that these types of queries is not what SSAS is designed for.
Finally I will try the same in Gemini with the data I have imported before.
I have created a column in the customer table, FullName with this DAX syntax: =[LastName] & ", " & [FirstName]. I also use English product name and SalesAmount.
On the same laptop response time is 10-15 seconds.
Conclusion: Gemini takes care of the scenario with end users creating leaf level queries that can make SSAS slow. Remember that SSAS is designed for answering top level or aggregated queries or if your queries goes from the top down to a special part of the cube.
Even if you not not load millions of records into Gemini it will still outperform running leaf level queries in Pivot tables on top of an Excel spreadsheet connected to SSAS, without Gemini..