Recursive Dimensions in Dimensional Model

An Account is assigned one Industry Classification code as per a Industry Classification Set. Under a different Classification Set, it can be assigned a different Code. E.g. under Classification set SIC, it categorized as Electrical and under another classification set it can be labeled as ‘Lifestyle’.
An industry classification set has multiple Industry classification codes under it. The codes can further have Classification codes under them. Note that there is a recursive hierarchy for Industry Classification.
My Account Fact records transactions for an account.
[img][/img]
One thought that has come to me is to use bridge to model the recursive relationship for ‘Ind. Classification’ and link it to ‘Ind. Classification Dim’ which includes ‘Ind. Classification Set’ flattened. Account Dim and ‘Ind. Classification Dim’ is linked through ‘Account Fact’
[img][/img]

Is this approach acceptable?

Why not have one Industry Dimension with both classifications? It's perfectly OK to have multiple hierarchies in one dimension. if you need to create aggregates using one of the hierarchies, then you can create mini dimensions from the single Industry Dimension.

Using a bridge is fine except you do not represent it correctly in your diagram. The FKs in the bridge should be the dimensional PKs, not the codes. When used it is joined between the dimension table and facts, not the dimension table and itself.

If you have multiple hierarchies on one dimension, you can either implement multiple bridge tables or include a hierarchy type code in a single bridge, to segregate the hierarchies.

ngalemmo :
I felt something is wrong with the FKs in the bridge. So it should be the PKs of dimensions. Or should the parent key be linked to Dimension and the child key be linked to Fact? While representing in ERwin, it drags both the FKs of bridge to the Fact as the PK for the bridge is composite key. So how do we represent this in ERwin?

I have observed another problem with this approach. If the account is classified using say three classifications sets, then the same fact has to repeated thrice(three rows in FACT). This is ok if the value of the fact changes for each classification, but the value of fact does not change for each of the classification, then is it a good design to repeat the same value thrice?

As far as drawing it, you are pretty much stuck with the relationships hanging off the dimension... which is unfortunate, since that is not the way it is used. Running one off the fact doesn't properly represent the model in ERWin, causing other issues. So I tend to leave the bridge off the main star schema and discuss it in a separate diagram when presenting the model to the users.

The fact would just have the basic fact to dimension relationship.

By the way, there is a trick in ERWin to change what FKs propogate to a table. In the logical model, define a key group that contains the column you wish to propogate. You can tag as 'do not generate' if you don't want an actual index. Then drag the relationship. It will move all the PK columns to the target table. Next, edit the relationship properties and in the Rolename panel select the key group you created in the migrated key drop down list. It will remove the extra columns from the target table.

I'm raising this old thread. I currently created a bridge table very similar to how Kimball described ih his book.

My bridge for example Has ParentCostCenterKey and ChildCostCenterKey. I made them both as Foreign Keys from the CostCenter Table.

In SSAS however I wanted to know how best to define relationships between the Bridge and fact table. Should I Simply create a relationship between the ChildKey to the fact table ? Or do I need additional references as well ?

Thanks,

I would not use a bridge table in SSAS. I would either flatten the hierarchy (BIDS Helper can do this for you), or keep it as a parent-child hierarchy within SSAS.