A Practical Guide for Power BI Developers Working with Power Pivots
A few things I wish I knew before I got started....
If you are a new Power BI developer, chances are at some point you will either be requested or volun-told to maintain or create a mystical relic of the past - a Power Pivot; connected to what is most likely a very important semantic model. I don't mean to be rude by calling Power Pivots a relic of the past, but with the tools that we work with today, Power Pivots generally feel... outdated. They lack the flare we expect from MSFT, and they certainly aren’t a solution that will ever include AI, but the tool is a buzz-saw for getting users data where they want it(Excel). My background is in Power BI, and only recently have I started working with PowerPivot’s. So, I wanted to write what I wish I knew before I started developing and maintaining these solutions.
If you aren't familiar with what Power Pivot is, it is essentially PBI desktop without visuals but in Excel. Seriously. Power Pivot can use power query, it has the ability to define relationships, and of course it can create calculations via measures. Of course PBI has many more features, but at their core they are similar.
These tools are so similar that 3 years ago Chris Webb was asked why they are separate products, and he responded with this -
"I think the functionality in Excel is great for Excel enthusiasts who will never use anything else... Excel has to move very slowly simply because it's so widely used and that's why new functionality is so slow to appear in it. Power BI has other advantages because it's focus is being a BI tool and nothing else and we can move a lot more quickly with it." - Chris Webb
You most likely didn’t build a model in Power Pivot, you either connected to the semantic model via excel or used “Analyze in Excel” which in turn connected to your semantic model via Power Pivot. Or you did neither, and somebody sent you an excel file and said - “hey ‘DEVELOPER’, ‘YOUR PREDECESSOR’ built this for me before he left and it’s not refreshing, can you do the needful?”
Well regardless of why you’re here hopefully you’ll find a tip or two below that will help you with whatever your mission is
1. Bi-directional/cross filtering and many-to-many relationships are not technically supported
If you break out PowerPivot and build a model in excel, you’ll find that it is only capable of making one to many relationships. Power BI made waves in 2015 when it released functionality that incorporated Bi-directional filtering, still lots of blog posts available that discuss this. For more on this I recommend this blog from Excelerator - BI
You might wonder why your model operates despite these features not being supported. It's important to understand that "not supported" doesn't mean "non-functional." However, should you connect to a semantic model using these configurations, be prepared for potential performance challenges.
2. Analyze in Excel - GET IT!
You’re going to want this plug-in. This will generate a connection to a PBIX model that is on your desktop. It expedites testing drastically. With it, any change you make in your model can be reflected in a PowerPivot without having to publish changes to the PBI Service. Tip #8 works in tandem with this tool. Download & learn more here
3. Multiple sheets connecting to the same dataset will impact performance
I don’t have measurement on this to back it up, only personal experience. A client has an important semantic model and a few of the users were duplicating the pivot table into hidden sheets… at around 5-6 tables utilizing the same connection - none of the tables would load. If a user is complaining about performance issues, I’d start here.
4. Disable AutoSave in Excel to improve refresh performance
Another topic while we’re discussing optimizing refreshes. This is pretty straightforward. Presently, AutoSave interrupts a Power Pivots refresh. If you have a lengthy refresh, AutoSave will interfere with the refresh by saving several times during it.
5. Where is my semantic model?
This is a frustration I’ve had where I was trying to show a user how they can connect to a semantic model, and it wouldn’t appear! So this tidbit is directly from Microsoft -
“For newly created semantic models, recently refreshed semantic models, or semantic models that you just got access to, it may take up to 24 hours for these semantic models to show up in the Power BI Semantic models pane.”
6. Customizing drill-throughs to curate results
This is really cool functionality that can be added into a Power Pivot. This allows users to double-click in the values section and retrieve new results that are derived from a customizable DAX query. Very powerful! Here is a great video on it.
I'm highlighting this because removing columns or measures referenced in a drill-through can lead to errors in your model - so beware of that as well.
7. Update the OLAP Drill through row-limit
By default this is set to 1,000 which I find to be too low. I don’t want to be in a situation where a user is using this drillthrough and doesn’t realize that they aren’t getting all of the rows that are in a dataset. To update this setting, right click the connection and modify this setting -
8. Updating connection string to avoid duplicating work
This is a boring one but is arguably the one I use the most. The connection string in power pivot can be seamlessly swapped out - so you can use the same excel workbook but switch the pivots connectivity from locally, to test, to prod without having to recreate all of your work. To update the connection string, just right click the PowerPivot connection in Excel and selection “Connection Properties”
9. Difference between “Insert PivotTable” and “Insert Table”
At some point in 2022/2023 Microsoft enabled the discovery of PBI Semantic Models in excel. With it, they gave two options -
The distinction between the two options is straightforward: one is a PowerPivot, and the other is a table, but there’s more to the story as they are different behind the scenes. When selecting insert table, you get to create a table using a GUI - dragging and dropping fields to fill out a table, and you can even add filters to create the result set that you want. What is this doing? This is an interface that is creating a static DAX query. This query is accessible in the connection properties pane. So, to add new columns/measures to the table, you’ll have to either recreate the table, or you’ll have to update the DAX query - I figure most will opt for option 1!
But an interesting note, is that this connection and static DAX query is actually used in PowerQuery to create the table. So, if you hop on over to PowerQuery you’ll see the new table - and here you can modify column names, make joins… etc and it will persist! The DAX Queries are generally more performant than PowerPivot, so if you are starting from scratch, I’d recommend connecting to your semantic models via the Insert Table feature as much as possible.
And that’s all I’ve got for you! Hopefully this will equip you to tackle whatever issue you’re working on. Also, if I missed anything, please tell me in the comments!
If you are interested, here is the release demo of PowerPivot from 2008, featuring a demo-er who is very excited that a product could handle 20 million rows
Awesome write up that anyone new PBI dev should read. Power pivot is like our great ancestor