Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

many to many relationship help

Go down

many to many relationship help Empty many to many relationship help

Post  yazid Fri Mar 25, 2016 1:47 pm

Hi everyone,

i hope you are doing well. i have to find a way for modeling the following case :

i have to be able to retrieve the weights (my measure) of a portfolio and it's constiuants having the following hierarchy :

portfolio --> index --> underlying (lowest level of granularity)

the problem is that i can have an index composed by other indexes and the on 4 levels, for example :

portfolio (p1)
-->index (idx1)
--> underlying(u1)
-->index (idx2)
--> index(idx1)
-->underlying(u1)

i have actually a fact table (Fact1) that is linked with an Object dimension (that dimension contains portfolios,indexes, underlyings and other types of objects).
portfolio composition is changing every day and also the same for indexes.
my fact table is as following :

FK_OBJECT_PARENT FK_OBJECT_CHILD FK_DATE WEIGHT
p1 idx1 20160301 0.5
idx1 u1 20160301 0.3
p1 idx2 20160301 0.6

as i said the portfolio composition is changing everyday so the next day we can have :

FK_OBJECT_PARENT FK_OBJECT_CHILD FK_DATE WEIGHT
p1 idx1 20160302 0.2
idx1 u3(new) 20160302 0.3
p1 idx2 20160302 0.6

So i would like to be able to display the weight using the above hierarchy.
First i added to FKs pointing on the same Object dimension in order to put in place the parent child hierarchy. is that good?
Then, i also have to be able to drill down on 4 levels.
Due to fact that the portfolio composition is changing everyday, i cannot store that in a dimension (the dimension would grow as fast as the fact table and this is not good)

Any idea please?

Many thanks

yazid

Posts : 1
Join date : 2016-03-03

Back to top Go down

Back to top

- Similar topics

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