BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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).
- Using PowerPivot with Analysis Services (tabular)
- Using PowerPivot (this blog)
- The 3 main differences between PowerPivot and SSAS
- 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.
Using PowerPivot
This page shows how you can get up and running with using PowerPivot within Excel.
Installing PowerPivot
You can run PowerPivot in any version of Excel from 2010 onwards. This blog uses Excel 2016.
There is one very important distinction: for Excel 2010 only Microsoft decided "measures" should be called "calculated fields". By 2013 they repented, and reinstated the term "measure".
Before running PowerPivot, make sure you've loaded the add-in. First go into Excel options:
Choose this option from the File menu in Excel.
Now go to the Add-ins tab, and choose Com Add-ins:
Choose COM Add-ins, then click on the Go... button.
Now check there's a tick next to PowerPivot!
Here the PowerPivot add-in is enabled.
Running PowerPivot
From a new workbook, you can load PowerPivot as follows:
Click on the PowerPivot tab of the Excel ribbon (it's usually further over to the right than this), then click on Manage.
You can now import data from SQL Server (for example) by clicking here:
Choosing to import from SQL Server.
Virtually every part of PowerPivot is now exactly the same as Analysis Services. For example:
The dialog boxes may looks slightly prettier in PowerPivot, but they do exactly the same thing.
Creating data models
The only real difference when creating data models is that there are more tools and ribbons and less menu options:
A typical example of how PowerPivot is easier and more fun to use - you get nice big buttons to move between data and diagram views.
For this blog I've imported the animal, product, species and transaction tables to get the following model:
This looks much nicer in Excel 2013 than it does in (as here) Excel 2016!
Creating pivot tables from PowerPivot
Just like in SSAS Tabular, you can create pivot tables based on your data model with virtually a single click:
How to create a pivot table based on your model.
The style of pivot table should look familiar!
Because I've hidden the other tables and columns from client view, this is all we see.
This sums up how PowerPivot is basically the same program as Analysis Services, but with prettier ribbons. What I'll now do is show the (relatively few) differences between the two applications.