Handling complex custom models from Dynamics 365

[wilson_smyth]

I have been able to connect to Dynamics 365 via the OData URL and can see a list of entities.

We have quite a complex custom set of entities and any useful reporting would requrire joining these entities.
This is easy when using SQL to query onsite dynamics, but Dynamics 365 doesnt allow sql queries.

The only options i can see are:

- use the power query editor and hope i can munge the data into a useful model,.

- write .NET code in the CRM Proxy to obtain the data in the shape i require. This seems like overkill.

Are there any simpler options available?

[v-danhe-msft]

Hi @wilson_smyth,

Based on my research, I suggest you use the merge function in Power BI Desktop, you could refer to below two links:

http://radacad.com/append-vs-merge-in-power-bi-and-power-query

https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

Regards,

Daniel He

[wilson_smyth]

the entity model is to complex for merge.

We will end up writing a crm proxy, and in this we will join the various entities together using LINQ and provide  this as a web service to PowerBI. Slightly overkill, but we have very good CRM developers here who can make it happen.

[v-danhe-msft]

Hi @wilson_smyth,

Ok, hope you could solve your problem and don't forget to close this topic.

Regars,

Daniel He