1

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

ChristopheCC
  • 97
  • 6

1 Answers1

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

ChristopheCC
  • 97
  • 6
  • why there's no reference to B1 and B2 in your tuple ? you can add them as attributes since you already added H1 and H2. – tuxmobil Jan 20 '16 at 10:33
  • Hi, This is because of the existing datamodel. Currently we have 3 files : - The Hierarchy file, giving me H1, H2 and H3 - The identifier file - The trade file. Currently, they are joined using the cptyID. Therefore for each trade fact, I will have the hierarchy corresponding to that CptyID and only the identifier corresponding to that cptyID. It is indeed doable to enhance my fact to build a hierarchy of identifier, but it would not fully solve my issue as one identifier can be in 2 different hierarchies and would therefore be considered as 2 different members. – ChristopheCC Jan 21 '16 at 02:43
  • do you mean that you want an identifier to be considered as the same member even if it's in 2 different levels / depths / hierarchies ? If yes then you should create an extra hierarchy with only identifiers, else (as you mentioned it) they will be considered as 2 different members. – tuxmobil Jan 21 '16 at 02:55
  • Yes, identifiers should be considered as the same member even if they are on 2 different levels. Creating an extra hierarchy with only identifiers will help to get the value for one identifier using filters (filtering on B1 will give me all the paths containing B1). But it still wont give what the users want, which is a single level identifier dimension. They want to have : B1 - 100 B2 - 100 B3 - 100 One way to do it would be to have a one-level analysis dimension with all the identifiers and query the cube for each of these identifiers, but the performance would be terrible. – ChristopheCC Jan 21 '16 at 05:49
  • 1
    in v5.3 you can have an analysis hierarchy backed by a store, so the members come directly from a store. Another feature that could help is to build an intermediary store in which you create some facts that will help you achieving what you have in mind thanks to the UpdateWhereProcedure which acts like a stored procedure while loading your data. – tuxmobil Jan 21 '16 at 09:23
  • Thanks (Benoit ?) ! I will have a look at it. – ChristopheCC Jan 22 '16 at 03:45