I've just started learning Data Modeling in DW. And now I'm a bit confused about choosing with field to build a Dimension Table and whether should I split into many DIM tables For example, I have a Employee Table and it's columns as below:
EmployeeID
FirstName
LastName
State
Age
Department
Position
Skin Color
Hair Color
Sale Amount (Fact)
Emp_DIM table content every column above except Sale Amount
and a Fact table that contains
EmployeeID (Surrogate Key)
Sale Amount (Fact)
Ok, put the Date DIM aside. I want to ask about the Emp_DIM table
According to what I read on the internet, the DIM table should have as many fields as possible, and contain descriptive information. But I'm still confused and not sure.
Should I add Department_DIM
If a Department has other attributes you need to report on, then you should consider splitting it out. This "normalization of dimensions" is also called "snowflaking", or moving from a star schema to a snowflake schema. But it's totally optional and people do it both ways.
Then the Fact_Table will also have DepartmentID
This a separate decision. This is denormalizing by putting non-key dimension attributes directly on your fact table, and it's a performance optimization. You might do this for a few key attributes.