BOOK_HIERARCHY - Atoti Hub

The BOOK_HIERARCHY table contains the multi-level book organizational structure and the desk-level information.

Column Name	Type	Not Null	Cube Field	Description
AS_OF_DATE	DATE	Y		Timestamp (at close of business) for the data.
BOOK	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.
HIERARCHY_LEVEL6	STRING		Level 6	Node at level 6 of the book hierarchy.
HIERARCHY_LEVEL7	STRING		Level 7	Node at level 7 of the book hierarchy.
HIERARCHY_LEVEL8	STRING		Level 8	Node at level 8 of the book hierarchy.
HIERARCHY_LEVEL9	STRING		Level 9	Node at level 9 of the book hierarchy.
HIERARCHY_LEVEL10	STRING		Level 10	Node at level 10 of the book hierarchy.
HIERARCHY_LEVEL11	STRING		Level 11	Node at level 11 of the book hierarchy.
HIERARCHY_LEVEL12	STRING		Level 12	Node at level 12 of the book hierarchy.
HIERARCHY_LEVEL13	STRING		Level 13	Node at level 13 of the book hierarchy.
HIERARCHY_LEVEL14	STRING		Level 14	Node at level 14 of the book hierarchy.
HIERARCHY_LEVEL15	STRING		Level 15	Node at level 15 of the book hierarchy.

Unique Key

Columns
AS_OF_DATE
BOOK

Incoming Joins

Source Table	Source Columns	Target Columns
TRADE_MAPPING	AS_OF_DATE BOOK	AS_OF_DATE BOOK

How to populate from a parent-child table

Instead of creating and populating the BOOK_HIERARCHY table directly, you can create a BOOK_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 BOOK_HIERARCHY table:

Create BOOK_HIERARCHY View with SQL Script

/*
------------------------------------------
    Create BOOK_HIERARCHY View

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

CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_1 AS
SELECT
	BOOK_PARENT_CHILD.NAME         AS HIERARCHY_LEVEL1,
	NULL                           AS HIERARCHY_LEVEL2,
	NULL                           AS HIERARCHY_LEVEL3,
	NULL                           AS HIERARCHY_LEVEL4,
	NULL                           AS HIERARCHY_LEVEL5,
	NULL                           AS HIERARCHY_LEVEL6,
	NULL                           AS HIERARCHY_LEVEL7,
	NULL                           AS HIERARCHY_LEVEL8,
	NULL                           AS HIERARCHY_LEVEL9,
	NULL                           AS HIERARCHY_LEVEL10,
	NULL                           AS HIERARCHY_LEVEL11,
	NULL                           AS HIERARCHY_LEVEL12,
	NULL                           AS HIERARCHY_LEVEL13,
	NULL                           AS HIERARCHY_LEVEL14,
	NULL                           AS HIERARCHY_LEVEL15,
	BOOK_PARENT_CHILD.NAME         AS BOOK,
	BOOK_PARENT_CHILD.DATE         AS AS_OF_DATE
From BOOK_PARENT_CHILD
WHERE BOOK_PARENT_CHILD.PARENT IS NULL;


CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_2 AS
SELECT
	INTERMEDIATE_BOOK_HIERARCHY_1.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	BOOK_PARENT_CHILD.NAME         AS HIERARCHY_LEVEL2,
	NULL                           AS HIERARCHY_LEVEL3,
	NULL                           AS HIERARCHY_LEVEL4,
	NULL                           AS HIERARCHY_LEVEL5,
	NULL                           AS HIERARCHY_LEVEL6,
	NULL                           AS HIERARCHY_LEVEL7,
	NULL                           AS HIERARCHY_LEVEL8,
	NULL                           AS HIERARCHY_LEVEL9,
	NULL                           AS HIERARCHY_LEVEL10,
	NULL                           AS HIERARCHY_LEVEL11,
	NULL                           AS HIERARCHY_LEVEL12,
	NULL                           AS HIERARCHY_LEVEL13,
	NULL                           AS HIERARCHY_LEVEL14,
	NULL                           AS HIERARCHY_LEVEL15,
	BOOK_PARENT_CHILD.NAME         AS BOOK,
	BOOK_PARENT_CHILD.DATE         AS AS_OF_DATE
From INTERMEDIATE_BOOK_HIERARCHY_1
JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_1.HIERARCHY_LEVEL1) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_1.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_3 AS
SELECT
	INTERMEDIATE_BOOK_HIERARCHY_2.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_BOOK_HIERARCHY_2.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	BOOK_PARENT_CHILD.NAME         AS HIERARCHY_LEVEL3,
	NULL                           AS HIERARCHY_LEVEL4,
	NULL                           AS HIERARCHY_LEVEL5,
	NULL                           AS HIERARCHY_LEVEL6,
	NULL                           AS HIERARCHY_LEVEL7,
	NULL                           AS HIERARCHY_LEVEL8,
	NULL                           AS HIERARCHY_LEVEL9,
	NULL                           AS HIERARCHY_LEVEL10,
	NULL                           AS HIERARCHY_LEVEL11,
	NULL                           AS HIERARCHY_LEVEL12,
	NULL                           AS HIERARCHY_LEVEL13,
	NULL                           AS HIERARCHY_LEVEL14,
	NULL                           AS HIERARCHY_LEVEL15,
	BOOK_PARENT_CHILD.NAME         AS BOOK,
	BOOK_PARENT_CHILD.DATE         AS AS_OF_DATE
From INTERMEDIATE_BOOK_HIERARCHY_2
JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_2.HIERARCHY_LEVEL2) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_2.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_4 AS
SELECT
	INTERMEDIATE_BOOK_HIERARCHY_3.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_BOOK_HIERARCHY_3.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	INTERMEDIATE_BOOK_HIERARCHY_3.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3,
	BOOK_PARENT_CHILD.NAME         AS HIERARCHY_LEVEL4,
	NULL                           AS HIERARCHY_LEVEL5,
	NULL                           AS HIERARCHY_LEVEL6,
	NULL                           AS HIERARCHY_LEVEL7,
	NULL                           AS HIERARCHY_LEVEL8,
	NULL                           AS HIERARCHY_LEVEL9,
	NULL                           AS HIERARCHY_LEVEL10,
	NULL                           AS HIERARCHY_LEVEL11,
	NULL                           AS HIERARCHY_LEVEL12,
	NULL                           AS HIERARCHY_LEVEL13,
	NULL                           AS HIERARCHY_LEVEL14,
	NULL                           AS HIERARCHY_LEVEL15,
	BOOK_PARENT_CHILD.NAME         AS BOOK,
	BOOK_PARENT_CHILD.DATE         AS AS_OF_DATE
From INTERMEDIATE_BOOK_HIERARCHY_3
JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_3.HIERARCHY_LEVEL3) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_3.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_5 AS
SELECT
	INTERMEDIATE_BOOK_HIERARCHY_4.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_BOOK_HIERARCHY_4.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	INTERMEDIATE_BOOK_HIERARCHY_4.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3,
	INTERMEDIATE_BOOK_HIERARCHY_4.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4,
	BOOK_PARENT_CHILD.NAME         AS HIERARCHY_LEVEL5,
	NULL                           AS HIERARCHY_LEVEL6,
	NULL                           AS HIERARCHY_LEVEL7,
	NULL                           AS HIERARCHY_LEVEL8,
	NULL                           AS HIERARCHY_LEVEL9,
	NULL                           AS HIERARCHY_LEVEL10,
	NULL                           AS HIERARCHY_LEVEL11,
	NULL                           AS HIERARCHY_LEVEL12,
	NULL                           AS HIERARCHY_LEVEL13,
	NULL                           AS HIERARCHY_LEVEL14,
	NULL                           AS HIERARCHY_LEVEL15,
	BOOK_PARENT_CHILD.NAME         AS BOOK,
	BOOK_PARENT_CHILD.DATE         AS AS_OF_DATE
From INTERMEDIATE_BOOK_HIERARCHY_4
JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_4.HIERARCHY_LEVEL4) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_4.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_6 AS
SELECT
	INTERMEDIATE_BOOK_HIERARCHY_5.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_BOOK_HIERARCHY_5.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	INTERMEDIATE_BOOK_HIERARCHY_5.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3,
	INTERMEDIATE_BOOK_HIERARCHY_5.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4,
	INTERMEDIATE_BOOK_HIERARCHY_5.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5,
	BOOK_PARENT_CHILD.NAME         AS HIERARCHY_LEVEL6,
	NULL                           AS HIERARCHY_LEVEL7,
	NULL                           AS HIERARCHY_LEVEL8,
	NULL                           AS HIERARCHY_LEVEL9,
	NULL                           AS HIERARCHY_LEVEL10,
	NULL                           AS HIERARCHY_LEVEL11,
	NULL                           AS HIERARCHY_LEVEL12,
	NULL                           AS HIERARCHY_LEVEL13,
	NULL                           AS HIERARCHY_LEVEL14,
	NULL                           AS HIERARCHY_LEVEL15,
	BOOK_PARENT_CHILD.NAME         AS BOOK,
	BOOK_PARENT_CHILD.DATE         AS AS_OF_DATE
From INTERMEDIATE_BOOK_HIERARCHY_5
JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_5.HIERARCHY_LEVEL5) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_5.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_7 AS
SELECT
	INTERMEDIATE_BOOK_HIERARCHY_6.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_BOOK_HIERARCHY_6.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	INTERMEDIATE_BOOK_HIERARCHY_6.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3,
	INTERMEDIATE_BOOK_HIERARCHY_6.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4,
	INTERMEDIATE_BOOK_HIERARCHY_6.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5,
	INTERMEDIATE_BOOK_HIERARCHY_6.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6,
	BOOK_PARENT_CHILD.NAME         AS HIERARCHY_LEVEL7,
	NULL                           AS HIERARCHY_LEVEL8,
	NULL                           AS HIERARCHY_LEVEL9,
	NULL                           AS HIERARCHY_LEVEL10,
	NULL                           AS HIERARCHY_LEVEL11,
	NULL                           AS HIERARCHY_LEVEL12,
	NULL                           AS HIERARCHY_LEVEL13,
	NULL                           AS HIERARCHY_LEVEL14,
	NULL                           AS HIERARCHY_LEVEL15,
	BOOK_PARENT_CHILD.NAME         AS BOOK,
	BOOK_PARENT_CHILD.DATE         AS AS_OF_DATE
From INTERMEDIATE_BOOK_HIERARCHY_6
JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_6.HIERARCHY_LEVEL6) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_6.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_8 AS
SELECT
	INTERMEDIATE_BOOK_HIERARCHY_7.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_BOOK_HIERARCHY_7.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	INTERMEDIATE_BOOK_HIERARCHY_7.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3,
	INTERMEDIATE_BOOK_HIERARCHY_7.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4,
	INTERMEDIATE_BOOK_HIERARCHY_7.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5,
	INTERMEDIATE_BOOK_HIERARCHY_7.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6,
	INTERMEDIATE_BOOK_HIERARCHY_7.HIERARCHY_LEVEL7 AS HIERARCHY_LEVEL7,
	BOOK_PARENT_CHILD.NAME         AS HIERARCHY_LEVEL8,
	NULL                           AS HIERARCHY_LEVEL9,
	NULL                           AS HIERARCHY_LEVEL10,
	NULL                           AS HIERARCHY_LEVEL11,
	NULL                           AS HIERARCHY_LEVEL12,
	NULL                           AS HIERARCHY_LEVEL13,
	NULL                           AS HIERARCHY_LEVEL14,
	NULL                           AS HIERARCHY_LEVEL15,
	BOOK_PARENT_CHILD.NAME         AS BOOK,
	BOOK_PARENT_CHILD.DATE         AS AS_OF_DATE
From INTERMEDIATE_BOOK_HIERARCHY_7
JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_7.HIERARCHY_LEVEL7) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_7.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_9 AS
SELECT
	INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3,
	INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4,
	INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5,
	INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6,
	INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL7 AS HIERARCHY_LEVEL7,
	INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL8 AS HIERARCHY_LEVEL8,
	BOOK_PARENT_CHILD.NAME         AS HIERARCHY_LEVEL9,
	NULL                           AS HIERARCHY_LEVEL10,
	NULL                           AS HIERARCHY_LEVEL11,
	NULL                           AS HIERARCHY_LEVEL12,
	NULL                           AS HIERARCHY_LEVEL13,
	NULL                           AS HIERARCHY_LEVEL14,
	NULL                           AS HIERARCHY_LEVEL15,
	BOOK_PARENT_CHILD.NAME         AS BOOK,
	BOOK_PARENT_CHILD.DATE         AS AS_OF_DATE
From INTERMEDIATE_BOOK_HIERARCHY_8
JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL8) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_8.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_10 AS
SELECT
	INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3,
	INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4,
	INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5,
	INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6,
	INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL7 AS HIERARCHY_LEVEL7,
	INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL8 AS HIERARCHY_LEVEL8,
	INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL9 AS HIERARCHY_LEVEL9,
	BOOK_PARENT_CHILD.NAME         AS HIERARCHY_LEVEL10,
	NULL                           AS HIERARCHY_LEVEL11,
	NULL                           AS HIERARCHY_LEVEL12,
	NULL                           AS HIERARCHY_LEVEL13,
	NULL                           AS HIERARCHY_LEVEL14,
	NULL                           AS HIERARCHY_LEVEL15,
	BOOK_PARENT_CHILD.NAME         AS BOOK,
	BOOK_PARENT_CHILD.DATE         AS AS_OF_DATE
From INTERMEDIATE_BOOK_HIERARCHY_9
JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL9) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_9.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_11 AS
SELECT
	INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3,
	INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4,
	INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5,
	INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6,
	INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL7 AS HIERARCHY_LEVEL7,
	INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL8 AS HIERARCHY_LEVEL8,
	INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL9 AS HIERARCHY_LEVEL9,
	INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL10 AS HIERARCHY_LEVEL10,
	BOOK_PARENT_CHILD.NAME         AS HIERARCHY_LEVEL11,
	NULL                           AS HIERARCHY_LEVEL12,
	NULL                           AS HIERARCHY_LEVEL13,
	NULL                           AS HIERARCHY_LEVEL14,
	NULL                           AS HIERARCHY_LEVEL15,
	BOOK_PARENT_CHILD.NAME         AS BOOK,
	BOOK_PARENT_CHILD.DATE         AS AS_OF_DATE
From INTERMEDIATE_BOOK_HIERARCHY_10
JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL10) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_10.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_12 AS
SELECT
	INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3,
	INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4,
	INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5,
	INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6,
	INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL7 AS HIERARCHY_LEVEL7,
	INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL8 AS HIERARCHY_LEVEL8,
	INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL9 AS HIERARCHY_LEVEL9,
	INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL10 AS HIERARCHY_LEVEL10,
	INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL11 AS HIERARCHY_LEVEL11,
	BOOK_PARENT_CHILD.NAME         AS HIERARCHY_LEVEL12,
	NULL                           AS HIERARCHY_LEVEL13,
	NULL                           AS HIERARCHY_LEVEL14,
	NULL                           AS HIERARCHY_LEVEL15,
	BOOK_PARENT_CHILD.NAME         AS BOOK,
	BOOK_PARENT_CHILD.DATE         AS AS_OF_DATE
From INTERMEDIATE_BOOK_HIERARCHY_11
JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL11) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_11.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_13 AS
SELECT
	INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3,
	INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4,
	INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5,
	INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6,
	INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL7 AS HIERARCHY_LEVEL7,
	INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL8 AS HIERARCHY_LEVEL8,
	INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL9 AS HIERARCHY_LEVEL9,
	INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL10 AS HIERARCHY_LEVEL10,
	INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL11 AS HIERARCHY_LEVEL11,
	INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL12 AS HIERARCHY_LEVEL12,
	BOOK_PARENT_CHILD.NAME         AS HIERARCHY_LEVEL13,
	NULL                           AS HIERARCHY_LEVEL14,
	NULL                           AS HIERARCHY_LEVEL15,
	BOOK_PARENT_CHILD.NAME         AS BOOK,
	BOOK_PARENT_CHILD.DATE         AS AS_OF_DATE
From INTERMEDIATE_BOOK_HIERARCHY_12
JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL12) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_12.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_14 AS
SELECT
	INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3,
	INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4,
	INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5,
	INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6,
	INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL7 AS HIERARCHY_LEVEL7,
	INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL8 AS HIERARCHY_LEVEL8,
	INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL9 AS HIERARCHY_LEVEL9,
	INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL10 AS HIERARCHY_LEVEL10,
	INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL11 AS HIERARCHY_LEVEL11,
	INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL12 AS HIERARCHY_LEVEL12,
	INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL13 AS HIERARCHY_LEVEL13,
	BOOK_PARENT_CHILD.NAME         AS HIERARCHY_LEVEL14,
	NULL                           AS HIERARCHY_LEVEL15,
	BOOK_PARENT_CHILD.NAME         AS BOOK,
	BOOK_PARENT_CHILD.DATE         AS AS_OF_DATE
From INTERMEDIATE_BOOK_HIERARCHY_13
JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL13) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_13.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_15 AS
SELECT
	INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3,
	INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4,
	INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5,
	INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6,
	INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL7 AS HIERARCHY_LEVEL7,
	INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL8 AS HIERARCHY_LEVEL8,
	INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL9 AS HIERARCHY_LEVEL9,
	INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL10 AS HIERARCHY_LEVEL10,
	INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL11 AS HIERARCHY_LEVEL11,
	INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL12 AS HIERARCHY_LEVEL12,
	INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL13 AS HIERARCHY_LEVEL13,
	INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL14 AS HIERARCHY_LEVEL14,
	BOOK_PARENT_CHILD.NAME         AS HIERARCHY_LEVEL15,
	BOOK_PARENT_CHILD.NAME         AS BOOK,
	BOOK_PARENT_CHILD.DATE         AS AS_OF_DATE
From INTERMEDIATE_BOOK_HIERARCHY_14
JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL14) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_14.AS_OF_DATE);


CREATE OR REPLACE VIEW BOOK_HIERARCHY_VIEW AS
	SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_1 UNION
	SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_2 UNION
	SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_3 UNION
	SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_4 UNION
	SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_5 UNION
	SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_6 UNION
	SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_7 UNION
	SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_8 UNION
	SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_9 UNION
	SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_10 UNION
	SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_11 UNION
	SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_12 UNION
	SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_13 UNION
	SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_14 UNION
	SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_15;


-- Store the results as a View:
CREATE OR REPLACE VIEW BOOK_HIERARCHY AS SELECT * FROM BOOK_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 BOOK_HIERARCHY AS SELECT * FROM BOOK_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.