Tables play a key role in a data warehouse. Tables are used in the staging layer to temporarily store data for staging purposes, where the data might be deleted once it has passed through. Or the tables might be used to store data permanently which is most likely in the layers that form the core data warehouse layers such as RAW and Business Integrated Layer.
There are multiple approaches to defining tables in the SAP HANA. The simplest method would be to execute create table statements in the SQL console of SAP HANA. However, this is not recommended because SAP HANA XSA offers a better approach. That is, to define the tables using source files so that the table definitions are collected with the other artifacts of the overall project. This means all the source files in your project can be transported and deployed together. If you define tables separately, for example, using the SQL console, they can easily become out of sync with the rest of the artifacts of the project. Without source file definitions it would also mean you would then need to define synonyms within your project that point to each table that you refer to in your procedures, functions and views. Remember, with XSA, each database object that your refer to must be represented with a source file in your project.
SAP HANA provides multiple source file types to define tables. These are:.
- .hdbtable
- .hdbdropcreatetable
- .hdbmigrationtable
- .hdbcds
Defining Tables using .hdbtable
The .HDBTABLE file format uses a well-known DDL-style syntax, which is equivalent to the SQL syntax in the CREATE TABLE SQL command, but without the leading create keyword. If you change the table definition the data is then automatically migrated to the new table. For very large tables this can be costly.
Defining Tables using .hdbdropcreatetable
The drop-create-table format does not provide any data migration. If you change a table definition, the build will drop an already deployed version of the table and then recreate the table. If the table contains data, the build is not allowed, since the data would be lost. You first have to delete the data in the table before the build.
Defining Tables using .hdbmigrationtable
In contrast to the .hdbtable, the migration-table format .hdbmigrationtable uses explicit versioning and migration tasks. This means that the modifications of the database table are explicitly specified by the developer in the source file using alter statements and are carried out on the database table exactly as specified in the statements, without incurring the cost of an internal table-copy operation such as the one performed by the .hdbtable format. This behavior makes the .hdbmigrationtable especially useful for tables that contain a lot of data. When a new version of an already existing table is deployed, the .hdbmigrationtable file performs the migration statements that are missing for the new version.
Defining Tables using .hdbcds
SAP developed a new data definition language called core data services (CDS). This language is used in many SAP solutions including SAP S/4HANA. The key idea is that as well as providing the definition of the table structure, you can also provide additional syntax to describe the semantics of the data known as annotations. For example, the currency of a measure or the title / label of each column that would appear in a report, could be defined within the table definition. With CDS you can also define the relationships to other tables by using associations.
There are two versions of CDS: ABAP CDS and native SAP HANA CDS. Both follow the same design principles and share much of the syntax. Because a custom SQL data warehouse does not use ABAP, we would therefore need to use SAP HANA native CDS. However, you should bear in mind that SAP have recently decided to discontinue the future development of native SAP HANA CDS and do not recommend this file type for new projects. SAP HANA CDS language is still supported if you choose to go ahead and use this type of file. Instead, SAP recommend you choose one of the other file formats to define your tables.
Just to be clear: ABAP CDS is also still supported but is not valid for native SAP HANA development projects. ABAP CDS is only applicable where an ABAP application server is implemented, such as with SAP S/4HANA. Native SAP HANA CDS for the on-premise version was redesigned for SAP HANA Cloud but uses a new design that is incompatible with the SAP HANA on-premise version. Many customers who begin with SAP HANA on-premise developments plan to move them to the cloud. This would be a problem if you had chosen native SAP HANA CDS on-premise as there is no automatic migration.
Note
At the time this course was developed, SAP HANA native CDS was still recommended. However, it makes very little difference to how the tables are defined in our project, so we decided to keep the CDS files and not rewrite this section. Ultimately, the generated tables are the same regardless of the source file you choose to define them. Our tables are quite simple and using .hdbtable or .hdbcds makes no difference to the final result, and they syntax is very similar.CDS Object Editing Using Plain Code or the Graphical Interface
The first layer where we need to implement tables is the STAGE folder. We will use the Web IDE to define the tables using SAP HANA CDS files. Every table required in the data model is defined in an individual CDS source file. It is possible to define multiple tables in one source file but we will keep things simple.
To create and edit CDS objects, Web IDE offers two possibilities. You can either use the CDS Code Editor, for plain code-based programming, or the graphical CDS Editor which enables GUI-oriented programming that does not require knowledge about the CDS language.
Following the reference architecture, CDS objects are located in the RAW and BID folder.
CDS Activation in SAP HANA Web IDE
In order to activate the CDS objects (transition from design-time objects to run-time objects) they must be built. In the process of building the CDS objects, the XSA platform will compile them and put them in dedicated HDI containers. When the build process is done, it is possible to inspect and interact with the tables using the Database Explorer in Web IDE. Here you can see all the database artifacts that run inside your container.
As with all other database objects that are built from source files, you should never directly delete them in the SQL console. Instead, you should always delete the source file and then rebuild the parent folder, even if the folder is empty. The build process will realize the run-time object has no corresponding source file and will automatically delete the run-time object.