Taking #PowerPivot to the Next Level

Power Pivot is an amazing, flexible and powerful business intelligence tool (among other things) and there is no doubt about that fact. As a feature included with Excel 2013 and 2016 (and an add-on for Excel 2010), Power Pivot allows user with a little technical expertise to integrate disparate data source together within a flexible data model. Once the data is loaded into Power Pivot, we easily have the ability to create powerful calculated measures, key performance indicators and user-defined hierarchies all within one of the most widely used business tools ever: Excel. With all those things in mind, its no wonder that Power Pivot is used by many users, teams and organizations across the globe.

Interesting in learning more about Power Pivot? Then watch this free webinar.

If you want to skip all the details and want the down and dirty summary, scroll to the bottom. 🙂

But, but, but….what’s wrong with Power Pivot?

The short answer is not much is “wrong” with Power Pivot because its a very powerful tool. In my opinion, Power Pivot has so much power and ability right out of the box its kind of ridiculous. But even with all its usability and flexibility, eventually you’ll come to the point where you may recognize that it’s time to take your Power Pivot workbook to the next level.

You may decide to do this for a few different reasons. First, Power Pivot workbooks created within Excel on your local computer utilize your local computer’s resources which may not be adequate. Secondly, sharing Power Pivot workbooks among team members can be problematic as the workbook size increases and as more team members wish to use the solution. Also, you may desire to implement a more granular level of security outside of limiting who can access the workbook. There are other considerations that may lead you to find your Power Pivot solution lacking, but these are three of the most common reasons.

Thankfully, there are several options to choose from in order to take your Power Pivot to the next level! And that’s the main point of this blog post: To make you aware of the three most straightforward migration patterns you may be considering.

Deploy the Power Pivot Workbook to Share Point

If your organization has purchased Share Point, probably the simplest way to start making your Power Pivot model available to your organization is to deploy your Power Pivot workbook to Share Point. Deploying the Power Pivot workbook to Share Point is as easy as clicking File, Save As and then navigating to the location in your SharePoint site.

By deploying your Power Pivot workbook to your Share Point site, your workbook will become accessible to authorized users with security being managed through the Share Point security infrastructure. Also, your Power Pivot workbook will also begin leveraging server resources for processing and querying. You’ll be able to schedule data refreshes so that you Power Pivot model will be automatically updated on the schedule you specify.

With Power Pivot on Share Point, you can share your Power Pivot model for others to query. Now its worth mentioning that Power Pivot workbooks on Share Point are limited to a 2 GB document size but because of the excellent compression of the in-memory storage engine, you’ll be able to fit more than 2 GB of data in a Power Pivot model.

Once your Power Pivot model is deployed to Share Point, users will be able to:

Create Power View reports in the web browser in Share Point
Export interactive Power View reports to Power Point
View pivot tables/charts included in the workbook using Excel Services
Create SQL Services Reporting Services reports & Performance Point dashboards with the Power Pivot model as a data source
Schedule data refreshes

But because of the workbook size limitation and lack of other enterprise level data model features such as row level, role-based security and incremental processing, you’ll more commonly find Power Pivot for Share Point used in smaller team business intelligence scenarios compared to enterprise scenarios.

Convert the Power Pivot Workbook to a Power BI Semantic Model

If your organization does not have Share Point in play but is an Office 365 customer (read here to learn which Office 365 business plans support Power BI), Power BI may be an excellent option. With the latest update to Power BI, you can now easily step through a wizard to convert your Power Pivot model (along with the Power View reports) to a Power BI semantic model.

Learn more about converting your Power Pivot model to Power BI!

By converting your Power Pivot model to a Power BI semantic model, you’ll gain the ability to publish your Power BI model to your Power BI site. By publishing your model, you’ll be able to:

Schedule refreshing of your data sources
Share your reports with your organization
Create dashboards based on multiple reports for insight into your organization’s most important metrics
Create new reports by dragging and dropping based on published data sources
Utilize Power Q & A to query your model
Easily view reports across desktops, tablets & mobile devices

Now there are some workbook size limitations you will need to consider, so if you’re looking to leverage a lot of data in a reporting solution, Power BI probably isn’t the way to go. Also, currently there is no way to secure a Power BI model at a row level. Melissa Coates has a nice piece on Power BI security considerations you should check out if you’re considering Power BI. But with Microsoft releasing monthly updates to Power BI, I wouldn’t be completely surprised if row level security was on the road map. It’s also worth noting that at the time of me writing this Power BI semantic models do not support user-defined hierarchies and key performance indicators. You can read more about the limits to converting Power Pivot to Power BI here.

Convert the Power Pivot Workbook to a Tabular Model

If your organization is neither a Share Point or Office 365 customer, converting your Power Pivot workbook to a Tabular model is probably going to be your best option for taking Power Pivot to the next level. But even if you have Share Point and/or Power BI, this may still be your best option as a Tabular model has some very key features that neither of the previous two options can offer:

Table partitioning and thus can support incremental data refreshes improving processing performance
Row level security using roles
The size of the data model can be substantial, especially consider Tabular models support direct query against SQL Server data sources
Tabular models can be utilized as data sources in Share Point for Power View, Power BI, SSRS, Performance Point and Excel

Obviously there are some features with a Tabular model that give it a leg up on Power Pivot for Share Point and Power BI as an enterprise analytics solution. But SSAS Tabular does not support near the number of non-traditional data sources that Power BI does, such as web, Sales Force, Google Analytics and more. So if you’re looking to integrate some of the non-traditional data sources within your Tabular model you’ll have to do some ETL work to make that happen.

Also, you’ll see that SSAS Tabular models do not support transforming data using Power Query so data sources consumed by SSAS Tabular will need to be somewhat cleansed, although DAX can help with this issue. SSAS is getting some improvements in SQL Server 2016 which may influence your decision. Also, SSAS Tabular models can be utilized as a data source for Power BI and Power View for Share Point.

Summary

Obviously the three different options can serve three very different purposes, but the purpose of this blog post is to present the three options, some of the differences between them and allow you to weigh the considerations.

I’ve done my best to recall the various considerations so if I left something out feel free to send me a message so I can update this posting. Thanks, friends!

So here’s the nuts and bolts break down of the differences:

Deploying Power Pivot for Share Point

Requirements

Share Point 2010 or 2013 configured with Power Pivot for Share Point
Excel 2010 or 2013 with Power Pivot plug-in installed/enabled

Pros

Easy deployment and sharing
Share pivot tables/charts via Share Point
Secure through Share Point
Can be used as source for SSRS, Performance Point, Power View reports
Creating reports in Power View is very easy using drag and drop
Power View reports with live connections can be exported to Power Point
Schedule refresh of Power Pivot
SSAS server behind the scenes
Can *probably* be developed by power user
Not necessarily managed by IT

Cons

Subject to Share Point 2 GB document size limit
No row level security
No incremental processing
Limited abilities to connect to non-traditional data sources

Converting Power Pivot to Power BI

Requirements

Appropriate Office 365 license
Power BI site
Power BI Desktop (not required but nice to have)

Pros

Easily connect to non-traditional data sources
Very easy to leverage cloud data sources along side traditional data sources
Very easy to create reports in Power BI using drag and drop
Free option available with Office 365 license
Data cleansing and transformation available with Power Query
Microsoft is releasing monthly updates at this point to continually improve the tool
Can *probably* be developed by power user
Not necessarily managed by IT

Cons

Subject to model and workbook size limitations
No row level security
No incremental data refreshes
Some unsupported visualization types compared to Power View
Doesn’t currently support KPIs & user-defined hierarchies
Power BI model can’t be queried outside of Power BI

Converting Power Pivot to SSAS Tabular Model

Requirements

SQL Server 2012 or later Enterprise or Business Intelligence Edition
Dedicated server for SSAS with enough high-performance memory to support processing of the model is recommended

Pros

Can support very large data volume
Supports incremental data refreshes
Supports row level security
SSAS features such as KPIs, hierarchies, perspectives are supported
Tabular models can be leveraged as data sources in Share Point, Power BI, SSRS, Excel, Performance Point and other third party tools such as Tableau
Parallel partition processing support in SQL Server 2016

Cons

SSAS developer required
Cannot natively connect to non-traditional data sources
No data cleansing with Power Query so data will need to be cleansed
Managed by IT (maybe not a “con” per se but just sayin’)
Direct query to SQL Server can be quite slow so best to just stick to with in-memory storage