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.
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.
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.
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
- Share Point 2010 or 2013 configured with Power Pivot for Share Point
- Excel 2010 or 2013 with Power Pivot plug-in installed/enabled
- 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
- 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
- Appropriate Office 365 license
- Power BI site
- Power BI Desktop (not required but nice to have)
- 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
- 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
- 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
- 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
- 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
So what are your thoughts on the above options? Please leave your feedback down below and let me know what you think!