How PowerPivot differs from SSAS Tabular, and using it to prototype data models
Part three of a four-part series of blogs

PowerPivot is virtually identical to SSAS Tabular underneatht the glossy exterior, but it has got a few extra features explained in this blog (which also shows how to import PowerPivot data models into Analysis Services, and why you might want to do this).

  1. Using PowerPivot with Analysis Services (tabular)
  2. Using PowerPivot
  3. The 3 main differences between PowerPivot and SSAS (this blog)
  4. Importing PowerPivot data models into SSAS Tabular

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 18 February 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

The 3 main differences between PowerPivot and SSAS

This page lists the 3 main differences between creating data models in PowerPivot and creating them in Analysis Services.

One other minor difference is that when you change a PowerPivot data model, pivot tables based on it automatically refresh.

Difference 1 - Implicit measures

In the example in the previous part of this blog, my model didn't actually include any measures.  However, this doesn't matter, as you can click on any numeric field to aggregate it:

Click on quantity field

When you click on a numeric field, PowerPivot will assume you want to aggregate it.


By default PowerPivot will sum numeric fields, but you can change any measure's value field settings:

Changing value field settings

Click on the drop arrow to the right of any measure to change how it's aggregated (summing, averaging, counting, etc).


What's going on is that PowerPivot does create a measure; it just makes it look like it hasn't.  To see this, return to your data model:

Hidden measure

Am I lying? There don't appear to be any measures in the transactions table.

To prove I am telling the truth, click on this tool on the Advanced tab of the ribbon:

Show implicit measures tool

Click on this tool to reveal all the measures created for you automatically.


PowerPivot reveals the hidden measures you've created:

The implicit measure created

PowerPivot created - and hid - a measure summing the quantity.

Although the SSAS method is more robust, it is nice being able to create and amend measures on the fly in PowerPivot.

Difference 2 - creating measures from Excel

Not only can you create measures automatically in Excel, but you can also create them manually.  First choose to create a measure:

Create new measure

Choose this option on the Excel PowerPivot ribbon.


You can now create a measure:

Create new measure

Here we're creating a measure called TotalQuantity within the Transaction table.


Before you get too excited, the Excel measure editor is just as bad as the SSAS one (I suspect they use the same underlying code, and it's just the user interface which looks different).

Measures that you create automatically show up in your data model and in your pivot table:

Two measures

Here the Quantity measure is an implicit one, and the TotalQuantity one we've explicitly created.


Difference 3 - linked tables

Let's suppose that you wanted to summarise sales by leggedness like this:

Sales by legs

We want to show a pivot table like this, but giving the legs column more meaningful values.


To do this, you could create the following additional worksheet within the workbook containing the PowerPivot data model:

Leggedness descriptions

A worksheet tying legs to descriptions.


Assuming you've created this table, click on any cell within it and choose this option from the PowerPivot tab of Excel:

Add to data model

Choose to add the worksheet to your data model.

Confirm that your table has got headers (if it doesn't, it won't work very well):

Table has headers

Tick the box to confirm the table has a header row.


PowerPivot adds another table to your data model, which is linked to the worksheet's data:

The new table

PowerPivot has created a new linked table called Table1.


What Excel has done is to create a table, and give it a name (which you can then change, as below):

Changing the table name

Change the table name on the DESIGN tab of the Excel ribbon.


In our case, we could then create a relationship between the Animal and Leggedness tables:

The legs relationship

A relationship linking the two tables.

You could then amend your pivot table to show the legs description column:

Legs description

You often need to click on ALL rather than ACTIVE to see a full list of the fields that you can include in a pivot table.


The final pivot table could look like this:

Pivot table showing legs

The final pivot table shows the description for legs, not the number.


I'm not convinced linked tables bring much to the party.  Because they're embedded within a workbook, you can only use them within the file in which you've created them.  It's hard to see what you lose by importing Excel data rather than linking to it, and easy to see what you gain.  It's also worth adding that when you import a PowerPivot data model in to SSAS Tabular, linked tables are converted to imported data.

For the final part of this blog, I'll look at why you might decide to import your PowerPivot data model into Analysis Services, and how to do this (using the data model we've created as an example).

This blog has 0 threads Add post