Phasing Data in SAP HANA Using Window Functions

Phasing Data in SAP HANA Using Window Functions

The term “phasing” here refers to the process of splitting a single source record into multiple records in the output to distribute a value over time. For example, we might have a Purchase Order worth $200 and a start and end date, and we want to distribute that $200 across those dates with day level accuracy:

Phasing Data in SAP HANA Using Window Functions

This process can be virtualised using HANA Window functions and the HANA time dimension table.

First, to see how the phasing calculation works in theory, we can imagine an additional interim table existing like this:

Phasing Data in SAP HANA Using Window Functions

Now to perform the above in HANA. First let’s get some test data:

drop table tx;
create column table tx as
(select to_varchar(123456) as PO,
200.0 as value,
date’2016-10-05′ as start_date,
date’2016-12-11′ as end_date
from dummy);
select * from tx;

Above script produces this:

Phasing Data in SAP HANA Using Window Functions

And here is the output of the final calculation:

Phasing Data in SAP HANA Using Window Functions

The phased results shown above match what was predicted in the earlier calculation. Here is the script that produces the above phased results:

select distinct
po
, calmonth
— , full_val
— , tot_num_days
— , days_in_month
— , days_in_month / tot_num_days as mshare
, round(full_val * (days_in_month / tot_num_days), 2) as month_val
from
( select tx.po
, mdays.CALMONTH
, tx.value as full_val
, count(*) over (partition by tx.po, mdays.CALMONTH) as days_in_month
, count(*) over (partition by tx.po) as tot_num_days
from
tx
inner join
“_SYS_BI”.”M_TIME_DIMENSION” mdays
on mdays.date_sql between tx.start_date and tx.end_date
) as base

order by po, CALMONTH;

This uses Window Functions to partition data from the M_TIME_DIMENSION table into days. By uncommenting some of the output fields in the script above you can see the calculation happening:

Phasing Data in SAP HANA Using Window Functions

Note that since we use an inner join on M_TIME_DIMENSIONS, we need to make sure that date records exist there for any date in a range we intend to phase, otherwise the results may not be as expected.

Leave a Reply

Your email address will not be published. Required fields are marked *