Share
Report
20Hi,
I've been working with our Dynamics 365 data for a few years now. I produce SQL Views to retrieve Dynamics data from various entities, based on the relationships set in Dynamics, so to present that various data via a single View to match that of the Dynamics Form. For example, the Dynamics account Form shows data from the account entity and the contact entity. The account SQL View pulls data from these two entities and presents it in the single account SQL View to mimic the account Form. I've performed this logic to the all SQL Views created, which encompass the fifty plus entities that we use in our reporting.
Having provided you with some context on my use of the Dynamics data, I am now considering if I should embark on attempting to normalize (to 3NF) the Dynamics data to be optimal for reporting. This seems likes a huge task and not something I've attempted before. I have completed some basic tutorials on normalizing transactional data and so I understand the logic, but to do this on something as big as Dynamics - I'm not sure if this is a wise move. I should also mention our Dynamics structure, the underlying data relationships, do still change with the continuing changes our Business users requires of Dynamics. I feel this will just add to the pain.
My question, have any of you created an OLAP model (star schema/snowflake schema) from the Dynamics transactional database (OLTP model)? Do you have any guidance, advice, experiences you can share?
I want to do the right thing but I'm a one-person band for all things data/reporting at my company, and I don't want to take on something that's too big for me or likely to run into issues that I cannot foresee. To date, I've produced some very complex reporting using my SQL View approach, with the modelling completed using Power Query inside Power BI, and all has working/is working great still. With the advent of A.I. I am expecting this kind of OLAP data model to be a must if A.I. if going to make use of the data. Maybe the approach should be to create smaller OLAP model data-marts that focus on a specific function rather than to go all out on the entire fifty plus entities we use.
I've been working with our Dynamics 365 data for a few years now. I produce SQL Views to retrieve Dynamics data from various entities, based on the relationships set in Dynamics, so to present that various data via a single View to match that of the Dynamics Form. For example, the Dynamics account Form shows data from the account entity and the contact entity. The account SQL View pulls data from these two entities and presents it in the single account SQL View to mimic the account Form. I've performed this logic to the all SQL Views created, which encompass the fifty plus entities that we use in our reporting.
Having provided you with some context on my use of the Dynamics data, I am now considering if I should embark on attempting to normalize (to 3NF) the Dynamics data to be optimal for reporting. This seems likes a huge task and not something I've attempted before. I have completed some basic tutorials on normalizing transactional data and so I understand the logic, but to do this on something as big as Dynamics - I'm not sure if this is a wise move. I should also mention our Dynamics structure, the underlying data relationships, do still change with the continuing changes our Business users requires of Dynamics. I feel this will just add to the pain.
My question, have any of you created an OLAP model (star schema/snowflake schema) from the Dynamics transactional database (OLTP model)? Do you have any guidance, advice, experiences you can share?
I want to do the right thing but I'm a one-person band for all things data/reporting at my company, and I don't want to take on something that's too big for me or likely to run into issues that I cannot foresee. To date, I've produced some very complex reporting using my SQL View approach, with the modelling completed using Power Query inside Power BI, and all has working/is working great still. With the advent of A.I. I am expecting this kind of OLAP data model to be a must if A.I. if going to make use of the data. Maybe the approach should be to create smaller OLAP model data-marts that focus on a specific function rather than to go all out on the entire fifty plus entities we use.
Thanks in advance.
All responses (
Answers (
