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 transformcomponent 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 Infive pillars constituting the output flow and processing options.


    • Map the DI_GENERATED_DATE” column with the target column DATE NATIVEdragging 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 “MappingUse the table below to complete the mapping of the remaining columns

Column Mapping
DATE_ID Julian(DI_GENERATED_DATE)
YEARNUM to_char(DI_GENERATED_DATE,’yyyy’)
MONTHNUM Month(DI_GENERATED_DATE)
BUSQUARTER Quarter(DI_GENERATED_DATE)

    • 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.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply