How to Write SAP Datasphere Data into NextTables Tables with a Task Chain

You will learn

How to load data from an SAP Datasphere space into a NextTables table on a schedule. NextTables stores its tables in the Open SQL schema of a database user of your space (schema name SPACE#USER). An SQL procedure in that schema reads your source view and merges the rows into the NextTables table, and a task chain runs the procedure on a schedule. The loaded rows are available in NextTables as soon as the run finishes.

This article is for SAP Datasphere modelers and NextTables administrators who want to fill a NextTables table from data that already lives in a Datasphere space, for example to prefill planning values that business users then maintain in NextTables.

The procedure writes only the columns you list, so values that users maintain in NextTables, such as comments, survive every load.

Prerequisites

• A read/write database connection from NextTables to the Open SQL schema of your space, set up as described in How to Create a Database Connection to SAP Datasphere. It is listed in NextTables under Administration → Databases.
• Access to the SAP HANA Database Explorer as the database user of that Open SQL schema.
• Space administrator rights in SAP Datasphere, to change the privileges of that database user.
• Permission in the space to create, deploy and run task chains in the Data Builder.
• Your consent for SAP Datasphere to run tasks on your behalf, given in your profile settings under Authorized Consent Settings. A task chain cannot run a procedure without it, even when you start the chain manually. The consent is valid for 365 days.
• Administrator access in NextTables, for Connect table and for the table settings.
• A space without the Relational Data Lake Engine. Task chains cannot run Open SQL procedures in spaces where it is enabled.

Two ways to create the target table

You can create the target table in NextTables (path A) or create it with SQL and then connect it to NextTables (path B). Both work. They differ in how a row is identified, and that decides what the load has to do.

Step-by-Step Instructions

1) Create the target table

Path A: create the table in NextTables

1. Create the table in NextTables as usual. NextTables creates it in the Open SQL schema and adds the technical column NEXTHUB_ID (VARCHAR(255), primary key, no default value).
2. Open the Table settings of the table. Under Data history, select Don't track data history and confirm the dialog. The full procedure, and what the change removes, is in How to turn off data history for externally written tables.

Path B: create the table with SQL and connect it in NextTables

1. Sign in to the SAP HANA Database Explorer as the Open SQL database user, and create the table with a business primary key:

   CREATE COLUMN TABLE "SALES#NEXTTABLES"."PLAN_DATA" (
     COMPANY_CODE  NVARCHAR(4)   NOT NULL,
     FISCAL_PERIOD NVARCHAR(7)   NOT NULL,
     ACCOUNT       NVARCHAR(10)  NOT NULL,
     AMOUNT        DECIMAL(17,2),
     COMMENT       NVARCHAR(500),
     PRIMARY KEY (COMPANY_CODE, FISCAL_PERIOD, ACCOUNT)
   );

2. In NextTables, open Administration → Tables and click Connect table.
3. Select the Database, the Table and the Folder the table should appear in.
4. Click Connect table to confirm.

The table can now be read and edited in NextTables. Because the table has a primary key, NextTables uses it to identify each row, and the panel asks for no key columns. A table connected this way starts with data history already turned off, so there is nothing to change in its settings.

2) Give the Open SQL user read access to the source

The procedure reads your source data with the rights of the Open SQL database user that owns it, while the task chain starts it on behalf of the space. That user can read views of the space that are exposed for consumption, and it must be allowed to pass that read access on. Without the grant option, every run stops with error 258 (insufficient privilege).

1. In the Data Builder, open the view that delivers the data to load (in the example V_PLAN_SOURCE), switch on Expose for Consumption, and deploy the view.
2. Open Space Management, select your space and click Edit.
3. Go to Database Access → Database Users, select the Open SQL database user and click Edit Privileges.
4. Switch on Enable Read Access (SQL) and, below it, With Grant Option. Save. Changes in this section take effect immediately.

3) Create the load procedure

In the SAP HANA Database Explorer, signed in as the Open SQL database user, create a procedure that merges the source into the target table. The MERGE statement matches rows on the business keys: it updates the rows that already exist and inserts the ones that do not.

Path A: table created in NextTables

CREATE PROCEDURE "SALES#NEXTTABLES"."LOAD_PLAN_DATA"
  LANGUAGE SQLSCRIPT
  SQL SECURITY DEFINER
AS
BEGIN
  MERGE INTO "SALES#NEXTTABLES"."PLAN_DATA" AS T
  USING "SALES"."V_PLAN_SOURCE" AS S
    ON  T.COMPANY_CODE  = S.COMPANY_CODE
    AND T.FISCAL_PERIOD = S.FISCAL_PERIOD
    AND T.ACCOUNT       = S.ACCOUNT
  WHEN MATCHED THEN
    UPDATE SET T.AMOUNT = S.AMOUNT
  WHEN NOT MATCHED THEN
    INSERT (NEXTHUB_ID, COMPANY_CODE, FISCAL_PERIOD, ACCOUNT, AMOUNT)
    VALUES (S.COMPANY_CODE || '|' || S.FISCAL_PERIOD || '|' || S.ACCOUNT,
            S.COMPANY_CODE, S.FISCAL_PERIOD, S.ACCOUNT, S.AMOUNT);
END;

Rows that already exist keep their NEXTHUB_ID, including rows that users created in NextTables. Only new rows receive an ID, built here from the business keys. The NEXTHUB_ID column is mandatory, has no default value and holds up to 255 characters, so the key columns of new rows must never be empty (NULL).

Path B: table created with SQL

Use the same procedure without NEXTHUB_ID. The table has no such column, and the primary key does the identification:

  WHEN NOT MATCHED THEN
    INSERT (COMPANY_CODE, FISCAL_PERIOD, ACCOUNT, AMOUNT)
    VALUES (S.COMPANY_CODE, S.FISCAL_PERIOD, S.ACCOUNT, S.AMOUNT);

In both paths, list under UPDATE SET only the columns that Datasphere owns. In the example, users maintain COMMENT in NextTables, so the procedure leaves it untouched. The source must deliver each key combination only once.

Name the columns of the target table as they appear in the Open SQL schema. For a table created in NextTables, these technical names can differ from the names shown in NextTables: spaces become underscores, and names may be converted to uppercase.

4) Allow the space to run the procedure

The task chain runs in the space, so the space needs the EXECUTE privilege on the Open SQL schema. Only the Open SQL database user can grant it.

1. In Space Management, under Database Access → Database Users, select the Open SQL database user and click Open Database Explorer.
2. In the SQL console, run:

   CALL "DWC_GLOBAL"."GRANT_PRIVILEGE_TO_SPACE"(
     OPERATION   => 'GRANT',
     PRIVILEGE   => 'EXECUTE',
     SCHEMA_NAME => 'SALES#NEXTTABLES',
     OBJECT_NAME => '',
     SPACE_ID    => 'SALES');

The procedure writes with the rights of the Open SQL database user that owns it, so the space needs no write privileges on the table itself. To remove the privilege later, run the same call with OPERATION => 'REVOKE'.

5) Run the procedure in a task chain

1. In the Data Builder, create a new Task Chain.
2. In the left panel, open the Others tab and the SQL Script Procedures folder. Procedures are listed with the database user's name in front, in the example NEXTTABLES.LOAD_PLAN_DATA. Drag the procedure onto the canvas, where it appears as a Run SQL Script Procedure task.
3. Place other steps in front of it if the load depends on them, for example a replication that fills the source.
4. Save and deploy the task chain.
5. Schedule the task chain, or run it manually. Each run of the procedure is listed with the task chain in the Data Integration Monitor, including the error code if it fails.

After the run, the rows appear in NextTables. Values that users entered stay in place:

Why this article does not use a data flow

A data flow can write to a table in the Open SQL schema, but it writes whole rows. Every column of the target table that the data flow does not map is set to empty on each row it writes. In the scenario of this article, users add values in NextTables to rows that Datasphere fills, for example comments on actuals that are reloaded every night. A data flow would empty those columns on every run.

Use the procedure for this scenario. Its MERGE updates only the columns listed under UPDATE SET, keeps the NEXTHUB_ID of every existing row, and leaves everything that users maintain untouched. SAP also recommends replication flows and transformation flows over data flows, and a transformation flow cannot write to the Open SQL schema.

Troubleshooting / FAQs