Forum Discussion
Understanding Star Schema? One Lookup Table vs Many
I am trying to build out the Star Schema and I am utilizing a fake corporate structure with regional stores.
When building the lookup table, would it be better to build a separate lookup table for each Region, State and Store or one table with all? If each, then I would have one Region table with the region and Unique ID, then the state would be linked to the Region Unique ID, and the store would be linked to the State Unique ID.
The real-world scenario I would use this for would end up being 100+ columns and 1000's of records. And most likely this would be hosted in a SharePoint List.
Or just build out one table such as?
| Region | State | Store |
| REG_1 | TX | A |
| REG_1 | TX | B |
| REG_1 | AR | A |
| REG_1 | AR | B |
| REG_1 | AR | C |
| REG_1 | TX | C |
| REG_1 | TX | D |
| REG_1 | AR | C |
| REG_2 | FL | A |
| REG_2 | FL | B |
| REG_2 | FL | C |
| REG_2 | GA | A |
| REG_2 | GA | B |
| REG_3 | NM | A |
| REG_3 | NM | B |
| REG_3 | NM | C |
| REG_3 | NM | D |
| REG_3 | CAL | A |
| REG_3 | CAL | B |
| REG_3 | CAL | C |
| REG_3 | CAL | D |
| REG_3 | WY | A |
| REG_3 | WY | B |
| REG_3 | WY | C |
2 Replies
Read about Normalization and the different levels (forms) all the way up to NF6.
In a real world scenario you can stop at level 2 or 3, no need to overthink this, especially not when the denormalized version (flat table) is not too big.
Thanks for the reply from lbendlin.
Hi AILearn2008 ,If you have a large amount of data and need to perform complex queries, I think splitting into three dimension tables is also a good option. Using a star architecture reduces data redundancy and also allows you to use the dimension tables as filters.
Here are some relevant documents that you can study:
The importance of star schemas in Power BI - SQLBI
Power BI Basics of Modeling: Star Schema and How to Build it - RADACAD
Best Regards,
Zhu
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
