indepndent hierarchies with common shared leafs

View previous topic View next topic Go down

indepndent hierarchies with common shared leafs

Post  asadri on Wed Jun 20, 2012 10:48 am

I have a design question:
There are 3 independent hierarchies. These hierarchies are a combination of fixed and variable hierarchies. All 3 have the same common base leaf. What is the best way to design this structure?


One way I have thought of is to use the bridge table that links to the underlying leaf dimension table:

Fact1
HierarchyADim
HierarchyBDim
HierarchyCDim

HierarchyADim -> HierarchyABridgeDim -> CommonLeafsDim
HierarchyBDim -> HierarchyBBridgeDim -> CommonLeafsDim
HierarchyCDim -> HierarchyCBridgeDim -> CommonLeafsDim


The logical model will show 3 copies of CommonLeafsDim, but physically it is single table

The type of the dimensions have not been determined yet (type 1 or type 2).

asadri

Posts : 1
Join date : 2012-06-20

View user profile

Back to top Go down

Re: indepndent hierarchies with common shared leafs

Post  ngalemmo on Wed Jun 20, 2012 6:54 pm

What do the hierarchy dims represent?

A bridge does not go between two dimensions, they go between a fact and a 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: indepndent hierarchies with common shared leafs

Post  Jeff Smith on Wed Jun 20, 2012 8:37 pm

It's OK to put multiple hiearchies into 1 dimension table. In fact, if the multiple hierarchies share the same lowest level, then it's preferred.

In can be a little tricky if some of the hierarchies are type 1 and some are type 2. If everything is a Type 1, then it's easy. If 2 of the 3 are Type 1 and the 3rd is Type 2, then it's not too bad. If 2 or more are type 2, then it's a pain.

If only one is Type 2, then I would build the dimension as if the Type 2 hierarchy were the single true hierarchy and treat the fields that are part of the Type 1 SCDs as attributes.

If you've got multiple Hierarchies with Type 2 SCDs, then treat all of the changing parts as the business key.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: indepndent hierarchies with common shared leafs

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