Hi Everyone

I have a need to create an integrated Geography Dimension in my EDW
layer. I have a need to analyze by State, MSA, County and Zipcode. I
cannot do State --> MSA --> County --> Zip because it is not a clean
hierarchy. Does anyone have any idea about structuring a geography
dimension ?

Try using "role playing" dimensions where you source more than 1
logical MS AS dimension from 1 physical table. You will need a fully
denormalized table with these values in separate fields. This approach
can result in an empty grid if you select conflicting values from the
two role playing dimensions. For example: if you select Zipcode=90210
in one dimension and MSA=Midwest in the other your selection will
result in an empty grid.

This is the way I have implemented levels that do not flow cleanly into
one another. Weeks is another good example, they do not flow cleanly
into months or quarters.

Good luck,
Sean

Thanks Sean for your reply

I am using an Oracle DB with Microstrategy as the front end reporting
tool, hence 'role playing' dimensions would have to be incorporated at
the DB level, dont have the luxury of using MS Analysis Services. I
like your concept though, can you give some examples using a database
table ?

Regards,
Ujval