Upgrading Power Pivot

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

Power Pivot gallery in Share PointIf 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.

Schedule Power Pivot data refreshes with Share PointBy 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.

Power Pivot Excel dashboard in Share PointWith 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

Converting Power Pivot & Power View to Power BIIf 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

SSAS Tabular ModelIf 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

Resources

View this free webinar with an introduction to Power Pivot

Learn more about converting Power Pivot to Power BI

Limitations to converting Power Pivot to Power BI

Here’s some more info on Power BI data sources and refreshes

Here’s information on the Office 365 business plans

Power BI model and workbook size limitations to be aware of

Power BI security considerations

What’s new in SSAS 2016?

Feedback?

So what are your thoughts on the above options? Please leave your feedback down below and let me know what you think!

4 thoughts on “Taking #PowerPivot to the Next Level”

Leave a Reply