> ## Documentation Index
> Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt
> Use this file to discover all available pages before exploring further.

# LEGAL_ENTITY_HIERARCHY

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 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](./trade_mapping) | AS\_OF\_DATE<br />LEGAL\_ENTITY | AS\_OF\_DATE<br />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.                                |

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:

<Accordion title="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;

  ```
</Accordion>

With this table and the SQL script included in the [online documentation](https://docs.activeviam.com/products/accelerators/frtb/5.1/online-help/database/legal_entity_hierarchy.), you can create a series of intermediate views to populate the multiple levels of the LEGAL\_ENTITY\_HIERARCHY table.

<Tip>
  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.
</Tip>