multiple hierarchy : single dimension vs multiple

View previous topic View next topic Go down

multiple hierarchy : single dimension vs multiple

Post  GBS74 on Thu May 06, 2010 5:29 am

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

GBS74

Posts : 4
Join date : 2009-07-29

View user profile

Back to top Go down

Re: multiple hierarchy : single dimension vs multiple

Post  ngalemmo on Thu May 06, 2010 11:23 am

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: multiple hierarchy : single dimension vs multiple

Post  da_2030 on Mon May 10, 2010 3:24 pm

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.

da_2030

Posts : 5
Join date : 2010-05-10

View user profile

Back to top Go down

Re: multiple hierarchy : single dimension vs multiple

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum