exploded_aggregation_technique_break_down.sql

SELECT
     -- Product Ancestor Attributes
       LPAD ('-', (products.ancestor_level_number - 1) * 7, '-')
     || products.ancestor_level_name       AS product_ancestor_level_name
     , LPAD ('-', (products.ancestor_level_number - 1) * 7, '-')
     || products.ancestor_node_name        AS product_ancestor_node_name
     -- Product Descendant Attributes
     , LPAD ('-', (products.descendant_level_number - 1) * 7, '-')
     || products.descendant_level_name       AS product_descendant_level_name
     , LPAD ('-', (products.descendant_level_number - 1) * 7, '-')
     || products.descendant_node_name        AS product_descendant_node_name
     -- Metrics
     , facts.customer_id
     , facts.sales_amount
  FROM sales_facts AS facts
    JOIN
       product_aggregation_dim AS products
    ON facts.product_id = products.descendant_node_natural_key
 WHERE products.descendant_node_name IN ('Nerds', 'Hershey Bar')
ORDER BY products.ancestor_node_sort_order ASC
       , products.descendant_node_sort_order ASC
       , facts.customer_id ASC
;