multiple hierarchy : single dimension vs multiple

Hi
Need to analysis sale revenue by store.
We have two separate hierarchy for geographic location
1. Country / County / Council / Store name
2. Country / Region / Area / Store name
Need valuable suggestion for how to design dimension for above to hierarchy, as there are two path for country to store name

Regards

The easiest thing to do would be to store country, county, council, region, and area as attributes on the store dimension.

GBS74 wrote:Hi
Need to analysis sale revenue by store.
We have two separate hierarchy for geographic location
1. Country / County / Council / Store name
2. Country / Region / Area / Store name
Need valuable suggestion for how to design dimension for above to hierarchy, as there are two path for country to store name

Regards

You can create generic hierarchy dimension with hierarchy name and buckets , say level1 ... level10, to hold different levels.

e.g.
*base key| *hierarchy key| hierarchy name | level1 | ......
1 | 1 | store-council | store1 |
1 | 2 | store-area | store1 |

*PK

(base key + hierarchy key) will go as FK to the fact. You can switch between alternate hierarchies by joining fact and dimension on base key and creating 2 separate dimension alias at store-council / store-area level by filtering on that hierarchy name ( store-council / store-area ).

This table can be used to store other hierarchies as well. You may extend this to have more than 10 levels depending on your requirement.