How can i get my aggregated exposure by identifiers across a hierarchy?

Question

Let's say I have the following data :

Trade Data :

TradeId,CptyID,Exposure
T1     ,    C3,     100
T2     ,    C2,      50
T3     ,    C6,     200

Business Hierarchy Data :

CptyID,L1-Acronym,L2-Acronym,L3-Acronym
C3,        H1,        H2,        H3
C2,        H4,        H5,        H2
C6,        H4,        H5,        H6

ID Mapping :

Acronym,CptyID,Identifier
H1     ,    C1,        B1
H2     ,    C2,        B2
H3     ,    C3,        B3
H4     ,    C4,        B4
H5     ,    C5,        B5
H6     ,    C6,        B6

IE having hierarchies like : level Acronym(Identifier)

 L1     H1(B1)        H4(B4)
 L2     H2(B2)        H5(B5)
 L3     H3(B3)    H2(B2)   H6(B6)
 Trade   T1       T2      T3

I would like to get the exposure by identifiers (B1, B2, B3, B4, B5, B6) where Exp(B1) = Exp(T1), Exp(B2) = Exp(T1)+Exp(T2)...

Joining them together doesn't work. It would give me 3 facts :

TradeID, CptyID, Exposure, L1-Acronym, L2-Acronym, L3-Acronym, Identifier
T1     , C3    ,      100,          H1,        H2,         H3,         B3
T2     , C2    ,       50,          H4,        H5,         H2,         B2
T3     , C6    ,      200,          H4,        H5,         H6,         B6

and give me the wrong results as I would only get the exposures for the identifiers at Level 3 :

Identifier,ResultInLive,ExpectedResult
B1        ,        Null,           100 (Null because I have no facts associated directly to B1)
B2        ,          50,           150
B3        ,         100,           100
B4        ,        Null,           250
B5        ,        Null,           250
B6        ,         200,           200

Another difficulty is that those dimensions can have a lot of members (>300K).

Kind regards,

Christophe

Answer 1

Thanks for your answer !

Each level of my Business Hierarchy data are "entities" which have identifiers.

For instance, lets only consider trade T1, who has an exposure of 100. I have a hierarchy of 3 levels:

1. the first level is H1, which has an identifier = B1
2. the second level is H2, which has an identifier = B2
3. the third and lower level is H3, which has an identifier of B3

The thing we are trying to achieved is to have an identifier dimension with members B1,B2, B3... with the right exposure.

Hence, in this case :

• B3 would have an exposure of 100 coming from T1 => Exposure(B3) = Exposure(T1)
• B2, who is B3 parent, would also have an exposure of 100 coming from T1 => Exposure(B2) = Exposure(T1)
• B1, who is B2 parent, would also have an exposure of 100 coming from T1 => Exposure(B1) = Exposure(T1)

Joining using the cptyId doesnt give us the expected result as the underlying fact would be :

TradeID, CptyID, Exposure, L1-Acronym, L2-Acronym, L3-Acronym, Identifier
T1     , C3    ,      100,          H1,        H2,         H3,         B3

Therefore, in ActivePivot Live, we would see :

Identifier,ResultIn AP Live,ExpectedResult
B1        ,            Null,           100 (Null because there is no facts associated directly to B1)
B2        ,            Null,           100 (Null because there is no facts associated directly to B2)
B3        ,             100,           100 (given by the trade fact)

In the first post, I also wanted to illustrate the fact that the same identifier can be in 2 different hierarchies.

For instance :

L1     H1(B1)        H4(B4)
L2     H2(B2)        H5(B5)
L3     H3(B3)    H2(B2)   H6(B6)
Trade   T1       T2      T3

we can see that B2 is present in at the L2 of the first hierarchy and L3 of the second hierarchy.

Therefore, we would expect to have Exposure(B2) = Exposure (T1) + Exposure (T2) = 150.

Kind regards