How to generate Dimension Date using SAP Data Services?

In this tutorial , learn how to design a batch job type allows the automatic generation of a dimension Time or Date using the component ” Date_Generation ” and then store the information in a table.

    Prerequisites: Access to MS SQL type database SERVER 2012 or other,
    The component ” Date_Generation “
    The structure of the target table ” TIME_DIM “
    Scripting the target structure ” time_dim ” found in the appendix.
    Version: 4.2 SAP Data Services
    Application: Data Services Designer

Create project

• Select Project > New Project .
• Enter the name of your new project .

The name can contain alphanumeric characters and underscores ( _). It can not contain spaces.

• Click Create

Create a job in project Area

• In the project , select the project name .
• Click the right mouse button and select New Batch Job or Job in real time.
• Change the name .

The name can contain alphanumeric characters and underscores ( _). It can not contain spaces.
The software opens a new workspace where you define the job

Create a WorkFlow

• In the project area , click the created job.
• Select the ” Workflow ” icon in the tool palette .
• Place the ” workflow ” in the job workspace

Create a DataFlow “DF_DIM_DATE”

• In the project area , click the created workflow.
• Select the data flow icon in the tool palette.
• Set the data flow in the workflow workspace.

Start creating your data flow.
Double-click the icon of the data Flow.
You can now start placing objects in the data flow diagram. Objects can be source types, target and / or Transforms.

Define a data flow “DF DIM_TIME“

The data flow consists of the following components:

• A “Date_Generation transform” component defined as the source.
• A transformation “Query” to generate the data from the clonnne “DI_GENERATED_DATE”
• An MS SQL SERVER table that receives the generated information.

Specify the components of the flow “DF_DIM_TIME”

• Open the data flow entitled “DF_DIM_TIME“
• Select In the local object library, click the “Transforms”
• The object “Date_Generation” is in “Data Integrator”
• Drag and drop the icon “Date_Generation” in the workspace of “dataflow” tab.
• Click the “query” available in the palette and place the right of the object “Date_Generation” icon
• In the local library of objects in the datastore “MSSQL_INSTANCE1“, drag the table “TIME_DIM” and drop the subject to the right of transformation “Query”  like  the following configuration:

Configure the component “Data Generation”

Click the object “Date_Generation” in the workflow space to open the transformation editor

Enter the following information in the Date tab Generation

• Start date: 2002.01.01
• End Date: 20014.12.31
• Increment: Daily
  

Click the green arrow found in the toolbar to return to the workspace of “dataflow“.

Configuring the transformation “Query”

In the project area, click on the icon “Query”, you will see that we have a single column “DI_GENERATED_DATE” in “schema In” five pillars constituting the output flow and processing options.

• Map the “DI_GENERATED_DATE” column with the target column “DATE NATIVE” dragging the source object in the target object.
  For mapping you can proceed as follows:
• Select “NATIVEDATE” column from “Schema Out”
• Then drag the object “DI_GENERATED_DATE” from “pattern input” and place it in the tab “Mapping” Use the table below to complete the mapping of the remaining columns

• Click the green arrow to return to the workspace.
• Save and run the job
• Check the proper integration of data into the table “TIME_DIM” MSQL SERVER target.