Forum Discussion
Star Schema - Many Dimensions or Broad Dimensions
This is a really broad question but the short answer is that generally, too many dimensions is bad. You can have many different dimensions but as you get near 20 or 30 total, you almost definitely have better modeling options at the source level. 1 dimension with 20 attributes is good, 20 dimensions with 1 attribute each is not so good. The problems that come with high numbers of dimensions are that it can create ambiguity and complicate slicing/filtering/relationships between tables as your model gets more complex.
I would highly recommend the SQLBI courses (intro courses are free and still contain tons of useful info) and any of their articles. They cover top level data modeling pretty well.. https://www.sqlbi.com/p/introduction-to-data-modeling-for-power-bi-video-course/
All right, thank you. Your answer mostly matches my experience and intuition.
But let's get it a little bit further and say we have two fact tables: One for actual sales and one for planned sales. However, the plan table is not as granular as the actual sales and only plans amount for month, team and customer. I added the green lines for these relationships
Problem with these green relationships: they are all many-to-many. So what would be a good way here. My thoughts for different options:
1. Extract the plan-dimensions from the dimension table and give them each their own table (which would look similar to my first approach in the original post)
2. Create a dummy entry in the dimension tables. This only has the information about the planned dimensions and NULLs for the unused. For example:
TeamID | TeamName | EmployeeId | EmployeeName
1 | Team A | 999 | Planning Dummy
This way, I still can compare actual vs plan
3. Somehow put the planning into the original fact table.
4. Ditch the whole star schema and go back to a snowflake.
Btw. I am looking more for a general discussion and tips, not for a solution for this special case. My actual use case is way bigger, but isn't it always ;-)
Thanks and best regarss
Patrick