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

[former_member192683]

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.

[mike_howles4]

Nice post, and quite timely.  Question… Suppose we take ‘Option 1’, which is where I have created the time tables and generated some time data in HDI container.  This works great for my build time DB Module, but when I deploy the module, the tables exist but are empty.  Is there a procedure I should call to fill them there?

EDIT - Figured it out by snooping at the http calls when generating time data on the build time version.  4 MDX statements were observed which I was able to capture and modify for execution on my deploy-time DB Module from DB Explorer:

MDX UPDATE TIME DIMENSION Day 1990 2025 FIRST_DAY_OF_WEEK Sunday TARGET_SCHEMA 'MD_GLOBAL' TARGET_TABLE 'M_TIME_DIMENSION';

MDX UPDATE TIME DIMENSION WEEK 1990 2025 TARGET_SCHEMA 'MD_GLOBAL' TARGET_TABLE 'M_TIME_DIMENSION_WEEK';

MDX UPDATE TIME DIMENSION MONTH 1990 2025 TARGET_SCHEMA 'MD_GLOBAL' TARGET_TABLE 'M_TIME_DIMENSION_MONTH';

MDX UPDATE TIME DIMENSION YEAR 1990 2025 TARGET_SCHEMA 'MD_GLOBAL' TARGET_TABLE 'M_TIME_DIMENSION_YEAR';

[former_member424083]

Hi,When I try Option 1, I get an error:

File creation error
No HDI service available , try activating the project

 

How should I solve it?

 

[former_member192683]

Hi,

Build your HANA database module before you go for the step Modeling Actions > Maintain Time Tables.

You should have an HDI service at first, to create tables on it.

[former_member701856]

Hi,

I am trying to setup a live connection to SAP HANA Cloud to enable time enriched dimensions in SAP Analytics Cloud (SAC). As some useful features in SAC (such as variance calculation, forecast on line charts, etc) depends on time hierarchies, I need to model time in my project, which consists on a cube Calculation View linked with some dimension Calculation Views using a Star Join. I have a SAP HANA Cloud running in a Cloud Foundry (CF) environment.

One solution would be importing the HANA MODEL DATE ENRICHMENT delivery unit (DU) to the remote HANA system. However, this feature is not supported currently with my type of connection, as described here: https://help.sap.com/viewer/00f68c2e08b941f081002fd3691d86a7/release/en-US/b152affae88841cb9fec310fb...

In this sense, I would like to know how can I use the M_TIME_DIMENSION tables to build time hierarchies in SAC?

Best Regards.

Marcos Carvalho.

[KarelVerbanck]

Hi Mike, MDX is not supported in hana cloud. Do you have an alternative for launching the statements on hana cloud?

 

Thx.

Kr,

Karel