Dynamics CRM 365 data import in Power BI from view

[dimitrievski]

Hello all,

I am trying to pull data from Dynamics CRM into Power BI. In CRM instance, there are views that contain exact data that I need to report on. I know that I can export to Excel and then pull in Power BI, but is there another way to pull that data via Web API? I tried to use default pbix template from Microsoft, but refresh is extremly slow and data pulled in does not exaclty fit my reporting purpose. 

What I am trying to do is pull data and match Oportunities to Goals in simple report. Goals are not even pulled in with default Microsoft pbix template for Dynamics CRM. 

Thanks for your help!

[dimitrievski]

Hi All,

It looks like a very good solution using fetchxml, but when I try it, it gives me an error with credentials, Error is "Credential Type is not supported.", and I am using Organizational account with admin rights on the CRM instance. 

[Anonymous]

@dimitrievski,

Do you get errors when connecting to CRM in Power BI Desktop following the guide in this article? Would you please double check if the URL you entered in web source is correct?

Regards,

[dimitrievski]

Hi, i am able to query both online and via pbix from desktop, it just doesn't work if i try to Get fromw web and i use fetchxtml generated link.

[Anonymous]

@dimitrievski,

Do you enter URL which is in format as <API address >/incidents?fetchXml=<URI we got>? How about you create the sample FetchXML query as descibed in the blog?

Regards,
Lydia

[dimitrievski]

hi @Anonymous,

I use a generated url to connect, it goes something like this

<API address>/Opportunity>fetchXml=%3Cfetch+version%3D%221.0%22+output-format%3D%22xml-platform%22+mapping%3D%22logical%22+distinct%3D%22false%22%3E%0D%0A*************%2Ffetch%3E

[Anonymous]

@dimitrievski,

Please check the last screenshot in that blog, the URL is formatted as <API address >/incidents?fetchXml=<URI we got>. 

Click “Get Data->Blank Query” to add a new blank query in Power BI Desktop, then paste the following code in Advanced Editor.

let
    Source = Json.Document(Web.Contents("<API address >/Opportunity?fetchXml=<URI we got>")) 
in
    Source 

Regards,
Lydia

[RV1]

Doesn't fetch xml have a limitation of 5000 records? What do we do if we are dealing with a large dataset?

[Anonymous]

@dimitrievski,

Based on my research, views in Dynamics CRM are stored as FetchXML query. In Power BI, we can’t use the FetchXML query directly, we need to encode the FetchXML query to URL so that it can be used in Web source. For more details, please review the following blog.

https://community.powerbi.com/t5/Community-Blog/CRM-Online-issues-with-oData-and-solution-with-Fetch...

Regards,
Lydia

[Greg_Deckler]

You can connect to the OData endpoint. You can use the CRM connector that is included in Power BI. But, I am not aware of any way to point it to a specific view, you will have to pare down the columns.