PUBLIC

SAP Data Services

Document Version: 4.2 Support Package 12 (14.2.12.0) – 2019-04-26

Tutorial
© 2019 SAP SE or an SAP affiliate company. All rights reserved.

THE BEST RUN

Content

1 Documentation changes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

2 Introduction to the tutorial. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

2.1 Audience and assumptions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
2.2 Tutorial objectives. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
2.3 Product overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Product components. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
The Designer user interface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12
About objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
2.4 Preparation for this tutorial. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Tasks required to prepare for the tutorial. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Tutorial structure. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Exiting the tutorial. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Resuming the tutorial. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .26

3 Source and target metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28

3.1 Logging in to the Designer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
3.2 About datastores. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Defining a datastore for the source (ODS) database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Defining a datastore for the target database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
3.3 Importing metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Importing metadata for ODS source tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Importing metadata for target tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
3.4 Defining a file format. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .34
3.5 Summary and what to do next. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36

4 Populate the Sales Organization dimension from a flat file. . . . . . . . . . . . . . . . . . . . . . . . . . . . 37

4.1 Creating a new project. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
4.2 Adding a new job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
4.3 Adding a workflow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
4.4 Adding a data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
4.5 Define the data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .41
Adding objects to the DF_SalesOrg data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Defining the order of steps in a data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Configuring the query transform. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
4.6 Validating the DF_SalesOrg data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
4.7 Addressing warnings and errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
4.8 Saving the project. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .46

Tutorial
2 PUBLIC Content
4.9 Ensuring that the Job Server is running. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .46
4.10 Executing the job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
4.11 Summary and what to do next. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

5 Populate the Time dimension table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

5.1 Opening the Class_Exercises project. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .52
5.2 Adding a job and data flow to the project. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
5.3 Adding the components of the time data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
5.4 Defining the flow of data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
5.5 Defining the output of the Date_Generation transform. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
5.6 Defining the output of the query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
5.7 Saving and executing the job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
5.8 Summary and what to do next. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

6 Populate the Customer dimension table from a relational table. . . . . . . . . . . . . . . . . . . . . . . . 58

6.1 Adding the CustDim job and workflow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59
6.2 Adding the CustDim data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
6.3 Define the data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Adding objects to a data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Configuring the query transform. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
6.4 Validating the CustDim data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
6.5 Executing the CustDim job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63
6.6 The interactive debugger. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63
Setting a breakpoint in a data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Debugging Job_CustDim with interactive debugger. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
Setting a breakpoint condition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
6.7 Summary and what to do next. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67

7 Populate the Material Dimension from an XML File. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

7.1 Nested data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
7.2 Adding MtrlDim job, workflow, and data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
7.3 Importing a document type definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
7.4 Define the MtrlDim data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Adding objects to DF_MtrlDim . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Configuring the qryunnest query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
7.5 Validating that the MtrlDim data flow has been constructed properly. . . . . . . . . . . . . . . . . . . . . . . . 75
7.6 Executing the MtrlDim job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
7.7 Leveraging the XML_Pipeline . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .76
Setting up a job and data flow that uses the XML_Pipeline transform. . . . . . . . . . . . . . . . . . . . . 77
Configuring the XML_Pipeline and Query_Pipeline transforms. . . . . . . . . . . . . . . . . . . . . . . . . . 78
7.8 Summary and what to do next. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79

8 Populate the Sales Fact Table from Multiple Relational Tables. . . . . . . . . . . . . . . . . . . . . . . . . 81

Tutorial
Content PUBLIC 3
8.1 Adding the SalesFact job, work flow, and data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
8.2 Creating the SalesFact data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
8.3 Defining the details of the Query transform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .83
8.4 Using a lookup_ext function for order status. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
8.5 Validating the SalesFact data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
8.6 Executing the SalesFact job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
8.7 Viewing Impact and Lineage Analysis for the SALES_FACT target table. . . . . . . . . . . . . . . . . . . . . . .91
8.8 Summary and what to do next. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93

9 Changed data capture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94

9.1 Global variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
9.2 Adding the initial load job and defining global variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .95
Adding a workflow, scripts, and data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
9.3 Replicating the initial load data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
9.4 Building the delta load job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .101
Adding the job and defining the global variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Defining the scripts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
9.5 Execute the initial and delta load jobs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Executing the initial load job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Changing the source data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Executing the delta-load job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
9.6 Summary and what to do next. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105

10 Data Assessment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107

10.1 Default profile statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
10.2 Viewing profile statistics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
10.3 The Validation transform. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
Creating a validation job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .111
Adding a job and data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Configuring the Validation transform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
10.4 Audit objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Adding a fail target table to the data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Creating audit functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
10.5 Viewing audit details in Operational Dashboard reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
View audit results. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
10.6 Summary and what to do next. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119

11 Recovery Mechanisms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121

11.1 Recoverable job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .122
11.2 Creating local variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
11.3 Creating the script that determines the status. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
11.4 Conditionals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124

Tutorial
4 PUBLIC Content
 Adding the conditional. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Specifying the If-Then work flows. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
11.5 Creating the script that updates the status. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
11.6 Verify the job setup. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
11.7 Executing the job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .129
11.8 Data Services automated recovery properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
11.9 Summary and what to do next. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130

12 Multiuser Development. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132

12.1 Central Object Library. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Central Object Library layout. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
12.2 How multiuser development works. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
12.3 Preparation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .134
Configuring the central repository. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Configuring two local repositories. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Associating repositories to your job server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Defining connections to the central repository. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .137
12.4 Working in a multiuser environment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Activating a connection to the central repository. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Importing objects into your local repository. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Adding objects to the central repository. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Check out objects from the central repository. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Checking in objects to the central repository. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
Setting up the user2 environment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
Undo checkout. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .149
Comparing objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Check out object without replacement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152
Get objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Filter dependent objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Deleting objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
12.5 Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160

13 Extracting SAP application data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161

13.1 SAP applications. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
13.2 Defining an SAP application datastore. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
13.3 Importing metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
13.4 Repopulate the customer dimension table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Adding the SAP_CustDim job, work flow, and data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Adding ABAP data flow to Customer Dimension job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .166
Defining the DF_SAP_CustDim ABAP data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
Executing the JOB_SAP_CustDim job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
ABAP job execution errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172

Tutorial
Content PUBLIC 5
13.5 Repopulating the material dimension table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Adding the material dimension job, work flow, and data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Adding ABAP data flow to Material Dimension job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Defining the DF_SAP_MtrlDim ABAP data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Executing the JOB_SAP_MtrlDim job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
13.6 Repopulating the Sales Fact table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Adding the Sales Fact job, work flow, and data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
Adding ABAP data flow to Sales Fact job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .180
Defining the DF_ABAP_SalesFact ABAP data flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Executing the JOB_SAP_SalesFact job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
13.7 Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188

14 Real-time jobs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .190

14.1 Importing a real-time job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
14.2 Running a real time job in test mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191

Tutorial
6 PUBLIC Content
1 Documentation changes

Significant changes to the Tutorial since the last update.

The following table contains changes to the documentation, and the related SAP Data Services version in which
the changes were made. The list begins with the most recent changes.

Change Notes Version

Removed the following topics: Topic obsolete 4.2 SP 9 Patch 1

● Accessing documentation from the

Web
● documentation set for SAP Data
Services

Added the following topics: Standard topics in all Data Services 4.2 SP 9 Patch 1
documents.
● Welcome
● SAP information resources

Removed the following topics: Topics were under Product overview. 4.2 SP 8 Patch 2
Removed because the concepts were 4.2 SP 9
● System configurations
for advanced users.
● Windows and UNIX implementa­
tion

Removed: Blueprints Topic was under Product overview, 4.2 SP 8 Patch 2

About objects. Tutorial participants do 4.2 SP 9
not need to download blueprints.

Removed: Environment requirements Topic was under Preparation for this tu­ 4.2 SP 8 Patch 2
torial. Removed because the concept 4.2 SP 9
was for advanced users.

Removed the following topics: Topics were under Setting up for the tu­ 4.2 SP 8 Patch 2
torial. Removed because an administra­ 4.2 SP 9
● BI platform and the Central Man­
tor should perform the tasks.
agement Server (CMS)
● Opening the Central Management
Console
● Installing SAP Data Services
● Verifying the Windows service
● Creating a new Data Services user
account

Tutorial
Documentation changes PUBLIC 7
Change Notes Version

Renamed the topic Setting up for the Topic is located under Preparation for 4.2 SP 8 Patch 2
tutorial to Tasks required to prepare for this tutorial. 4.2 SP 9
the tutorial.

Added the following topics to the Tuto­ These topics make it easier for readers 4.2 SP 8 Patch 2
rial: to find additional documents that we 4.2 SP 9
reference in other topics.
● SAP Information resources
● Accessing documentation from the
Web
● Documentation set for SAP Data
Services

Removed all Terminology topics These topics were not consistent, and 4.2 SP8 Patch 2
many terms were used in more than
one section.

Tutorial
8 PUBLIC Documentation changes
2 Introduction to the tutorial

This tutorial introduces you to the basic use of SAP Data Services Designer by explaining key concepts and
providing a series of related exercises and sample data.

Data Services Designer is a graphical user interface (GUI) development environment in which you extract,
transform, and load batch data from flat-file and relational database sources for use in a data warehouse. You
can also use Designer for real-time data extraction and integration.

2.1 Audience and assumptions

The tutorial is for users experienced in many areas of database management, SQL, and Microsoft Windows.

The tutorial introduces core SAP Data Services Designer functionality. We wrote the tutorial assuming that you
have experience in some of the following areas:

● Database management or administration

● Data extraction, data warehousing, data integration, or data quality
● Source data systems
● Business intelligence

2.2 Tutorial objectives

After you complete this tutorial, you will be able to extract, transform, and load data from various source and
target types, and understand the concepts and features of SAP Data Services Designer.

You will know about the various Data Services objects such as datastores and transforms, and you will be able
to define a file format, import data, and analyze data results.

You will learn how to use Data Services Designer features and functions to do the following:

● Verify and improve your source data quality

● Capture changed data
● View and print metadata reports
● Examine data through a job using the debugger
● Recover from runtime errors
● Set up a multiuser development environment
● Set up and run real-time data processing

Tutorial
Introduction to the tutorial PUBLIC 9
2.3 Product overview

Data Services extracts, transforms, and loads (ETL) data from heterogeneous sources into a target database or
data warehouse. You specify data mappings and transformations by using Data Services Designer.

Data Services combines industry-leading data quality and integration into one platform. It transforms your
data in many ways. For example, it standardizes input data, adds additional address data, cleanses data, and
removes duplicate entries.

Data Services provides additional support for real time data movement and access. It performs predefined
operations in real time, as it receives information. The Data Services real time components also provide
services to Web applications and other client applications.

For a complete list of Data Services resources, see the Designer Guide.

Product components [page 10]

Descriptions of the components that are a part of SAP Data Services.

The Designer user interface [page 12]

Use the many tools in SAP Data Services Designer to create objects, projects, data flows, and
workflows to process data.

About objects [page 13]

SAP Data Services objects are entities that you create, add, define, modify, or work with in the software.

2.3.1 Product components

Descriptions of the components that are a part of SAP Data Services.

Data Services component descriptions

Component Description

Designer Data Services user interface that enables users to:

● Create, test, and execute jobs that populate a data warehouse

● Create objects and combine them by dragging their icons onto the workspace to create source-to-
target flow diagrams
● Configure objects by opening their editors from the data flow diagram
● Define data mappings, transformations, and control logic
● Create applications by combining objects in to workflows (job execution definitions) and data flows
(data transformation definitions)

Job Server Application that launches the Data Services processing engine and serves as an interface to the engine
and other components in the Data Services suite.

Engine Executes individual jobs that you define in the Designer to effectively accomplish the defined tasks.

Tutorial
10 PUBLIC Introduction to the tutorial
 Component Description

Repository Database that stores Designer predefined system objects and user-defined objects including source and
target metadata and transformation rules. Create a local repository and then a central repository to
share objects with other users and for version control.

Access Server Passes messages between Web applications and the Data Services Job Server and engines. Provides a
reliable and scalable interface for request-response processing.

Administrator Web administrator that provides the following browser-based administration of Data Services resources:

● Scheduling, monitoring, and executing batch jobs

● Configuring, starting, and stopping real-time services
● Configuring Job Server, Access Server, and repository usage
● Configuring and managing adapters
● Managing users
● Publishing batch jobs and real-time services via web services

The following diagram illustrates Data Services product components and relationships.

Parent topic: Product overview [page 10]

Tutorial
Introduction to the tutorial PUBLIC 11
Related Information

The Designer user interface [page 12]

About objects [page 13]

2.3.2 The Designer user interface

Use the many tools in SAP Data Services Designer to create objects, projects, data flows, and workflows to
process data.

The Designer interface contains key work areas that help you set up and run jobs. The following illustration
shows the key areas of the Designer user interface.

Parent topic: Product overview [page 10]

Tutorial
12 PUBLIC Introduction to the tutorial
Related Information

Product components [page 10]

About objects [page 13]

2.3.3 About objects

SAP Data Services objects are entities that you create, add, define, modify, or work with in the software.

Each Data Services object has similar characteristics for creating and configuring objects.

Characteristics of Data Services objects

Characteristic Description

Properties Text that describes the object. For example, the name, de­
scription, and creation date describes aspects of an object.

Attributes Properties that organize objects and make them easier for
you to find. For example, organize objects by attributes such
as object types.

Classes Determines whether an object can be used again in a differ-

ent job. Object classes are “reusable” and “single-use”.

The Designer contains a Local Object Library that is divided by tabs. Each tab is labeled with an object type.
Objects in a tab are listed in groups. For example, the Project tab groups projects by project name and further
by job names that exist in the project.

Local Object Library tabs:

● Projects
● Jobs
● Workflows
● Data flows
● Transforms
● Datastores
● Formats
● Functions

Parent topic: Product overview [page 10]

Related Information

Product components [page 10]

The Designer user interface [page 12]

Tutorial
Introduction to the tutorial PUBLIC 13
2.3.3.1 Object hierarchy
Object relationships are hierarchical.

The highest object in the hierarchy is the project. The subordinate objects appear as nodes under a project. You
add subordinate objects to the project in a specific order. For example, A project contains jobs, jobs contain
workflows, and workflows contain data flows.

The following diagram shows the hierarchical relationships for the key object types within Data Services.

Tutorial
14 PUBLIC Introduction to the tutorial
Related Information

About objects [page 13]

Projects and subordinate objects [page 15]
Data flows [page 16]

2.3.3.1.1 Projects and subordinate objects

Projects contain jobs, workflows, and data flows as subordinate objects.

A project is the highest-level object in Designer hierarchy. Projects provide a way to organize the subordinate
objects, which are jobs, workflows, and data flows.

A project is open when you can view it in the project area. If you open a different project from the Project tab in
the object library, the project area closes the current project and shows the project that you just opened.

Projects and subordinates

Object Subordinate objects Subordinate description

Project Job The smallest unit of work that you can

schedule independently for execution.
Jobs are made up of workflows and
data flows that direct the software in
the order and manner of processing.

Job Workflow Incorporates data flows into a coherent

flow of work for an entire job.

Data flow Process flow by which the software

transforms source data into target data.

2.3.3.1.2 Work flows

A work flow specifies the order in which SAP Data Services processes subordinate data flows.

Arrange the subordinate data flows under the work flow so that the output from one data flow is ready for input
to the intended data flow.

Tutorial
Introduction to the tutorial PUBLIC 15
A work flow is a reusable object. It executes only within a Job. Use work flows to:

● Call data flows

● Call another work flow
● Define the order of steps to be executed in your job
● Pass parameters to and from data flows
● Define conditions for executing sections of the project
● Specify how to handle errors that occur during execution

Work flows are optional.

The Data Services objects you can use to create work flows appear as icons on the tool palette to the right of
the workspace. If the object isn't applicable to what you have open in the workspace, the software disables the
icon. The following table contains the programming analogy of each object to describe the role the object plays
in the work flow.

Object Programming Analogy

Procedure
Workflow

Declarative SQL select statement

Data Flow

Subset of lines in a procedure

Script

If, then, else logic

Conditional

A sequence of steps that repeats as long as a condition is

While Loop true

Try block indicator

Try

Try block terminator and exception handler

Catch

Description of a job, work flow, data flow, or a diagram in a

Annotation
workspace

2.3.3.1.3 Data flows

A data flow is the process by which the software transforms source data into target data.

Data flows process data in the order in which they are arranged in a work flow.

A data flow defines the basic task that Data Services accomplishes. The basic task is moving data from one or
more sources to one or more target tables or files.

You define data flows by identifying the sources from which to extract data, the transformations that the data
should undergo, and the targets.

Tutorial
16 PUBLIC Introduction to the tutorial
Use data flows to:

● Identify the source data to read

● Define the transformations to perform on the data
● Identify the target table to load data

A data flow is a reusable object. It is always called from a work flow or a job.

2.3.3.2 Object naming conventions

A consistent naming convention for Data Services objects helps you easily identify objects listed in an object
hierarchy.

SAP uses the following naming conventions:

Object Prefix Suffix Example

Job JOB JOB_SalesOrg

Work flow WF WF_SalesOrg

Data flow DF DF_Currency

Datastore DS ODS_DS

Related Information

Naming conventions for objects in jobs

2.3.3.3 About deleting objects

To delete an object, first decide whether to delete the object from the project or delete the object from the
repository.

When you delete an object from a project in the project area, the software removes the object from the project.
The object is still available in the object library and the repository.

Tutorial
Introduction to the tutorial PUBLIC 17
When you delete the object from the object library, the software deletes all occurrences of the object from the
repository. If the object is called in separate data flows, the software deletes the object from each data flow.
The deletion may adversly affect all related objects.

To protect you from deleting objects unintentionally, the software issues a notice before it deletes the object
from the repository. The notice states that the object is used in multiple locations, and it provides the following
options:

● Yes: Continues with the delete of the object from the repository.
● No: Discontinues the delete process.
● View Where Used: Displays a list of the related objects in which the object will be deleted.

2.4 Preparation for this tutorial

Ensure that you perform all of the preparation for the tutorial so that you can successfully complete each
exercise.

The preparation may include some steps that your administrator has already completed. You may need to
contact your administrator for important connection information and access information related to those
tasks.

We have a complete documentation set for SAP Data Services available on our User Assistance Customer
Portal. If you are unclear about a process in the tutorial, or if you don't understand a concept, refer to the online
documentation at http://help.sap.com/bods.

Tasks required to prepare for the tutorial [page 18]

An overview of the steps to prepare for the SAP Data Services tutorial exercises, and who should
perform the steps.

Tutorial structure [page 25]

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

Exiting the tutorial [page 26]

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

Resuming the tutorial [page 26]

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

2.4.1 Tasks required to prepare for the tutorial

An overview of the steps to prepare for the SAP Data Services tutorial exercises, and who should perform the
steps.

 Note

If your administrator has already completed these steps, you may be able to skip the tutorial set up section.

You must have sufficient user permission to perform the exercises in the tutorial. For information about
permissions, see the Administrator Guide.

Tutorial
18 PUBLIC Introduction to the tutorial
You or an administrator sets up your system for this tutorial. Instructions for administrator-only tasks are not
included in the tutorial. The following table lists each task and who performs the task.

Task Who performs

Install Central Management Server (CMS) by installing either Administrator. More information in the Installation Guide.
the SAP BusinessObjects Business Intelligence platform (BI
platform) or the Information platform services platform (IPS
platform).

Install SAP Data Services Administrator. Steps are in the Installation Guide.

Create user account for tutorial participants. Administrator. Steps are in the Administrator Guide.

Create tutorial repository, source, and target databases You or a user who has permission to perform these tasks in
your RDBMS. Steps are in the tutorial.

Establish the tutorial repository as your local repository by Administrator or you, if you have sufficient permission.
using the Repository Manager, the Server Manager, and the Steps are in the tutorial.
Central Management Console (CMC)

Run the tutorial scripts to create source and target tables. Administrator or you. Steps are in the tutorial.

1. Creating repository, source, and target databases on an existing RDBMS [page 20]
Create the three databases using your preferred RDBMS.
2. Creating a local repository [page 21]
Use the repository database that you created earlier in your RDBMS to create a local repository.
3. Defining a job server and associating your repository [page 21]
Use the Data Services Server Manager to configure a new job server and associate the job server with
the local repository.
4. Configuring the local repository in the CMC [page 22]
To continue preparing the SAP Data Services local repository, you enter connection information in the
Central Management Console (CMC)
5. Running the provided SQL scripts [page 23]
Run the tutorial SQL scripts to create the sample source and target tables.

Task overview: Preparation for this tutorial [page 18]

Related Information

Tutorial structure [page 25]

Exiting the tutorial [page 26]
Resuming the tutorial [page 26]

Tutorial
Introduction to the tutorial PUBLIC 19
2.4.1.1 Creating repository, source, and target databases
on an existing RDBMS

Create the three databases using your preferred RDBMS.

An administrator, or a user with sufficient permissions to your RDBMS must perform these steps.

1. Log on to your RDBMS.

2. (Oracle only). Optionally create a service name alias.
Set the protocol to TCP/IP and enter a service name; for example, training.sap. The service name can
act as your connection name.
3. Create three databases and create a user account and password for each one.
We suggest that you use the values in the following table for the databases. We use these values in the
tutorial SQL scripts and throughout the exercises in the tutorial:

Database user names and passwords

Database type User name Password

Repository repo repo

Source ods ods

Target target target

4. Grant access privileges for the user account. For example, grant connect and resource roles for Oracle.
5. Use the following table as a worksheet to note the connection names, database versions, user names, and
passwords for the three databases that you create. We refer you to this information in several of the
exercises in the tutorial.

Value Repository Source Target

Database connection name (Oracle)

or database server name

Database name (such as Oracle or

MS-SQL Server)

Database version

User name

Password

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

Next task: Creating a local repository [page 21]

Tutorial
20 PUBLIC Introduction to the tutorial