Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello friends.
My question is about the delay in downloading data from Power BI desktop connected to a Dynamics 365 (CRM) online. For example, I have a report that connects to 5 tables of which, one of them (leads table) weighs around 2.5Gb bringing 15 columns (300,000 records approximately). That operation takes about 10 hours to complete. The internet connection I have is 10Mbps and my PC is a Windows 10, Intel Core i5 7th Gene with 16Gb Ram. Why is there so much delay?
From yesterday until today, the update of the report was completed. However, when I press the Refresh option, the delay is repeated. While I use DirectQuery, this option does not allow me to make queries to put filters:
This is my relationship diagram from Power BI, the leads table is highlighted in red:
Is the delay due to my network connection? Is the normal take a delay for more than 2GB of data from an online environment?
Thanks!
Hello
I have the same problem with Odata connexion
I have trying to connect Dynamics 365 CRM via WEB to Powerbi and it is ok and very fast.
But I have not the same information beetween date returned from Odata and WEB : by WEB some data is missing ?
Anybody know why
Thank's a lot
Best regards
An alternative to the standard OData connector is to use a modified Web.Page connector in combination with pre-selecting columns in the API. Here's an example:
Web.Page(Binary.Buffer(Web.Contents("https://tenant.api.crm4.dynamics.com/api/data/v8.2/entity?$select=column1,column2")))
You then need to drill down until you get to your data stored as JSON. Here's a full example:
let Source = Web.Page(Binary.Buffer(Web.Contents("https://tenant.api.crm4.dynamics.com/api/data/v8.2/entity?$select=column1,column2"))), Data = Source{0}[Data], Children = Data{0}[Children], Children1 = Children{1}[Children], #"Removed Other Columns" = Table.SelectColumns(Children1,{"Text"}), #"Parsed JSON" = Table.TransformColumns(#"Removed Other Columns",{{"Text", Json.Document}}), #"Expanded Text" = Table.ExpandRecordColumn(#"Parsed JSON", "Text", {"value"}, {"value"}), #"Expanded value" = Table.ExpandListColumn(#"Expanded Text", "value"), #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"column1", "column2"}, {"column1", "column2"}) in #"Expanded value1"
I haven't tried this on large datasets.
We found the CRM online to Power BI to be extremely slow - although its performance has improved this past year.
We ended up replicating the CRM data to an Azure VM running SQL. Currently, we use the KingswaySoft tools for this (very manual process - (DTSX package)). ScribeSoft has an on-line sevice that works good.. basically set it up and forget about it. We used it for a year but when it came time to renew the new pricing structure was too costly for what we needed.
It is on our list to start evaluating the Data Export Service (CRM Online data to Azure).
We can't do too much on improving query speed when retrieving such large amount of data.
I suggest you import those data from API into a on-premise database. Then use "Direct Query" connecting to that database.
Regards,
How can I download (using Power BI) the CRM Online tables by using the API?
That is, download the records of the 4 tables that I'm consulting to the Power BI custom tables.
Hi,
You would have to make use of OrganizationService API to retrive the data from the entity\table you want.
or export the data using Export to excel inside CRM
or use SSIS Package (KingswaySoft) to pull the data and push it in custom database
or use Data Export Service to push the data to Azure SQL
https://nishantrana.me/2017/03/19/configuring-data-export-service-in-microsoft-dynamics-365/
Thanks,
Nishant Rana
Thanks for your response.
Although I can't find the option to use DirectQuery, only the Load button appears to go to the QueryEditor :
Is DirectQuery enabled through the CRM Dynamics 365 API from Power BI Desktop?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |