Understanding Star Schema? One Lookup Table vs Many

[AILearn2008]

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

[Anonymous]

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 Team

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

[lbendlin]

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.