Creating a HANA calculation view for currency conversion providing exchange rates for all days including non-working days like holidays and weekends

There is sometimes a need to calculate the exchange rate for weekends and holidays in business use cases.  Since there is no exchange rate for these dates because they are non-working days, the business usually decides to take the most recent previous exchange rate and apply it to these dates.  For example, since December 30, 2017 is a Saturday, the exchange rate for this date will be the exchange rate for December 29, 2017 (Friday) – the most recent previous working day with an exchange rate.  This blog illustrates how to create a HANA calculation view that will provide the exchange rate from USD to EUR of any date in the period of the last 3 years.

1. We first create a projection on the table TCURR applying a filter on the exchange rate type (KURST), from-currency (FCURR) and to-currency (TCURR).  Also, create a calculated column EFFECTIVE_DATE converting GDATU to a real date.

2. Create an aggregation on FCURR and TCURR to get the distinct FCURR and TCURR.

3. Create a projection with a DUMMY_JOIN with a value of ‘1’.

4. Create a projection on the built-in time dimension table M_TIME_DIMENSION and filter it for the last 3 years. And also create a DUMMY_JOIN with a value of ‘1’.

5. Create a join between the projection and time dimension joining on the DUMMY_JOIN effectively creating a row for each day of the last 3 years.

6. Create a left outer join on the previous join and the first projection and taking the exchange rate (UKURS) from the first projection, effectively filling the exchange rate for days that have an exchange rate.

7. Create a projection with a filter on UKURS is null to get rows with no exchange rates.

8. Create a projection with a filter on UKURS is not null to get rows with exchange rates.

9. Create a join between the no_rate set and the have_rate set on FCURR and TCURR, effectively creating several rows with exchange rates for each date that has no exchange rate.

10. Create a projection with a filter of DATE_SAP_no_rate > DATE_SAP_have_rate, effectively removing rows that have a future date than the date that has no exchange rate.

11. Create an aggregation and using a max on DATE_SAP_have_rate effectively getting the most recent previous date with an exchange rate.

12. Create a projection on table TCURF with a filter on the exchange rate type to get any ratio for the from-currency that needs to be applied to the exchange rate.

13. Create a join between the set that has the most recent previous date and the set that has all the previous dates, effectively filling in the exchange rate for the most recent previous date.

14. Create an aggregation on TCURF with a min on GDATU to get one single ratio.

15. Create a union between the set that previously had no rates but now has the rate filled and the set that originally had exchange rates.

16. Create a join between the TCURF and the resulting union to get the ratio. Then create a calculated column UKURS_FINAL that applies the ratio to properly adjust the exchange rate.

17. Finally, create a projection with the desired final columns.

18. Final Semantics.

And there you have it, a calculation view for currency conversion with the exchange rate filled for every day of the last 3 years.