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:

This process can be virtualised using HANA Window functions and the HANA time dimension table. A big thanks to @lars.breddemann for providing the solution to this, the code presented here is all courtesy of Lars.

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

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:

And here is the output of the final calculation:

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:

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.