Multi-valued attribute that has fact type data

Hi all,

I have a scenario where I have a dimension that stores information about site, lets call it a store. For each store I want to be able to record simple demographic information about each store in the guise of gender and some broad age bands.

What I have done so far is created a junk(?) dim to store all combinations of gender and age band

What I need to do is then record the demographic information against each store in a many to many, which would be fine, however I need to record two "facts" about each demographic combination for each store in the guise of number of people and a percentage of the total population that relates to the store. Both these "facts" ideally need to be additive so I can do roll up calculations within the hierarchy of the store dimension.

This demographic information is only updated about once a year but needs to be available all year round and behave as one would expect when doing time based queries.

I initially went with a bridge table but this doesn't seem to fit the need to provide these facts in an additive manner.

All guidance will be greatly appreciated.

Thanks in advance

Sounds like you've described a demographic fact table. Dimensions are Store, Gender, Age_Band etc.