Skip to Content
Author's profile photo Alexander Rosell

Unit Testing SAP HANA Views – A Beginners Guide

In SPS 09 the unit test framework XSUnit (which bases on open-source framework Jasmine) was introduced by SAP. By using this tool server-side JavaScript unit tests can be created for XS applications. As well, the mocking framework Mockstar was introduced which permits to mock database objects or substitute tables/views by stubs. In interaction both tools make it possible to write unit tests for complex database objects like attribute views, analytic views, calculation views and procedures.

This blog guides through the initial difficulties to draft the first unit tests. Furthermore, it examines advanced aspects like measuring the code coverage and debugging unit tests.


All relevant test tools/frameworks have been bundled by SAP in delivery unit HANA_TEST_TOOLS which is not automatically deployed but available as non-automatic content. For us it worked to download HANATESTTOOLS.tgz and deploy it using the HANA Studio (REPO.IMPORT privilege is required for this activity). According to the SAP development documentation it is possible as well to deploy the delivery unit via Application Lifecycle Management.

Note: After installation the test tools are available in package sap.hana.testtools. In HANA Studio the package content can be inspected in Repository Browser or Project Explorer. On the opposite, the System view will show “hidden objects” since it only displays objects of type Analytic View, Attribute View, Calculation View, Analytic Privileges, Decision Tables, and Procedures.

The following privileges and roles are required to create and run unit tests:

  • User must be a standard user (since stub tables are always are created within the user’s schema).
  • Select permission must be granted on tables/views under test (in schema where the original tables/views reside).
  • (optional) For viewing and updating unit tests the following privileges must be granted on the package where the unit tests reside:


    Additionally, the execute privilege on SYS.REPOSITORY_REST must be granted to access the HANA repository.

  • (optional) Roles sap.hana.xs.ide.roles::EditorDeveloper and sap.hana.xs.debugger::Debugger are required for using the web-based development workbench and debug unit tests while runtime.
  • (optional) Role sap.hana.testtools.common::TestExecute is required for using the code coverage facility. (Even if this rule is designed for another purpose it is the only role which grants SELECT on the SAP_HANA_TEST schema.)

Writing Unit Tests

Unit tests are supposed to be created as XS JavaScript Library files (suffix .xsjslib) in HANA Studio. The basic syntax is explained in detail on the Jasmine project page. Simplest example:

/*global jasmine, describe, beforeOnce, it, expect*/
describe("test suite", function() {
       beforeOnce(function() {
              // called before running the test suite

       it("unit test", function() {

describe() implements a test suite and it() implements one unit test within that suite. The comment in the first line is specific for HANA. It must be added to each unit test file and tells the HANA Studio to consider the listed functions to be available. expect() brings along many functions to implement the unit test assertions.

A great variety of unit tests examples is provided with the test tools demos which can be found in package sap.hana.testtools.demos. Thus, this blog leaves it with a pretty simple unit test example. The object under test is an attribute view (AT_EMPLOYEE) which joins tables EMPLOYEE and DEPARTMENT with an outer join:


Note: Both, the attribute view as well as the unit test file must be located in a package which belongs to an XS Application.

When it comes to tests on views it’s mandatory to manipulate the content of referred tables in order to compare the view’s results afterwards with expected values. Certainly, that contradicts the idea of unit tests which are supposed to  test objects isolated. To solve that issue the Mockstar framework can be used to create a temporary copy of the view under test and replace the originally referenced tables with stub tables. These stub tables can be manipulated without worries. The following code would be used to isolate AT_EMPLOYEE:

var testEnv = mockstarEnvironment.defineAndCreate({
    targetPackage : 'UnitTesting.temp.' + mockstarEnvironment.userSchema,
    schema : 'UNIT_TESTING', // original schema
    model : { // object under test
        schema : '_SYS_BIC',
        name : 'UnitTesting/AT_EMPLOYEE'
    substituteTables : { // tables to be substituted
        "empl" : 'EMPLOYEE',
        "dept" : 'DEPARTMENT'

It’s common practice (within SAP’s demo unit tests) to do that isolation within the beforeOnce() function. After running the unit test for a first time the object under test will be copied into the specified target package. Its source tables will be substituted by identical copies of the original tables which are located in the user schema of the user who ran the test.

Note: It’s not possible to configure the target schema. The substitute tables will always be created in the user’s schema who run the test. If the original tables are located in the same schema (of the testing user) the unit test execution will cause the original tables to be deleted!

Note: Objects under test, in our example the attribute view, are taken from schema “<definition.schema>_SYS_BIC”, since that schema contains all activated objects.

A first, simple unit test could be one which truncates the employee table and checks if the view is running and is returning an empty result set:

// truncate employee table (substitute)
testEnv.clearTestTables([ 'empl' ]);

// check if the view’s result set is empty
sqlExecutor = new SqlExecutor(jasmine.dbConnection);
var actual = sqlExecutor.execQuery('select * from ' + testEnv.getTestModelName());
expect(actual).toMatchData({}, [ "ID" ]);

Complete Unit Test Example

After discussing all aspects of creating unit tests in the previous chapter I would like to show a complete unit test example in this chapter. Therefore, let’s extend the previous example and create a second calculation view (CA_DEPARTMENT_STATS) which simply aggregates the results of the first calculation view (CA_EMPLOYEE – I re-built AT_EMPLOYEE as calculation view with identical functionality for this example):


That view counts the number of employees per department. Two simple unit tests which prove the view’s functionality would be:

  1. Is the number of employees per department is calculated correctly
  2. Are employees without assignment to a department are counted correctly, as well

The following unit test suite implements both unit tests for view CA_DEPARTMENT_STATS:

/*global jasmine, describe, beforeOnce, beforeEach, it, xit, expect*/
var SqlExecutor = $.import("sap.hana.testtools.unit.util", "sqlExecutor").SqlExecutor;
var mockstarEnvironment = $.import("sap.hana.testtools.mockstar", "mockstarEnvironment");
* Test suite for testing CA_DEPARTMENT_STATS calculation view
describe("CA_DEPARTMENT_STATS", function() {
  var sqlExecutor = null;
  var testEnv = null;
     * Setup unit test environment
    beforeOnce(function() {
        testEnv = mockstarEnvironment.defineAndCreate({
            targetPackage : "UnitTesting.temp." + mockstarEnvironment.userSchema,
            schema : "_SYS_BIC",
            model : { // object under test
                name : "UnitTesting/CA_DEPARTMENT_STATS"
            substituteViews : { // views to be substituted
                "empl" : "UnitTesting/CA_EMPLOYEE"
     * Before each unit test: Reset test data in stubs
    beforeEach(function() {
        sqlExecutor = new SqlExecutor(jasmine.dbConnection);
        testEnv.fillTestTable("empl", [{
            ID : 1,
            NAME : "John Doe",
            DEPARTMENT : "IT"
            ID : 2,
            NAME : "Jane Doe",
            DEPARTMENT : "IT"
            ID : 3,
            NAME : "Peter Carrot",
            DEPARTMENT : "Accounting"
     * Check if aggregation by department works
    it("should return correct number of employees per department", function() {
      var expected = [{
          DEPARTMENT : "IT",
          NUM_EMPLOYEES : 2
          DEPARTMENT : "Accounting",
          NUM_EMPLOYEES : 1
      var actual = sqlExecutor.execQuery("select * from " + testEnv.getTestModelName());
      expect(actual).toMatchData(expected, [ "DEPARTMENT" ]);
     * Check if employees without assignment to a department are shown as well
    it("should aggregate employees without assignment to a department", function() {
        testEnv.fillTestTable("empl", [{
            ID : 4,
            NAME : "John Dolittle"
            ID : 5,
            NAME : "Jane Auster"
        var expected = [{
            DEPARTMENT : null,
            NUM_EMPLOYEES : 2
        var actual = sqlExecutor.execQuery("select * from " + testEnv.getTestModelName() + " where department is null");
        expect(actual).toMatchData(expected, [ "DEPARTMENT" ]);

Running Unit Tests

Because XS applications are run on the HANA server unit tests can’t be started out of HANA Studio. Before a unit test can be run it must be activated and then be run on HANA server using HANA’s web frontend which is accessible in default setup on URI http://<hostname>:80<instance>.

On the one hand, on HANA’s web frontend the test runner tool can be used. It is contained in the HANA test tools and does accept parameters which define the target unit test. It searches <package> for unit test with name <pattern>:


On the other hand, unit tests can be run from HANA’s Web-based Development Workbench:


Note: Microsoft Internet Explorer 10+, Mozilla Firefox, and Google Chrome are supported.

Test Data

The XSUnit framework brings along two mechanisms to populate substitute tables with test data. These test tables can be populated with single test records. With regard to the unit test example above the following snippet demonstrates how to insert single records:

testEnv.fillTestTable('empl', {
       ID : 123,
       NAME : 'John Doe',
       DEPARTMENT_ID : 1

On the other hand, substitute table can be populated from CSV files which are available in the HANA repository. Thereby, the CSV properties and source package must be defined within the Mockstar environment definition, the table load is done by a separate command which can be placed anywhere in the unit test suite:

testEnv = mockstarEnvironment.defineAndCreate({ // only supplement for definition!
     csvPackage : "UnitTesting.object",
     csvProperties: {
         separator: ";",
         headers: true,
         decSeparator: ","

testEnv.fillTestTableFromCsv("empl", "employee.csv");

Expected values can be matched against the actual view output as illustrated by the following example:

var expected = {
     ID : [ 123 ],
     NAME : [ 'John Doe' ] ,
var actual = sqlExecutor.execQuery('select * from ' + testEnv.getTestModelName());
expect(actual).toMatchData(expected, [ "ID" ]);

Truncating substitute tables works for single as well as for all dependent tables:

testEnv.clearTestTables([ 'dept' ]);

Code Coverage

Since SRS 09 the open-source code coverage library BlanketJS is integrated into the HANA Test Tools. It measures to which extent JavaScript code is covered by unit tests. Unfortunately, for unit tests on model views (attribute views, analytic views, calculation views and procedures) this approach can’t be applied.

Debug Unit Tests

When running unit tests for a first time you might miss privileges or have semantic failures in your code. If that occurs you will be provided with stack traces and error messages. If those information are not sufficient to understand the issue there are options to debug server-side JavaScript code. Thereby both, your unit test code as well as the HANA test tool libraries can be debugged (to a certain extent).

For enabling the debugging functionality follow the instructions on These instructions will guide you (1) to deploy delivery unit HANA_XS_BASE and (2) add section “debugger” to “xsengine.ini”. Afterwards the debugging can be initiated from HANA Studio or from Developer Workbench.

Regarding the example unit test above a possible scenario for receiving a meaningless error message is when you miss privileges to run the attribute view. In that case error “258 – insufficient privilege” will be returned. When running the unit test from Developer Workbench the following output will be shown:


To initiate debugging (1) make sure that the target session for debugging is THIS_SESSION in editor settings, (2) set a breakpoint on the code line were you want to start the debugging by clicking on the line number and (3) click on link “Create test report” which is located bellow the test results. The link will re-run the unit test in a new browser tab. Because you set a breakpoint the execution will stop at the specified code line and the debug control will appear in the Workbench:


Use the Step In (F3) and Step Over (F4) buttons to navigate through the code.

Note: When it comes to debugging the Development Workbench behaves different depending whether the file is a standard JavaScript file or an Unit Test file. Standard JavaScript files can be run immediately in debug mode by setting breakpoints and run the script/library. Unit Test files must be run in another browser tab (via TestRunner.xsjs) to debug that file in Workbench.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Very nice article. Thanks !!!

      Author's profile photo Former Member
      Former Member


      I have a problem with unit testing a calculation view. I followed this article, and it worked fine. I saw that the test model, the converted calculation view, was generated under "_SYS_BIC".""UnitTesting.temp.<test user>".

      Later, I added a parameter to the calculation view. I found that the generated view for testing was not adapted. It lacked the new parameter. How can I enforce generation of the test model?

      I am on SP09, (97).

      Thanks, Volker.

      Author's profile photo Alexander Rosell
      Alexander Rosell
      Blog Post Author

      Hello Volker,

      I‘ve experienced as well that XSUnit reuses test objects sometimes. However, as soon as you clear the objects in your temporary test package (see Mockstar parameter “targetPackage”) the objects in _SYS_BIC will be deleted automatically. The next run of you unit tests will generate the test model again.

      Best regards, Alexander

      Author's profile photo Sivakumar Palaniswamy
      Sivakumar Palaniswamy

      Hi Volker,

      Can you please me in this HANA XS Unit Test?

      I am trying a simple scenario like this in blog but not able to achieve it. It me error that dependency table not found.

      Appreciate the help!


      Author's profile photo Former Member
      Former Member

      Hello Alexander,

      thanks, it worked.

      However, I had some after-work to do. The unit tests were stuck with an error message like:

      beforeOnce: Error: Repository:
      change(s) have wrong/unexpected status;Repository::releaseChange(): not all
      contributions to change (srcSystem: ANA, changeNumber: 116619, container: )
      have been validated in
      /sap/hana/testtools/unit/util/internal/repositoryChangeManager.xsjslib (line

      So I had to find all open changes in the change manager which contained the deleted generated objects, and release them. These changes were not only my changes but also auto-generated changes from Mockstar.

      Then it worked.

      Regards, Volker.

      Author's profile photo Venkata Narasimha Rao Sandu
      Venkata Narasimha Rao Sandu

      Good article..


      do you have any example of how to unit test the CDS Views in HANA or ABAP with mock test frame work ?

      Author's profile photo Former Member
      Former Member

      Hi, Alexander,

      I've a question is, if the model calling a substitute procedure which needs to create substitute tables, how can I do it in Jasmine? Can we create substitute tables for the substitute procedures within Jasmine Unit Test?

      Author's profile photo Sivakumar Palaniswamy
      Sivakumar Palaniswamy

      Dear Alexander Rosell , i was trying to replicate your employee example in my system under my repository package with just one table called employee just to reconcile the number of records between table and cal view output. But i am not able to progress as i am getting error like below . Any help on this is much appreciated.

      beforeOnce: Error: Test Model creation is aborted: found no dependency to be substituted in /sap/hana/testtools/mockstar/hierarchyCreator.xsjslib (line 40)
      Author's profile photo Amaury Van Espen
      Amaury Van Espen

      Good morning,

      Is there a SAP Training about HANA_TEST_TOOLS ?
      not able to find anything else than which does not seem to be related to HANA_TEST_TOOLS / XS Unit or Jasmine

      Thank you