PowerBI and Dynamics 365 Integration

[FN]

Hello community,

in our company recently we switched from an old CRM to Dynamics 365.

Now I am setting up all kinds of reports for all different departments in PowerBI Desktop with all the data coming from the Dynamics 365. With the new CRM, when I get data from the dynamics 365, it is no longer "real data" but lots of codes.

One Example:
When I got data for an opportunity status, the old CRM got me e.g. the Column statecode and then the different entries in there where "Open", "Disqualified" and "Qualified".

Now with the Dynamics 365, the Column statecode only has codes in it. Here for example in the column statecode I can find the values 0, 1 and 2. Now if I want to report something for the statecode, I first have to get in touch with the corporate applications department to find out what these values 0,1 and 2 really mean. And once they send me what this really means (0 = Open, 1 = Disqualified, 2 = Qualified), I am not finished.

Now what I have to do to make reports senseful is to use the function "Replace values" to set all 0s in this column to Open etc. But if I have to do that for 100+ columns and probably 1000+ values, it is really annoying and also it looks like this is also decreasing the performance of PowerBI desktop.

Is there a different opportunity to deal with this problem?

Thank you in advance!

[seabrew]

Our environments are not apples to apples, but still relevent to your question. 

We have a (non-MS) vendor hosted Dynamics CRM 2017 solution and an on-prem SQL Server data warehouse. To integrate our warehouse with Dynamics, we use Scribe Online Replication Services. This syncs CRM entities data onto an on-prem SQL database and allows us to get the CRM schema and underlying data. I use this then to write SQL queries that are then consumed by our data model in Power BI. I think the price of Scribe Online is about $100 a month. 

All of the CRM code/lookup values fields have a hidden field that you can query. In the scribe replication services world it is fieldname_displayname. For example in the screenshot, I am querying the oppurtunties entities and showing the original "code" field and then underlying string value of the code. 

Having the CRM data in SQL that you can get to is really powerful. Hope this helps you!

[v-qiuyu-msft]

Hi @FN,

Based on my research, the Dynamics 365 store entities state code in numbers rather than actual meaningful word. You can see: Default status and status reason values. So you have to convert numbers to meaningful words after you retrieved data in Power BI desktop by yourself.

You can double confirm it from Dynamics CRM forum here.

Best Regards,
Qiuyun Yu