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
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.
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.
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?
User | Count |
---|---|
160 | |
110 | |
96 | |
86 | |
75 |
User | Count |
---|---|
159 | |
136 | |
133 | |
81 | |
61 |