Skip to Content

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:

/wp-content/uploads/2013/01/scn1_171433.png

Store Master Table:

/wp-content/uploads/2013/01/scn1_171433.png

Sales Target Table:

/wp-content/uploads/2013/01/scn1_171433.png

Store Transaction Table:

/wp-content/uploads/2013/01/scn1_171433.png

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.

/wp-content/uploads/2013/01/scn1_171433.png

Then to create an analytic view with the Store transaction table and the above mentioned attribute view.

/wp-content/uploads/2013/01/scn1_171433.png

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.

/wp-content/uploads/2013/01/scn1_171433.png

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

  1.    Using procedure to update the table with the data from calculation view
  2.    Using windows task scheduler along with HDBSQL to schedule the procedure.

A scripted calculation view is created as shown below.

/wp-content/uploads/2013/01/scn1_171433.png

/wp-content/uploads/2013/01/scn1_171433.png

A procedure is created to update the table as shown below.

/wp-content/uploads/2013/01/scn1_171433.png

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

/wp-content/uploads/2013/01/scn1_171433.png

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

/wp-content/uploads/2013/01/scn1_171433.png

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.

/wp-content/uploads/2013/01/scn1_171433.png

Finally in the triggers tab, the task can be scheduled based on the requirements. For analysis, we have chosen one time.

/wp-content/uploads/2013/01/scn1_171433.png

/wp-content/uploads/2013/01/scn1_171433.png

Task runs at the scheduled time. Here in our scenario, scheduled time is 5:37 AM.

/wp-content/uploads/2013/01/scn1_171433.png

Finally the file is generated with data as shown.

/wp-content/uploads/2013/01/scn1_171433.png

References

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Lars Breddemann

    Nice setup and a pretty blog post!

     

    Two remarks:

    • Please don’t ever use the logon parameters for hdbsql to schedule anything or to put them into a batch file.
      You’re putting logon data to highly confidential information (salary data…) into a plain text file.
      Instead please do use the secure userstore (hdbuserstore) facility that allows to store the full set of logon data encrypted (see http://help.sap.com/hana/hana_sec_en.pdf, http://help.sap.com/hana/hana_admin_en.pdf
      , and my super old blog post HANA backup script  hint… a little bit more security, please! if you like to) for details on how to use it.
    • Maybe I don’t see it, but why exactly are you looping over the result of your calc view when you could easily create a calculated measure directly in a calc view instead?
      You could then easily directly UPSERT the result of the calc view to your table.

    – Lars

    (0) 
  2. Veena R

    Hi Rajesh,

     

    Nice workaround and it is very much helpful.

    But I have a query. Is this workaround specific to a particular system.

    How to schedule in the production environment ?

    How to transport this scheduler ?

    (0) 

Leave a Reply