skip to Main Content

Power Pivot to SSAS Tabular in less than 30 minutes

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!

Read the Rest