Create Time-Related Tables and Generate Time Data in SAP HANA XSA Platform

Introduction

Time data is necessary almost in any kind of data manipulation process, as well as adding time-dimension to the calculation views. So, ways to go for the time-dimension has changed a bit with the release of SAP HANA XSA Platform. In this blog post, you’ll see how to create time-related tables if required, and generate time data through the steps on SAP WEB IDE.

If you’d like to check how time data generation is handled in XS Classic, which is the previous version of XS Advanced, you can visit this nice blog on this link.

Context

You can create and fill time-related tables by using the standard tool SAP WEB IDE provides.

There are two options to generate time data in XSA Platform in terms of where database objects are stored. These objects can be stored in:

1. HDI Container’s Schema.
2. Classical _SYS_BI Schema

Let’s go through these in detail.

Option 1: In the HDI Container’s Schema

This is the brand new method came with XSA and lets you have HDI container specific time tables. In this case, there is a newly created SAP HANA Database Module which means time tables must be created at first and time data will be generated afterwards.

Procedure

In the image below, you see an already created MTA project called “HANA01” and a HANA Database Module with the name of “HANADB01”. I assume you’ve already created these. In the workspace view, we do right click on the HANADB01 folder which corresponds to SAP HANA Database Module, and choose Modeling Actions > Maintain Time Tables

The following preferences are presented as in the image below. If you only need time data with day granularity you can just select it as per your requirement and only tables related to the selected preferences will be created for the HDI container. For demonstration purposes, all the options are selected.

If you click “Generate Data After Creation” option, tables will be automatically created and data will be generated as soon as you click “Generate” button. In other words, it is an immediate trigger for the database module building process.

HANA CDS documents are created automatically under the automatically created “time_tables” folder. Each CDS document refers to the time table with the same name.

Now, switch to the Database Explorer view and verify the tables in the HDI container.

For the Gregorian calendar type, modeler has generated time dimension data into M_TIME_DIMENSION_YEAR, M_TIME_DIMENSION_MONTH, M_TIME_DIMENSION_WEEK, M_TIME_DIMENSION tables in the HDI container.

Data can be verified as follows.

Option 2: In the Classical _SYS_BI Schema

This option lets you have time data in the classical system default _SYS_BI Schema. This is also the option we only had in XS Classic. Before XSA, it was developed by using Hana Studio/Eclipse.

Tables are present as default within _SYS_BI Schema, so only time data has to be generated.

Prerequisite

For this option, you will need an HDI Container based on a user-defined service which has insert privileges to the _SYS_BI Schema. CROSS_SCHEMA_PRODUCTS HDI will be used for this purpose.

Procedure

Go to the Database Explorer view, expand the HDI container, right click on “Tables” and select “Generate Time Data”.

Following preferences are displayed. I choose “Day” as granularity and “Gregorian” as calendar type in this case.

Open an SQL Console and verify the data with the following SQL statement.

select * from "_SYS_BI"."M_TIME_DIMENSION";

--For other granularity levels
select * from "_SYS_BI"."M_TIME_DIMENSION_MONTH";
select * from "_SYS_BI"."M_TIME_DIMENSION_WEEK";
select * from "_SYS_BI"."M_TIME_DIMENSION_YEAR";

In the following image, data in the “_SYS_BI”.”M_TIME_DIMENSION” is previewed.

Conclusion

So far, we’ve covered details to create time-related tables and data in the SAP HANA XSA Platform. After having tables and data ready, you can now add time-related tables in calculation views as a data source or use them in any executable SQL statements. The same steps can also be used in SAP Cloud Platform Cloud Foundry.

Which option you’ll choose mostly depends on the requirement. However, the first option provides flexibility to have a separate configuration to generate time data for each SAP HANA Database Module. So, each HANA Database Module can have its own generated time data.