LEGAL_ENTITY_HIERARCHY - Atoti Hub

The LEGAL_ENTITY_HIERARCHY table contains the multi-level legal entity organizational structure.

Column Name	Type	Not Null	Cube Field	Description
AS_OF_DATE	DATE	Y		Timestamp (at close of business) for the data.
LEGAL_ENTITY	STRING	Y		Leaf node of the legal entity hierarchy. This matches the last non-_DATAMEMBER_ node in levels 1 - 15.
HIERARCHY_LEVEL1	STRING		Level 1	Node at level 1 of the legal entity hierarchy.
HIERARCHY_LEVEL2	STRING		Level 2	Node at level 2 of the legal entity hierarchy.
HIERARCHY_LEVEL3	STRING		Level 3	Node at level 3 of the legal entity hierarchy.
HIERARCHY_LEVEL4	STRING		Level 4	Node at level 4 of the legal entity hierarchy.
HIERARCHY_LEVEL5	STRING		Level 5	Node at level 5 of the legal entity hierarchy.

Unique Key

Columns
AS_OF_DATE
LEGAL_ENTITY

Incoming Joins

Source Table	Source Columns	Target Columns
TRADE_MAPPING	AS_OF_DATE LEGAL_ENTITY	AS_OF_DATE LEGAL_ENTITY

Populating From a ParentChild Table

The LEGAL_ENTITY_HIERARCHY table does not have to be created and populated directly. Instead, a LEGAL_ENTITY_PARENT_CHILD table can be created to store the parent-child relationships.

Column Name	Type	Not Null	Cube Field	Description
NAME	STRING	Y		Child in the parent-child relationship.
PARENT	STRING			Parent in the parent-child relationship. Null means the child is a root node.
DATE	DATE	Y		Timestamp (at close of business) for the data.

With this table and the following SQL script, you can create a series of intermediate views to populate the multiple levels of the LEGAL_ENTITY_HIERARCHY table:

Create LEGAL_ENTITY_HIERARCHY View with SQL Script

/*
------------------------------------------
    Create LEGAL_ENTITY_HIERARCHY View

    1. Create intermediate views (to populate each level of the LEGAL_ENTITY_HIERARCHY)
    2. Join all intermediate views together
------------------------------------------
*/

CREATE OR REPLACE VIEW INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_1 AS
SELECT
	LEGAL_ENTITY_PARENT_CHILD.DATE AS AS_OF_DATE,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS LEGAL_ENTITY,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS HIERARCHY_LEVEL1,
	NULL                           AS HIERARCHY_LEVEL2,
	NULL                           AS HIERARCHY_LEVEL3,
	NULL                           AS HIERARCHY_LEVEL4,
	NULL                           AS HIERARCHY_LEVEL5
From LEGAL_ENTITY_PARENT_CHILD
WHERE LEGAL_ENTITY_PARENT_CHILD.PARENT IS NULL;


CREATE OR REPLACE VIEW INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_2 AS
SELECT
	LEGAL_ENTITY_PARENT_CHILD.DATE AS AS_OF_DATE,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS LEGAL_ENTITY,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_1.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS HIERARCHY_LEVEL2,
	NULL                           AS HIERARCHY_LEVEL3,
	NULL                           AS HIERARCHY_LEVEL4,
	NULL                           AS HIERARCHY_LEVEL5
From INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_1
JOIN LEGAL_ENTITY_PARENT_CHILD ON (LEGAL_ENTITY_PARENT_CHILD.PARENT = INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_1.HIERARCHY_LEVEL1) AND (LEGAL_ENTITY_PARENT_CHILD.DATE = INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_1.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_3 AS
SELECT
	LEGAL_ENTITY_PARENT_CHILD.DATE AS AS_OF_DATE,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS LEGAL_ENTITY,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_2.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_2.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS HIERARCHY_LEVEL3,
	NULL                           AS HIERARCHY_LEVEL4,
	NULL                           AS HIERARCHY_LEVEL5
From INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_2
JOIN LEGAL_ENTITY_PARENT_CHILD ON (LEGAL_ENTITY_PARENT_CHILD.PARENT = INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_2.HIERARCHY_LEVEL2) AND (LEGAL_ENTITY_PARENT_CHILD.DATE = INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_2.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4 AS
SELECT
	LEGAL_ENTITY_PARENT_CHILD.DATE AS AS_OF_DATE,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS LEGAL_ENTITY,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_3.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_3.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_3.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS HIERARCHY_LEVEL4,
	NULL                           AS HIERARCHY_LEVEL5
From INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_3
JOIN LEGAL_ENTITY_PARENT_CHILD ON (LEGAL_ENTITY_PARENT_CHILD.PARENT = INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_3.HIERARCHY_LEVEL3) AND (LEGAL_ENTITY_PARENT_CHILD.DATE = INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_3.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_5 AS
SELECT
	LEGAL_ENTITY_PARENT_CHILD.DATE AS AS_OF_DATE,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS LEGAL_ENTITY,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS HIERARCHY_LEVEL5
From INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4
JOIN LEGAL_ENTITY_PARENT_CHILD ON (LEGAL_ENTITY_PARENT_CHILD.PARENT = INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4.HIERARCHY_LEVEL4) AND (LEGAL_ENTITY_PARENT_CHILD.DATE = INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4.AS_OF_DATE);


-- Join all intermediate views together
CREATE OR REPLACE VIEW LEGAL_ENTITY_HIERARCHY_VIEW AS
    SELECT * FROM INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_1 UNION
    SELECT * FROM INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_2 UNION
    SELECT * FROM INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_3 UNION
    SELECT * FROM INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4 UNION
    SELECT * FROM INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_5;


-- Store the results as a View:
CREATE OR REPLACE VIEW LEGAL_ENTITY_HIERARCHY AS SELECT * FROM LEGAL_ENTITY_HIERARCHY_VIEW;

-- Alternatively, store the results as a Table. This table will need to be manually kept up to date, unlike if it were a view:
CREATE OR REPLACE TABLE LEGAL_ENTITY_HIERARCHY AS SELECT * FROM LEGAL_ENTITY_HIERARCHY_VIEW;

Views can impact query and aggregation performance.Injecting the results of the final constructed view into a table can improve query performance. The tradeoff is that this final table will need to be manually kept up to date if the underlying parent-child tables are updated.