indepndent hierarchies with common shared leafs

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).

What do the hierarchy dims represent?

A bridge does not go between two dimensions, they go between a fact and a dimension.

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.