Using PowerPivot to combine cube (SSAS) data with manual data
With the release of PowerPivot CPT3 you can connect to a SSAS cube, a feature i was really looking forward to and i think will be used a lot. The best mashup possible between traditional BI and ad hoc questions. Think about a salesof the current year compared to the forecast of this year. Where the Current year is available and the forecast is determined by an analyst in Excel.
First thing we notice when we open the PowerPivot window is the addition of Analysis services on the from database tab.
We can connect to a analysis server using the regular data connection.
Next we can type an MDX query (like any analyst is going to do that) or choose for design:
Of course we go for design, this opens our familiar analysis query designer, here we can drag and drop desired data from the cube.
When we press OK, the data will be loaded to PowerPivot:
One big issue i see here is that the data coming from SSAS needs to be “joinable”. You usually want to join on code instead of description but what you see is that most cubes consist of solely description or code – description. Think of the Month member, usually has the monthname as value. The Adventureworks cube had Monthname + year as value so this makes joining easier. One major design essential for you SSAS cube, try to add a code member to all your dimension and a date member.
The datatypes from SSAS are not recognized themselves so you have to set it yourselves on the measures row and I like to rename to columns to somewhat nicer description.
Ok next up is loading the forecast data, our analyst has created a sheet that with the same month column, subcategory’s and sales forecast and puts it into our worksheet. To make it easier we used the same name as our ssas columns.
You can load the data into PowerPivot bij creating a linked table with one press on the button. You have to create a table first (CTRL-L inside the table will create it very easy)
Now we have the data in our PowerPivot environment we can start analyzing. So we create a PivotTable in our Excel sheet. I drag the salesamount and sales forecast into the values and drag the month on the row.
PowerPivot CTP3 automatically sees we need a relationship to join our two tables, a warning shows up on the top right hand:
i click create but no relationship is detected. To discover what is wrong we have to go back to the powerpivot window and try to create the relationship ourselves.
hmm it appears you have to a master data table for your dimension. Hmm not too user friendly …
Ok back to the table import tool, we have to load data from the time dimension into our worksheet, we open SSAS cube and select Year and Month. Since this is a dimension table we don’t include any measures and click include empty cells. This gives us a unique row of data to use as dimension table. But when loading the data into PowerPivot we get 0 Rows imported, oblivious PowerPivot doesn’t handle MDX query without measures well. Too bad we can’t use this option since this is the preferred option that because when the data refreshes i want to have the latest values from the cube. Update: This has been fixed in the next version and is a known bug.
On to our second option we do it ourselves in Excel. I copied the columns from the facttable into excel and use the “remove duplicates” feature to create unique columns. Use Create linked table to load the 2 tables into powerpivot. we now have 4 tables available in PowerPivot, 2 measure tables and 2 dimension tables.
When we return to Excel we see a warning that our model is modified and we can refresh the Pivottable with one press of the button, great new function.
I now drag and drop the months on the y-ax with sales amount and sales forecast in the pivottable, PowerPivot automatically detects relationship when the columns are called the same and lets the user create them, resulting in the following:
I now want to slice on category and subcategory, when i drag these values in the slicer PowerPivot again automatically detects the relationship.
One more cool thing we would like to add is to see the difference between sales amount and sales forecast so lets add a new measure that subtracts the 2 measures, i added a new measure in the Inetnet sales amount table with the following formula:
=’Internet Sales’[Sum of Sales Amount] – ‘Table3′[Sum of Sales forecast]
Resulting in this sheet eventually:
After this I cleaned it up a bit to hide some of the dimension fields from the fact table, you can download the excel sheet yourself and play with the result: date.xslx. I try to create a movie about this post this week to walk it step by step because in understand some parts go a little fast in this blog post.
Conclusion: PowerPivot is great but there are still a lot of things you need to know when you want to create a Ad hoc BI without technical knowledge, i had meant to create this blog post in a few hours but eventually took me a lot more and two bugs in the process I hope MS isn’t quiet celebrating, although i love the product to let end users play with it they still have to make it more userfriendly.
