sql server - Data warehouse design, multiple dimensions or one dimension with attributes? -

working on data warehouse , looking suggestions on having numerous dimensions versus on large dimension attributes.

we have dimentity, dimstation, dimzone, dimgroup, dimcompany , have multiple fact tables contain keys each of dimensions. best way or better have 1 dimension, dimentity , include station, zone, group , company attributes of entity?

we have gone route of separate dimensions our etl isn't work populate , build out star schema issue. performance , maintainability important. these dimensions not change looking guidance on best way handle such dimensions.

fact tables have on 100 million records. entity dimension has around 1000 records , others listed have under 200 each.

without knowing star schema table definitions, data cardinality, etc, it's tough give yes or no. it's going balancing act.

for read performance, fact table should skinny possible , dimension should short (low row count) possible. consolidating dimensions typically means fact table gets skinnier while dimension record count increases.

if can consolidate dimensions without adding significant number of rows consolidated dimension, may worth looking into. may can combine low cardinality dimensions junk dimension , achieve nice balance. dimensions high cardinality attributes shouldn't consolidated.

here's kimball university article on dimensional modeling. addresses centipede fact tables , how recommends using junk dimensions.