One of the ways to interact with a SSAS cube is through an ordinary Excel Pivot Table. You can do this by the following steps:
1. Insert Pivot Table
2. Select “Use an external data source”.
3. Choose or create a connection to your SQL SSAS cube.
Once you have established your connection, you can choose measures and dimensions from your cube to plot out your pivot table.
The Advantage of a Pivot Table Over PowerPivot
When PowerPivot fetches data from a cube, it has to pre-fetch all the possible cells to be used within the model as a local cache. This results in a lot of rows to be fetched especially if there are a lot of dimensions as the cells end up being all possible combinations of these dimensions.
In contrast, a pivot table fetches only data as needed. This makes it much faster as you drill up and down various dimensions. For example, I have a cube containing 35 million records for airline delays. I can create a pivot table against this cube by connecting to it from excel and inserting a pivot table.
The key difference with a pivot table compared to PowerPivot is it only fetches cells in the cube that are needed to display what is needed instead of pre-fetching all possible cells. This makes manipulating the cube measures and dimensions much faster especially with large data sets. Here is a pivot table that shows airline delays by hour:
If I click on any of the hour, the moment I click on the pivot table Excel runs a query against the cube to fetch the cells needed to display the data:
The speed is very fast to drill because its only actually pulling up 60 rows (one for each minute). If we tried to do this in PowerPivot, it would need to pull in every row for every second to create a local data set and this would be very slow to fetch the data.
The Big Advantage – Refreshing the Data
In addition to being high performance to create the pivot table in the first place, its also much faster to refresh the data. Again, the reason is because Excel only fetches the data it needs instead of trying to create a local data cache in memory for all possible records. The refresh on my pivot table is less than 1 second for 35 million records!
The Big Limitation – PowerView only works with PowerPivot
The big limitation to this approach is that PowerView only works with PowerPivot. It will not all you to use an ordinary pivot table as a data source. You are limited to creating dashboards using Pivot Charts.