Leaf level queries in Gemini (PowerPivot) and SSAS 2008
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.
Tags: performance, excel