Power BI and the Speed(ier) Desktop

I can be an extremely impatient person about anything I think should be faster.

I’m diving in deep with Power BI and for most of the early on lessons, the data sources used are Excel and…ahem…Microsoft Access.  I don’t know a DBA alive that enjoys working with Access.  Its great for the common user to have a database application, but we can barely use the words “Access” and “Database” in the same sentence.  In my heart, I will always be a performance person and working with Power BI desktop with Microsoft Access is enough to make you want to throw your PC out the window, especially when talking data sets of 10 million rows or more..  I felt there had to be, at least, some way to help speed up the performance on Power BI when using this combination.

Now in a real life scenario, the first recommendation would be to filter the data set down so that it wouldn’t put so much pressure, resulting in poor performance.  I was offered some great links that presented that, along with other best practices and I’ll link them here, as the Power BI community offered up some AWESOME responses to my quest for answers:

Melissa Coates has a great Check List for Finalizing a Power BI Data Model post

Meagan Longoria sent me the Power BI Performance Best Practices link for Microsoft, which is filled with great information that everyone who works with Power BI should know.

Ginger Grant tweeted out the Marco Russo webinar,  My Power BI Report is Slow.

As for me, I had the added challenge of working with the edX lessons, not much of the above is an option, as the labs successful completion relies on entering correct counts for the results post lab work with the required data sets.  If you filtered the data or optimized the data model, the counts would be off and you WOULD FAIL.

What’s a girl to do to get through this without pulling her hair out and feeling the quality of the experience wasn’t impacted?  I can’t be the only one who felt this way and I know how users react when these types of situations happen.  I’m a DBA and in the database world, no matter who the performance impact culprit is, the database is guilty until proven innocent.  In this new world, Power BI credibility is the one impacted for new users who are just starting to learn about his powerful tool, when the goal is to empower the user.

I searched Google for some best practices, but most of them surrounded how to model the data more effectively vs. working with the products.  It demonstrates why performance specialists from all areas are essential to creating solutions and how NOTHING should be off the table.

OK, so if I’m working from scratch, this is the time to test out my own ideas and if I fall flat on my face, so be it. 🙂

Another reason I love the Microsoft Surface, outside of the ability to replace keyboards as I burn through them, is Microsoft’s ability to easily add disk to virtual memory in a swap file.  Now you might think this isn’t a great feature, but with SSD for the hard drive, you can gain significant performance, even when you swap.

My Surface Book has 16G of memory, which is pretty darn good, but I decided to supplement the swap file with 24G, max 32G from the SSD.  After the restart, then I went to Power BI Desktop, started it up and clicked on File –> Options –> Options and Settings –> Options.

I bumped up the data load default from 4G to 12G, (and if you’re looking at that cache value, know that I rebooted and tested different data models before this screenshot.)

I then monitored the memory usage both at rest and when applying the query changes to see how the performance changed.  I was able to get around a 40% increase in performance by upping the data load memory, (using about 8G of memory and just under 3G of swap) but how many people have the resources I have or the amount of fast swap?

Luckily, a number of folks also recommended I reach out to Adam Saxton and Patrick LeBlanc from Guy in a Cube.  Most don’t realize that my new position I have at Microsoft used to be held by Patrick, so this is why I’m “drinking from the fire hose” at an incredible rate.  I’ve got some big shoes to fill and if anyone knows my skill set, it’s massive, but I’m new to Power BI, (got brain power, will travel… :))  I’d already reached out via email earlier to Patrick, as he’s been gracious enough to be open to discussing my challenge and we chatted on the phone.  When I explained the scenario to him, he agreed, it was worth discussing with others higher up on the Power BI team, as my experience was most likely not isolated.

I really want to thank everyone that jumped in and offered links, suggestions and responded.  Love this community and stay tuned – I’ll let you know if there is action on this item, but until then, memory is king.