Solved: Power BI Connection To CRM/Sales Transaction locking/performance

Power BI Connection To CRM/Sales Transaction locking/performance

Hi All

I'm getting into connecting PBI to Dataverse. Is there best practice in terms of performance and locking issues. I can see all tables and I'm assuming if I run a query against them will it affect end users/other integrations updating the records?

Categories:

Microsoft Dynamics CRM

Dynamics 365 Sales

I've created and run a number of reports through PowerBI into Dataverse. From a user perspective, you should not see any performance issues between utilizing CRM and refreshing the PowerBI report / semantic model. Many of the models I have run include hundreds of thousands of records in any organization with hundreds of users globally and have never run into a problem. So I wouldn't worry about that side.

Now, for your report, you are going to want to only target what is absolutely necessary to squeeze the most performance and reduce your import or query times. This means you should only grab tables that you need to reference, and if you can filter down those tables it's even better (for example, only looking at Opportunities from the past 3 years if nobody needs information earlier than that).

Finally, the largest performance enhancement I have seen in the Dataverse connector is to prevent the query from automatically creating the relationship navigation. The link below has a section called "Performance Issues Related to Relationship Columns". These are the columns that appear at the end of your query and allow you to expand out to related or child tables. You can stop the query from creating these columns, which makes a big difference on your tables that see a lot of connections (like Accounts, Users, etc.)

Power Query Dataverse connector - Power Query | Microsoft Learn

Finally, I've found that reducing columns has a bigger performance impact than reducing rows, so get rid of all those unnecessary columns in your table.

Good luck and let me know if you need anything else!

If this answer helped, please consider marking as verified

Quick Links