Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jengwt
Helper V
Helper V

Difficulties Retreiving Data from Dynamics

I have been having some extreme difficulties loading data into Powre BI from Dynamics. Some small tables will load into Power BI after maybe 5 or 10 minutes, but larger tables (60,000 - 150,000 rows) can take hours or days to load. We are expected to be creating live reports for folks in our company, but we can't actually get any of our live data to load in a timely fashion.

 

This has been attempting using the Get Data / Dynamics 365 (online) and OData Feed functionalities. We are also looking into our own custom FetchXML statements and the Get Data / Azure functionality. I am going to start seperate discussions concerning those, and will link them here when they are created.

3 REPLIES 3
jengwt
Helper V
Helper V

After an arduous investigation with Microsoft data architects, what we found here was:

 

1) In your queries, only include fields that you know you will need (use the Remove Columns functionality, or code the M-code fetch). This will obviously cut down on the amount of data to be fetched.

 

2) Make sure your Dynamics tables are indexed, this will decrease fetch times.

 

3) Hunt down fields that are glitchy (we determined that a particular field in a table was very glitchy and yielded the overwhelming majority of the load time, as the fetch process for that field was erroring out).

 

Using these methods, we were able to cut our data refresh times from (30 minutes to indefinite) down to about a minute.

 

@AndrewSEA 

synergised
Resolver II
Resolver II

It is really slow to go against CRM online directly.  We ended up replicating the data to SQL for our reports.  You can used KingswaySoft components,  Microsoft data export or Scribe has a cloud based solution.

 

We evaluated the offering from Microsoft.. but it doesn't pull the optionset names and linked names in to the table like KingswaySoft and Scribe do.

We have such a system already, but it only updates nightly, and we want these reports to be near-live. We have some tickets open with Microsoft and are exploring workarounds and other options.

Besides, if we use another DB, what would be the point of having Dynamics?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.