Scheduling a job in SAP HANA using HDBSQL and windows task scheduler

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.

Source: scn.sap.com