How PowerPivot differs from SSAS Tabular, and using it to prototype data models
Part four 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
  4. Importing PowerPivot data models into SSAS Tabular (this blog)

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.

Importing PowerPivot data models into SSAS Tabular

So you've created a beautiful data model in PowerPivot.  How could you import this into the cumbersome beast that is Analysis Services?  And why would you want to do this anyway?

Reasons to import PowerPivot data models into SSAS Tabular

Creating a data model in PowerPivot is great, but it does leave you with some problems:

Problem Notes
Sharing The Excel workbook that you've created contains all of your tables, so the only practical way to share your model is to email the workbook to someone.
Security It isn't possible in PowerPivot to control who sees which bits of your model (either by setting security roles or by creating perspectives).

The obvious path is therefore to prototype your data model in PowerPivot, get business users to agree that it looks good, and then upload it into SSAS Tabular for all to share.

Importing a PowerPivot model into Analysis Services Tabular

To create a project in SSAS based on an Excel workbook containing a PowerPivot data model, first choose to create a new project:

Creating a new project

Choose to create a new project in Analysis Services.

Choose the obvious option!

Import PowerPivot model

Choose to import your model from PowerPivot.

Find and double-click on the data model you want to import:

Choosing a data model

Choose the Excel workbook containing your data model.

Analysis Services imports the model:

Imported model

The PowerPivot data model created earlier in this blog.

The only two changes are:

  • Our implicit measures in PowerPivot have been turned into explicit ones in Analysis Services; and
  • The linked Excel table has been turned into a fixed table.

You can now test this model, then deploy it to your production server to allow other people to use it!

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