Intro from Rob

At a conference three years ago, I saw my former colleagues at Microsoft announce something very significant:  Power Pivot was getting a bigger brother.  Power Pivot’s formula language (DAX) and the overall “tables/relationships/calc columns/measures” modeling experience was going to be “ported” into Microsoft’s flagship, industrial-strength BI platform – SQL Server Analysis Services (SSAS).

At the time, that led me to post the following diagram:

Power Pivot Broke the First Wall – and We’re Still Getting Accustomed to Our New Powers.
But Power Pivot’s Bigger Brother (SSAS Tabular) Breaks a Future Wall Too.

Years have passed and that has already become the new normal.  Microsoft rolled out Power View and had it only work against Power Pivot / Tabular (and not against traditional SSAS) – a decision they later had to dilute, but still a clear signal of their future intent.  BI luminaries like Chris Webb and the Italians wrote a book on Tabular.  As revolutions go, this one was quite non-eventful.

Not that Excel Pros MUST move upstream and start using SSAS.  Quite the contrary – I still personally have not!  But it is VERY good to know that it’s there, for several reasons:

  1. Microsoft is betting heavily on “the Power Pivot way.”  You don’t “infect” your flagship product with something new unless that new thing is awesome.  Power Pivot – that thing running on your desktop – is good enough for the heavyweight BI pros.  Digest that thought.
  2. There’s an “upgrade path” for important Power Pivot models.  This is a great selling point for IT if they are nervous about Power Pivot.  Unlike regular Excel workbooks, a Power Pivot workbook that becomes business critical CAN be “taken over” by IT, and made into something centralized and blessed, without having to rewrite it.
  3. There’s an “upgrade path” for Excel Pros.  With very little effort, an established Power Pivot pro can “change hats” and label herself a Business Intelligence Pro, a Tabular Modeler – even if she were “just” an Excel Pro a couple years ago.  Again, not that she has to, because Power Pivot itself offers practically limitless power.  She just can.  Exciting huh?

Avichal Singh is one such Excel Pro who has been making that transition.  He’s been so kind as to write up his experiences so far, and MAKE US A VIDEO SHOWING US WHAT THAT LOOKS LIKE!  (Holy cow I had not realized there was a video in this post until just this moment.  I am Jack’s Awestruck Amazement.)

Take it Away, Avichal…

By Avichal Singh

This is a follow up to my original post – My Power Pivot Journey. In this post I would like to detail the migration to Analysis Services and its implications we experienced. For general comparison of Analysis Services versus PowerPivot read Comparing Analysis Services and PowerPivot (look for table under Analysis Services Offerings). Our main reason for switching to Analysis Services was scalability. We were reaching 100MB with Excel+PowerPivot and after migration to SSAS Tabular our database has grown to 700+MB with 25+ Million rows overall.

My (Non) Background as Developer

If you are like me, you might have opened up Visual Studio in the past in a misguided attempt to develop something or opened up someone else’s project and felt somewhat lost. Do not let that fear stop you.

Developing SSAS Tabular Model in Visual Studio is not that much different than doing it in Excel PowerPivot. Authoring your model will feel surprisingly similar, but you will find more changes around deploying and using your model.

In the video below and the rest of the article I will show you, exactly – how to migrate, what changes to expect and how to use the new interface.

PowerPivot to SSAS Tabular: Migration and other tips

Acronyms I use:-
SSDT = SQL Server Data Tools
SSMS = SQL Server Management Studio
SSAS = SQL Server Analysis Services

Migrating PowerPivot to SSAS

I am using the following:-

– Excel 2010 with PowerPivot add in (information, download)

– SQL Server Data Tools for Visual Studio 2010 (information, download).

– SQL Server Analysis Server in Tabular Mode ([link removed due to 404] information, TechNet: Install Analysis Services in Tabular Mode). You would need:-
  Workspace Server: SSAS Server to host the data as you author the model
  Deployment Server: SSAS Server to deploy the model to, for use by all users
They can be the same server.

I would highly recommended that you perform the migration on your SSAS Tabular Workspace Server (in other words, it is a local server, as opposed to a remote server), else it would only import workbook metadata (See TechNet: Import from PowerPivot)

To perform the migration, in SSDT, go to File > New Project > select ‘Import from PowerPivot’ (found under Business Intelligence > Analysis Services). You would then be prompted to select the PowerPivot Excel file that you want to import.

What to expect when you are migrating

  1. New Interface
  2. Saving your model is much faster with smaller files
  3. No linked tables
  4. Connecting via Excel, No PowerPivot Field List
  5. Deploying Your Model
  6. Security and Access
  7. Model Refresh
  8. Enabling SharePoint Power View Reporting

1. New Interface: A picture is worth a thousand words! Click to open PPT file which maps each PowerPivot interface element to it’s counterpart in SSDT Visual Studio.

PowerPivot to SSAS Tabular Interface Changes Cheat Sheet

2. Saving your model is much faster with smaller files

If your model is large (ours were approaching 100MB when we switched), saving them starts to become a pain. With Excel+PowerPivot the data is stored within the Excel file (See cool PowerPivot FAQ factoid) and a File > Save is saving the model definition and data.

With SSAS Tabular the model definition is disconnected from model data: model definition lives in your project files whereas the model data resides on the Workspace Server (an .abf file keeps a backup of data, but you can ignore that file for most purposes). This makes saving your project a snap and makes it easier to keep your model definition under Source Control if you choose to do so.

3. No linked tables: Tabular model does not support linked tables. If you have them in your PowerPivot model that you migrate to SSAS, these would end up as Pasted tables. Which I find quite unsavory and hard to use/update. PowerPivot or Tabular, you’re probably better off staying away from both Linked and Pasted Tables and powering your model from a Database Server (see Why PowerPivot is Better Fed From a Database).

4. Connecting via Excel, No PowerPivot Field List

While developing, from SSDT you would click ‘Analyze in Excel’ to connect to the working model in Excel.

 SSDT Analyze in Excel

Once deployed, you can connect to your tabular model from Excel by going to Data > From Other Sources > From Analysis Services.

Excel Data from Analysis Services

In either of these cases, what you would see is the classic PivotTable Field List and not the PowerPivot Field List. I loved the PowerPivot Field List, but life goes on without it Disappointed smile

  • Remember, you can still use Slicers. It would just take a few more clicks to insert them, and you would need to position them manually. Select your PivotTable and click Insert > Slicer.
  • Use the ‘Show fields related to:’ selection to get some sanity back. PowerPivot Field List was nice that it organized the elements more cleanly. However, the ‘Show fields related to:’ works really well in filtering the classic PivotTable Field List to only items relevant to your selection. Comes in handy if you are working with a large model with many distinct datasets.

    PivotTable Field List OUT PivotTable Field List IN

    5. Deploying Your Model: In SSDT, Open Solution Explorer > Right Click your Model > click Deploy (Open Properties to change the Deployment Server)

    Deploying your Model

    6. Security and Access

    This part is admittedly no fun, but important nonetheless. If you can get help from an ‘Admin’ it may make things a bit smoother. See TechNet: Connect to a Tabular Model Database (SSAS) for more information.

    Find a cheat sheet below and do make sure Firewall rules allow Analysis Services traffic (see TechNet article).

    Set up permissions so… Easiest way
    Model can refresh on your SSAS Deployment Server Make sure the account under which SSAS is running has access to the “Data Connections” in your model*
    Users can connect to the SSAS Tabular Model using Excel Create a ReadOnly role and add users as members to the role (I tend to do it on the SSMS Server. You can also do it in SSDT)
    Creating ReadOnly Role
    SharePoint can connect to SSAS (to build Power View Reports) You must grant the Service Account being used by SharePoint membership to the server administrator role in Analysis Services. Check with your SharePoint Administrator for the specific account.

    * Note: when you process data within SSDT you are likely doing so with your own user credentials. However when you attempt to process it on the server, it would use the credentials the SSAS server is running as. So make sure that account has access to your source data as well.

    7. Model Refresh

    You can process data within SSDT but remember this is only your working copy.

    Process Model

    Your main focus would be to keep the deployed version up to date. Your options are:-

  • Refresh model manually using SSMS. Connect to SSAS Tabular using SQL Server Management Studio. Right Click Database > Process Database > Select Process FullProcess Database
  • (Preferred!) Schedule an automatic refresh. Many ways to do this, simplest and the one we use is to create a SQL Server Agent Job with a step as below.
    Type = SQL Server Analysis Services Command
    Command = sample below, change based on your DatabaseID*

    <Process xmlns=””> <Type>ProcessFull</Type>

    SQL Server Analysis Services Command

    * Note: One serious gotcha here, the ‘DatabaseID’ you need in this command is usually same as the Database Name nut not always. It’s best to double check by right clicking your Tabular Database and clicking Properties.

    8. Enabling SharePoint Power View Reporting

    Make sure you have followed the steps under ‘Security and Access’ above. To create Power View Reports on your SharePoint site connected to your SSAS Tabular Model you would need to Create a BI Semantic Model Connection to a Tabular Model Database. Once created, it behaves similar to an Excel+PowerPivot file and you can click on the Power View icon to create a new Power View Report.

    • Create Power View Report

    So there you have it folks!