Forum Discussion
advice on data architecture
Hello Community - Been watching all of the various videos on the import modes, composite models, DQ, etc etc. We are in the process of building out our corporate data structure and I have some questions for the more experienced folks out there.
Sort of a two part question:
Our source data is generally ERP or CRM and we have dataflows connected to both. We have various departments like sales, engineering, manufacturing, etc.
1. Does it make sense to have datamodels/datasets at the fact table level? For example, a dataset specifically built around the sales table, another one built around the orders table, etc. In that example, they would likely contain the same dimensional tables (customers, products, date table, etc), only the fact tables would differ. And if so, if creating reports that needed to use both, what is the best way of bringing them together? Keeping in mind, we are asked to add modifications that require new measures/columns fairly frequently so connecting purely via a live connection does not really work for that purpose.
2. Our business does not require "live" to the minute reporting. A table being refreshed twice a day for us is perfectly fine. And we have less than 10 million rows of data. Would a composite model make sense as we do (or will) likely have a need to augment our ERP and CRM data with other sources.
3. Does it make sense to have "organizationa'" data sets? In other words, datasets that are specific to certain departments (and possibly these departments would need to use one of the data sets that another department is also using). For example, our Quality department has reports that require the sales and orders tables.
Any guidance is appreciated!
2 Replies
- parry2k
Super User
Anonymous Great question and I can share my thought but it will easier to jump on a call and discuss this. Security and governance are going to play the biggest role to decide to create separate models by department or a single organization model. The good news is that your data size is very small.
Feel free to reach out by email (in my signature) and we can schedule a call
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- v-deddai1-msft
Community Support
Hi Anonymous ,
In my option, why you not bring the two fact table in the same dataset, then you will not have duplicated dimensional tables. You can have a dimensional table connect to both of fact tables. And If you want to create a visual from these fact tables together, it would be more convenient. You can follow the video: https://www.youtube.com/watch?v=TnyRsO4NJPc
For large data, I suggest you use direct query mode. Of course, composite mode will work.
For the third question, I think if you should put all the fact table in the same dataset, for the premium, you can have 10GB limit in size of dataset.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai