Persistence Types

There are three different persistence types: tables, native data store objects (NDSOs) and temporal tables.

Tables

The SAP HANA database supports two types of table: those that store data either column-wise (column store tables) or row-wise (row store tables). SAP HANA is read-optimized for column storage and this is usually the most common type of table used.

Conceptually, a database table is a two-dimensional data structure with cells organized in rows and columns. Computer memory, however, is organized as a one-dimensional, linear sequence of column values. For a row store table, all the columns of each record appear one after the other. Whereas for a column store table, all the value of a column across all records in the table, appear one after the other. There are times when a row store maybe better to use than a column store:

When to use which storage type:

Column Store

Calculations are typically executed on individual or a small number of columns.
The table is searched based on the values of a few columns.
The table has a large number of columns.
The table has a large number of rows and columnar operations are required (aggregate, scan, and so on)
High compression rates can be achieved because the majority of the columns contain only a few distinct values (compared to the number of rows).

Row Store

The application needs to process only one single record at one time (many selects and/or updates of single records).
The application typically needs to access the complete record.
The columns contain mainly distinct values so compression rate would be low.
Neither aggregations nor fast searching are required.
The table has a small number of rows (for example, configuration tables).

Standard tables (row or column) can be created directly in the SQL Console using SQL statements. They can also be generated from the build of a design-time file of either the type .hdbtable or .hdbcds. Use .hdbtable if you want to work with standard, well-known SQL syntax to create tables. Use CDS if you would like to take advantage of the additional rich, domain meta-data that can be embedded into the tables alongside the basic table definition.

Note

In this course we use CDS artifacts for our basic table definition, although .hdbtable files could also have been used.

Native Data Store Object

Native DataStore Objects (NDSO) are actually a collection of standard SAP HANA column tables, but also include automatically generated procedures for loading and activating data in complex data staging scenarios. NDSOs are created using the design-time file type .dwfndso

NDSOs provide a persistence object with additional semantics to determine the delta and were established to model SAP HANA SQL Data Warehouses. NDSOs can handle multiple inbound queues (NDSO InboundQueue) to load data from different sources. They allow merging of delta data and also full data loads and also provide delta-data processing capabilities to connected data targets. The data merge process is triggered by the NDSO Activation Process/Step. NDSOs can process records based on the source-data specified RECORDMODE values (such as INSERT/UPDATE/DELETE) also known as the CDC-Pointer. NDSOs are developed with the SAP Web IDE for load monitoring and request handling features such as roll-back. They can integrate natively with SAP HANA flowgraphs allowing parallel loading, and also with third-party ETL tools. Finally, they support the "delta language" of SAP provided data source extractors, which means you could use them to load SAP source data using delta handling, with very little effort.

Temporal Tables in the DW Context

Temporal tables are tables whose records are associated with one or more temporal periods.

System-versioned tables allow change tracking on database tables.

Validity periods are automatically maintained by the database whenever a record is changed.
Retrospective changes to the validity period of a record are strictly prohibited. A new version is inserted for every change to preserve a records history.
Temporal information must have a very high granularity.

Application-time period tables capture the time, in which a record is valid in the real world.

Validity periods are determined by the application.
Application can update validity period of a record, for example, to correct errors.
Temporal information can arbitrarily reflect the past, present or future at any granularity.

Bi-temporal tables combine system-versioned tables and application-time period tables.

Temporal Tables: System-versioned Tables

System-versioned tables in SAP HANA are based on regular column-store tables. As such, they offer all capabilities that are available with column store tables.

Two structurally equivalent tables: CURRENT and HISTORY.
Tables are automatically combined in the SQL layer.
Validity periods are based on data type TIMESTAMP.
Inherent support for table partitioning and associated features.
Table export and import.
Validity periods are visible externally.

Note

The first type of temporal tables were known as history tables. These handled a very basic historization. History tables have now been replaced by system-versioned tables and application-time period tables.

System-versioned tables and application-time period tables can be created directly in the SQL Console using SQL statements. They can also be created using design-time files of the type hdbsystemversioning and .hdbapplicationtime.