many to many relationship help

View previous topic View next topic Go down

many to many relationship help

Post  yazid on 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

View user profile

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