I am looking for the right approach how to implement group/member like relationship in the cube dimension. i have attached the smaller version of my tables currently I have. Table-1 is SecurityTable, Table-2 is the CompositeSecurity table and Table-3 is PositionHolding Table.
CompositeSecurity is also Security that presents in Security table. but main difference is they contain another Securities and/or CompositeSecurities with their weight respect to parent.
for example
In Table-1 IBM,MSFT,My1,My2,My3 are listed Securities.
But My1,My2 are also listed in Table-2 under ParentID so they are ComositeSecurity.
ParentID=3 which is CompositeSecurity "My1" made up of 40%-IBM and 60%-MSFT
ParentID=4 which is CompositeSecurity "My2" made up of 20%-IBM and 80%-My1 Again "My1" is CompositeSecurity so We need to break down it until we reach the leaf level.
In the Table-3, I have current holding by Security.
in the result what user want to see is, when he query for total Quantity for any Security, if that security is CompositeSecurity then break down the composite in to leaf level Securities and find the respective Quantiy value using the percentage Weight available.
For Example "What is My Total IBM Holding?"
Table-3 has
IBM as Direct holding of 100
MSFT is not a CompositeSecurity.
My1 is CompositeSecuriy and My1 has Direct Holding of IBM as 40% of My1.
My2 is CompositeSecurity and My2 has Direct Holding of IBM as 20% of My2 My also has aother CompositeSecurity My1 as 80% of My2
So membership link is My2-->My1-->IBM with respective weight of 100%-->802%-->40%
I have attached the sample result how the break down should come up on cube when user select Quantity as measure and IBM on rows.
What I need help with is how do I design this Security and CompositeSecuity as Dimension so that I can achieve the break down. I do have currently Secutity DIM and Position fact table with Table-1 and Table-3. now I have to introduced Table-2 in this.
things that have thought of so far
1) create a new combine view from t-1 and t-2 for new DIM. the probelm with this is, Security DIM is link to FACT with ID column and when I join table-1 and Table-2 i will have repeated ID values so PK on Security DIM is not possible.
2) tried to design table-1 and table-2 as Parent/Child DIM but not able to complete.
