Technique for managing conformed dimensions with different granularity

Technique for managing conformed dimensions with different granularity

I have two business processes which take measurements at two levels within one conformed dimension.

One business process has information at the city level, and the other business process has information at the state level.

My question is what is the best way to implement this conformed dimension?

Would I be best having a dimension table something like this:

Dimension_Sid	City	State	Country
1	Dover	Delaware	USA
2	Wilmington	Delaware	USA
3	Not recorded	Delaware	USA
4	Montgomery	Alabama	USA
5	Not recorded	Alabama	USA
6	Birmingham	Alabama	USA
7	Not recorded	Illinois	USA
8	Chicago	Illinois	USA
9	Springfield	Illinois	USA

Or would I have two separate but conformed dimension tables like this:

Dimension_Sid	City	State	Country
1	Dover	Delaware	USA
2	Wilmington	Delaware	USA
3	Montgomery	Alabama	USA
4	Birmingham	Alabama	USA
5	Chicago	Illinois	USA
6	Springfield	Illinois	USA

Dimension_Sid	State	Country
1	Delaware	USA
2	Alabama	USA
3	Illinois	USA

Or does it not really matter, or is there a better way?

You can do it either way. Having just a state dimension can sometimes be useful independent of a city, state, zip, msa, etc, type hierarchy. The grain of the fact tables using a dimension doesn't have any impact on whether the dimension is conformed.

Agreed. Conformity is more a matter of the attribute values being consistent rather than using the same physical table.