I have a requirement where I maintain type 2 SCD in my dimension table as below.
For example on day_1 the material code M-01 has a name mat_1_old - now the transactions on day_1 are mapped with dim_mat_master_id 1 for M-01. Now on day_2 the name for M-01 gets updated as mat_1_new and hence this is updated to is_current as true and the transaction on day_2 are mapped with dim_mat_master_id as 2.
Now if I am doing aggregation over mat_name (say for example) - on directly joining I would get aggregations for mat_1_old as well as mat_1_new as follows :
select dmm.material_name, sum(qty) as quantity
from fct_material_transaction fmt
join dim_material_master dmm on
fmt.dim_mat_master_id = dmm.dim_mat_master_id
group by dmm.material_name
result
mat_1_new, 60
mat_1_old, 100
But I need aggregation based on latest mapping, how to do that?. Is below approach correct?
with transaction_det as (
select dmm.material_code, fmt.qty from fct_material_transaction fmt
join dim_material_master dmm on
fmt.dim_mat_master_id = dmm.dim_mat_master_id
)
select dmm.material_name, sum(qty) from transaction_det td join dim_mat_master dmm on
td.material_code = dmm.material_code where dmm.is_current_rec
group by dmm.material_name
result
mat_1_new, 160
I just did a self join on the dimension table which is_current_rec and this is working perfectly.