Loading Data into the Data Warehouse Layer

Objectives

After completing this lesson, you will be able to:

  • Describe how to build a Data Warehouse layer using a Standard DataStore Object

InfoObject-based Standard DataStore Object

Scenario Overview

In the next step of the data flow we define another DataStore Object (advanced).

But unlike the previous object, which was defined as a Staging object type using fields, this one will be defined as a Standard object type and will use InfoObjects instead of fields.

InfoObject-based Standard DataStore Object: system settings

In our scenario, the sales transactional data is loaded from the Corporate Memory which uses a field-based Staging DataStore Object. The target will be the InfoObject-based Standard DataStore Object. The object has modeling type Standard DataStore Object with Write Change Log option selected. The setting Write Change Log is typically used for a DataStore Object (advanced) in the Data Warehouse layer. This is because a change log enables the management of delta records in downstream data flows.

With this type of DataStore Object (advanced), a data load has to be activated in order to apply the changes and enable the records to be available for reporting. For testing purposes activation of requests can be manually executed, but for production systems, activation of requests is part of an automated process chain which can be scheduled.

In the InfoObject-based Standard DataStore Object, four InfoObjects have been defined as part of the key. This key defines the uniqueness of records in the DataStore Object (advanced) and is used to handle delta records. As this DataStore Object (advanced) may also be filled with sales transactional data from other sources systems, the InfoObject 0LOGSYS (Source System) is also defined as part of the key and provides an indication of the origin of the data.

Watch the following demo to explore settings of the InfoObject-based Standard DataStore Object.

Explore a DataStore Object for Storing Data in the Data Warehouse Layer

Transformation

Scenario Overview

The next step in our data flow is to define a Transformation between the field-based Staging DataStore Object and the InfoObject-based Standard DataStore Object.

Transformation between the field-based Staging DataStore Object and the InfoObject-based Standard DataStore Object: system settings

In our scenario, the fields from the field-based Staging DataStore Object are mapped to the InfoObjects from the InfoObject-based Standard DataStore Object. Most source fields are mapped 1:1 to the target InfoObjects because for many source fields there is one target InfoObject and the value should not be changed during loading. However, four of the InfoObjects use a different transformation rule. We need these four rules because either the source does not provide the field, or the field is provided but the value needs adjusting.

Here are our rules:

  • 0CALYEAR: Transformation rule Formula (f) is used to derive the year from the source field DELIVERYDATETIME

  • 0LOGSYS: Transformation rule Constant (c) is used to fill it with value T41 because this is not provided by the source system.

  • 0CALDAY: Transformation rule Time is used to derive the date from the source field CREATIONDATETIME

  • 0D_NW_ROLE: Transformation rule Constant (c) is used to fill it with value 1 because this is not provided by the source system.

Watch the following demo to explore settings of our second Transformation:

Explore the Transformation to Data Warehouse Layer

Data Transfer Process

Scenario Overview

The next step of the data flow is to define a Data Transfer Process (DTP). This DTP will move data between the field-based Staging DataStore Object and the InfoObject-based Standard DataStore Object.

Data Transfer Process between between the field-based Staging DataStore Object and the InfoObject-based Standard DataStore Object: system settings

In our scenario, a Data Transfer Process of the type Full is defined. This type of DTP is not capable of loading delta records. Additionally, once again, there is no Filter defined in our DTP.

Watch the following demo to explore the settings of the DTP:

Explore the Data Transfer Process to Load the Data Warehouse Layer

Log in to track your progress & complete quizzes