Friday, August 19, 2016

How to Report against OpenPages Business Entities (hierarchical data modeling)

Contents
1 Context
2 Sample hierarchy
3 Openpage solutions
3.1 RT_ENTITY
3.2 RT__ENTITY_ENTITY
3.3 RV_ENTITY_GPC
3.4 [Business Entity GPC]
4 Prompt cases
4.1 Case 1 - Only the selected entities
4.2 Case 2 - The selected entity AND the next level
4.3 Case 3 - The selected entity AND all levels below
4.4 Case 4 - The next level BELOW the selected entity
4.5 Case 5 - All levels BELOW the selected entity
5 Report cases
5.1 Case 1 - Only the selected entities – NO aggregation
5.2 Case 2 - Only the selected entities – WITH aggregation – incl. Entity itself
5.3 Case 3 - Only the selected entities – WITH aggregation – NOT incl. Entity itself
5.4 Case 4 - The next level BELOW the selected entity - NO aggregation
5.5 Case 5 - The next level BELOW the selected entity - WITH aggregation – Incl. Entity itself
5.6 Case 6 - The next level BELOW the selected entity - WITH aggregation – NOT Incl. Entity itself
5.7 Case 7 - All levels BELOW the selected entity
6 Appendix
6.1 Loss data in detail with GPC business entity

  1. Context

From business point of view, it is relative easy to understand business entity as hierarchical objects, and each business entity can associate with other risk objects such as loss event. However, to report data based on OpenPages report Framework becomes difficult, as we need to understand the real behavior of this report framework. This document uses a sample data to provide a guideline of report development based on OpenPages report framework with respect to business entity. 5 prompt cases and 7 report cases will be documented.
  1. Sample hierarchy

In order to explain the concept, a sample data is provided as below. Assume loss with red color is actual loss for that node, while the total loss is shown as green color.
  1. Openpage solutions

Behind the scene, IBM Openpages create report framework as below


Report Framework
[DEFAULT (Relational)].[Business Entity GPC]
Report Views
RV_ENTITY_GPC
Report Tables
RT_ENTITY, RT__ENTITY_ENTITY
Basic data table
RESOURCES, RESRELATIONSHIPS, ASSETTYPES, etc


    1. RT_ENTITY



ENTITY_ID
FULL_PATH
1
/Node1
2
/Node1/Node2
3
/Node1/Node3
4
/Node1/Node2/Node4
5
/Node1/Node2/Node5
6
/Node1/Node2/Node6
7
/Node1/Node2/Node5/Node7
8
/Node1/Node2/Node5/Node8


    1. RT__ENTITY_ENTITY



P_ENTITY_ID
C_ENTITY_ID
DISTANCE
1
1
0
1
2
1
1
3
1
1
4
2
1
5
2
1
6
2
1
7
3
1
8
3
2
2
0
2
4
1
3
3
0
3
5
1
3
6
1
3
7
2
3
8
2
4
4
0
5
5
0
5
7
1
5
8
1
6
6
0
7
7
0
8
8
0




    1. RV_ENTITY_GPC



RT__ENTITY_ENTITY

RT__ENTITY_ENTITY (Distance = 0,1)

RV_ENTITY_GPC
P_ENTITY_ID
C_ENTITY_ID
DISTANCE

P_ENTITY_ID
C_ENTITY_ID
DISTANCE

G_ENTITY_ID
P_ENTITY_ID
G2P_DISTANCE
C_ENTITY_ID
P2C_DISTANCE
1
1
0

1
1
0

1
1
0
1
1
1
2
1

1
2
1

1
2
1
2
1
1
3
1

1
3
1

1
2
1
4
1
1
4
2

2
2
0

1
3
1
3
1
1
5
2

2
4
1

1
3
1
5
1
1
6
2

3
3
0

1
3
1
6
1
1
7
3

3
5
1

1
3
1
7
1
1
8
3

3
6
1

1
3
1
8
1
2
2
0

4
4
0

2
2
0
2
2
2
4
1

5
5
0

2
4
1
4
2
3
3
0

5
7
1

3
3
0
3
3
3
5
1

5
8
1

3
5
1
5
3
3
6
1

6
6
0

3
5
1
7
3
3
7
2

7
7
0

3
5
1
8
3
3
8
2

8
8
0

3
6
1
6
3
4
4
0





4
4
0
4
4
5
5
0





5
5
0
5
5
5
7
1





5
7
1
7
5
5
8
1





5
8
1
8
5
6
6
0





6
6
0
6
6
7
7
0





7
7
0
7
7
8
8
0





8
8
0
8
8




    1. [Business Entity GPC]



#
G_ENTITY_ID
G_ENTITY_FULL_PATH
P_ENTITY_ID
P_ENTITY_FULL_PATH
G2P_DISTANCE
C_ENTITY_ID
C_ENTITY_FULL_PATH
P2C_DISTANCE
1
1
/Node1
1
/Node1
0
1
/Node1
0
2
1
/Node1
2
/Node1/Node2
1
2
/Node1/Node2
0
3
1
/Node1
2
/Node1/Node2
1
4
/Node1/Node2/Node4
1
4
1
/Node1
3
/Node1/Node3
1
3
/Node1/Node3
0
5
1
/Node1
3
/Node1/Node3
1
5
/Node1/Node3/Node5
1
6
1
/Node1
3
/Node1/Node3
1
6
/Node1/Node3/Node6
1
7
1
/Node1
3
/Node1/Node3
1
7
/Node1/Node3/Node5/Node7
2
8
1
/Node1
3
/Node1/Node3
1
8
/Node1/Node3/Node5/Node8
2
9
2
/Node1/Node2
2
/Node1/Node2
0
2
/Node1/Node2
0
10
2
/Node1/Node2
4
/Node1/Node2/Node4
1
4
/Node1/Node2/Node4
0
11
3
/Node1/Node3
3
/Node1/Node3
0
3
/Node1/Node3
0
12
3
/Node1/Node3
5
/Node1/Node3/Node5
1
5
/Node1/Node3/Node5
0
13
3
/Node1/Node3
5
/Node1/Node3/Node5
1
7
/Node1/Node3/Node5/Node7
1
14
3
/Node1/Node3
5
/Node1/Node3/Node5
1
8
/Node1/Node3/Node5/Node8
1
15
3
/Node1/Node3
6
/Node1/Node3/Node6
1
6
/Node1/Node3/Node6
0
16
4
/Node1/Node2/Node4
4
/Node1/Node2/Node4
0
4
/Node1/Node2/Node4
0
17
5
/Node1/Node3/Node5
5
/Node1/Node3/Node5
0
5
/Node1/Node3/Node5
0
18
5
/Node1/Node3/Node5
7
/Node1/Node3/Node5/Node7
1
7
/Node1/Node3/Node5/Node7
0
19
5
/Node1/Node3/Node5
8
/Node1/Node3/Node5/Node8
1
8
/Node1/Node3/Node5/Node8
0
20
6
/Node1/Node3/Node6
6
/Node1/Node3/Node6
0
6
/Node1/Node3/Node6
0
21
7
/Node1/Node3/Node5/Node7
7
/Node1/Node3/Node5/Node7
0
7
/Node1/Node3/Node5/Node7
0
22
8
/Node1/Node3/Node5/Node8
8
/Node1/Node3/Node5/Node8
0
8
/Node1/Node3/Node5/Node8
0


  1. Prompt cases

    1. Case 1 - Only the selected entities

Filter
G2P_DISTANCE = 0
G_ENTITY_FULL_PATH in ('/Node1','/Node1/Node2')
Query Items
G_ENTITY_ID
G_ENTITY_FULL_PATH
Result


G_ENTITY_ID
G_ENTITY_FULL_PATH
1
/Node1
2
/Node1/Node2


    1. Case 2 - The selected entity AND the next level

Filter
P2C_DISTANCE = 0
G_ENTITY_FULL_PATH in ('/Node1')
Query Items
P_ENTITY_ID
P_ENTITY_FULL_PATH
Result


P_ENTITY_ID
P_ENTITY_FULL_PATH
1
/Node1
2
/Node1/Node2
3
/Node1/Node3


    1. Case 3 - The selected entity AND all levels below

Filter
G_ENTITY_FULL_PATH in ('/Node1')
Query Items
C_ENTITY_ID
C_ENTITY_FULL_PATH
Result


C_ENTITY_ID
C_ENTITY_FULL_PATH
1
/Node1
2
/Node1/Node2
3
/Node1/Node3
4
/Node1/Node2/Node4
5
/Node1/Node3/Node5
6
/Node1/Node3/Node6
7
/Node1/Node3/Node5/Node7
8
/Node1/Node3/Node5/Node8


    1. Case 4 - The next level BELOW the selected entity

Filter
G2P_DISTANCE = 1
P2C_DISTANCE = 0
G_ENTITY_FULL_PATH in ('/Node1')
Query Items
P_ENTITY_ID
P_ENTITY_FULL_PATH
Result


P_ENTITY_ID
P_ENTITY_FULL_PATH
2
/Node1/Node2
3
/Node1/Node3


    1. Case 5 - All levels BELOW the selected entity

Filter
G2P_DISTANCE = 1
G_ENTITY_FULL_PATH in ('/Node1')
Query Items
C_ENTITY_ID
C_ENTITY_FULL_PATH
Result


C_ENTITY_ID
C_ENTITY_FULL_PATH
2
/Node1/Node2
3
/Node1/Node3
4
/Node1/Node2/Node4
5
/Node1/Node3/Node5
6
/Node1/Node3/Node6
7
/Node1/Node3/Node5/Node7
8
/Node1/Node3/Node5/Node8


  1. Report cases

Sample below is based on data (s.  Sample hierarchy for detail )


ENTITY
ENTITY_FULL_PATH
Loss
3
/Node1/Node3
$2
4
/Node1/Node2/Node4
$1
7
/Node1/Node3/Node5/Node7
$3
All these loss is associated with Child Entity based on OpenPages Framework in term of modelling, while loss can happen to any entity from view of end users.


    1. Case 1 - Only the selected entities – NO aggregation

Filter
G2P_DISTANCE = 0
G_ENTITY_FULL_PATH in ('/Node1','/Node1/Node2')
Query Items
G_ENTITY_ID
G_ENTITY_FULL_PATH
Result


G_ENTITY_ID
G_ENTITY_FULL_PATH
Loss
1
/Node1
$0
2
/Node1/Node2
$0
Actually Cognos will display no data
Behind the scene
select G_ENTITY_ID, G_ENTITY_FULL_PATH, sum(Loss) from GPC
where G2P_DISTANCE = 0 and G_ENTITY_FULL_PATH in ('/Node1','/Node1/Node2')
group by G_ENTITY_ID, G_ENTITY_FULL_PATH


#
G_ENTITY_ID
G_ENTITY_FULL_PATH
P_ENTITY_ID
P_ENTITY_FULL_PATH
G2P_DISTANCE
C_ENTITY_ID
C_ENTITY_FULL_PATH
P2C_DISTANCE
Loss
1
1
/Node1
1
/Node1
0
1
/Node1
0

9
2
/Node1/Node2
2
/Node1/Node2
0
2
/Node1/Node2
0



    1. Case 2 - Only the selected entities – WITH aggregation – incl. Entity itself

Filter
G_ENTITY_FULL_PATH in ('/Node1','/Node1/Node2')
Query Items
G_ENTITY_ID
G_ENTITY_FULL_PATH
Result


G_ENTITY_ID
G_ENTITY_FULL_PATH
Loss
1
/Node1
$6
2
/Node1/Node2
$1


Behind the scene
select G_ENTITY_ID, G_ENTITY_FULL_PATH, sum(Loss) from GPC
where G_ENTITY_FULL_PATH in ('/Node1','/Node1/Node2')
group by G_ENTITY_ID, G_ENTITY_FULL_PATH


#
G_ENTITY_ID
G_ENTITY_FULL_PATH
P_ENTITY_ID
P_ENTITY_FULL_PATH
G2P_DISTANCE
C_ENTITY_ID
C_ENTITY_FULL_PATH
P2C_DISTANCE
Loss
1
1
/Node1
1
/Node1
0
1
/Node1
0

2
1
/Node1
2
/Node1/Node2
1
2
/Node1/Node2
0

3
1
/Node1
2
/Node1/Node2
1
4
/Node1/Node2/Node4
1
$1
4
1
/Node1
3
/Node1/Node3
1
3
/Node1/Node3
0
$2
5
1
/Node1
3
/Node1/Node3
1
5
/Node1/Node3/Node5
1

6
1
/Node1
3
/Node1/Node3
1
6
/Node1/Node3/Node6
1

7
1
/Node1
3
/Node1/Node3
1
7
/Node1/Node3/Node5/Node7
2
$3
8
1
/Node1
3
/Node1/Node3
1
8
/Node1/Node3/Node5/Node8
2

9
2
/Node1/Node2
2
/Node1/Node2
0
2
/Node1/Node2
0

10
2
/Node1/Node2
4
/Node1/Node2/Node4
1
4
/Node1/Node2/Node4
0
$1


    1. Case 3 - Only the selected entities – WITH aggregation – NOT incl. Entity itself

Filter
G2P_DISTANCE = 1
G_ENTITY_FULL_PATH in ('/Node1','/Node1/Node2')
Query Items
G_ENTITY_ID
G_ENTITY_FULL_PATH
Result


G_ENTITY_ID
G_ENTITY_FULL_PATH
Loss
1
/Node1
$6
2
/Node1/Node2
$1


Behind the scene
select G_ENTITY_ID, G_ENTITY_FULL_PATH, sum(Loss) from GPC
where G2P_DISTANCE = 1 and G_ENTITY_FULL_PATH in ('/Node1','/Node1/Node2')
group by G_ENTITY_ID, G_ENTITY_FULL_PATH


#
G_ENTITY_ID
G_ENTITY_FULL_PATH
P_ENTITY_ID
P_ENTITY_FULL_PATH
G2P_DISTANCE
C_ENTITY_ID
C_ENTITY_FULL_PATH
P2C_DISTANCE
Loss
2
1
/Node1
2
/Node1/Node2
1
2
/Node1/Node2
0

3
1
/Node1
2
/Node1/Node2
1
4
/Node1/Node2/Node4
1
$1
4
1
/Node1
3
/Node1/Node3
1
3
/Node1/Node3
0
$2
5
1
/Node1
3
/Node1/Node3
1
5
/Node1/Node3/Node5
1

6
1
/Node1
3
/Node1/Node3
1
6
/Node1/Node3/Node6
1

7
1
/Node1
3
/Node1/Node3
1
7
/Node1/Node3/Node5/Node7
2
$3
8
1
/Node1
3
/Node1/Node3
1
8
/Node1/Node3/Node5/Node8
2

10
2
/Node1/Node2
4
/Node1/Node2/Node4
1
4
/Node1/Node2/Node4
0
$1


    1. Case 4 - The next level BELOW the selected entity - NO aggregation

Filter
G2P_DISTANCE = 1
P2C_DISTANCE = 0
G_ENTITY_FULL_PATH in ('/Node1')
Query Items
P_ENTITY_ID
P_ENTITY_FULL_PATH
Result


P_ENTITY_ID
P_ENTITY_FULL_PATH
Loss
2
/Node1/Node2
$0
3
/Node1/Node3
$2


Behind the scene
select P_ENTITY_ID, P_ENTITY_FULL_PATH, sum(Loss) from GPC
where G2P_DISTANCE = 1 and P2C_DISTANCE = 0 and G_ENTITY_FULL_PATH in ('/Node1')    
group by P_ENTITY_ID, P_ENTITY_FULL_PATH


#
G_ENTITY_ID
G_ENTITY_FULL_PATH
P_ENTITY_ID
P_ENTITY_FULL_PATH
G2P_DISTANCE
C_ENTITY_ID
C_ENTITY_FULL_PATH
P2C_DISTANCE
Loss
2
1
/Node1
2
/Node1/Node2
1
2
/Node1/Node2
0

4
1
/Node1
3
/Node1/Node3
1
3
/Node1/Node3
0
$2


    1. Case 5 - The next level BELOW the selected entity - WITH aggregation – Incl. Entity itself

Filter
G2P_DISTANCE = 1
G_ENTITY_FULL_PATH in ('/Node1')
Query Items
P_ENTITY_ID
P_ENTITY_FULL_PATH
Result


P_ENTITY_ID
P_ENTITY_FULL_PATH
Loss
2
/Node1/Node2
$1
3
/Node1/Node3
$5


Behind the scene
select P_ENTITY_ID, P_ENTITY_FULL_PATH, sum(Loss) from GPC
where G2P_DISTANCE = 1 and G_ENTITY_FULL_PATH in ('/Node1')    
group by P_ENTITY_ID, P_ENTITY_FULL_PATH


#
G_ENTITY_ID
G_ENTITY_FULL_PATH
P_ENTITY_ID
P_ENTITY_FULL_PATH
G2P_DISTANCE
C_ENTITY_ID
C_ENTITY_FULL_PATH
P2C_DISTANCE
Loss
2
1
/Node1
2
/Node1/Node2
1
2
/Node1/Node2
0

3
1
/Node1
2
/Node1/Node2
1
4
/Node1/Node2/Node4
1
$1
4
1
/Node1
3
/Node1/Node3
1
3
/Node1/Node3
0
$2
5
1
/Node1
3
/Node1/Node3
1
5
/Node1/Node3/Node5
1

6
1
/Node1
3
/Node1/Node3
1
6
/Node1/Node3/Node6
1

7
1
/Node1
3
/Node1/Node3
1
7
/Node1/Node3/Node5/Node7
2
$3
8
1
/Node1
3
/Node1/Node3
1
8
/Node1/Node3/Node5/Node8
2



    1. Case 6 - The next level BELOW the selected entity - WITH aggregation – NOT Incl. Entity itself

Filter
G2P_DISTANCE = 1
P2C_DISTANCE <> 0
G_ENTITY_FULL_PATH in ('/Node1')
Query Items
P_ENTITY_ID
P_ENTITY_FULL_PATH
Result


P_ENTITY_ID
P_ENTITY_FULL_PATH
Loss
2
/Node1/Node2
$1
3
/Node1/Node3
$3


Behind the scene
select P_ENTITY_ID, P_ENTITY_FULL_PATH, sum(Loss) from GPC
where G2P_DISTANCE = 1 and P2C_DISTANCE <> 0 and G_ENTITY_FULL_PATH in ('/Node1')    
group by P_ENTITY_ID, P_ENTITY_FULL_PATH


#
G_ENTITY_ID
G_ENTITY_FULL_PATH
P_ENTITY_ID
P_ENTITY_FULL_PATH
G2P_DISTANCE
C_ENTITY_ID
C_ENTITY_FULL_PATH
P2C_DISTANCE
Loss
3
1
/Node1
2
/Node1/Node2
1
4
/Node1/Node2/Node4
1
$1
5
1
/Node1
3
/Node1/Node3
1
5
/Node1/Node3/Node5
1

6
1
/Node1
3
/Node1/Node3
1
6
/Node1/Node3/Node6
1

7
1
/Node1
3
/Node1/Node3
1
7
/Node1/Node3/Node5/Node7
2
$3
8
1
/Node1
3
/Node1/Node3
1
8
/Node1/Node3/Node5/Node8
2



    1. Case 7 - All levels BELOW the selected entity

Filter
G2P_DISTANCE = 1
G_ENTITY_FULL_PATH in ('/Node1')
Query Items
C_ENTITY_ID
C_ENTITY_FULL_PATH
Result


C_ENTITY_ID
C_ENTITY_FULL_PATH
Loss
3
/Node1/Node3
$2
4
/Node1/Node2/Node4
$1
7
/Node1/Node3/Node5/Node7
$3


Behind the scene
select C_ENTITY_ID, C_ENTITY_FULL_PATH, sum(Loss) from GPC
where G2P_DISTANCE = 1 and G_ENTITY_FULL_PATH in ('/Node1')    
group by C_ENTITY_ID, C_ENTITY_FULL_PATH


#
G_ENTITY_ID
G_ENTITY_FULL_PATH
P_ENTITY_ID
P_ENTITY_FULL_PATH
G2P_DISTANCE
C_ENTITY_ID
C_ENTITY_FULL_PATH
P2C_DISTANCE
Loss
2
1
/Node1
2
/Node1/Node2
1
2
/Node1/Node2
0

3
1
/Node1
2
/Node1/Node2
1
4
/Node1/Node2/Node4
1
$1
4
1
/Node1
3
/Node1/Node3
1
3
/Node1/Node3
0
$2
5
1
/Node1
3
/Node1/Node3
1
5
/Node1/Node3/Node5
1

6
1
/Node1
3
/Node1/Node3
1
6
/Node1/Node3/Node6
1

7
1
/Node1
3
/Node1/Node3
1
7
/Node1/Node3/Node5/Node7
2
$3
8
1
/Node1
3
/Node1/Node3
1
8
/Node1/Node3/Node5/Node8
2



  1. Appendix

    1. Loss data in detail with GPC business entity



#
G_ENTITY_ID
G_ENTITY_FULL_PATH
P_ENTITY_ID
P_ENTITY_FULL_PATH
G2P_DISTANCE
C_ENTITY_ID
C_ENTITY_FULL_PATH
P2C_DISTANCE
Loss
1
1
/Node1
1
/Node1
0
1
/Node1
0

2
1
/Node1
2
/Node1/Node2
1
2
/Node1/Node2
0

3
1
/Node1
2
/Node1/Node2
1
4
/Node1/Node2/Node4
1
$1
4
1
/Node1
3
/Node1/Node3
1
3
/Node1/Node3
0
$2
5
1
/Node1
3
/Node1/Node3
1
5
/Node1/Node3/Node5
1

6
1
/Node1
3
/Node1/Node3
1
6
/Node1/Node3/Node6
1

7
1
/Node1
3
/Node1/Node3
1
7
/Node1/Node3/Node5/Node7
2
$3
8
1
/Node1
3
/Node1/Node3
1
8
/Node1/Node3/Node5/Node8
2

9
2
/Node1/Node2
2
/Node1/Node2
0
2
/Node1/Node2
0

10
2
/Node1/Node2
4
/Node1/Node2/Node4
1
4
/Node1/Node2/Node4
0
$1
11
3
/Node1/Node3
3
/Node1/Node3
0
3
/Node1/Node3
0
$2
12
3
/Node1/Node3
5
/Node1/Node3/Node5
1
5
/Node1/Node3/Node5
0

13
3
/Node1/Node3
5
/Node1/Node3/Node5
1
7
/Node1/Node3/Node5/Node7
1
$3
14
3
/Node1/Node3
5
/Node1/Node3/Node5
1
8
/Node1/Node3/Node5/Node8
1

15
3
/Node1/Node3
6
/Node1/Node3/Node6
1
6
/Node1/Node3/Node6
0

16
4
/Node1/Node2/Node4
4
/Node1/Node2/Node4
0
4
/Node1/Node2/Node4
0
$1
17
5
/Node1/Node3/Node5
5
/Node1/Node3/Node5
0
5
/Node1/Node3/Node5
0

18
5
/Node1/Node3/Node5
7
/Node1/Node3/Node5/Node7
1
7
/Node1/Node3/Node5/Node7
0
$3
19
5
/Node1/Node3/Node5
8
/Node1/Node3/Node5/Node8
1
8
/Node1/Node3/Node5/Node8
0

20
6
/Node1/Node3/Node6
6
/Node1/Node3/Node6
0
6
/Node1/Node3/Node6
0

21
7
/Node1/Node3/Node5/Node7
7
/Node1/Node3/Node5/Node7
0
7
/Node1/Node3/Node5/Node7
0
$3
22
8
/Node1/Node3/Node5/Node8
8
/Node1/Node3/Node5/Node8
0
8
/Node1/Node3/Node5/Node8
0