many to many relationship help
Page 1 of 1
many to many relationship help
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
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
Similar topics
» Need an Entity-Relationship (ER) Diagram (that describe the relationship between entities)
» 1 to many relationship
» Many to Many to Many relationship
» many to many relationship question
» 1:M relationship between Dims?
» 1 to many relationship
» Many to Many to Many relationship
» many to many relationship question
» 1:M relationship between Dims?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum