| 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 book hierarchy. This matches the last non-_DATAMEMBER_ node in levels 1 - 15. | |
| HIERARCHY_LEVEL1 | STRING | Level 1 | Node at level 1 of the book hierarchy. | |
| HIERARCHY_LEVEL2 | STRING | Level 2 | Node at level 2 of the book hierarchy. | |
| HIERARCHY_LEVEL3 | STRING | Level 3 | Node at level 3 of the book hierarchy. | |
| HIERARCHY_LEVEL4 | STRING | Level 4 | Node at level 4 of the book hierarchy. | |
| HIERARCHY_LEVEL5 | STRING | Level 5 | Node at level 5 of the book 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
Instead of creating and populating the LEGAL_ENTITY_HIERARCHY table directly, you can create a LEGAL_ENTITY_PARENT_CHILD table 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. |
Create LEGAL_ENTITY_HIERARCHY View with SQL Script
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;
For performance reasons, views may 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.