Using the SSAS Tabular Model, Week 3

So last time we finished building a basic model with data from a variety of different data sources.  Perhaps you are getting a bit impatient to see what the cube might look like.  If we had built this model in PowerPivot for Excel, we could at this point simply click the button from the PowerPivot window to create a Pivot Table or a Pivot Chart.  With a few additional clicks, we could drag a field or two from the fact table to the body of the table and then drag a couple of fields from the dimension tables to the rows and columns of our table.  Before you know it, we would have a useful analysis of the data in the form of either a table or a chart.

In SSAS, there is not direct way to show the pivot table and certainly, there is no pivot chart.  However, you  have noticed an option in the Model dropdown menu: Analyze in Excel.

The first time I tried this, it looked promising so I clicked it.  It did open Excel and it did show the Pivot Table Fields panel along the right side of the screen.  However, every field that I tried to drag from the Sales table, such as SalesAmount or SalesQuantity, to the Values portion of the fields panel gave me the following error message:

I was confused by the message.  After all, why would I not be able to move a numeric field (yes, it is formatted as currency, but that still makes it numeric) to the Values portion of the table.   To be honest, I puzzled over this message for some time.  The conclusion I came to is that either this is an error or perhaps, just maybe, it is because the data for this pivot table does not really reside in Excel.  This is a fact that you can easily verify by opening the PowerPivot window only to discover that there is no model defined within Excel.  SSAS is merely using Excel as a vehicle to display the data from the Tabular model.

If that is true, then perhaps the solution would be to create a calculated measure back in the Tabular model and see if that field can be placed in the values portion of the pivot table.

So I returned to the Tabular model in SSAS and looked through the menu option until I found the option to Show Measure Grid in the Table drop down menu.

I now had a calculation area beneath each table similar to the one found in PowerPivot 2012 for Excel that I have discussed before.  Clicking in one of the cells beneath the Sales Amount column, I entered the following DAX equation:

SumOfSales:=SUM([SalesAmount])

Crossing my fingers, I pressed the Enter key and was rewarded after a second or two with a calculated value in the cell that look like:

SumOfSales: 8341224364.8324

That value looked like it could be the sum of sales for all products across all years for the Contoso database.  However, reading large values such as this can be a challenge.  My first thought was to right click on the value to see if there was a format option in the menu.  There wasn’t.  However, I noticed that the Properties window displayed my new calculated measure along with some properties.  One of the properties, Format, looked promising so I clicked first in the field and when a dropdown arrow appeared to the right of the field, I opened the dropdown to show the built-in formats available to me.

Selecting Currency gave me a nicely formatted value of:

SumOfSales: $8,341,224,364.83

Great.  So now that I have at least one calculated measure, I thought that I might try that Analyze in Excel option again. This time a new instance of Excel opened with the Pivot Table Fields populated at the top with a ‘new’ table containing one field, SumOfSales, my calculated measure.  I could easily tell that this table was different from the others because it had a summation symbol before the table name letting me know that this calculated value came from the Sales table.

This field I could drag down to the Values box.  I could also simply click the check box before the calculated measure’s name and it would automatically be sent to the Values box.

I then added the Product Category Description, Product SubCategory Description, and Product Description in that order to the Rows box.  I also added Calendar Year to the Columns box.  I now had something that closely resembled a pivot table that I might have created using PowerPivot for Excel.

The problem with dragging the three individual description fields over to the Rows box is that it would be so easy to get the order of the descriptions wrong.  For example, I might have:

Product Description

Product SubCategory Description

Product Category Description

Or I might even have:

Product SubCategory Description

Product Category Description

Product Description

The point is that unless user really know the structure of the data, it is very easy for them to get the hierarchy of a set of dimensions incorrect.  I decided that fixing this problem would be my next task that unfortunately for you will have to wait until next week.

BTW, my presentation on Tabular modeling at OPASS was postpone for one week due to scheduling conflicts by the meeting venue.  The meeting was rescheduled for October 24th.  If you are interested, go to http://www.opass.org to sign up.

C’ya.