How PowerPivot differs from SSAS Tabular, and using it to prototype data models
Part two 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 (this blog)
  3. The 3 main differences between PowerPivot and SSAS
  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.

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:

Excel Options

Choose this option from the File menu in Excel.

 

 

Now go to the Add-ins tab, and choose Com Add-ins:

COM Add-ins

Choose COM Add-ins, then click on the Go... button.

Now check there's a tick next to PowerPivot!

PowerPivot add-in

Here the PowerPivot add-in is enabled.

 

Running PowerPivot

From a new workbook, you can load PowerPivot as follows:

Managing PowerPivot

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:

From SQL Server

Choosing to import from SQL Server.

Virtually every part of PowerPivot is now exactly the same as Analysis Services.  For example:

Importing data in PowerPivot

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:

Diagram view

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:

Data diagram

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:

Creating pivot tables

How to create a pivot table based on your model.

The style of pivot table should look familiar!

Pivot table fields

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.

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