Forum Discussion

FN's avatar
FN
Regular Visitor
8 years ago

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 ...
Need Help
Show and Tell
Tips and Tricks
seabrew's avatar
seabrew
Helper II
8 years ago

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!