Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
Showing results for 
Search instead for 
Did you mean: 
Active Participant
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
, 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
( 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
inner join
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.
1 Comment
Labels in this area