Thursday, December 1, 2016

How does suppression work with DMR on CQM or DQM


Overview

There are 4 cases listed below

Example #
Suppress Property
Model Ordering
Compatible Query Mode
Dynamic Query Mode
Case 1
None
None
Nulls suppressed
No suppression
Case 2
None
OLAP
No suppression
No suppression
Case 3
Nulls or (Default)
None
Nulls suppressed
Nulls suppressed
Case 4
Nulls or (Default)
OLAP
No suppression
Nulls suppressed

Suppress Property

pane of Report Studio with a property called Suppress that can have one of three states:

·         (Default), when the property value is not set, implying data-provider-dependent behaviour with respect to null suppression

·         None, implying that no values are suppressed from report output

·         Nulls, implying that null values are suppressed
In Compatible Query Mode, however, the Suppress query property is ignored for a report based on a DMR package.
Model Ordering


Tuesday, November 1, 2016

How to make Cognos connection dynamic based on selected environment

Assuming that we have DEV,SIT and UAT, we would like to connect Cognos with different databases based on selected environment. Certainly, we want framework to be same for all three different environments.  This document will demonstrate a practical solution with Netezza, where framework manager connect with MULTIPLE databases.

 Environment in detail
There are three different environments: DEV, SIT and UAT with single Netezza server
Each environment has one main database and multiple other databases.

DEV: MainDB_DEV, OtherDB1_DEV, OtherDB2_DEV and OtherDBn_DEV
SIT: MainDB_SIT, OtherDB1_SIT, OtherDB2_SIT and OtherDBn_SIT
UAT: MainDB_UAT, OtherDB1_UAT, OtherDB2_UAT and OtherDBn_UAT

Objective
To share the same report portal, same framework manager and connect to different databases based on user selection.
In other word, when user selects DEV, then Framework Manager Package will point to MainDB_DEV, OtherDB1_DEV, OtherDB2_DEV and OtherDBn_DEV. when user selects SIT, then Framework Manager Package will point to MainDB_SIT, OtherDB1_SIT, OtherDB2_SIT and OtherDBn_SIT.
 
Analysis
Two typical solutions below are not applicable for this situation:
How to prompt for selection of data source and/or schema (https://www-304.ibm.com/support/docview.wss?uid=swg21374654)
Dynamically change the data source based on logged in user (without SDK) (https://www-304.ibm.com/support/docview.wss?uid=swg21342840)
The main challenge is that there are multiple databases with each environment, it is not single database.

 Solution
Step1:
Make Cognos functional ID (Cognos User ID, password) can access all databases, including MainDB_DEV, OtherDB1_DEV, OtherDB2_DEV and OtherDBn_DEV, and all databases for SIT and UAT.

Step2:
Make a single Cognos data source called [Cognos Data Source] for the sake of explanation. Then make three real Netezza connections under [Cognos Data Source] to [MainDB_DEV], [MainDB_SIT] and [MainDB_UAT]. This gives users a possibility to choose what environment to use:  [MainDB_DEV], [MainDB_SIT] and [MainDB_UAT]

Make Database connection in framework manager as [Cognos Connection] point to [Cognos Data Source].
 

Step3:
Create a new Table OTHER_DB_MAP (DB_NAME_KEY, DB_NAME_VALUE) into MainDB and insert data as below

MainDB_DEV:
                DB_NAME_KEY = OtherDB1; DB_NAME_VALUE = OtherDB1_DEV
                DB_NAME_KEY = OtherDB2; DB_NAME_VALUE = OtherDB2_DEV
                DB_NAME_KEY = OtherDBn; DB_NAME_VALUE = OtherDBn_DEV
MainDB_SIT:
                DB_NAME_KEY = OtherDB1; DB_NAME_VALUE = OtherDB1_SIT
                DB_NAME_KEY = OtherDB2; DB_NAME_VALUE = OtherDB2_SIT
                DB_NAME_KEY = OtherDBn; DB_NAME_VALUE = OtherDBn_SIT
MainDB_UAT:
                DB_NAME_KEY = OtherDB1; DB_NAME_VALUE = OtherDB1_UAT
                DB_NAME_KEY = OtherDB2; DB_NAME_VALUE = OtherDB2_UAT
                DB_NAME_KEY = OtherDBn; DB_NAME_VALUE = OtherDBn_UAT
Step4:
Add this table into data layer with select * from [Cognos Connection].[OTHER_DB_MAP]
And then make it available in parameters map.
 
Step5:
Model query objects from MainDB at data tier as below
Select * from [Cognos Connection].[TableName]
Please note that scheme is specified in framework manager

Model query objects from OtherDB at data tier as below
Select * from #$OTHER_DB_MAP{' OtherDB1'}#.[schemeName].[TableName]
Select * from #$OTHER_DB_MAP{' OtherDB2'}#.[schemeName].[TableName]
Select * from #$OTHER_DB_MAP{' OtherDBn'}#.[schemeName].[TableName]

Notes
This solution is just for a practical situation, where all tables from other database are used as single table exposed in framework.  However, if these tables from other databases have to join with main database tables, it is not good solution, as it will impair the performance. The best solution in this case would be loads all these data into the main database, so that tables can be joined to get better performance.
Please also make sure that Catalog is NOT specified

Tuesday, October 11, 2016

How to make column chart with breaks – update – with high performance

This report is based on Ex post How to make column chart with breaks against ORACLE database.  It is intended to figure out solution to develop this report with best performance.


Sort by SortGroup, Sort Year, xCategory

1
2
3
4
5
6
7
8
9
10
11
12
SortGroup
1
1
1
2
3
3
3
3
3
4
5
5
Year
2013
2014
2015
S1
2014
2014
2014
2014
2015
S2
2014
2015
xCategory
2013
2014
2015
S1
Q1 2014
Q2 2014
Q3 2014
Q4 2014
Q1 2015
S2
YTD 2014
YTD 2015


Filters: (Do not use cast, as it will use local process)
3 Year
to_char(to_number(nvl([Year data item], 0))+1)=?p_year?  OR
to_char(to_number(nvl([Year data item], 0))+2)=?p_year? OR
to_char(to_number(nvl([Year data item], 0))+3)=?p_year?


5 Quarters
( to_char(to_number(nvl([Year data item], 0))+0)=?p_year? and
[Quarter data item] <=?p_quarter? )
OR
( to_char(to_number(nvl([Year data item], 0))+1)=?p_year? and
[Quarter data item] >=?p_quarter? )


2 YTDs
( to_char(to_number(nvl([Year data item], 0))+0)=?p_year? and
[Quarter data item] <=?p_quarter? )
OR
( to_char(to_number(nvl([Year data item], 0))+1)=?p_year? and

[Quarter data item] <=?p_quarter? )

Sunday, September 11, 2016

How to format Cognos chart labels

Believe or not, it is not very easy to add to add labels on bar chart like below, show number in stack bar and get total display on top of each bar.
Design view
Make total show on top of bar
Format the total on top of bar


Make value show on stacked bar
Format value show on stacked bar, please note you need to change it on default measure, not on the series


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