Wednesday, 3 January 2018

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.

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live
2. Create an aggregation on FCURR and TCURR to get the distinct FCURR and TCURR.

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live

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

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live

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’.

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live

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.

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live

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.

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live

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

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live

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

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live


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.

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live


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.

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live

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

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live

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.

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live

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.

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live

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

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live

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.

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live

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.

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live

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

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live

18. Final Semantics.

SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Live

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