Implementing the Data Warehouse Model

In Unit 2 we introduced to you a reference SAP SQL Data Warehouse architecture. Now, in this unit, you will see how the architecture is implemented in SAP Web IDE.

The database artifacts we will create will be stored in a SAP HANA Database (HDB) module. A SAP HANA database module is a collection of related design-time database artifacts, such as data models, views, procedures and of course tables. If you build the HDB module, the design-time objects become run-time objects which are collected in respective HDI containers. When you create a HDB module in SAP HANA Web IDE, the folders cfg and src are created automatically for you. The cfg folder is used to store configuration files for the module, for example the services.hdbgrants file which is used to manage roles and privileges for the module. The src folder is used to develop the actual database artifacts. In our example, it contains individual folders for the respective layers of the reference architecture - namely the Stage area, the Raw and Business Integrated Data Warehouses, the Virtual Analytical Layer, and the Data Mart.

For the purpose of creating scheduled Task Chains we will use a Data Warehouse Foundation (DWF) module. This is basically a node.js application that will run the Task Chains for you. In our example we are using separate folders for the DWF application backend and the DWF application components. For technical reasons both folders share the same hierarchy in the project repository. The Backend folder contains all the backend files necessary to run the service. The DWF folder contains the actual DWF components, such as Task Chains or Native DataStore Objects (NDSO).

As our main focus in this unit is on the implementation of the reference architecture via the BIonHANA HDB module, you can find a more detailed explanation of the HDB folders below.

The Stage folder contains two sub-folders, etl and synonyms. Flowgraphs are stored in the etl folder and synonyms are stored in the synonym folder. Further details about the purpose and functionality of flowgraphs and synonyms is discussed in the next lesson.

Similar to the STAGE folder, the RAW folder also contains an etl sub-folder for flowgraphs. Instead of synonyms, however, here we can find a tables sub-folder which contains CDS objects stored in .hdbcds files. These CDS objects are used to implement the Physical Data Model in SAP Web IDE. This is discussed in more detail later.

Like the RAW folder, the BID folder contains an etl sub-folder and a tables sub-folder. Additionally, here we use a sequences sub-folder. Further details about the purpose and functionality of sequences is discussed in Unit 2.

The VAL folder contains a views sub-folder, which stores the calculation views that are necessary to realize the Virtual Analytical Layer.

The DM folder contains a views sub-folder, which stores the calculation views that are necessary to realize the Data Mart.

As mentioned above, when introducing the RAW folder, the Physical Data Model can be implemented in SAP HANA Web IDE using the SAP HANA Core Data Service (CDS) - a data definition language developed by SAP. Using CDS the PDM is defined and stored in CDS objects. These CDS objects can be created by exporting the PDM diagram that was designed in the SAP HANA EAD. Every table involved in the PDM is stored in an individual CDS object, together with the references to other tables. The exported CDS objects can be implemented into SAP HANA Web IDE using a HDB module.

To create and edit CDS objects, SAP HANA 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.

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 run-time objects using the Database Explorer in SAP HANA Web IDE. Here you can see all the database artifacts that run inside your personal development sandbox, which is defined by the XSA Space you are working in. The whole process of deploying objects is discussed in more detail in the exercises.