Recursive Dimensions in Dimensional Model

View previous topic View next topic Go down

Recursive Dimensions in Dimensional Model

Post  kiran.mv on Mon Mar 21, 2011 9:30 am

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?


Last edited by kirank on Fri Apr 08, 2011 2:28 am; edited 1 time in total

kiran.mv

Posts : 13
Join date : 2011-03-10

View user profile

Back to top Go down

Re: Recursive Dimensions in Dimensional Model

Post  Jeff Smith on Mon Mar 21, 2011 11:05 am

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Recursive Dimensions in Dimensional Model

Post  ngalemmo on Mon Mar 21, 2011 11:28 am

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Recursive Dimensions in Dimensional Model

Post  kiran.mv on Wed Mar 23, 2011 1:26 am

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?



kiran.mv

Posts : 13
Join date : 2011-03-10

View user profile

Back to top Go down

Re: Recursive Dimensions in Dimensional Model

Post  ngalemmo on Wed Mar 23, 2011 11:49 am

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Recursive Dimensions in Dimensional Model

Post  mru22 on Mon Aug 15, 2011 3:26 pm

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,

mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: Recursive Dimensions in Dimensional Model

Post  John Simon on Mon Aug 15, 2011 7:44 pm

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.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Recursive Dimensions in Dimensional Model

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