multiple_dimension_aggregation.sql

SELECT 
     -- Product Dimension
       LPAD (' ', (products.ancestor_level_number - 1) * 7, ' ')
     || products.ancestor_level_name       AS product_level_name
     , LPAD (' ', (products.ancestor_level_number - 1) * 7, ' ')
     || products.ancestor_node_name        AS product_node_name
     -- Geography Dimension
     , LPAD (' ', (geographies.ancestor_level_number - 1) * 7, ' ')
     || geographies.ancestor_level_name       AS geography_level_name
     , LPAD (' ', (geographies.ancestor_level_number - 1) * 7, ' ')
     || geographies.ancestor_node_name        AS geography_node_name
     -- Aggregates
     , SUM (facts.sales_amount)           AS sum_sales_amount
     , SUM (facts.unit_quantity)          AS sum_unit_quantity
     , COUNT (DISTINCT facts.customer_id) AS distinct_customer_count
     , COUNT (*)                          AS count_of_fact_records
  FROM sales_facts AS facts
    JOIN
       product_aggregation_dim AS products
    ON facts.product_id = products.descendant_node_natural_key
    JOIN
       geography_aggregation_dim AS geographies
    ON facts.geography_id = geographies.descendant_node_natural_key
GROUP BY products.ancestor_node_name
       , products.ancestor_level_name
       , products.ancestor_level_number
       , products.ancestor_node_sort_order
       --
       , geographies.ancestor_node_name
       , geographies.ancestor_level_name
       , geographies.ancestor_level_number
       , geographies.ancestor_node_sort_order
ORDER BY products.ancestor_node_sort_order ASC
       , geographies.ancestor_node_sort_order ASC