cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
poncoy Frequent Visitor
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!

 

7 REPLIES 7
Moderator v-sihou-msft
Moderator

Re: Slow download from CRM Online

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

poncoy Frequent Visitor
Frequent Visitor

Re: Slow download from CRM Online

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?

 

synergised Member
Member

Re: Slow download from CRM Online

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

 

 

 

poncoy Frequent Visitor
Frequent Visitor

Re: Slow download from CRM Online

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.
poncoy Frequent Visitor
Frequent Visitor

Re: Slow download from CRM Online

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.

nishantranacrm Frequent Visitor
Frequent Visitor

Re: Slow download from CRM Online

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/

ramy New Member
New Member

Re: Slow download from CRM Online

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.