Handling Large Data Sets

[supton]

Hello! I am pretty new to Power BI and I am looking for advice on best practices. We are attempting to use Power BI to report on large sets of Dynamics 365 data (around 1.5 GB). I have used the connector in Power BI Desktop to connect to Dynamics 365 and the data refresh takes quite a long time, so long in fact that it errors out when I try to refresh on the Power BI Web service. We also need to pull the new data entered in Dynamics 365 at least once every hour, and the only way I see to set up the hourly data pulls is on the Power BI Web service. Ultimately, I believe I need to reduce the amount of data that I am pulling from CRM, so that I can perform the hourly refreshes in the power bi web service properly. My question to everyone is, what is the best practice of doing this? Should I store the CRM data somewhere in an azure database and use power bi "live"? Or is it best practice to only pull the direct summation data from CRM and report on that data (this wouldnt make sense to me because then we wouldnt be able to use the filter functionality that power BI provides). Any best practices or guidance of how to handle large data sets (especially coming from Dynamics CRM or Dynamics 365) would be much appreciated. Thank you very much!

[Seth_C_Bauer · Accepted solution]

@supton How I wish I was just starting my CRM project. You'll find pretty quickly that to build out a solution you will either spend a ton of time pulling in only the data you need, and the processing will still take awhile, or you look for other solutions.

I manually built out a solution using Kingswaysoft to import all the tables from our online instance to a local DB. Built a tabular model on that data set and created my Power BI Reports.

MSFT released Solution Templates 2 days before I launched the reports... I cried 🙂

Check out the Solution Template for Dynamics 365 here - this basically guides you through building a full scale solution in Azure. 

I'm assuming you want to do more than just query a few things, the above is my experiance and recommendations, but other approaches on a smaller scale could work, I just got frustrated with all the processing time, searching for things etc. And we needed a full support solution to report on all aspects of our CRM.

[supton]

Thank you so much for the reply! Using the Solution Template this way, we would still need an azure subscription though, correct? My initial thought was to setup the Dynamics 365 Data Export Service myself to bring the data into Azure, and have Power BI report live off of that, but considering this solution template uses the data export service and walks you through everything it seems like this is a better option! Is Power BI mostly meant to report off of smaller data sets normally? It seems as though everyone should have run into this issue at some point!

[Seth_C_Bauer]

@supton Azure sub - yes

No, Power BI can scale, the issue is accessing the CRM data in the methods allowed. It's slow retrieving the information.
So I would say the limitation is in the connection type, not necessarily in Power BI.

There are different approaches to solve different problems.

[KHorseman]

Our system is so insanely customized that there is no way any template would ever be useful for us.

[Seth_C_Bauer]

@KHorseman Ours too, maybe it was a good thing I didn't invest time into the template to hit a dead end due to the "over" customization in ours. Don't know, but I wanted to dream that there was an easy button I just missed out on 🙂

[KHorseman]

We're just coming to the end of launching a whole new Dynamics 365 built from scratch. Most of the new design work is based on me explaining why the old design was bad for reporting. 😄

[KHorseman]

We use an Azure mirror for our CRM. But in any case you should try to write your queries to pull the minimum amount of data needed for the report. Don't pull every column if you don't need them, filter rows, etc.

[supton]

Thank you for the reply! I think I am going to try to rid the data of unncessary columns at this point, but considering I am reporting on summation data of all the rows according to certain linking criteria, I dont think that filtering into specific rows would make too much sense! We are getting over 2000 new records in a single custom entity daily (right now, we have a single entity with 1.5 million records), and we need to form reports on all of them, including the older stuff.

[KHorseman]

Columns make more of a difference than rows. Try not to query any columns that you don't strictly need.