skip to Main Content

By Avi Singh [Twitter]

Part I in Series. Part II >>Power Pivot to SSAS Tabular: Automatically Publish Excel Workbooks

A key milestone in my Power Pivot journey (read or watch) was when I let my baby go. I uploaded my beautiful Power Pivot model on our server and now it was no longer just mine. Many others could connect to it and create reports that they wanted, using the tools that they liked (Excel, Power View, SSRS etc.). I hadn’t quite realized the power of what I had unleashed. Read on or watch the video below for a fresh take on this, but also check out my earlier post – Migrating From Power Pivot to Analysis Services Tabular Model.


YouTube Video

Server Option: Separate Model and Reports

The best advantage, in my opinion, of going the server route is the ability to separate the model from the reports (many other advantages, but I’ll focus on this one for now). Excel Power Pivot models, typically start around 10 MB and go up from there – 50MB, 100MB, 1GB…

Without the server option, your Excel file would include – The Power Pivot model + Any and every report that you (ever) need. This ends up fairly chaotic very soon, with lots and lots of sheets with different pivot tables, Power View sheets etc. The file becomes hard to share, use or update.

Excel Power Pivot file gets bloated as the model grows and you keep adding more “Reports” within the same fileExcel Power Pivot file gets bloated as the model grows and you keep adding more “Reports” within the same file

The way out (or up!) is easy. You have a few server options available

· SharePoint

· SSAS (SQL Server Analysis Services) Tabular

· Power BI*

With the server option it would be possible to upload your model and build separate connected reports. The Core and Thin framework introduced earlier.

 Server option lets you separate the model (Core) from the Reports, which can now be really Thin!
Server option lets you separate the Model from the Reports
(Reports can now be really thin and created using your tool of choice
– Excel, Power View, SSRS etc.)

* Power BI v1 and v2 (in Preview) offer different functionality than SharePoint or SSAS.  V1 is limited but V2 offers reports and dashboards connected to your model. At this point, neither lets you create thin Excel workbooks connected to a Power BI model in cloud.

The freedom you get from this will be dizzying at first. Now you, or really anyone in your team can create the needed reports connected to the single model, using the tools they like – Excel, Power View, SSRS etc. So you still retain the single source of truth but allow for flexibility and customization of reports per user needs.

SSAS Tabular – no programming needed!

Of the server options available, SharePoint is what usually comes to mind. But let me highlight the SSAS Tabular option for you, the option that folks often overlook.

Easy Setup: SSAS Tabular is well within the reach of a typical Excel user, whereas SharePoint for Power Pivot can be a bear to set up, and typically needs heavy IT involvement.

SSAS Tabular however, could be set up by the business group themselves, as long as they have a machine where they can run it on. In my last role at Microsoft, for a long time this machine was my desktop, and it worked great (we did upgrade to a server eventually).

In the accompanying video you can see it takes me less than 30 minutes to setup SSAS tabular, upload my Excel workbook and start creating thin reports connected to my model.

Directly Upload your Excel Workbooks (no programming, no Visual Studio): You do not need to use Visual Studio or have any programming skills. All it takes is a right click to start importing your Excel Power Pivot model into SSAS Tabular. Besides filling out your filename etc. it takes exactly two mouse clicks!


Click image to view enlarged or Click to Watch the same steps in Youtube Video

SSAS Tabular – Automatically Publish Excel Workbooks

Onetime upload is fine, but then you face a choice as to how to continue developing your model. You can:-

a) Switch to using Visual Studio as your development environment (this article has some guidelines)

b) Or continue using Excel to develop Power Pivot Models and then publish them to SSAS Tabular.

For option b), ideally the “publish” should be automated and can be done using Power Update or other mechanisms. I will cover this in more detail in my next blog post. Subscribe to our blog to make sure you do not miss any updates from us.

Next Blog Post in Series >> Automatically Publish Excel Workbooks to SSAS

Power On!
Avi Singh

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 44 Comments
  1. Awesome post Avi. Your post from October 2013 got me into this whole Data Warehouse thing which I used to be intimidated by.

    You inspired me to do the SSAS Tabular installation and read the Kimball book. Now I’m still stuck trying to convince management to buy the Business Intelligence license for SQL Server.

    Question, are you still using perspectives when there are multiple fact tables involved like you mentioned in this comment?
    http://powerpivotpro.com/2013/10/guest-post-migrating-from-power-pivot-to-analysis-services-tabular-model/#comment-12293

    1. On perspectives…Not so much as multiple fact(data) tables, but multiple data sets. One model is far more useful than multiple smaller models. And that is where SSAS Tabular allows you to go. And creating Perspectives makes things a little easier to use and maintain.

      e.g. If you have Sales and Budget Tables, they are both fact (data) tables. But I would not create separate perspectives for each.

      At Microsoft Learning, we had four lines of business – Courseware, Certification, eLearning, Microsoft Press (Books). I created Perspectives for each since users typically were only interested in one dataset (each dataset would have more than one fact table and many Lookup/Dimension tables). But of course the whole Model is also available for any reporting, where you can add elements from any of those to show correlation and patterns.

      Bringing change anywhere is hard, even if it’s for everyone’s good. Keep up the good work and consider all your options (besides SSAS Tabular) : SharePoint (3rd party hosting is an easy way to do this without heavy lifting. Portalfront.com is one we have used), Power BI (especially given the recent changes), Excel combined with Power Update

  2. Fantastic insight!

    I used to think along the lines development first for PowerPivot (self serve BI), then grow upwards to SharePoint (team BI), and then finally cross the bridge to SSAS, tabular (enterprise BI).

    What this blog says to me is that one can jump from PowerPivot to SSAS, tabular with relative ease, and teams and the enterprise will benefit.

    Interesting!

    1. Folks often think of SharePoint first. It just has more mindshare. Where it works SSAS Tabular is a great option. With the release of Power BI SSAS Connector, it should play along nicely with the new Power BI Cloud service as well (that had been a sore point earlier).

  3. Avi,

    Great video! I am not that familiar with SSAS, so please excuse this basic question. After I upload my power pivot model, how do I handle future changes (new measures, additional data). Are these changes made in SSAS?

    Thank you,
    Brent

    1. Hey Brent, I plan to elaborate more on that in my next blog post in this series (trying to keep it bite sized). But the last paragraph talks about some of this. The answer is different depending on which change you are speaking of – Model changes (e.g. New Measures, Pulling in New Tables) or Data Refresh.

      Data Refresh: You can pull off easily in SSAS Tabular (with some caveats I will cover in the my next blog post). See Model Refresh in my previous SSAS Article for some hints.

      Model Changes (e.g. New Measures, Pulling in New Tables): To handle future changes to the model you can:-

      a) Switch to using Visual Studio as your development environment (this article has some guidelines)

      b) Or continue using Excel to develop Power Pivot Models and then publish them to SSAS Tabular

      With a) Essentially you stop using Excel as Authoring environment and use Visual Studio instead. You will still use Excel to debug/test model as you develop and of course as a reporting tool against the published model.

      With b) You continue using Excel. I feel, this is best combined with some means to automatically publish the Excel workbook. Power Update is a clear option. There are others as well, look for my next article where I will cover that.

  4. I feel this is a great example to call Power BI a self service BI, and it gives a quick and sound solution for the BI people rather than IT to build a tabular model from excel, and it unleashes other business user to analyze the organized data. Thank you for the great post!

    1. Yes! It is about time we put the “Business” back in Business Intelligence. Trend over the past year had been BI increasingly became this arcane and heavy machinery that only IT groups could operate. PowerPivot/Power BI puts the power back in the hands of the Business User. IT still has an important place in the new BI landscape, but more as an enabler/partner ; rather than holding the keys to the BI kingdom. You would find this an interesting read: Self-Serve BI Tool Scorecard: PowerPivot vs. the Others, and Why Not All Checkboxes Are Equal

      1. For what its worth, +1 for provoking business users with a “we’re mad as hell and not going to take it any more” message 🙂 Powerful. Sticky. And true.

        Also, excellent guidance on how the Excel pro can graduate to to SSAS Tabular. Spent the better part of today laying down a new Windows 8.1 operating system, Downloading eval SQL Server, SSDT….trying to get things up and running.

        Far cry from Vlookup’s but completely doable for the Excel person who sets their sites on it.

    1. Doc, this might be over my head. But if you paste the exact error message and link to a screenshot (you can upload that on onedrive/dropbox/…) maybe one of us can offer some help. I googled “Compressed Stream Errors SSAS” and came across this MSDN article, which may be of help. It suggests
      > Try changing Network\Responses\EnableCompression to 0 and bounce the SSAS service

        1. The only verdict I can tell is… Power Pivot is certainly not ready for prime time. I have been grinding with this software since I left Collie’s University… following every rule… the product is not viable.

          1. We have over 50 designs on SharePoint. I applied this technique to only one file. It is the only file that throws a compressed stream error at me and is all but dead in the water. We have called in the Microsoft dudes to check it out. The cubes don’t appear to be loading into the cache at all… I am guessing that the files are executing queries against the data with every slice. If I click every single slicer selection in a specific view, and then disconnect from the network entirely, shouldn’t I still be able to “refire” any of those slicer and the data should already be available in RAM?

          2. Perhaps the “Furious Spreadsheeter” manifested in that last post. It’s a good thing you can’t throw digital software.

      1. I spoke with Troy Ault at Microsoft. This method is not recommended because it “creates different hooks” than if you had deployed your cube through Visual Studio. He asked me to deploy it the simple way, and i explained that the Avi Method was as simple as it gets… Drag and Drop a File and then Click a Button. I can’t even get into Visual Studio with less effort than that. He is going to dig deeper and try to explain just exactly what is happening underneath the hood that may cause issues.

  5. Hi Avi!

    I’m trying to use Visual Studio to import data from excel file but stuck with an error:

    The ‘Microsoft.Mashup.OleDb.1’ provider is not registered on the local machine.

    My file contains PowerQuery connections. Any ideas?

    1. Is there any plans for microsoft to address this gap?? It seems very unfortunate that you cannot create the SSAS model if you use Power Query!

  6. Hi Avi,

    I’m quite new to SSAS and I am an independent consultant providing management accounting services to small business and wanted to leverage of PowerPivot and possibly SSAS to deliver financial reports online. I am currently only using Excel to produce PowerPivot models. I do not current have access to any sql server infrastructure. Is there a way for me to access\setup SSAS on my local PC?

    Please excuse me if this comes accross as a very newbie question

    Kind regards

    Manish

    1. Manish, for SSAS starting out with a SQL Server Developer Edition would be the least cost option (unless you have access to it via MSDN Subscription or the likes). The Developer edition is good for trying it out. For rolling it out in production you would need to upgrade to a different edition. Here are some links which may be of help. You should also try PowerBI.com cloud service, which is free for upto 1GB.

      SQL Server Editions
      SQL Server Developer Center
      Info on Breadth Editions: Developer and Express
      Get cheap Microsoft Software, the legal way

  7. Hi, I was about to publish me model into SSAS too.., on SQL2014. SSAS won’t process any powerpivot table with calculated columns. Fun thing, I tried old old powerpivot files that also have calculated columns and it works… until I add a new calculated column. Then it crashes. Anyone ever noticed this?

  8. How do you connect to your SSAS tabular model in Excel Services? I was able to create the connection in excel, just like the video shows and it works fine on my local drive. However, once the workbook is saved in SharePoint when I access it in excel services and make a slicer selection I get a data refresh error. Anyone have a solution to this? I am using SharePoint 2013. Thanks!

      1. Hi Avi,

        Thanks for you quick reply. I have created the BISM connection file. Power View now connects as expected but Excel Services reports created using this connection still return a refresh error. When I run the SQL tracer the log shows that ‘Anonymous User’ is accessing the file. When I use Power View it passes through my effective user name. Do I need to grant Excel Services access to the SSAS cube? It seems like the authentication process is different between Power View and Excel services. We are not using Kerberos for authentication.

        Thanks Again for your help!

  9. Hi Avi,
    I have brought my model to SSAS and can work with it fine. But how can I pivot en refresh online? Excel services of a sheet with the option powerBI enabled is not refreshing online. Is it only possible to refresh sheets in excel en via PowerBI 2.0?

    1. Hi Menno, I did the same as you a few weeks ago and here are my conclusions:
      1- ssas won’t refresh with powerbi (office 365)
      2- ssas will refresh with powerbi.com but you can’t use regular excel sheets yet. I asked question to people at Microsoft and they told me that this option should be available sometime in 2015.

      This is what I did meanwhile: click the option to refresh the excel file (the one connected to ssas) on opening.
      Publish these files on SharePoint (office365)
      Obviously, the refresh option only work if the computer is connected on the work network.

  10. Hi Avi,
    Thank you for the excellent and helpful articles!! We’re in the process of implementing SSAS Tabular as a semantic layer and are pondering self-service tool options for the day-to-day ad hoc user.

    Excel has nice functionality when connected to SSAS Tabular models, but I’ve read that it passes MDX instead of DAX under the covers and that negates the advantages of the tabular engine. Power View and Power BI Preview are options but seem less robust than Excel.

    What’s your recommendation for this use case?

    Thanks very much,
    Vince

    1. It may pass MDX instead of DAX, but the statement that “it negates the advantages of the tabular engine” is not accurate. You do not lose any advantages of having a PowerPivot/Tabular model. We transitioned our Excel PowerPivot model to SSAS Tabular at Microsoft and it only went from good to great.

      Excel is a great tool, but don’t feel like you need to stick to just one UI tool. That is the best part about Power Pivot/SSAS Tabular you can connect numerous visualization engines to it. Watch recording of our last webinar where I speak about this. Read one of Rob’s posts about Visualization: http://powerpivotpro.com/2014/11/visualization-layers-in-perspective-the-last-mile/

      1. Vince, I dove deeper into this. When Excel is talking to a PowerPivot/Tabular Model it is actually always speaking in MDX. I ran a trace on Excel PowerPivot model and an Excel file connecting to an SSAS Tabular server. In both cases, I saw an MDX query being issued (in fact the queries were identical).

        However, when I use Power View in Excel it issues DAX Queries. So PowerPivot/SSAS Tabular are essentially the same engine, and it understands DAX as well as MDX. It is upto the client connecting to it whether to use DAX or MDX. Excel PivotTables/Charts always seem to use MDX. But that’s the same for PowerPivot within Excel or SSAS Tabular. So there should not be any difference/drawback in that regard for switching to SSAS Tabular. I’ll see if I can do a future posts with screenshots to clarify this whole thing for others as well.

  11. Hi Avi,

    I was finally able to get SSAS installed on a remote server. I connected, but I am a bit confused on the Restore Database name and location. Is that the name of my database my power pivot data model is connected to? I know the name, but i’ll have to check with the folder location with our enterprise IT folks.

    Thanks!

    1. I should note that my power pivot data model is connected to a SQL server database. I also use Power Query with my calendar table (dimDate). After reading, I am wondering if I will need to install Management studios on my remote server and upload my file from there. Thanks

  12. Hi Avi,
    I have created a data model using Excel Power Query and Excel Power pivot. There are 5 tables. Then Restored to SSAS Tabular. Then I connected to the SSAS Tabular using the Data Connections >> From Other Sources >> From Analysis Services and pulled the Model. I was able to re-create the report I wanted and no issues up until that point. Now when I go to Power Pivot Manage option I was not able to see all the tables. I see only 3 tables. How do I get all the tables? The reason behind to get all the tables is if the end user wants to create additional KPI’s or measures they could do it without seeking the help of IT. If this is the expected behavior then what will be the best solution in your experience to create additional KPI’s or measures.
    Thanks!!

      1. I removed the table created using power query and re-created by using powerpivot external datasources. Then restored to SSAS Tabular again. Then re-created the report from Analysis Service. Then opened the Power Pivot >> Manage. I see only my dimension table but not the actual fact table with data in it. Do you know a way to bring back the fact tables back.

          1. We were never able to use this technique. Start in SSAS and be done or deploy the PP using Visual Studio were our only viable options. The half baked temporary cube that gets created in the PPGallery in SharePoint is not an actual deployed cube at all.

  13. Does migrating a PP model to SSAS help with performance issues? At my work, we are not permitted to install Office 64-bit, and as my model and calculations in PP have become more complex, I’ve found Excel to be increasingly slow and unstable (to the point of being nearly unusable). Will SSAS address help address these issues, or will I still primarily be limited by the memory and Office type of my local machine?

    1. SSAS will solve this issue as Excel only connect to the model, which resides on a server. SSAS is only available in high-end version of SQL though. BI and enterprise. We installed SQL BI, the price was around 80,000$.

  14. Hi Avi, this post is fantastic for people who works on large data sets. I have encountered few issues while working with them.

    Using Visual Studio
    After importing the existing Powerpivot model into Visual Studio, I can analyze the model by “Analyze in Excel” button / option. It creates a temporarily connection between SSAS tabular database and Excel. But when i close the Visual Studio Project, temporarily connection was gone and my excel file become static. Now if i have change or modify anything i have to reopen the same Visual Studio project.

    How can i change / modify it without open the Visual Studio project?

    Using Sql Server Management Studio
    I have SQL Server Analysis Services loaded in a personal laptop.
    Creating Pivot Reports in Excel by using Data from Analysis Services. when i share them through mail / network drive, those reports become static. Other users are not able to filter or modify the view. This happen because their system isn’t connected with my SSAS database.

    How can i make it Dynamic report (Filter / Change view) for the other users? How can map my system database with their system?

    How can I also do it through Visual Studio?

Leave a Comment or Question