Applies to:
SAP HANA developers.
Summary
Job scheduling option is important in all databases for particular scenarios. As of now SAP HANA does'nt have an option to schedule jobs. But we can have a workaround using hdbsql and windows task scheduler to schedule jobs in SAP HANA. In this document, we describe step-by-step procedure to follow for such a scenario.
Authors: Rajesh Jeyaram Raja, Prasath Vettrinathan, Nikhil Joy
Company: Infosys Limited
Created on: 04-Jan-2013
Author's Bio
Rajesh Jeyaram Raja:
Rajesh has around 2 years of professional experience in the IT industry and has experience in SAP BW, BO and HANA . He is a Systems Engineer working in Infosys.
Prasath Vettrinathan:
Prasath has around 5 years of professional experience and has expertise in SAP BW, BO and HANA. He is an Associate consultant working in Infosys.
Nikhil Joy:
Nikhil has 2 years of professional experience and has expertise in SAP BW , BO and HANA. He is a Systems Engineer working in Infosys.
Table of Contents
- Introduction
- The Scenario
- Challenges faced
- Workaround
- References
Introduction:
SAP HANA as we know is the talk of the town and with its humongous capabilities has made a huge impact in the market. Since SAP HANA is still at its nascent stage it throws as many challenges. This document discusses about two such challenges faced and suggests a workaround for them.
The Scenario:
The requirement was to generate a file with pay roll data on last day of every month using the following data.
The Master data for the entire scenario included the Employee master data (Employee number, name and salary), Store master data (Store number, Area/Locality, Sales Manager). The daily transactions of the stores are stored in a separate table. There is also a table which shows the Monthly Target fixed for each employee.
Let us consider that the following are the data in the tables
Employee Master Table:
Store Master Table:
Sales Target Table:
Store Transaction Table:
The logic for the incentive is to compare the sales carried out by the employee in a month with his target and give him incentives as shown below.
Condition | Incentive |
Total sales is greater than twice the target set | 10% of the base salary |
Total sales is lesser than twice the target set but greater than or equal to the target | 5% of the base salary |
Total sales is lesser than the target set | - |
Challenges Faced:
The solution that was initially thought of was to create attribute view by joining the Employee master and Store master tables.
Then to create an analytic view with the Store transaction table and the above mentioned attribute view.
Finally creating a scripted calculation view to calculate the revised salary and update it to the table. But the calling of procedures with read/write operations was not allowed inside a calculation view.
The work around that was thought of was to create a procedure that reads the calculation view and update the details in the table. But the problem here was that the user had to run the calculation view every time whenever the details had to be updated.
To sum up, the following were the challenges faced in implementing this scenario
1. Calling of a procedure with write operations is prohibited within a calculation view
2. Scheduling of a procedure without the usage of third party scheduling tools
Workaround:
A fitting answer to overcome these challenges is HDBSQL – a command line tool that is available when HANA client is installed in a system.
As mentioned earlier , HDBSQL is a command line tool that can be used to access the database directly without the usage of HANA Studio. To know more on HDBSQL please refer to the HANA Database Administration guide.
As we became familiar with HDBSQL, we came up with workarounds
- Using procedure to update the table with the data from calculation view
- Using windows task scheduler along with HDBSQL to schedule the procedure.
A scripted calculation view is created as shown below.
A procedure is created to update the table as shown below.
Finally we could use HDBSQL to call the procedure and pass the data from the result table in to the file. But the procedure has to be scheduled. To do so a sql script is created as shown
\o is the command used in HDBSQL to pass the results of the query into a file.
Then a batch file is created which can call the script using HDBSQL.
Now this batch file can be scheduled using the Task scheduler.(Start->All programs->Accessories->System tools->Task scheduler). And then create a task and in Actions tab click on new and select the batch file that is to be run.
Finally in the triggers tab, the task can be scheduled based on the requirements. For analysis, we have chosen one time.
Task runs at the scheduled time. Here in our scenario, scheduled time is 5:37 AM.
Finally the file is generated with data as shown.
References
- SAP Managed Tags:
6 Comments
