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.


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 Default status of the perimeter not yet included in a sign-off process. It uses the latest cube data


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 INTRA-DAY,TOTAL REVIEWABLE,UNAPPROVED 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


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
SignOffAnalysisConfig Spring configuration Abstract configuration, when implemented it activates the analysis hierarchy on a specific cube
PnLSignOffAnalysisConfig Spring configuration Analysis hierarchy for Pnl cube
SensiSignOffAnalysisConfig Spring configuration Analysis hierarchy for Sensi cube
VarSignOffAnalysisConfig Spring configuration Analysis hierarchy for VaR cube
SignOffTaskConfig Spring configuration Analysis hierarchy for all the cubes within the ecosystem
SignOffAnalysisHierarchy Analysis Hierarchy Post-processor that builds the members of the Sign-off analysis hierarchy
SignOffAggregationProcedure Aggregation Procedure Post-processor that re-buckets the facts to the relevant members of the analysis hierarchy
SignOffDatastoreSchemaDescriptionPostProcessor Schema Description Post-Processor Post-processor that modifies the datastore schema to add the sign-off store and the main store index
SignOffRestService REST service The sign-off REST service
SignOffDto DTO The DTO used by the REST service to modify the sign-off tasks
SignOffTaskFinder Bean Provides the lookup functionality to find any sign-off task linked to a perimeter. It is used by the aggregation procedure and the tuple publisher
SignOffTuplePublisherFactory Tuple Publisher Bean that provides the tuple publisher instances for the main stores to compute the _Version field in the ETL