Would you recommend re-modelling MS Dynamics 365 data from OLTP to OLAP for use with Power BI?

[D_PBI]

Hi,

I would like your advice on modelling MS Dynamics 365 data for use with PBI.
I have experience of using PQ to extract data from Transactional (OLTP) systems and then re-model that data to fit a Multi-Dimensional (OLAP) schema so to serve PBI better, however, I should say that these re-modelling exercises were using a lesser volume of data than MS Dynamics 365 contains.

I would like you advice on whether I should re-model MS Dynamics 365 transactional data into a multi-dimensional model?
Are you able to share you experiences (good and bad) on when you have tried to re-model or just left it as a OLTP schema?

If you do suggest I attempt to re-model it do you recommend:

a) re-modelling the entire MS Dynamics 365 schema or just the used Entiries (I am a novice on MS Dynamics 365 so am still learning how to unlock Option Sets, etc...)?
b) re-modelling the data in PQ or look to re-model it using SQL Server in the cloud and the multi-dimensional layer (and why)?

I don't wish to bite off more than I can chew, however I do want to build a sustainable PBI/MS Dynamics 365 reporting solution so your guidance will be very much appreciated.

<please note: I had previously placed another post encompassing many questions but at the advice of the moderator I have split my questions into separate posts>

[dax]

Hi D_PBI

If you want to connect to Dynamic 365, I think you could use  Dynamics 365 (online) , then modify it in Edit Queries and create corresponding relationship between tables.

If you want to use modified data and relationship, I think you could load the data in sql server and create cube in SSAS, then you could use SSAS  cube directly in PowerBI.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[D_PBI]

@dax - thanks for your response. I am aware of the different options we have to model data within the MS BI world, however my question was more so seeking if others have taken the steps of creating a Mutli-Dimensional model, from the Relational model that Dynamics 365 is, and what benefits or lack of benefits did they find?

Also, I'll be interested to know if they re-modelled it in PQ or went the whole hog and re-modelled it using a separate SQL Server database and SSAS, and then hooked PBI into that.

If anyone would like to share their experiences, and why they did it the way they did, it will be good to hear?
Thanks.