Sign-Off analysis hierarchy

The Sign-Off Analysis Hierarchy reflects the perimeters of the sign-off process managed by the sign-off server.

Analysis Hierarchy content

The Analysis Hierarchy is three levels deep. The hierarchy is slicing, meaning it has no ALL Member level. By default, the selected level is [INTRA-DAY].

Level	Depth	Description	Members
Feed	1	How the data are updated	INTRA-DAY: the default member, the whole cube data at the latest value. TOTAL REVIEWABLE: the whole data with frozen perimeters under review.
Status	2	The current sign-off task status	For example, “UNAPPROVED”
Task	3	The unique sign-off task name	For example: “Sensi BSM::2018-09-28”

The hierarchy depth depends on the information to display. The member [INTRA-DAY] has no deeper level. Additionally, the path [TOTAL REVIEWABLE].[UNAPPROVED] has no level 3.

Example

Here is an example of the Analysis Hierarchy structure:

Feed / Status / Task	Sample Measure	Comment
INTRA-DAY	16.2	Default member, it displays the latest content of the cube.
TOTAL REVIEWABLE	16.0	Sign-off view, the data belonging to an initiated sign-off task is frozen.
> APPROVED	3.0	A specific status in the sign-off process, the level aggregates all the tasks with the same status.
> > Commodity::2018-09-28	1.0	A sign-off task with the “APPROVED” status.
> > Sensi BSM::2018-09-28	2.0	Another sign-off task with the “APPROVED” status.
> INITIATED	4.0	A specific status in the sign-off process; the level aggregates all the tasks with the same status.
> > Bonds::2018-09-28	4.0	A sign-off task with the “INITIATED” status.
> UNAPPROVED	9.0	The default status of the perimeter not yet included in a sign-off process. It uses the latest cube data.

Setup

You can customize the analysis hierarchy using the configuration files.

Key	Value	Description
sign-off.adjustments.cube.signOffFeedLevel.cubeName	Feed@Sign-off Status@Sign-off Status	Name and path of the analysis hierarchy’s level 1.
sign-off.adjustments.cube.signOffStatusLevel.cubeName	Status@Sign-off Status@Sign-off Status	Name and path of the analysis hierarchy’s level 2.
sign-off.adjustments.cube.signOffTaskLevel.cubeName	Task@Sign-off Status@Sign-off Status	Name and path of the analysis hierarchy’s level 3.
sign-off.level.members	LATEST,SNAPSHOT,PRELIMINARY	Names of the level members generated by the cube itself, the other names come from the sign-off server.
sign-off.cubeId.levels	AsOfDate@Date@Dates,Book@Books@Booking,Desk@Desks@Booking	Levels eligible to be in a sign-off task perimeter.

Sign-off task perimeter

The Analysis Hierarchy uses a side store to describe the perimeter for a given sign-off task. The eligible levels for task filtering are defined by the property “sign-off.cubeId.levels” for each cube. The defined levels must be a standard level associated with a store field and not an analysis hierarchy level. All non-empty criteria for the levels will generate a filtered location with the following pattern: WHERE ([dimension1].[hierarchy1].[level1].[filter1], [dimension2].[hierarchy2].[level2].[filter2], …)

Sign-off task store

The data linked to a sign-off task are stored in a specific store per cube.

CubeName	Main store	Sign-off store	cubeId
VaR-ES Cube	TradePnLs	TradePnLsSignOff	var
Sensitivity Cube	TradeSensitivities	TradeSensitivitiesSignOff	sensi
PLCube	PnL	PnLSignOff	pnl

The sign-off store is created dynamically from the list of filterable levels and has the following format:

Field	Type	Key	Description
Name	string	X	Sign-off task name
Status	string		Current sign-off status
hierarchy name 1	computed		Holds the content of the 1st filter, the column name is the hierarchy name of the filtered level, and the type is computed from the base store
hierarchy name 2	computed		Holds the content of the 2nd filter
…	…		…
hierarchy name n	computed		Holds the content of the last filter

The [_Version] hierarchy

To support the snapshotting feature, the cube now includes a specific single-level hierarchy named “_Version”. It is a technical, hidden hierarchy. This hierarchy is attached to a primary key that allows the system to duplicate the fact and create a snapshot. By default, the _Version equals LIVE, but in snapshotting, the snapshot version is OLD and the incoming version is NEW.

Feed / Status	_Version / LIVE	_Version / OLD	_Version / NEW
INTRA-DAY	X		X
TOTAL REVIEWABLE	X	X
> APPROVED	X	X
> INITIATED	X	X
> UNAPPROVED	X

ETL

The _Version field, linked to the [_Version] hierarchy must be filled with the correct value. The ETL achieves this with the following logic: Does the input line belong to a task perimeter?

No : Set the _Version field to LIVE.
Yes :
1. Set the _Version field to NEW.
2. Update any existing line _Version field to OLD.

Main store modifications

To generate the [_Version] hierarchy, an extra key field named _Version is added to the base store of the cube.

Discarding a sign-off task

When a sign-off task is discarded, all the lines tagged OLD inside the perimeter are deleted and the ones tagged NEW are re-tagged as LIVE.

REST service

The sign-off store is never updated from the default ETL but via REST service. The REST service is intended to be called by the sign-off server. It is located at the URL http://localhost:10010/risk-activepivot/services/rest/v3/sign-off. and provides three main services:

createSignOffTask: create a new sign-off task on the cube.
updateState: update the status of a sign-off task.
discardSignOffTask: remove the sign-off task.

Technical configuration

Class	Kind	Usage
IMADRCSignOffAnalysisConfig	Spring configuration	Analysis hierarchy for IMA DRC cube
InternalModelApproachSignOffAnalysisConfig	Spring configuration	Analysis hierarchy for IMA cube
StandardisedApproachSignOffAnalysisConfig	Spring configuration	Analysis hierarchy for Standardised Approach cube
PLSignOffAnalysisConfig	Spring configuration	Analysis hierarchy for PL cube
SignOffTaskConfig	Spring configuration	Analysis hierarchy for all the cubes within the ecosystem
SignOffRestService	REST service	The sign-off REST service

​Analysis Hierarchy content

​Example

​Setup

​Sign-off task perimeter

​Sign-off task store

​The [_Version] hierarchy

​ETL

​Main store modifications

​Discarding a sign-off task

​REST service

​Technical configuration