skip to Main Content

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:

http://powerpivotpro.com/wp-content/uploads/2010/11/excel2bievolution1.png

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 www.linkedin.com/in/avichalsingh

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 (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 Full

    Process 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=”http://schemas.microsoft.com/analysisservices/2003/engine”> <Type>ProcessFull</Type>
    <Object>
    <DatabaseID>UN_Population</DatabaseID>
    </Object>
    </Process>

    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!

  • Avi Singh

    Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI.

    This Post Has 38 Comments
    1. I was excited to see this post, but left wondering about measures (caculated fields) that I depend on and is the magic of power pivot for what I need it to do. What am I missing that keeps this possible?

        1. Your measures would import as is and work the same way as before. The magic should stay intact 🙂 In fact, as far as I know, all other settings are preserved. In the Model that I import, the Measure “Population” gets imported and works as before (you can see me use the measure in the imported SSAS model after minute 4:24 in the Video). The “Sort Column By” setting that I had on Column “AgeGrp” in “Population Table is also preserved upon import.

          1. Thanks Avichal for the video. I have to admit, seeing the “relationship chain” disappear in SSAS is a bit scary. Any novice would assume that your workbook relationships have disappeared too. I would guess that all relationships still exist in the PowerPivot model?

            1. Scott,

              The relationships are maintained in the SQL Server Data Tools Visual Studio project which is like your source code definition. You then deploy your project to SSAS and, I think you’re right, I don’t see the relationships showing in SSAS. However, you can go back into the project to see the relationships.

              When you point Excel to SSAS, you aren’t actually using Power Pivot, just a regular Pivot Table which is pointed to the SSAS database. So, you won’t see the relationships in your Excel file either.

              Tim

            2. Indeed, no relationships were harmed in the filming of the tutorial video 🙂 The relationships along with the your measures and all other settings (Sort By Column etc.) are all preserved as is. Once you migrate to Visual Studio, if you switch to Diagram View, you would see all your relationships visually. Or you can invoke the Table > Manage Relationships… menu item to see them as a list and be able to edit them.

              The “chain” symbol next to the Linked Table in Excel+PowerPivot just indicates that it is linked back to an Excel Sheet (Excel Table). Since that is not supported in SSAS Tabular, the data is essentially pasted in upon migration and the “chain” symbol next to the table name (“Location” in the example in Video) disappears. But this does not affect any relationship, which are retained as is.

    2. Nice post Rob!
      Just a note about Power View – today Power View for SharePoint can connect to Analysis Services Multidimensional (requires SSAS 2012 SP1 CU5 or later builds). In a (hopefully) near future, also Power View in Excel will be able to do the same.

      1. Good point Marco.

        To clarify to any readers…
        Power View in Excel: does have the ability to connect to SSAS Tabular Models; it does not seem to be able to connect to Multidimensional models.
        Power View on SharePoint: can connect to either.

        To connect Power View in Excel to an SSAS Tabular model, just follow: Data > From Other Sources > From Analysis Services > enter server information and select model > Click Finish > “Import Data” prompt > Select “Power View Report” (other options are Table, PivotTable Report, PivotChart, Only Create Connection”

        And there you go, you have a Power View Sheet in Excel connected to your SSAS Tabular model.

    3. Great article! It should probably be mentioned that SSAS Tabular requires SQL Server 2012 Business Intelligence version at a minimum; SQL Server 2012 Standard does not allow Tabular instances.

    4. Avichal,

      Thank you so much for this post. Although I have been using Power Pivot, I have always been intimidated by Analysis Services. This post gave me the courage to try it. I am surprised how much it looks and feels like Power Pivot. There is hardly any learning curve at all.

      I just downloaded the SQL Server 2012 Evaluation Version:
      http://www.microsoft.com/en-us/download/details.aspx?id=29066

      I tried both importing from a Power Pivot file and creating a model from scratch which feels exactly like creating a Power Pivot model in Excel.

      Now that your post has blasted me out of my intertia, I downloaded the book that you mentioned in a previous post and have been going through it:
      http://www.amazon.com/Microsoft-Server-2012-Analysis-Services/dp/0735658188/ref=sr_1_1?ie=UTF8&qid=1382717125&sr=8-1

      Analysis Services opens up a lot of possibilities, including the ability to refresh your data on some kind of interval which we currently do manually now in our Power Pivot files. Then the Excel files that generate off of the Analysis Services data can be set to auto refresh when they open. This gets us the functionality that we have been looking for without needing to install the complicated SharePoint piece. It also gives us a place to publish the “one version of the truth” in the event that we wind up in a situation of Excel file wild wild west.

      Tim

      1. Glad to hear that. I was pleasantly surprised myself when I first switched over to SSAS. SharePoint is indeed a bit of a bear to setup and maintain, but don’t discount it’s advantages though. Once setup the Power View reports that you (and possibly users) create, quickly become the focal point of the whole team. We still have the Excel option but that’s mostly for power users. Most business users, use Power View reports and are delighted by them!

        SharePoint would be a good portion to get some expert outside help for. See http://powerpivotpro.com/2013/06/the-ideal-powerpivot-professional-how-to-hire-grow-or-become-one

        1. You do need an Enterprise license of SQL though to install the SharePoint piece right? Currently we just have a Standard license.

    5. Avichal,

      I noticed that you can’t created multiple models within the same SQL Server Data Tools project. Now, ideally I would just create one database with all my Power Pivot models so users could just go to one place in order to get the model they are looking for. The problem is that I want the user to know which dimensions are valid for a particular fact table. But, I can’t create a separate model for each fact table. The best solution that I could come up with is to create a perspective for each fact table within my one model that has only the dimensions that are valid for that fact table. I’m not sure though if the whole model is loading into memory, even though only some of tables are being used in the perspective which could significantly impact performance.

      Do you have a better idea of how to approach this?

      Tim

      1. Excellent question Tim! First a small rant 🙂 Indeed Tabular Model development does not have some features seen in the MultiDimensional Cube Development, which would be nice to have. Watch this video How Do I: Defining and Deploying a Cube? under SSAS “How Do I?” Videos to get a flavor. Not only can you create multiple models within the same project but you just have to define your Dimensions once and reuse them in all your models. Nowhere do I find this more painful than for the Date dimension. Virtually any PowerPivot/Tabular model that I build would need a Date dimension, and for each one I have to recreate all the settings (Mark as Date Table and the multiple Sort by Column settings e.g. for Weekday, MonthName etc.). This would be especially priceless if you subscribe to the Data Warehouse Bus Architecture in Kimball methodology (See Data Warehouse Toolkit Chapter 3) where you build a common set of dimension tables that all fact tables connect to (Date, Product, Customer etc.). End of rant.

        Given the lack of the above, we have settled on pretty much the same approach as you outlined. We have a large single Model with multiple perspectives. When I am connecting to the model from excel, my favorite feature now is the “Show field(s) related to” in the PivotTable Field List. See the video in my post for more; around 4:24 mark I discuss analyzing the data in excel and around the 5:00 mark I speak about this feature. Essentially you can use this to select a “Fact” table and it would filter the display ONLY to the tables (Dimensions) that the table is related to. Given how large and complex our model has become this really helps me keep my sanity.

        Let’s talk about the memory issue. I am not sure creating multiple models would save you anything, in fact it may cost you extra. If you create multiple models and they are being used, they would all be loaded into memory anyway. By keeping a single model at least you may save on keeping just one copy of some of the Dimension tables (e.g. Date, Product, Customer, Geography and other such dimensions, multiple fact table may connect to just one of these). If you had multiple models, you would have to have multiple copies of these as well. SSAS can manage memory fairly well. Before we go any further, First Rule. TRY NOT TO RUN SQL SERVER ON THE SAME SERVER AS SSAS. Rule Corollary: If you have to run SQL Server and SSAS on the same server, make sure you limit the memory used by SQL Server. SQL Server is a memory hog and will grow till it consumes near everything.
        If you are concerned about memory and performance read

        DAX Formulas for PowerPivot Chapter 19 Performance.
        SSAS: BISM Tabular Model Chapter 9 (xVelocity) Chapter 18 (Optimization)

        Real quick, you can right click on SSAS instance in SQL Server Management Studio > Click Properties >Click General > Look for Memory\.. Settings. You would see settings like LowMemoryLimit, TotalMemoryLimit etc. If between 0 and 100 this indicates a percent. We have left these to default which is LowMemoryLimit=65% TotalMemoryLimit=80%. As memory usage reaches and exceeds these limits SSAS aggressively dumps stuff from memory to reduce memory usage. If there is too much of this then the server would always seem slow. But in our experience we have never had to worry about this as SSAS seems to do a good job of managing which items it keeps in memory, but your mileage may vary.

    6. Along with the change in the look of the PowerPivot field list in Excel when connected to a Tabular. You cannot create implicit calculations when connecting a pivot table to Tabular Model. This is problematic, when your business may want to count anything…. (field called TYPE with 100 different type values and I want a count for each type all as discrete column value for each in the same pivot table).

      Do you know a true work around? Create Explicit Calculations, Manually adding the Tabular data and creating a Explicit Calc, learning and writing MDX calculated measure form Analyze menu isn’t an option for these users.

      1. Good point, I had encountered that, but hadn’t quite run into a scenario where that was an issue.

        I hope I am understanding your problem correctly, I tried to create a sample data file with the information you provided. You may be able to solve it using disconnected slicers. See image below and the Excel Workbook.
        Click to Enlarge

        Here are the formulas used
        SelectedType:=IF(HASONEVALUE(‘Type'[Type]), FIRSTNONBLANK(‘Type'[Type],1), BLANK())
        CountValuesAll:=COUNTROWS(‘Values’)
        CountValuesByType:=CALCULATE([CountValuesAll], FILTER(‘Values’, ‘Values'[Type] = [SelectedType]))

      2. Our IT department has decided this SASS tabular is the way to go – and if you have a well defined, stable model and all you want to do is create powerviews, it would be great I guess.

        The tabular model in Excel with the pivot table is inferior in many ways. I not only lose counts, but distinct counts too – unless the IT guy who builds the tabular model adds measures for these for every field I could possilby every want. This makes the list very cluttered and difficult to find anything.

        Also – we rely a lot on metrics that are % or ratios and I don’t think those work – or I need IT to create all of them.

        My self-service ability is completely removed and I am back to going to a developer again. It stinks. Unless… I use DAX to pull in the tables from the tabular model to power pivot, build it again, and then do what I need to do.

        These are not adviseable for every situation – but I see can be great for large models where they work.

        1. Business Intelligence belongs to the business users, not with IT. IT should be an enabler but often holds the reigns of BI – not the right approach. Rob wrote about the “Two Trends” in BI and the powerful one is the ground up movement by the Business/Excel users. Things are changing, but slowly.

    7. Any insight on how or when we will be able to create an ssas model based on a powerpivot model with power query connections? Power query seems like the perfect (if not first, outside access applications) business etl tool, if only more integrated with ssas deployment somehow! Outside of ssis development I’m wondering what my options are or if Microsoft plans on anything on this front.
      Thanks
      Kellan

      1. Clarifying comment that the next Power Update release is fixing this according to cd but mainly talking about SSDT deployment.

    8. I have an Excel/Powerpivot file with a powerview report (several pages), and i’ve followed this tutorial to export Powerpivot data to SSAS tabular model. Can i alter the source in the Excel/Powerpivot file and naturally maintain the powerview report?, or do i have to recreate the reports in a new file, with a connection to SSAS tabular model?

      Regards

    9. I have a model that has a linked table with configuration parameters that affects to the next calculations. I usually write the parameters in it, update the linked table, and observe the new results in my reports. It is like a command board.

      I am thinking to pass my model to SSDT. I am worried to lost this ability.
      ¿Can I override the data of my parameter table from Excel to SSDT by mean VBA?

      1. How about keeping the linked table in a separate Excel file, then connecting to the file from Power Pivot (Get External Data > From Other Sources > Excel), that way if it’s ported to SSAS it would still work. You can simply update the Excel file and when model refreshes (you can also refresh one table at a time) the value (parameter) would be updated.

    10. Avi, following on from Kellan’s question re Power Query, if you set up a Power Pivot Model containing data pulled using Power Query and then publish this to SSAS Tabular will the connections still work and can they be edited in the SSAS Tabular environment?

    11. Avi – Thanks for ALL the support! Quick question, did you get a chance to see Ricardo’s quesrion yet. Or simply, when I connect my powerpivot Excel to Tabluar model on SSAS, do I have to recreate them reports? I already have all the reports built using the existing powerpivot Excel datamodel which I just migrated to SSAS. Is there a way to just change the source to point from Excel Data Model to the SSAS Tabular model? Thanks!

    12. Hi Avi – Same question as KarthiP – I have about 20 workbooks which are very complex, I can easily convert the PowerPivot models to SSAS using Visual Studio, but then I’m stuck with recreating the workbooks to use the new data source?? Is there a tool that does this?? Thanks!

      1. Karthi, I clearly couldn’t help you in time. Jim I am hoping I am more of a help to you. Hmmm…first I figured you could just change the “Connection” in Excel or change the Connection Properties for the involved Pivot Tables. But when I tried it, they are hard coded to “ThisWorkBookDataModel” (in Excel 2013) and cannot be changed. It may be possible to switch the connection using VBA. I would create another PivotTable in the same file which connects to SSAS. And then try using VBA to cycle through the old pivots (which connected to the Excel-PowerPivot data model) and see if I can switch them using VBA. If that still does not work, try asking Scott Senkeresty – he’s usually a whiz with hacking PowerPivot.

    13. Hi,

      The data refresh is working fine in my test environment ,But in production it is a new server and i had created a tabular model and roles and deployed to same share point ,When i try to refresh within the browser ,i am getting the below error but if i download to local desktop and refresh it works fine ,the roles also works fine,at the same time if i schedule a data refresh ,this also works fine,I am not sure why it is not working when opening in browser

      ‘An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh:’

    14. Hi Avi,

      We are using power pivot reports which are created using DAX language from SSAS 2012 tabular model.

      After the reports are created we are uploading the power pivot reports to sharepoint 2013. The reports are getting deployed successfully and

      also i can see the data but the thumbnails for the reports are not appearing as i am getting red cross in a white circle.

      We don’t know what is the issue as clearly sometime back it was working fine suddenly it stopped working.

      Kindly suggest.

      Regards,
      Nitin

    Leave a Comment or Question