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.
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 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 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