prmoore77

node_id,node_natural_key,node_name,level_name,parent_node_id,is_root,is_leaf,level_number,node_json,node_json_path,node_sort_order,level_1_node_id,level_1_node_natural_key,level_1_node_name,level_1_level_name,level_2_node_id,level_2_node_natural_key,level_2_node_name,level_2_level_name,level_3_node_id,level_3_node_natural_key,level_3_node_name,level_3_level_name
71ed36fa-3944-9aa0-a017-30872d0d4f13,0,All Products,Total Products,,TRUE,FALSE,1,"{'node_id': 71ed36fa-3944-9aa0-a017-30872d0d4f13, 'node_natural_key': 0, 'node_name': All Products, 'level_name': Total Products, 'parent_node_id': NULL, 'is_root': True, 'is_leaf': False, 'level_number': 1, 'node_sort_order': 1}","[{'node_id': 71ed36fa-3944-9aa0-a017-30872d0d4f13, 'node_natural_key': 0, 'node_name': All Products, 'level_name': Total Products, 'parent_node_id': NULL, 'is_root': True, 'is_leaf': False, 'level_number': 1}]",1,71ed36fa-3944-9aa0-a017-30872d0d4f13,0,All Products,Total Products,,,,,,,,
0e81b2fb-c845-4ca0-a001-34780b4b3bae,20,Candy,Category,71

prmoore77

ancestor_node_id,ancestor_node_natural_key,ancestor_node_name,ancestor_level_name,ancestor_level_number,ancestor_is_root,ancestor_is_leaf,ancestor_node_sort_order,descendant_node_id,descendant_node_natural_key,descendant_node_name,descendant_level_name,descendant_level_number,descendant_is_root,descendant_is_leaf,descendant_node_sort_order,net_level
71ed36fa-3944-9aa0-a017-30872d0d4f13,0,All Products,Total Products,1,TRUE,FALSE,1,71ed36fa-3944-9aa0-a017-30872d0d4f13,0,All Products,Total Products,1,TRUE,FALSE,1,0
71ed36fa-3944-9aa0-a017-30872d0d4f13,0,All Products,Total Products,1,TRUE,FALSE,1,0e81b2fb-c845-4ca0-a001-34780b4b3bae,20,Candy,Category,2,FALSE,FALSE,2,1
71ed36fa-3944-9aa0-a017-30872d0d4f13,0,All Products,Total Products,1,TRUE,FALSE,1,1ef9d2eb-e14b-2080-8026-1f1040728321,201,Hershey Bar,UPC,3,FALSE,TRUE,3,2
71ed36fa-3944-9aa0-a017-30872d0d4f13,0,All Products,Total Products,1,TRUE,FALSE,1,8d8b9c76-fd41-b7b9-b9fd-10d22f5ae767,202,Nerds,UPC,3,FALSE,TRUE,4,2
71ed36fa-3944-9aa0-a017-30872d0d4f13,0,All Pr

prmoore77

CREATE TABLE product_nodes
(
    node_id                VARCHAR(36)  DEFAULT uuid()
  , node_natural_key       INTEGER      NOT NULL
  , node_name              VARCHAR(100) NOT NULL
  , level_name             VARCHAR(100) NOT NULL
  , parent_node_id         VARCHAR(36)
--
  , CONSTRAINT product_nodes_pk PRIMARY KEY (node_id)
  , CONSTRAINT product_nodes_uk_1 UNIQUE (node_natural_key)

prmoore77

-- Root (Top) Node (has no parent node)
INSERT INTO product_nodes (node_natural_key, node_name, level_name, parent_node_id)
VALUES (0, 'All Products', 'Total Products', NULL);

-- Produce Category Level Node
INSERT INTO product_nodes (node_natural_key, node_name, level_name, parent_node_id)
VALUES (10, 'Produce', 'Category', (SELECT node_id
                                      FROM product_nodes
                                     WHERE node_name = 'All Products'));

prmoore77

prmoore77

CREATE OR REPLACE TEMPORARY TABLE product_nodes_temp
AS
SELECT node_id
     , node_natural_key
     , node_name
     , level_name
     , parent_node_id
     , CASE WHEN parent_node_id IS NULL
               THEN TRUE
               ELSE FALSE

prmoore77

prmoore77

CREATE OR REPLACE TABLE product_reporting_dim
AS
WITH RECURSIVE parent_nodes (
    node_id
  , node_natural_key
  , node_name
  , level_name
  , parent_node_id
  , is_root
  , is_leaf

prmoore77

CREATE OR REPLACE TABLE product_aggregation_dim
AS
WITH RECURSIVE parent_nodes (
    node_id
  , node_natural_key
  , node_name
  , level_name
  , parent_node_id
  , is_root
  , is_leaf

prmoore77

CREATE TABLE sales_facts (
  product_id    INTEGER NOT NULL
, customer_id   VARCHAR (100) NOT NULL
, date_id       DATE    NOT NULL
, unit_quantity NUMERIC NOT NULL
, sales_amount  NUMERIC NOT NULL
)
;

INSERT INTO sales_facts (product_id, customer_id, date_id, unit_quantity, sales_amount)