cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
FN Frequent Visitor
Frequent Visitor

PowerBI and Dynamics 365 Integration

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!

2 REPLIES 2
Moderator v-qiuyu-msft
Moderator

Re: PowerBI and Dynamics 365 Integration

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 

 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
seabrew Regular Visitor
Regular Visitor

Re: PowerBI and Dynamics 365 Integration

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!

Scribe Online RS example.PNG

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 306 members 3,219 guests
Please welcome our newest community members: