CODEWITHSUNDEEP
data-modelingdata-warehouse
Carl
Carl

Reputation: 1371

Link fact tables at different granularity levels of a dimension

New to data warehouse design. I have a denormalised dimension table representing geographies (e.g. suburb, city, state). This is a slowly changing dimension.

Also have multiple fact tables, each at different grain levels.

Is it possible to model this so the fact tables use surrogate keys, whilst maintaining a denormalised dimension table?

data model

Upvotes: 2

Views: 3038

Answers (1)

NickW
NickW

Reputation: 9768

If you have effectively the same dimensional data but at different grains then you handle this by creating "aggregate" dimensions. In your example, copy the dim_geo table definition (not the data), name the dim to something like dim_geo_city and drop all the columns at a lower granularity than city (e.g. suburb_id, suburb). If you have facts at the state level then you would create dim_geo_state in the same way - and so on for any further levels of aggregation.

Fact_population will continue to reference dim_geo but fact_housing should reference dim_geo_city.

The easiest way to populate aggregate dims is to run a SELECT DISTINCT on the base dim (dim_geo) and only include the columns that exist in the target dim (dim_geo_city) - you then take the resulting data and apply the appropriate SCD logic to insert/update it into the target dim.

enter image description here

Upvotes: 2

Related Questions