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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
poncoy
Frequent Visitor

Slow download from CRM Online

 

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?

 

15-11-2017 10-08-35 a- m-.png  

 

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:

 

15-11-2017 10-34-14 a- m-.png

 

 

This is my relationship diagram from Power BI, the leads table is highlighted in red:

15-11-2017 10-37-32 a- m-.png 

 

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!

 

8 REPLIES 8
acdeslions
Regular Visitor

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

ramy
New Member

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.

synergised
Resolver II
Resolver II

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

 

 

 

Thank you very much everyone for your prompt responses!
This is a very interesting topic because I have a lot of respect for PowerBI. However, in Microsoft's online environments and APIs, it has to be improved considerably. This shouldn't be so complicated, because we're talking about the same manufacturer and the same software ecosystem.

Conclusion: It's not recommended to use a connection via OData URL from Power BI to connect to CRM online and see live reports that consult tables of more than 300K rows.
v-sihou-msft
Employee
Employee

@poncoy

 

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.

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/guidance/data/retrieve-specific-col...

 

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

https://nishantrana.me/

Thanks for your response.

Although I can't find the option to use DirectQuery, only the Load button appears to go to the QueryEditor
:

LoadWhitoutDirectQuery.png

 

Is DirectQuery enabled through the CRM Dynamics 365 API from Power BI Desktop?

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.