2.4.1.

2 Creating a local repository

Use the repository database that you created earlier in your RDBMS to create a local repository.

1. Select Start Programs SAP Data Services 4.2 Data Services Repository Manager.

The SAP Data Services Repository Manager opens.

2. Choose Local from the Repository type dropdown list.
3. Select the name of the RDBMS that you used to create the repository database from the Database type
dropdown list.

The remaining connection options are based on the database type you choose.
4. Enter the connection information for the RDBMS repository database that you created.

Use the information in the worksheet that you completed in Creating repository, source, and target
databases on an existing RDBMS [page 20].
5. Type repo for both User and Password.
6. Click Create.

The repository database that you created earlier is now your local repository.

Next, define a Job Server and associate the repository with it.

Task overview: Tasks required to prepare for the tutorial [page 18]

Previous task: Creating repository, source, and target databases on an existing RDBMS [page 20]

Next task: Defining a job server and associating your repository [page 21]

2.4.1.3 Defining a job server and associating your

repository

Use the Data Services Server Manager to configure a new job server and associate the job server with the local
repository.

1. Select Start Programs SAP Data Services 4.2 Data Services Server Manager .

The SAP Data Services Server Manager opens.

2. In the Job Server tab click Configuration Editor.

The Job Server Configuration Editor opens.

3. Click Add.

The Job Server Properties opens.

4. Enter a unique name in Job Server name.
5. Enter a port number in Job Server port.

Enter a port number that is not used by another process on the computer. If you are unsure of which port
number to use, increment the default port number.

Tutorial
Introduction to the tutorial PUBLIC 21
6. Click Add in the Associated Repositories group on the left.

The options under Repository Information at right become active.

7. Select the RDBMS database type that you used for the repository from the Database type dropdown list.

The remaining connection options that appear are applicable to the database type you choose.
8. Enter the remaining connection information based on the information that you noted in the worksheet in
Creating repository, source, and target databases on an existing RDBMS [page 20].

 Example

Type repo for the User name and Password.

9. Select the Default repository checkbox.

Only select Default repository for the local repository. There can be only one default repository. If you are
following these steps to set up a different repository other than the local repository, do not select the
Default repository option.
10. Click Apply to save your entries.

You should see <database_server>_repo_repo in the Associated Repositories group at left.

11. Click OK to close the Job Server Properties.
12. Click OK to close the Job Server Configuration Editor.
13. Click Close and Restart in the Server Manager.
14. Click OK to confirm that you want to restart the Data Services service.

Task overview: Tasks required to prepare for the tutorial [page 18]

Previous task: Creating a local repository [page 21]

Next task: Configuring the local repository in the CMC [page 22]

2.4.1.4 Configuring the local repository in the CMC

To continue preparing the SAP Data Services local repository, you enter connection information in the Central
Management Console (CMC)

Before you can grant repository access to your user, configure the repository in the Central Management
Console (CMC).

1. Log in to the Central Management Console using your tutorial user name and password, tutorial_user
and tutorial_pass.
2. Click Data Services from the Organize list at left.
The Data Services management view opens.

3. Select Manage Configure Repository from the menu at the top.

The Add Data Services Repository view opens.
4. Enter a name in Repository Name.
For example, enter Tutorial Repository.

Tutorial
22 PUBLIC Introduction to the tutorial
 5. Enter the connection information for the database you created for the local repository.
6. Click Test Connection.
A dialog appears indicating whether or not the connection to the repository database was successful. Click
OK. If the connection failed, verify your database connection information and re test the connection.
7. Click Save.
The Add Data Services Repository view closes.
8. In the Data Services view, click the Repositories folder node at left.
The existing configured repositories appear. Verify that the new repository is included in the list.
9. Click Log Off to exit the Central Management Console.

Task overview: Tasks required to prepare for the tutorial [page 18]

Previous task: Defining a job server and associating your repository [page 21]

Next task: Running the provided SQL scripts [page 23]

2.4.1.5 Running the provided SQL scripts

Run the tutorial SQL scripts to create the sample source and target tables.

Data Services installation includes a batch file (CreateTables_<databasetype>.bat) for several of the
supported database types. The batch files run SQL scripts that create and populate tables on your source
database and create the target schema on the target database. If you used the suggested file names, user
names, and passwords for the “ods” and “target” databases, you only add the connection name to the
appropriate areas in the script.

1. Locate the CreateTables batch file for your specific RDBMS in the Data Services installation directory.
The default location is <LINK_DIR>\Tutorial Files\Scripts.
2. Right-click and select Edit.

 Tip

Use a copy of the original script file. Rename the original script file indicating that it is the original.

3. If you are not using the suggested user name and password, ods/ods and target/target, update the
script file with the user name and password that you used for the database name and target name.

The Oracle batch file is CreateTables_ORA.bat. It contains the following commands:

sqlplus ods/ods@<connection> @ODS_ORA.sql > CreateTables_ORA.out

sqlplus target/target@<connection> @Target_ORA.sql >> CreateTables_ORA.out

The Microsoft SQL Server batch is CreateTables_MSSQL2005.bat. It contains the following commands:

isql /e /n /U ods /S <servername> /d ods /P ods /i ODS_MSSQL.sql /o

Tutorial_MSSQL.out
isql /e /n /U target /S <servername> /d target /P target /i
Target_MSSQL.sql /o Target_MSSQL.out

Tutorial
Introduction to the tutorial PUBLIC 23
  Note

For Microsoft SQL Server 2008, use the CreateTables_MSSQL2005.bat file.

The output files provide logs that contain success or error notifications that you can examine.
4. Save and close the .bat file.
5. Double-click the batch file name to run the SQL scripts.
6. Use the applicable RDBMS query tool to check your source ODS database.
The following tables should exist on your source database after you run the script. These tables should
include a few rows of sample data.

Descriptive name Table name in database

Customer ods_customer

Material ods_material

Sales Order Header ods_salesorder

Sales Order Line Item ods_salesitem

Sales Delivery ods_delivery

Employee ods_employee

Region ods_region

7. Use an RDBMS query tool to check your target data warehouse.

The following tables should exist on your target database after you run the script.

Descriptive name Table name in database

Sales Org Dimension salesorg_dim

Customer Dimension cust_dim

Material Dimension mtrl_dim

Time Dimension time_dim

Employee Dimension employee_dim

Sales Fact sales_fact

Recovery Status status_table

CDC Status CDC_time

Task overview: Tasks required to prepare for the tutorial [page 18]

Previous task: Configuring the local repository in the CMC [page 22]

Tutorial
24 PUBLIC Introduction to the tutorial
2.4.2 Tutorial structure

We use a simplified data model for the exercises in this tutorial to introduce you to SAP Data Services features.

The tutorial data model is a sales data warehouse with a star schema that contains one fact table and some
dimension tables.

In the tutorial, you perform tasks on the sales data warehouse. We divided the tasks in to the following
segments:

Tutorial segments

Segment Lessons

Populate the Sales Organization Dimension from a flat file Introduces basic data flows, query transforms, and source
and target tables. The exercise populates the Sales Organi­
zation Dimension table from flat-file data.

Populate the Time Dimension table using a transform Introduces Data Services functions. This exercise creates a
data flow for populating the Time Dimension table.

Populate the Customer Dimension from a relational table Introduces data extraction from relational tables. This exer­
cise defines a job that populates the Customer Dimension.

Populate the Material Dimension from an XML File Introduces data extraction from nested sources. This exer­
cise defines a job that populates the Material Dimension.

Populate the Sales Fact table from multiple relational tables Continues data extraction from relational tables and introdu­
ces joins and the lookup function. The exercise populates
the Sales Fact table.

Complete each segment before going on to the next segment. Each segment creates the jobs and objects that
you need in the next segment. And we reinforce each skill in subsequent segments. As you progress, we
eliminate detailed steps for some of the basic skills that we introduced earlier.

Tutorial
Introduction to the tutorial PUBLIC 25
Parent topic: Preparation for this tutorial [page 18]

Related Information

Tasks required to prepare for the tutorial [page 18]

Exiting the tutorial [page 26]
Resuming the tutorial [page 26]
Product overview [page 10]

2.4.3 Exiting the tutorial

You can exit the tutorial at any point in this tutorial.

To exit the tutorial, follow these steps:

1. From the Project menu, click Exit.

If you haven't saved your changes, the software prompts you to save your work before you exit.
2. Click Yes to save your work.

After the software saves your work, it exits Data Services.

Task overview: Preparation for this tutorial [page 18]

Related Information

Tasks required to prepare for the tutorial [page 18]

Tutorial structure [page 25]
Resuming the tutorial [page 26]

2.4.4 Resuming the tutorial

If you exited the tutorial, you can resume the tutorial at any point.

1. Log in to the Designer and select the repository in which you saved your work.
The Designer window opens.
2. From the Project menu, click Open.
3. Click the name of the tutorial project you want to work with, then click Open.

The Designer window opens with the project and the objects within it displayed in the project area.

Tutorial
26 PUBLIC Introduction to the tutorial
Task overview: Preparation for this tutorial [page 18]

Related Information

Tasks required to prepare for the tutorial [page 18]

Tutorial structure [page 25]
Exiting the tutorial [page 26]

Tutorial
Introduction to the tutorial PUBLIC 27
3 Source and target metadata

The software uses metadata for the source and target objects to connect to the data location and to access the
data.

Source and target metadata is especially important when you access data that is in a separate environment
than your Data Services environment.

For the tutorial, you set up logical connections between Data Services, a flat-file source, and a target data
warehouse.

Logging in to the Designer [page 28]

You need to log into the Designer to perform the exercises in the tutorial. After you log in to the
Designer a few times, you won't need to refer to these steps, and you will remember your log in
credentials.

About datastores [page 29]

A datastore is an object that provides a logical channel or connection to a specified database. Use a
datastore to connect to your source and target database and to import metadata and data to the
repository.

Importing metadata [page 32]

Import metadata for individual tables using a datastore object.

Defining a file format [page 34]

File formats are a set of properties that describe the structure of a flat file.

Summary and what to do next [page 36]

After you complete the tasks in the Source and target metadata section, make sure that you select to
save the project. The information that you created in this section is saved to the local repository and is
available the next time you log in to Data Services.

3.1 Logging in to the Designer

You need to log into the Designer to perform the exercises in the tutorial. After you log in to the Designer a few
times, you won't need to refer to these steps, and you will remember your log in credentials.

Obtain the required repository user credentials from your administrator. Before you begin, review the options in
step 2 to ensure you have the correct information.

1. Open SAP Data Services Designer.

The SAP Data Services Repository Login opens.

2. Enter the repository user credentials as described in the following table.

Tutorial
28 PUBLIC Source and target metadata
 Option Description

System-host[:port] The name of the Central Management Server (CMS) system. You may also
need to specify the port when applicable.

User name The name that your administrator used to define you as a user in the Central
Management Console (CMC).

Password The password that your administrator used to define you as a user in the CMC.

Authentication The authentication type used by the CMS.

3. Click Log On.

The software displays a list of existing local repositories.

4. Select the applicable repository and click OK.
A prompt appears asking for the repository password.
5. Enter the repository password and click OK.

Data Services Designer opens. See an example of the Designer interface in The Designer user interface
[page 12].

Next you learn about how to use a datastore to define the connections to the source and target databases.

Task overview: Source and target metadata [page 28]

Related Information

About datastores [page 29]

Importing metadata [page 32]
Defining a file format [page 34]
Summary and what to do next [page 36]
About datastores [page 29]

3.2 About datastores

A datastore is an object that provides a logical channel or connection to a specified database. Use a datastore
to connect to your source and target database and to import metadata and data to the repository.

After you have configured the datastore, use it in a dataflow as a source or target.

● As a source, the software accesses the data through the connection information in the datastore.
● As a target, the software outputs processed data into the target database using the datastore connection
information.

Data Services provides several database types for which you can create database datastores.

Tutorial
Source and target metadata PUBLIC 29
In addition to other elements such as functions and connection information, the metadata in a datastore
consists of the following table elements:

● Table name
● Column names
● Column data types
● Primary key columns
● Table attributes

After you have created and configured a datastore, it appears in the local object library in Designer based on
how it is defined:

● If the connection is for a table, it appears in the Datastores tab.

● If the connection is for a flat file, it appears in the Formats tab.

 Note

Even though you designate datastores as a source or target in dataflows, datastores only function as
connections. You define the actual source and target objects when you define data flows later in the
tutorial.

Defining a datastore for the source (ODS) database [page 30]

Create a database datastore to use as a connection to the ODS database that you created when you set
up for the tutorial.

Defining a datastore for the target database [page 32]

Create a database datastore to use as a connection to the database that you named “target” when you
set up for the tutorial.

Parent topic: Source and target metadata [page 28]

Related Information

Logging in to the Designer [page 28]

Importing metadata [page 32]
Defining a file format [page 34]
Summary and what to do next [page 36]

3.2.1 Defining a datastore for the source (ODS) database

Create a database datastore to use as a connection to the ODS database that you created when you set up for
the tutorial.

Use the information from the worksheet that you completed in Creating repository, source, and target
databases on an existing RDBMS [page 20] for completing the options in the datastore that you create in the
following steps.

Tutorial
30 PUBLIC Source and target metadata
1. Open the Datastores tab in the Local Object Library in Designer and right-click in the blank area.
2. Select New from the popup menu.

The Create New Datastore window opens.

3. Type ODS_DS for Datastore name.
4. Select Database from the Datastore type dropdown list.
5. Select the applicable database from the Database type dropdown list.
Choose the database type that you used to create the source database. For example, if you created the
ODS database file using Microsoft SQL Server, select Microsoft SQL Server from the list.

The remaining options change based on the database type you choose.
6. Complete the remaining options that appear after you choose the database type.

The options in the following table are options that are present for most database types. Find your database
in the heading and go down the column to see the options related to that database. If the table doesn't
have your database type, see the Reference Guide for a list of options for each supported database type.

Oracle DB2 MS SQL Server Sybase ASE

Database Subtype Options:

○ Azure PaaS
○ Azure VM
○ On Premise

Database version Database Version Database Version Database version

CDC Options: CDC Options: CDC Options: CDC Options:

○ No CDC ○ No CDC ○ No CDC ○ No CDC
○ Native CDC ○ Replication Server CDC ○ Native CDC ○ Replication Server CDC
○ Replication Server CDC ○ Replication Server CDC

Use TNS name Use data source name Database Subtype Database Version
(DSN)

Hostname Database server name Database Version Database server name

SID or Service Name Database name Database server name Database Name

Port Port Database Name User Name

User Name User Name User name Password

Password Password Password Enable Automatic Data

Transfer

Enable Automatic Data

Transfer

7. Click OK.
Data Services saves a datastore for your source in the repository.

Related Information

Importing metadata [page 32]

Tutorial
Source and target metadata PUBLIC 31
Object naming conventions [page 17]

3.2.2 Defining a datastore for the target database

Create a database datastore to use as a connection to the database that you named “target” when you set up
for the tutorial.

Define a datastore for the “target” database using the same procedure as for the source (ODS) database.
Name the datastore Target_DS.

Related Information

Defining a datastore for the source (ODS) database [page 30]

3.3 Importing metadata

Import metadata for individual tables using a datastore object.

After you create a database datastore for the database, you can import table information from the database.

You can import metadata in the following ways:

● By browsing
● By file name
● By searching

All of these methods are explained in the Reference Guide.

For the tutorial, we take you through the steps to browse for metadata.

1. Importing metadata for ODS source tables [page 33]

Access the ODS datastore external metadata in Designer to import all of the table metadata.
2. Importing metadata for target tables [page 33]
Access the Target datastore external metadata in Designer to import all of the table metadata.

Parent topic: Source and target metadata [page 28]

Related Information

Logging in to the Designer [page 28]

About datastores [page 29]

Tutorial
32 PUBLIC Source and target metadata
Defining a file format [page 34]
Summary and what to do next [page 36]

3.3.1 Importing metadata for ODS source tables

Access the ODS datastore external metadata in Designer to import all of the table metadata.

1. In the Datastores tab, right-click the ODS_DS datastore and click Open.

The names of all the tables in the database defined by the datastore named ODS_DS display in the
workspace. Notice that the External Metadata option at the top of the workspace is automatically selected.
2. Optional. Resize the Metadata column by double-clicking with the resize cursor on the column separator.
3. Select all of the tables to highlight them:
○ ods.ods_customer
○ ods.ods_delivery
○ ods.ods_employee
○ ods.ods_material
○ ods.ods_region
○ ods.ods_salesitem
○ ods.ods_salesorder

Right-click and select Import.

Data Services imports the metadata for each table into the local repository.

 Note

For Microsoft SQL Server databases, the owner prefix might be “dbo” instead of “ods”. For example,
dbo.ods_customer instead of ods.ods_customer.

4. In the Object Library Datastores tab, expand the Tables node under ODS_DS to verify that the tables have
been imported into the repository.

Task overview: Importing metadata [page 32]

Next task: Importing metadata for target tables [page 33]

3.3.2 Importing metadata for target tables

Access the Target datastore external metadata in Designer to import all of the table metadata.

1. In the Datastores tab, right-click the Target_DS datastore and click Open.

The names of all the tables in the database defined by the datastore named Target_DS display in a window
in the workspace. Notice that the External Metadata option at the top of the workspace is automatically
selected.

Tutorial
Source and target metadata PUBLIC 33
2. Optional. Resize the Metadata column by double-clicking with the resize cursor on the column separator.
3. Select all of the tables to highlight them:
○ target.CDC_time
○ target.cust_dim
○ target.employee_dim
○ target.mtrl_dim
○ target.sales_fact
○ target.salesorg_dim
○ target.status_table
○ target.time_dim

Right-click and select Import.

Data Services imports the metadata for each table into the local repository.

 Note

For Microsoft SQL Server databases, the owner prefix might be “dbo” instead of “target”. For example,
dbo.cust_dim instead of target.cust_dim.

4. In the Object Library Datastores tab, expand the Tables node under Target_DS to verify the tables have
been imported into the repository.

Task overview: Importing metadata [page 32]

Previous task: Importing metadata for ODS source tables [page 33]

3.4 Defining a file format

File formats are a set of properties that describe the structure of a flat file.

Use the Data Services file format editor to create a flat file format for sales_org.txt.

1. Open the Formats tab In the Local Object Library and right-click in a blank area in the tab.

2. Select New File Format .

The File Format Editor opens.
3. In the General group at left, make the following settings:
○ Type: Delimited
○ Name: Format_SalesOrg
4. In the Data File(s) group, set the Location option to Local.
5. For the File name(s) option, click the file icon to browse for and select %LINK_DIR%\Tutorial Files
\sales_org.txt. Click Open.
6. Select Yes for the prompt asking to overwrite the current schema.
The file format editor displays sample data from the sales_org.txt file in the data pane in the lower
right.

Tutorial
34 PUBLIC Source and target metadata
7. In the Default Format group select ddmmyyyy from the Date parameter. If ddmmyyyy is not in the
dropdown list, type it in the Default Format space.

The date format matches the date data under the Field3 column in the data pane in the lower right.
8. In the Input/Output group, set Skip row header to Yes.
9. Select Yes for the prompt asking to overwrite the current schema.

The software replaces the original column headers, Fieldx, in the data pane with contents from the 2nd row.
The software also changes the values in the Field Name column in the upper right to the column names.
10. In the schema attributes pane in the upper right, click the cell under the Data Type column in the DateOpen
row. Select date from the dropdown list to change the data type.
The following screen capture shows the completed File Format Editor.

11. Click Save & Close.

Task overview: Source and target metadata [page 28]

Tutorial
Source and target metadata PUBLIC 35
Related Information

Logging in to the Designer [page 28]

About datastores [page 29]
Importing metadata [page 32]
Summary and what to do next [page 36]

3.5 Summary and what to do next

After you complete the tasks in the Source and target metadata section, make sure that you select to save the
project. The information that you created in this section is saved to the local repository and is available the next
time you log in to Data Services.

Also, before you exit Designer, close any open workspace tabs by clicking the X icon in the upper right of each
workspace.

What you have learned in the Source and target metadata section:

● How to define a datastore from Data Services to your target data warehouse
● How to import metadata from target tables into the local repository
● How to define a flat file format and a connection to flat-file source data

What is next: In the next section you populate the Sales Org.Dimension table with data from the
sales_org.txt flat file.

Parent topic: Source and target metadata [page 28]

Related Information

Logging in to the Designer [page 28]

About datastores [page 29]
Importing metadata [page 32]
Defining a file format [page 34]
Populate the Sales Organization dimension from a flat file [page 37]

Tutorial
36 PUBLIC Source and target metadata
4 Populate the Sales Organization
dimension from a flat file

Populate the Sales Org. dimension table with data from a source flat file named Format_SalesOrg.

The following diagram shows the Star Schema with the Dimension file circled.

Each task in this segment builds a Data Services project. Each project contains objects in a specific hierarchical
order.

At the end of each task, save your work. You can either proceed to the next task or exit Data Services. If you exit
Data Services before you save your work, the software asks that you save your work before you exit.

1. Creating a new project [page 38]

Begin the tutorial by creating a new project and opening it in the Project Area of the SAP Data Services
Designer.
2. Adding a new job [page 39]
Create a job for the Class_Exercises project.
3. Adding a workflow [page 39]
Workflows contain the order of steps in which the software executes a job.
4. Adding a data flow [page 40]
Create a data flow named DF_SalesOrg inside the workflow WF_SalesOrg.
5. Define the data flow [page 41]
To define the instructions for building the sales organization dimension table, add objects to
DF_SalesOrg in the workspace area.
6. Validating the DF_SalesOrg data flow [page 44]
Perform a design-time validation, which checks for construction errors such as syntax errors.
7. Addressing warnings and errors [page 45]

Tutorial
Populate the Sales Organization dimension from a flat file PUBLIC 37
 If there are warnings and errors after you validate your job, fix the errors. The job does not execute with
existing errors. You do not need to fix the cause for the warnings because warnings do not prohibit the
job from running.
8. Saving the project [page 46]
You can save the steps you have completed and close Data Services at any time.
9. Ensuring that the Job Server is running [page 46]
Before you execute a job (either as an immediate or scheduled task), ensure that the Job Server is
associated with the repository where the client is running.
10. Executing the job [page 47]
Execute the job to move data from your source to your target.
11. Summary and what to do next [page 49]
In the exercises to populate the Sales Organization dimension table, you learned new skills that you will
use for just about any data flow, and you learned about using functions in an output schema and much
more.

Related Information

Object hierarchy [page 14]

4.1 Creating a new project

Begin the tutorial by creating a new project and opening it in the Project Area of the SAP Data Services
Designer.

Log in to the Designer and follow these steps to create a new project:

1. Select Project New Project .

A list of your existing projects appears. If you do not have any projects created, the list is empty.
2. Enter the following name in Project name: Class_Exercises.
3. Click Create.

The project Class_Exercises appears in the Project Area of the Designer, and in the Project tab of the Local
Object Library.

Next, create a job for the new project. If you plan to exit Data Services, save the project.

Task overview: Populate the Sales Organization dimension from a flat file [page 37]

Next task: Adding a new job [page 39]

Tutorial
38 PUBLIC Populate the Sales Organization dimension from a flat file
Related Information

Projects and subordinate objects [page 15]

Object naming conventions [page 17]

4.2 Adding a new job

Create a job for the Class_Exercises project.

If you are logged out of SAP Data Services Designer, log in and open the project named Class_Exercises.

Follow these steps to create a new job for the Class_Exercises project:

1. Right-click in the Project Area and select New Batch Job.

2. Rename the new job JOB_SalesOrg.

The job appears in the Project Area under Class_Exercises, and in the Jobs tab under the Batch Jobs node
in the Local Object Library.

Save the new job and proceed to the next exercise. Next you add a workflow to the job JOB_SalesOrg.

Task overview: Populate the Sales Organization dimension from a flat file [page 37]

Previous task: Creating a new project [page 38]

Next task: Adding a workflow [page 39]

Related Information

Projects and subordinate objects [page 15]

Object naming conventions [page 17]

4.3 Adding a workflow

Workflows contain the order of steps in which the software executes a job.

In Designer, open the Class_Exercises project and expand it to view the JOB_SalesOrg job.

Follow these steps to add a workflow to the JOB_SalesOrg job:

1. Select JOB_SalesOrg in the Project Area.

The job opens in the workspace and the tool palette appears to the right of the workspace.

Tutorial
Populate the Sales Organization dimension from a flat file PUBLIC 39
2. Select the workflow button from the tool palette and click the blank workspace area.

A workflow icon appears in the workspace. The workflow also appears in the Project Area hierarchy under
the job JOB_SalesOrg.

 Note

Workflows are easiest to read in the workspace from left to right and from top to bottom. Keep this
arrangement in mind as you add objects to the workflow workspace.

3. Rename the workflow WF_SalesOrg.

4. Expand the JOB_SalesOrg job in the Project Area and click WF_SalesOrg to open it in the workspace.

An empty view of the workflow appears in a new workspace tab. Use this area to define the elements of the
workflow.

Next you create a data flow to add to WF_SalesOrg.

Task overview: Populate the Sales Organization dimension from a flat file [page 37]

Previous task: Adding a new job [page 39]

Next task: Adding a data flow [page 40]

Related Information

Work flows [page 15]

4.4 Adding a data flow

Create a data flow named DF_SalesOrg inside the workflow WF_SalesOrg.

Make sure the workflow is open in the workspace. If it is not open, click the WF_SalesOrg workflow in the
Project Area.

1. Click the data flow button on the tool palette to the right of the workspace.
2. Click the workspace.

The data flow icon appears in the workspace and the data flow icon also appears in the Project Area.
3. Enter DF_SalesOrg as the name for the dataflow in the text box above the icon in the workspace.

The project, job, workflow, and data flow objects display in hierarchical form in the Project Area. To navigate
to these levels, expand each node in the project area.
4. Click DF_SalesOrg in the Project Area to open a blank definition area in the workspace.

Next, define the data flow DF_SalesOrg in the definition area that appears in the workspace.

Tutorial
40 PUBLIC Populate the Sales Organization dimension from a flat file
Task overview: Populate the Sales Organization dimension from a flat file [page 37]

Previous task: Adding a workflow [page 39]

Next task: Define the data flow [page 41]

Related Information

Data flows [page 16]

4.5 Define the data flow

To define the instructions for building the sales organization dimension table, add objects to DF_SalesOrg in
the workspace area.

Build the sales organization dimension table by adding a source file, query object, and a target table to the DF
SalesOrg data flow in the workspace:

The next three tasks guide you through the steps necessary to define the content of a data flow:

1. Add objects to the data flow.

2. Connect them in the order that data flows through them.
3. Define the query that maps the source columns to the target columns.

Adding objects to the DF_SalesOrg data flow [page 42]

Add objects to the DF_SalesOrg data flow workspace to start building the data flow.

Defining the order of steps in a data flow [page 42]

To define the sequence for the dataflow DF_SalesOrg, connect the objects in a specific order.

Configuring the query transform [page 43]

Configure the query transform by mapping columns from the source to the target object.

Task overview: Populate the Sales Organization dimension from a flat file [page 37]

Previous task: Adding a data flow [page 40]

Next task: Validating the DF_SalesOrg data flow [page 44]

Tutorial
Populate the Sales Organization dimension from a flat file PUBLIC 41
4.5.1 Adding objects to the DF_SalesOrg data flow

Add objects to the DF_SalesOrg data flow workspace to start building the data flow.

Make sure the workspace for DF_SalesOrg is open.

1. Open the Formats tab In the Local Object Library and expand the Flat Files node.
2. Click and drag Format_SalesOrg to the workspace and release it.

Position the object to the left of the workspace area to make room for other objects.

A notification appears for you to indicate whether to make the object a source or target.
3. Click Make Source.

4. Click the Query icon on the tool palette. and click in the workspace.

The Query icon appears in the workspace. Drag it to the right of the Format_SalesOrg source object in
the workspace.
5. Open the Datastores tab in the Local Object Library and expand the Target_DS node.
6. Click and drag SALESORG_DIM to the workspace and drop it to the right of the Query icon.

A notification appears for you to indicate whether to make the object a source or target.
7. Click Make Target.

All the objects necessary to create the sales organization dimension table are now in the workspace. In the next
section, you connect the objects in the order in which you want the data to flow.

4.5.2 Defining the order of steps in a data flow

To define the sequence for the dataflow DF_SalesOrg, connect the objects in a specific order.

1. Click the square on the right edge of the Format_SalesOrg source file and drag your pointer to the
triangle on the left edge of the query transform.

When you drag from the square to the triangle, the software connects the two objects with a line. If you
start with the triangle and go to the square, the software won't connect the two objects.
2. Use the same drag technique to connect the square on the right edge of the query transform to the triangle
on the left edge of the SALESORG_DIM target table.

Tutorial
42 PUBLIC Populate the Sales Organization dimension from a flat file
The order of operation is established after you connect all of the objects. Next you configure the query
transform.

4.5.3 Configuring the query transform

Configure the query transform by mapping columns from the source to the target object.

Before you can configure the query transform, you connect the source to the query, and the query to the target
in the workspace. Learn more about creating a data flow and configuring the query transform in the Reference
guide.

When you connect the objects in the data flow, the column information from the source and target files appears
in the Query transform to help you set up the query.

1. Double-click the query in the Project Area.

The query editor opens. The query editor contains the following areas:
○ Schema In pane: Lists the columns in the source file
○ Schema Out pane: Lists the columns in the target file
○ Options pane: Contains tabs for defining the query
Because the query is connected to the target in the data flow, the software automatically copies the target
schema to the Schema Out pane.
2. Select the column icon of the specified input column in the Schema In pane and drag it to the
corresponding column in the Schema Out pane. Map the columns as listed in the following table.

Input column Output column

SalesOffice SALESOFFICE

DateOpen DATEOPEN

Region REGION

 Note

We do not use the Country column for this data flow.

Tutorial
Populate the Sales Organization dimension from a flat file PUBLIC 43
 After you drag the input column to the output column, an arrow icon appears next to the source column to
indicate that the column has been mapped.

The following list names the areas of the query editor marked with red letters in the image.
○ A. Target schema
○ B. Source schema
○ C. Query option tabs
○ D. Column mapping definition

3. Select a field in the Schema Out area and view the column mapping definition in the Mapping tab of the
options pane. For example, in the image above the mapping for the SalesOffice input column to the
SALESOFFICE output column is: Format_SalesOrg.SalesOffice.
4. Click the Type column cell for the SALESOFFICE column in the Schema Out pane and select Decimal.
5. Set Precision to 10 and Scale to 2 in the Type:Decimal popup. Click OK.

6. Click the Back arrow icon from the toolbar at the top of the page to close the query editor and return to
the data flow worksheet.
7. Save your work.

4.6 Validating the DF_SalesOrg data flow

Perform a design-time validation, which checks for construction errors such as syntax errors.

The Validation menu provides design-time validation options. You can check for runtime errors later in the
process.

1. Click DF_SalesOrg in the Project Area.

2. Select Validation Validate Current View .

Tutorial
44 PUBLIC Populate the Sales Organization dimension from a flat file
  Note

There are two validation options to choose:

○ Current View validates the object definition open in the workspace.
○ All Objects in View validates the object definition open in the workspace and all of the objects that it
calls.

You can alternatively use the icon bar and click Validate Current and Validate All to perform the same
validations.

After the validation completes, Data Services displays the Output dialog with the Warning tab indicating any
warnings.

 Note

Two warning messages appear indicating the Data Services will convert the data type for the SALESOFFICE
column.

An Error tab contains any validation errors. You must fix the errors before you can proceed.

Task overview: Populate the Sales Organization dimension from a flat file [page 37]

Previous task: Define the data flow [page 41]

Next task: Addressing warnings and errors [page 45]

Related Information

Executing the job [page 47]

4.7 Addressing warnings and errors

If there are warnings and errors after you validate your job, fix the errors. The job does not execute with existing
errors. You do not need to fix the cause for the warnings because warnings do not prohibit the job from running.

After you validate a job, an output window appears listing warnings and errors if applicable.

1. Right-click an error or warning notification and click View.

Data Services displays the Message window in which you can read the expanded notification text.
2. For errors, double-click the error notification to open the editor of the object containing the error.

After you validate the job with no errors, you have completed the description of the data movement for the
sales organization dimension table.

Tutorial
Populate the Sales Organization dimension from a flat file PUBLIC 45