cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Handling Large Data Sets

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Handling Large Data Sets

@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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

9 REPLIES 9
Highlighted
Super User I
Super User I

Re: Handling Large Data Sets

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User III
Super User III

Re: Handling Large Data Sets

@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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

Highlighted
Super User I
Super User I

Re: Handling Large Data Sets

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User III
Super User III

Re: Handling Large Data Sets

@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 🙂


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Highlighted
Super User I
Super User I

Re: Handling Large Data Sets

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. 😄





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Handling Large Data Sets

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!

Highlighted
Helper I
Helper I

Re: Handling Large Data Sets

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.

Highlighted
Super User III
Super User III

Re: Handling Large Data Sets

@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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Highlighted
Super User I
Super User I

Re: Handling Large Data Sets

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors