Power BI is one of the best things to happen to Microsoft CRM (now Dynamics 365). If you're anything like me, you probably jumped right in as soon as it was available and started pulling in and manipulating tables, and pretty soon were slicing and analyzing CRM data in new and very exciting ways. Why read the manual when you can be building reports?
One reason is performance. If you built your first data sets like I did, you also found that more you manipulated the data, expanding option sets to get friendly names and joining tables together, the slower it got until it became unusable. Even small data sets quickly became huge time sinks as refresh times stretched. When my reports started to become literally uneditable due to the lag, it was time to stop playing and start learning good data set design.
One Simple Trick
Fortunately there is one very simple rule which eliminates the performance issues I was having - never expand columns.
It turns out that whenever you expand a column - say to replace 0, 1, and 2 with Open, Won, and Lost in a custom table you've created to store the values on an opportunity record or replace an Account GUID with fields from the related record - Power BI makes a database query... for each row in the table. Each expansion times each row times the size of your data set can easily add up to a hundred thousand individual queries from a thousand row table. This was the performance killer I was unknowingly building into the design of my reports, and there is nothing to warn you as you do. Power BI happily lets you expand column after column with no warning or indication that it's completely unnecessary.
As powerful as Power BI is, or perhaps, because Power BI is so powerful, many best practices are also unintuitive. For example, if you're like me and like to dive into new products and start clicking buttons and building things. Avoiding column expansion may be a simple, one sentence rule, but implementing it requires a completely different approach to building datasets.
This is the first in a series of blog posts will take you through one such approach to building Power BI datasets from CRM that are optimized for performance, without sacrificing any of the benefits of expanding and joining tables.
This blog was written for the CRM/Power BI Administrator who is already competent with the basics of connecting Power BI to CRM data, building data sets, and creating reports, but looking to take those reports to the next level in terms of performance and efficiency. We won't cover the basic skills on a click-by-click level here, but focus on several key practices in detail. If you need more guidance on Power BI basics, this Microsoft TechNet entry will get you started with general guidance and some sample PBIX files to play with: Use Power BI with Microsoft Dynamics 365. We've also posted a Power BI Overview article here.
Want more Power BI efficiency tips and best practices? Read our technical whitepaper: 3 Best Practices to Improve Power BI Speed with Microsoft Dynamics 365 (formerly Dynamics CRM).