☰
Navigation :

LEGAL_ENTITY_HIERARCHY

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

note

The content of this table is populated from the content of the LEGAL_ENTITY_PARENT_CHILD table.

Column Name Type Not Null Cube Field Default Value1 Description
AS_OF_DATE DATE Y Timestamp (at close of business) for the data.
LEGAL_ENTITY STRING Y [Organization].[Legal Entities] N/A Leaf node of the book hierarchy. This matches the last non-_DATAMEMBER_ node in levels 1 - 15.
LEGAL_ENTITY_HIERARCHY_LEVEL1 STRING Y Level 1 N/A Node at level 1 of the legal entity hierarchy.
LEGAL_ENTITY_HIERARCHY_LEVEL2 STRING Y Level 2 N/A Node at level 2 of the legal entity hierarchy.
LEGAL_ENTITY_HIERARCHY_LEVEL3 STRING Y Level 3 N/A Node at level 3 of the legal entity hierarchy.
LEGAL_ENTITY_HIERARCHY_LEVEL4 STRING Y Level 4 N/A Node at level 4 of the legal entity hierarchy.
LEGAL_ENTITY_HIERARCHY_LEVEL5 STRING Y Level 5 N/A 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_ATTRIBUTES AS_OF_DATE
LEGAL_ENTITY		 AS_OF_DATE
LEGAL_ENTITY

Table creation script

Snowflake

Table creation

create OR REPLACE table LEGAL_ENTITY_HIERARCHY(
	AS_OF_DATE DATE NOT NULL,
	LEGAL_ENTITY STRING NOT NULL DEFAULT 'N/A',
	LEGAL_ENTITY_HIERARCHY_LEVEL1 STRING NOT NULL DEFAULT 'N/A',
	LEGAL_ENTITY_HIERARCHY_LEVEL2 STRING NOT NULL DEFAULT 'N/A',
	LEGAL_ENTITY_HIERARCHY_LEVEL3 STRING NOT NULL DEFAULT 'N/A',
	LEGAL_ENTITY_HIERARCHY_LEVEL4 STRING NOT NULL DEFAULT 'N/A',
	LEGAL_ENTITY_HIERARCHY_LEVEL5 STRING NOT NULL DEFAULT 'N/A',
	primary key (AS_OF_DATE, LEGAL_ENTITY)
);

insert into LEGAL_ENTITY_HIERARCHY
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L1.CHILD AS LEGAL_ENTITY,
	L1.CHILD AS LEGAL_ENTITY_HIERARCHY_LEVEL1,
	'N/A' AS LEGAL_ENTITY_HIERARCHY_LEVEL2,
	'N/A' AS LEGAL_ENTITY_HIERARCHY_LEVEL3,
	'N/A' AS LEGAL_ENTITY_HIERARCHY_LEVLE4,
	'N/A' AS LEGAL_ENTITY_HIERARCHY_LEVEL5
	from LEGAL_ENTITY_PARENT_CHILD as L1
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L2.CHILD AS LEGAL_ENTITY,
	L1.CHILD AS LEGAL_ENTITY_HIERARCHY_LEVEL1,
	L2.CHILD AS LEGAL_ENTITY_HIERARCHY_LEVEL2,
	'N/A' AS LEGAL_ENTITY_HIERARCHY_LEVEL3,
	'N/A' AS LEGAL_ENTITY_HIERARCHY_LEVLE4,
	'N/A' AS LEGAL_ENTITY_HIERARCHY_LEVEL5
	from LEGAL_ENTITY_PARENT_CHILD as L1
	inner join LEGAL_ENTITY_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L3.CHILD AS LEGAL_ENTITY,
	L1.CHILD AS LEGAL_ENTITY_HIERARCHY_LEVEL1,
	L2.CHILD AS LEGAL_ENTITY_HIERARCHY_LEVEL2,
	L3.CHILD AS LEGAL_ENTITY_HIERARCHY_LEVEL3,
	'N/A' AS LEGAL_ENTITY_HIERARCHY_LEVLE4,
	'N/A' AS LEGAL_ENTITY_HIERARCHY_LEVEL5
	from LEGAL_ENTITY_PARENT_CHILD as L1
	inner join LEGAL_ENTITY_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	inner join LEGAL_ENTITY_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT)
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L4.CHILD AS LEGAL_ENTITY,
	L1.CHILD AS LEGAL_ENTITY_HIERARCHY_LEVEL1,
	L2.CHILD AS LEGAL_ENTITY_HIERARCHY_LEVEL2,
	L3.CHILD AS LEGAL_ENTITY_HIERARCHY_LEVEL3,
	L4.CHILD AS LEGAL_ENTITY_HIERARCHY_LEVLE4,
	'N/A' AS LEGAL_ENTITY_HIERARCHY_LEVEL5
	from LEGAL_ENTITY_PARENT_CHILD as L1
	inner join LEGAL_ENTITY_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	inner join LEGAL_ENTITY_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT)
	inner join LEGAL_ENTITY_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT)
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L5.CHILD AS LEGAL_ENTITY,
	L1.CHILD AS LEGAL_ENTITY_HIERARCHY_LEVEL1,
	L2.CHILD AS LEGAL_ENTITY_HIERARCHY_LEVEL2,
	L3.CHILD AS LEGAL_ENTITY_HIERARCHY_LEVEL3,
	L4.CHILD AS LEGAL_ENTITY_HIERARCHY_LEVLE4,
	L5.CHILD AS LEGAL_ENTITY_HIERARCHY_LEVEL5
	from LEGAL_ENTITY_PARENT_CHILD as L1
	inner join LEGAL_ENTITY_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	inner join LEGAL_ENTITY_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT)
	inner join LEGAL_ENTITY_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT)
	inner join LEGAL_ENTITY_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT)
	where L1.PARENT is null;

  1. If the default value is marked as empty, it means that the default value is 'null' for nullable fields, and that a value needs to be explicitly set for non-nullable fields.  ↩︎