Develop a Custom Data Warehouse on SAP HANA

SAP Native DataStore Object

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Explain the basic functionality of the SAP Native DataStore Object

SAP Native DataStore Object

Definition

The Native DataStore Object (NDSO) technically consists of three connected tables: The Inbound Queue which handles the data load, the Active Data Table which holds the actual present data, and the Change Log which stores the changes that were made to the Active Data Table.

NDSO functionality:

  • Provides a central persistency object with additional semantics to determine deltas
  • Was established to model SAP HANA SQL Data Warehouse systems
  • Handles multiple Inbound Queues to load data from different sources
  • Allows merging of delta data and full data loads into its reportable content and also provides delta-data processing capabilities to connected data targets
  • Data merge process triggered by the NDSO-Activation Process/Step

The NDSO is capable of processing records based on the source-data specified RECORDMODE column that contains values such as INSERT, UPDATE, and DELETE. These values are also known as the Change Data Capture (CDC) pointer. The RECORDMODE describes how the individual records will be processed and managed. There are six options available:

  • 'N': The record delivers a new image (Insert).
  • 'A': The record delivers an additive image (Update).
  • 'D': The record needs to be deleted (Delete).
  • 'R': The record delivers a reverse image.
  • 'X': The record delivers a before image.
  • '<SPACE>': The record delivers an after image (FULL Load Scenarios - Measure Aggreation: MOVE).

Benefits

  • Provides request management and delta handling out of the box
  • Is delivered with a user-friendly interface (Web IDE) for load monitoring and request handling features such as roll-back
  • Handles multiple inbound queues to load data from different sources
  • Can be defined in a textual and graphical way
  • Integrates natively with EIM flowgraphs allowing parallel loading, and with third-party ETL
  • Supports the "delta language" of SAP data source extractors

Use Case 1

The following figures show four use cases for the NDSO.

In use case 1, we have an initial load dataset as well as a delta load dataset. First you see what happens when the data is loaded initially. Afterward, you see what happens when a delta dataset is loaded.

First, the initial load dataset is loaded to the Inbound Queue table with record mode 'N', which means the record is supposed to deliver a new image (or it is supposed to be inserted). Note that for now the Active Data Table and the Change Log remain unchanged. Only upon NDSO activation (either manually via SAP Web IDE or via a Task Chain) is the data from the Inbound Queue loaded to the Change Log and the Active Data Table.

To explain the delta load we focus on the first row in the delta load dataset (Customer: "Muhammed MacIntyre"). It shows a change to an existing record. We see the RecordMode is 'A' which means the record is supposed to deliver an additive image - or in other words, is supposed to be updated. More specifically, we see that the order quantity and sales values are supposed to be increased. Again, the Active Data Table and the Change Log remain unchanged until the NDSO is activated.

After activating the NDSO for customer Muhammed MacIntyre, there are now three records in the Change Log with different RecordModes, OrderQuantities and Sales values:

  • RecordMode 'N' which means the record delivers a new image (insert) (OrderQuantitiy = 6, Sales = 261.54)
  • RecordMode ' ' (blank) which means the record delivers an after image. Or in other words, the record displays the actual situation after the delta load (OrderQuantity = 10 (6+4), Sales = 436 (261.54 + 174.46))
  • RecordMode 'X' and reversed sign ("-") which means the record delivers a before image (OrderQuantity = -6, Sales = -261.54)

In the active table finally only the after image record is updated (OrderQuantity = 10, Sales = 436). If a request which is already loaded and activated needs to be deleted, this is possible any time with the information tracked in the Change Log (Before-/ After Images). With the NDSO all these mechanisms are delivered out-of-the box and can easily be customized according to the requirements of a given scenario. This drastically reduces development efforts and ensures data consistency.

Use Case 2:

In this use case, we have an initial load dataset as well as a delta load dataset again. The difference to use case 1 is that the datasets are processed via two connected NDSOs with different granularities concerning the key columns in their respective Active Data Tables. Again, first you see what happens when the data is loaded initially. Afterwards, you see what happens when a delta dataset is loaded.

First, the dataset is loaded to the Inbound Queue of the first NDSO. Upon activation, the data is processed by the Active Data Table (key column SalesOrderID) and the Change Log of the first NDSO - this process is similar to the one in use case 1. Note that the key column in the Active Data Table is SalesOrderID.

Now, in the second NDSO there are some differences. The Change Log of the first NDSO (not the initial load dataset) is loaded into the Inbound Table of the second NDSO. Upon activation of the second NDSO the data is processed by the Active Data Table and the Change Log of the NDSO. Note that the key columns in the Active Data Table of the second NDSO are CustomerNo, ProductID, and CalYear.

The delta load of the first NDSO works exactly as in use case 1.

Similar steps to the delta load of the fist NDSO can be observed in the second NDSO. However, the data loaded into the Inbound Queue of the second NDSO comes from the Change Log of the first NDSO. Note that all records except those with RecordMode 'N' are loaded here. Upon activation of the second NDSO, again, only the after image record for Muhammed McIntyre is stored in the Active Data Table. At the same time all three reference records are stored in the Change Log of the second NDSO, similarly to the Change Log of the first NDSO.

Use Case 3

If you want to roll back the Active Data Table to a previous state, you can delete specific load requests. Use case 3 shows you how this works.

In this use case the Inbound Queue remains empty. Instead the NDSO is activated with a DELETE statement. In the example, to roll back the latest changes on the first NDSO, we delete the records of the technical attribute activationID in the Change Log that match the value 9 (DELETE activationID = 9).

Accordingly, the respective records in the Active Data Table are deleted.

Similarly, the latest changes on the second NDSO can be rolled back via a DELETE statement in the technical attribute activationID = 8.

Use Case 4

NDSOs can be integrated directly into calculation views. This can be achieved, for example, using a Join node that takes as an input, the Active Data Table of the NDSO.

Scheduling the Data Load and Activation

To load the NDSO inbound queue, you create a flowgraph. A flowgraph provides the full etl functionality to extract harmonize, cleanse and transform data from multiple sources of type. Simply assign an NDSO as the target of the data flow. When you do this, a projection and procedure are automatically generated and inserted into the flow. The projection node takes care of the column mapping and generation of extra required technical columns. The procedure takes care of the loading to the inbound queue.

To schedule the flowgraph, you create a task chain.

To activate the load request to an NDSO you can either use the NDSO 'Manage' console. Simply select each load request and hit the Activate button. You can also roll-back requests using the Delete button.

There is a special task included in the task chain editor that is used to kick-off the activation of a load request. This means you can activate immediately after a successful load.

Note
Notice how the loading task is not a flowgraph but a procedure. This is because a flowgraph that is used to load a NDSO is automatically set to a procedure type of flowgraph, and not a task type. Simply specify the name of the NDSO load procedure that was generated when you created the flowgraph.

Create and Stage Data through a Native DataStore Object

Save progress to your learning plan by logging in or creating an account