If you’ve played with PowerPivot at all, it’s pretty obvious how flexible the tool is in terms of creating data models. After you initially create a model, you may want to spend some additional time with it to make sure users can easily leverage what you’ve created in other tools. If you don’t, the PowerPivot model you publish probably won’t see lots of re-use outside of the PowerPivot gallery and Excel because users won’t be able to get at the values they want to.
What am I talking about? Here’s an example:
I pointed PowerPivot at the Adventure Works DW sample database, and brought all its tables and rows into my workbook. I then published the workbook directly into a SharePoint 2010 PowerPivot Gallery. I didn’t bother to add any UI to the sheets in the workbook (no PivotTables, PivotCharts, Slicers).
Next, I launched Report Builder 3.0, created a new Analysis Services data source and typed in the location of the *.xlsx I published. I chose the single “sandbox” in the workbook as the database name. Here’s a screenshot of my new RB data source being created (embedded image truncated, click below to view complete screenshot) :
Then, add a DataSet to this Data Source and launch the Query Designer. Notice anything wrong with this picture?
That’s right — our dimensions are showing up as measure groups AND dimensions! And if you scroll down the dimension list, you’ll see that the the fact tables not only show up in the measure group list, but as dimensions, too!
Finally, crack open one of the measure groups – It contains NONE of the measures that are defined in the PowerPivot model – only “_Count FactSalesQuota” in the screenshot below.
What the hell is going on? What you’re seeing is a function of how PowerPivot “thinks”. To PowerPivot, everything is a dimension unless proven otherwise, and therefore all of the facts in your measure groups are considered dimension attributes until PowerPivot understands that the attribute in question should really be considered a measure.
So let’s fix all this stuff up so we have a usable model, shall we?
To begin with, we’re going to need to help PowerPivot figure out which of our fields represent measures. In PowerPivot there are implicit and explicit measures. Any field you drag into the Values list of a PivotTable/Chart is regarded as an implicit measure by PowerPivot.
I’ve opened up that empty AWDW workbook again, and added a PivotTable. Next, I’ll add the SalesAmountQuota measure from the FactSalesQuota table to the Values area of the Field List (embedded image truncated, click below to view complete screenshot).
After publishing the updated workbook to MOSS, let’s look at Report Builder again.
There you go – an implicit measure. By adding a field to the Values list of the Gemini Task pane, we’ve tipped PowerPivot off to the fact that SalesAmountQuota is indeed a fact.
However, if you look at the dimension list, you’re still going to see FactSalesQuota as a dimension, and SalesAmountQuota (along with 4-5 other fields) as a possible dimension attribute. Well, that ain’t right.
How to fix? Make that measure an explict measure. Here’s what to do.
Remove the SalesAmountQuota from the Values List in your PivotTable.
Switch back into the PowerPivot window, find the FactSalesQuota table, and select all the columns. Finally, right-click the selected columns and choose to Hide Columns | From Gemini and PivotTable
Back in the worksheet, click Refresh All on the Data ribbon, and your field list should look something like this (note how our measure group name is “dimmed” – embedded image truncated, click below to view complete screenshot):
Right-click FactSalesQuota, and select Add New Measure. Create a formula which points to the SalesAmountQuota field you hid just a second ago (embedded image truncated, click below to view complete screenshot):
When you’re done, you should have something that looks like this (embedded image truncated, click below to view complete screenshot):
That’s just about it. Publish your workbook again, and fire up Report Builder. Note that the explicit measure you added now shows as part of the measure group FactSalesQuota and that FactSalesQuota doesn’t appear at all under dimensions.
And some notes:
In order to get the data model to refresh in Report Builder as I made changes in Excel and re-published, I found I had to sometimes delete the published workbook and/or IISReset. Using the Reconnect/Refresh button in Report Builder’s SSAS query designer didn’t refresh my schema for some reason.
Many thanks to Oliver Matrat, a senior PM on the Analysis Services team for helping me out when I was confused about how to make this stuff work – most of what I’ve related here comes directly from him.
