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.
