Hi there,

If you mean something like an employee_dim and compensation_fact, where you have to show a report of let's say the 2nd level managers (or Executives from my example above) who spent most money on compensation last year then you have pretty complex query.

You have a hierarchical employee dim table with self-referencing recursive relationship, hence you write self joins and then use union to be able to add up manager salaries and individual contributor salaries. If you have another mgmt level, it adds another union operator.

In data warehouses we optimize for read (analysis/complex query). We don't optimize for writes (reduced redundancy, DML anomalies etc.). Hence you're allowed to denormalize hierarchies into a dim with much more no. of records - it changes the design though.

Here's the example and SQL.

employee_dim

employee_ID, emp_name, manager_ID, designation...

10, jack, 5, executive

20, john, 10, manager

30, jil, 20, individual contributor

compensaction_fact

employee_ID, date, salary_change_amt...

10, 1-1-2020, 1000

20, 2-1-2020, 500

30, 3-1-2020, 250

5, 4-1-2020, 3000

SELECT

union_resultset.executive_name,

SUM(union_resultset.salary_change_amt) AS tot_salary_chng_amt_by_executive

FROM

(

--GET MANAGER SALARIES UNDER EXEC

SELECT

exec.emp_name AS executive_name,

comp.salary_change_amt AS salary_change_amt

FROM compensation_fact comp, employee_dim exec, employee_dim mgr

WHERE

comp.employee_ID = mgr.employee_id AND --PICK MGR RECORDS FROM FACT

exec.employee_ID = mgr.manager_ID

comp.date between <some_date_here> and <some_other_date_here>

UNION ALL

SELECT

--GET IC SALARIES UNDER EXEC

exec.emp_name as executive_name,

comp.salary_change_amt AS salary_change_amt

FROM compensation_fact comp, employee_dim exec, employee_dim mgr, employee_dim ic

WHERE

comp.employee_ID = ic.employee_id AND --PICK INDIVIDUAL CONTRIBUTOR RECORDS FROM FACT

exec.employee_ID = mgr.manager_ID AND

mgr.employee_ID = ic.manager_ID AND

comp.date between <some_date_here> and <some_other_date_here>

) union_resultset

GROUP BY union_resultset.executive_name;