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

Best Practice on using Power BI with MS Dynamics 365 (online) ?

Hi,
My company has recently implemented MS Dynamics 365 (online CRM) as their transactional solution. To coincide with this, the company is introducing Power BI to lead their reporting strategy. It's solely on me to form this reporting solution and, of course, wanting to create the most suitable solution possible, using the best practices, I'm wishing to seek some guidance on that best practices, or pitfuls I should be aware of. I am still investigating the matter myself but it's wise to reach out to others whom have followed the same road.

My questions are asked without understanding the end-user reporting requirements, haven't reached that point yet, so these questions most probably will be aimed at the source architectural side of things.

Questions:

1) The MS Dynamics 365 (online CRM) is a transactional system, thus follows OLTP, but BI solutions should follow the OLAP (Star Schema) approach. This should also provide an optimal model for DAX. 
a) What is the best approach to work with D365 data?
b) Is it to simply connect to the D365 tables, choosing which tables in the connection, and then transform your tables to follow an OLAP model using Power Query (PQ)?
c) Is it to create a full-blown OLAP model (Star Shema) as a immediary layer between D365 and Power BI?  If yes, how/where would you create this OLAP model - use SSIS from D365 to a SQL Server database? (with this approach one could upstream, using T-SQL queries, so the heavy work is done on SQL Server). Or should I look to create this OLAP model in Power Query, using all the tables from D365 (then you may have volume issues)?

 

2) To connect to D365 application I believe there are just two connection methods - Dynamics 365 (online) or OData feed. Neither of these methods allow the use of custom T-SQL to upstream the heavy work to the source, in this case the OLTP D365 application.  Another consideration of not using T-SQL in the connection is if D365 OLTP backend changes (i.e. a field is removed or renamed) then, considering when selecting a table from D365 and all the fields are brought in before unwanted fields are removed in PQ, PQ will detect that field change and throw an error. If I am somehow able to write a T-SQL query to retrieve only the required fields then we may avoid this scenario.
a) Is there a way to write T-SQL, or equivalent, queries to select only the relevant fields from the table(s) so to satisfy the above considerations?

3) Is there a benefit to using the MS Dynamics 365 (online) connection over the OData feed connection, and visa versa?

 

4) There is this CRM specific feature called 'OptionsSets'. This is something new to me and how I've seen opening the additional labels (i.e. human friendly names) via the use of XrmToolBox, so not just to have the meaningless keys, seems a real chore.
a) Is there anyway to bypass this 'OptionSets' feature in Power BI and thus to see all the fields, both the labels and keys, directly from the 'Get Data' option and therefore not need to use XrmToolBox?

 

5) When viewing the MS Dynamics 365 application contents it shows two items - Tables and Functions.
a) Obviously the tables hold the data but has anyone ever used the functions available for reporting and can you give me a scenario in which they were used?

I think that's all the questions for now - Phew!  If you've read this far you've done well.

 

Any insight on how best to use Power BI with MS Dynamics 365, in particular my questions, will be greatly appreciated and I'm sure others will find it extremely useful too.

Thanks.

2 REPLIES 2
adny Frequent Visitor
Frequent Visitor

Re: Best Practice on using Power BI with MS Dynamics 365 (online) ?

Hi @D_PBI 

 

Lots of questions and I cant answer them all, but maby I can give you some good material to work with.

 

First off, if you havent seen it already crmchartguy has some really good blogs/guides, this one might get you far: https://crmchartguy.com/2019/07/23/comparing-the-three-main-ways-to-get-dynamics-365-data-into-power...

 

If you want to be able to track changes in your data its a good idea to have some kind of layer between your D365 enviroment and PBI, as you say this can be a SQL server or DW depending on how much data you have. Data Export Service is a free addon you can use to replicate D365, more info here: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/admin/replicate-data-microsoft-azur...

 

Hope this helps a bit!

 

br

Adrian

Community Support Team
Community Support Team

Re: Best Practice on using Power BI with MS Dynamics 365 (online) ?

Hi @D_PBI 

It is better to cut large questions to small ones and post for each question so to get more reply and answers.

There is a video how to use Power Bi and Dynamic 365

https://www.youtube.com/watch?v=81w0Oq89TUs

https://ax-dynamics.com/article/7-steps-to-get-started-with-bi-desktop-and-d365

 

Best Regards
Maggie

 

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.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 3,188 guests
Please welcome our newest community members: