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.
The way out (or up!) is easy. You have a few server options available
· 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.
* 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.