SAP Business objects DATA SERVICES Designer Guide
Designer Guide
■ SAP BusinessObjects Data Services XI 4.0 (14.0.0)
2010-12-02
Copyright
© 2010 SAP AG. All rights reserved.SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP
Business ByDesign, and other SAP products and services mentioned herein as well as their respective
logos are trademarks or registered trademarks of SAP AG in Germany and other countries. Business
Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web
Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well
as their respective logos are trademarks or registered trademarks of Business Objects S.A. in the
United States and in other countries. Business Objects is an SAP company.All other product and
service names mentioned are the trademarks of their respective companies. Data contained in this
document serves informational purposes only. National product specifications may vary.These materials
are subject to change without notice. These materials are provided by SAP AG and its affiliated
companies ("SAP Group") for informational purposes only, without representation or warranty of any
kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The
only warranties for SAP Group products and services are those that are set forth in the express
warranty statements accompanying such products and services, if any. Nothing herein should be
construed as constituting an additional warranty.
2010-12-02
Contents
Introduction...........................................................................................................................19Chapter 1
1.1
1.1.1
1.1.2
1.1.3
1.1.4
1.2
1.2.1
1.2.2
2.1
2.2
3.1
3.1.1
3.1.2
3.1.3
3.2
3.3
3.3.1
3.3.2
3.3.3
3.3.4
3.3.5
3.3.6
3.3.7
3.3.8
3.3.9
3.4
3.5
Welcome to SAP BusinessObjects Data Services.................................................................19
Welcome...............................................................................................................................19
Documentation set for SAP BusinessObjects Data Services.................................................19
Accessing documentation......................................................................................................22
SAP BusinessObjects information resources.........................................................................23
Overview of this guide............................................................................................................24
About this guide.....................................................................................................................25
Who should read this guide....................................................................................................25
Logging into the Designer.....................................................................................................27Chapter 2
Version restrictions................................................................................................................27
Resetting users......................................................................................................................28
Designer User Interface........................................................................................................29Chapter 3
Objects..................................................................................................................................29
Reusable objects...................................................................................................................29
Single-use objects..................................................................................................................30
Object hierarchy.....................................................................................................................30
Designer window...................................................................................................................31
Menu bar...............................................................................................................................32
Project menu..........................................................................................................................33
Edit menu...............................................................................................................................33
View menu.............................................................................................................................34
Tools menu............................................................................................................................34
Debug menu..........................................................................................................................36
Validation menu.....................................................................................................................36
Dictionary menu.....................................................................................................................37
Window menu........................................................................................................................38
Help menu..............................................................................................................................38
Toolbar...................................................................................................................................39
Project area ..........................................................................................................................40
2010-12-023
Contents
3.6
3.7
3.8
3.8.1
3.8.2
3.8.3
3.8.4
3.8.5
3.8.6
3.8.7
3.9
3.9.1
3.9.2
3.9.3
3.10
3.11
3.11.1
3.11.2
3.11.3
3.11.4
3.11.5
3.11.6
3.11.7
3.11.8
3.12
3.12.1
3.12.2
3.12.3
3.12.4
3.12.5
3.12.6
3.12.7
Tool palette............................................................................................................................41
Designer keyboard accessibility.............................................................................................43
Workspace............................................................................................................................43
Moving objects in the workspace area...................................................................................44
Connecting objects................................................................................................................44
Disconnecting objects............................................................................................................44
Describing objects ................................................................................................................45
Scaling the workspace...........................................................................................................45
Arranging workspace windows...............................................................................................45
Closing workspace windows..................................................................................................46
Local object library.................................................................................................................46
To open the object library.......................................................................................................47
To display the name of each tab as well as its icon.................................................................48
To sort columns in the object library.......................................................................................48
Object editors........................................................................................................................48
Working with objects..............................................................................................................49
Creating new reusable objects...............................................................................................49
Changing object names..........................................................................................................50
Viewing and changing object properties.................................................................................51
Creating descriptions.............................................................................................................52
Creating annotations .............................................................................................................55
Copying objects.....................................................................................................................56
Saving and deleting objects....................................................................................................56
Searching for objects.............................................................................................................58
General and environment options...........................................................................................60
Designer — Environment.......................................................................................................60
Designer — General..............................................................................................................62
Designer — Graphics.............................................................................................................63
Designer — Central Repository Connections.........................................................................64
Data — General.....................................................................................................................64
Job Server — Environment....................................................................................................65
Job Server — General...........................................................................................................65
4.1
4.1.1
4.1.2
4.1.3
4.1.4
4.2
4.2.1
Projects and Jobs.................................................................................................................67Chapter 4
Projects.................................................................................................................................67
Objects that make up a project..............................................................................................67
Creating a new project...........................................................................................................68
Opening existing projects.......................................................................................................68
Saving projects......................................................................................................................69
Jobs.......................................................................................................................................69
Creating jobs.........................................................................................................................70
2010-12-024
Contents
4.2.2
5.1
5.2
5.2.1
5.2.2
5.2.3
5.2.4
5.2.5
5.2.6
5.2.7
5.2.8
5.2.9
5.3
5.3.1
5.3.2
5.3.3
5.4
5.4.1
5.4.2
5.4.3
5.5
5.5.1
5.5.2
5.5.3
5.5.4
5.5.5
5.5.6
5.5.7
5.5.8
5.5.9
Naming conventions for objects in jobs..................................................................................71
Datastores.............................................................................................................................73Chapter 5
What are datastores?.............................................................................................................73
Database datastores..............................................................................................................74
Mainframe interface...............................................................................................................74
Defining a database datastore................................................................................................77
Configuring ODBC data sources on UNIX..............................................................................80
Changing a datastore definition..............................................................................................80
Browsing metadata through a database datastore..................................................................81
Importing metadata through a database datastore..................................................................84
Memory datastores................................................................................................................90
Persistent cache datastores...................................................................................................94
Linked datastores...................................................................................................................97
Adapter datastores................................................................................................................99
Defining an adapter datastore..............................................................................................100
Browsing metadata through an adapter datastore................................................................102
Importing metadata through an adapter datastore................................................................102
Web service datastores.......................................................................................................103
Defining a web service datastore.........................................................................................103
Browsing WSDL metadata through a web service datastore................................................104
Importing metadata through a web service datastore...........................................................106
Creating and managing multiple datastore configurations.....................................................106
Definitions............................................................................................................................107
Why use multiple datastore configurations?.........................................................................108
Creating a new configuration................................................................................................108
Adding a datastore alias.......................................................................................................110
Functions to identify the configuration..................................................................................110
Portability solutions..............................................................................................................112
Job portability tips................................................................................................................116
Renaming table and function owner......................................................................................117
Defining a system configuration...........................................................................................121
6.1
6.2
6.3
6.3.1
6.3.2
6.3.3
File formats.........................................................................................................................125Chapter 6
Understanding file formats...................................................................................................125
File format editor..................................................................................................................126
Creating file formats.............................................................................................................128
To create a new file format...................................................................................................128
Modeling a file format on a sample file.................................................................................129
Replicating and renaming file formats...................................................................................130
2010-12-025
Contents
6.3.4
6.3.5
6.4
6.4.1
6.4.2
6.4.3
6.5
6.5.1
6.5.2
6.5.3
6.5.4
6.5.5
6.5.6
6.5.7
6.6
6.6.1
6.6.2
6.6.3
6.6.4
6.7
6.7.1
6.7.2
6.7.3
6.7.4
6.8
6.8.1
6.9
6.9.1
6.9.2
6.9.3
6.10
To create a file format from an existing flat table schema.....................................................131
To create a specific source or target file...............................................................................131
Editing file formats................................................................................................................132
To edit a file format template................................................................................................132
To edit a source or target file...............................................................................................133
Change multiple column properties......................................................................................133
File format features..............................................................................................................134
Reading multiple files at one time.........................................................................................134
Identifying source file names ...............................................................................................135
Number formats...................................................................................................................135
Ignoring rows with specified markers....................................................................................136
Date formats at the field level...............................................................................................137
Parallel process threads.......................................................................................................137
Error handling for flat-file sources.........................................................................................138
File transfers........................................................................................................................141
Custom transfer system variables for flat files......................................................................141
Custom transfer options for flat files....................................................................................143
Setting custom transfer options...........................................................................................144
Design tips...........................................................................................................................145
Creating COBOL copybook file formats...............................................................................146
To create a new COBOL copybook file format.....................................................................146
To create a new COBOL copybook file format and a data file..............................................147
To create rules to identify which records represent which schemas.....................................148
To identify the field that contains the length of the schema's record.....................................148
Creating Microsoft Excel workbook file formats on UNIX platforms .....................................148
To create a Microsoft Excel workbook file format on UNIX ..................................................149
Creating Web log file formats...............................................................................................150
Word_ext function................................................................................................................151
Concat_date_time function...................................................................................................151
WL_GetKeyValue function...................................................................................................152
Unstructured file formats......................................................................................................153
7.1
7.1.1
7.1.2
7.1.3
7.1.4
7.1.5
7.1.6
7.1.7
Data Flows..........................................................................................................................155Chapter 7
What is a data flow?.............................................................................................................155
Naming data flows................................................................................................................155
Data flow example................................................................................................................155
Steps in a data flow..............................................................................................................156
Data flows as steps in work flows........................................................................................156
Intermediate data sets in a data flow....................................................................................157
Operation codes..................................................................................................................157
Passing parameters to data flows.........................................................................................158
2010-12-026
Contents
7.2
7.2.1
7.2.2
7.2.3
7.3
7.3.1
7.3.2
7.3.3
7.3.4
7.3.5
7.4
7.4.1
7.4.2
7.5
7.5.1
7.5.2
7.5.3
7.6
7.6.1
7.6.2
7.6.3
7.6.4
7.7
Creating and defining data flows..........................................................................................159
To define a new data flow using the object library.................................................................159
To define a new data flow using the tool palette...................................................................159
To change properties of a data flow.....................................................................................159
Source and target objects....................................................................................................160
Source objects.....................................................................................................................161
Target objects......................................................................................................................161
Adding source or target objects to data flows......................................................................162
Template tables....................................................................................................................164
Converting template tables to regular tables........................................................................165
Adding columns within a data flow .......................................................................................166
To add columns within a data flow........................................................................................167
Propagating columns in a data flow containing a Merge transform........................................168
Lookup tables and the lookup_ext function...........................................................................169
Accessing the lookup_ext editor..........................................................................................170
Example: Defining a simple lookup_ext function....................................................................171
Example: Defining a complex lookup_ext function ................................................................173
Data flow execution.............................................................................................................176
Push down operations to the database server......................................................................177
Distributed data flow execution............................................................................................177
Load balancing.....................................................................................................................178
Caches................................................................................................................................179
Audit Data Flow overview.....................................................................................................179
8.1
8.2
8.3
8.3.1
8.3.2
8.4
8.4.1
8.4.2
8.5
8.5.1
8.5.2
8.6
8.6.1
8.6.2
8.6.3
8.6.4
Transforms..........................................................................................................................181Chapter 8
To add a transform to a data flow.........................................................................................183
Transform editors.................................................................................................................184
Transform configurations......................................................................................................185
To create a transform configuration......................................................................................185
To add a user-defined field ..................................................................................................186
The Query transform ...........................................................................................................187
To add a Query transform to a data flow..............................................................................188
Query Editor.........................................................................................................................188
Data Quality transforms ......................................................................................................190
To add a Data Quality transform to a data flow.....................................................................190
Data Quality transform editors.............................................................................................192
Text Data Processing transforms.........................................................................................195
Text Data Processing overview............................................................................................195
Entity Extraction transform overview.....................................................................................196
Using the Entity Extraction transform....................................................................................199
Differences between text data processing and data cleanse transforms...............................200
2010-12-027
Contents
8.6.5
8.6.6
8.6.7
8.6.8
8.6.9
9.1
9.2
9.3
9.4
9.5
9.5.1
9.5.2
9.5.3
9.6
9.6.1
9.7
9.7.1
9.7.2
9.7.3
9.8
9.8.1
9.8.2
9.8.3
9.9
9.9.1
9.9.2
Using multiple transforms.....................................................................................................201
Examples for using the Entity Extraction transform...............................................................201
To add a text data processing transform to a data flow........................................................202
Entity Extraction transform editor.........................................................................................204
Using filtering options..........................................................................................................205
Work Flows.........................................................................................................................207Chapter 9
What is a work flow?............................................................................................................207
Steps in a work flow.............................................................................................................208
Order of execution in work flows..........................................................................................208
Example of a work flow........................................................................................................209
Creating work flows.............................................................................................................210
To create a new work flow using the object library...............................................................210
To create a new work flow using the tool palette .................................................................210
To specify that a job executes the work flow one time.........................................................210
Conditionals.........................................................................................................................211
To define a conditional.........................................................................................................212
While loops..........................................................................................................................213
Design considerations..........................................................................................................213
Defining a while loop............................................................................................................215
Using a while loop with View Data........................................................................................216
Try/catch blocks..................................................................................................................216
Defining a try/catch block....................................................................................................217
Categories of available exceptions.......................................................................................218
Example: Catching details of an error...................................................................................219
Scripts.................................................................................................................................219
To create a script.................................................................................................................220
Debugging scripts using the print function............................................................................221
10.1
10.2
10.3
10.3.1
10.3.2
10.3.3
10.3.4
10.3.5
10.4
10.4.1
Nested Data........................................................................................................................223Chapter 10
What is nested data?...........................................................................................................223
Representing hierarchical data.............................................................................................223
Formatting XML documents.................................................................................................226
Importing XML Schemas......................................................................................................226
Specifying source options for XML files ..............................................................................231
Mapping optional schemas...................................................................................................232
Using Document Type Definitions (DTDs) ...........................................................................234
Generating DTDs and XML Schemas from an NRDM schema.............................................236
Operations on nested data...................................................................................................236
Overview of nested data and the Query transform...............................................................237
2010-12-028
Contents
10.4.2
10.4.3
10.4.4
10.4.5
10.4.6
10.4.7
10.4.8
10.5
10.5.1
11.1
11.2
11.2.1
11.2.2
11.2.3
11.3
11.3.1
11.3.2
11.3.3
11.4
11.4.1
11.4.2
11.4.3
11.4.4
11.5
11.5.1
11.5.2
11.5.3
11.6
11.6.1
11.6.2
11.6.3
11.7
11.7.1
11.7.2
11.7.3
FROM clause construction...................................................................................................237
Nesting columns .................................................................................................................240
Using correlated columns in nested data..............................................................................242
Distinct rows and nested data..............................................................................................243
Grouping values across nested schemas.............................................................................243
Unnesting nested data ........................................................................................................244
Transforming lower levels of nested data.............................................................................247
XML extraction and parsing for columns...............................................................................247
Sample Scenarios................................................................................................................248
Real-time Jobs....................................................................................................................255Chapter 11
Request-response message processing...............................................................................255
What is a real-time job?........................................................................................................256
Real-time versus batch.........................................................................................................256
Messages............................................................................................................................257
Real-time job examples........................................................................................................258
Creating real-time jobs.........................................................................................................260
Real-time job models............................................................................................................260
Using real-time job models...................................................................................................261
To create a real-time job with a single dataflow....................................................................263
Real-time source and target objects.....................................................................................264
To view an XML message source or target schema.............................................................265
Secondary sources and targets............................................................................................265
Transactional loading of tables.............................................................................................266
Design tips for data flows in real-time jobs...........................................................................267
Testing real-time jobs...........................................................................................................267
Executing a real-time job in test mode..................................................................................268
Using View Data..................................................................................................................268
Using an XML file target.......................................................................................................268
Building blocks for real-time jobs..........................................................................................269
Supplementing message data..............................................................................................269
Branching data flow based on a data cache value.................................................................272
Calling application functions.................................................................................................274
Designing real-time applications...........................................................................................274
Reducing queries requiring back-office application access....................................................275
Messages from real-time jobs to adapter instances.............................................................275
Real-time service invoked by an adapter instance.................................................................275
12.1
Embedded Data Flows........................................................................................................277Chapter 12
Overview of embedded data flows.......................................................................................277
2010-12-029
Contents
12.2
12.3
12.3.1
12.3.2
12.3.3
12.3.4
12.3.5
13.1
13.2
13.2.1
13.3
13.3.1
13.3.2
13.3.3
13.3.4
13.4
13.4.1
13.4.2
13.4.3
13.5
13.5.1
13.5.2
13.5.3
13.6
13.7
13.7.1
13.8
13.8.1
13.8.2
13.8.3
13.8.4
13.8.5
13.8.6
Example of when to use embedded data flows.....................................................................278
Creating embedded data flows.............................................................................................278
Using the Make Embedded Data Flow option.......................................................................279
Creating embedded data flows from existing flows...............................................................281
Using embedded data flows.................................................................................................281
Separately testing an embedded data flow...........................................................................284
Troubleshooting embedded data flows.................................................................................285
Variables and Parameters...................................................................................................287Chapter 13
Overview of variables and parameters..................................................................................287
The Variables and Parameters window.................................................................................288
To view the variables and parameters in each job, work flow, or data flow............................288
Using local variables and parameters...................................................................................290
Parameters..........................................................................................................................291
Passing values into data flows..............................................................................................291
To define a local variable......................................................................................................292
Defining parameters.............................................................................................................292
Using global variables ..........................................................................................................294
Creating global variables......................................................................................................294
Viewing global variables ......................................................................................................295
Setting global variable values...............................................................................................295
Local and global variable rules..............................................................................................299
Naming................................................................................................................................299
Replicating jobs and work flows...........................................................................................299
Importing and exporting........................................................................................................299
Environment variables..........................................................................................................300
Setting file names at run-time using variables.......................................................................300
To use a variable in a flat file name.......................................................................................300
Substitution parameters.......................................................................................................301
Overview of substitution parameters....................................................................................301
Using the Substitution Parameter Editor...............................................................................303
Associating a substitution parameter configuration with a system configuration...................305
Overriding a substitution parameter in the Administrator......................................................307
Executing a job with substitution parameters .......................................................................307
Exporting and importing substitution parameters..................................................................308
14.1
14.2
14.2.1
Executing Jobs....................................................................................................................311Chapter 14
Overview of job execution....................................................................................................311
Preparing for job execution...................................................................................................311
Validating jobs and job components.....................................................................................312
2010-12-0210
Contents
14.2.2
14.2.3
14.3
14.3.1
14.3.2
14.3.3
14.4
14.4.1
14.4.2
14.5
14.5.1
14.5.2
15.1
15.1.1
15.1.2
15.1.3
15.1.4
15.1.5
15.1.6
15.2
15.2.1
15.2.2
15.2.3
15.3
15.3.1
15.3.2
15.4
15.4.1
15.4.2
15.4.3
15.4.4
15.4.5
15.4.6
15.4.7
Ensuring that the Job Server is running................................................................................313
Setting job execution options...............................................................................................313
Executing jobs as immediate tasks.......................................................................................314
To execute a job as an immediate task.................................................................................314
Monitor tab .........................................................................................................................315
Log tab ...............................................................................................................................316
Debugging execution errors.................................................................................................316
Using logs............................................................................................................................317
Examining target data...........................................................................................................320
Changing Job Server options...............................................................................................320
To change option values for an individual Job Server...........................................................323
To use mapped drive names in a path..................................................................................325
Data Assessment................................................................................................................327Chapter 15
Using the Data Profiler.........................................................................................................328
Data sources that you can profile.........................................................................................328
Connecting to the profiler server..........................................................................................329
Profiler statistics..................................................................................................................330
Executing a profiler task.......................................................................................................333
Monitoring profiler tasks using the Designer........................................................................338
Viewing the profiler results...................................................................................................339
Using View Data to determine data quality...........................................................................345
Data tab...............................................................................................................................346
Profile tab............................................................................................................................347
Relationship Profile or Column Profile tab.............................................................................347
Using the Validation transform.............................................................................................347
Analyzing the column profile.................................................................................................348
Defining a validation rule based on a column profile..............................................................349
Using Auditing .....................................................................................................................350
Auditing objects in a data flow..............................................................................................351
Accessing the Audit window................................................................................................355
Defining audit points, rules, and action on failure..................................................................356
Guidelines to choose audit points .......................................................................................359
Auditing embedded data flows.............................................................................................360
Resolving invalid audit labels................................................................................................362
Viewing audit results ...........................................................................................................363
16.1
16.2
Data Quality........................................................................................................................367Chapter 16
Overview of data quality.......................................................................................................367
Data Cleanse.......................................................................................................................367
2010-12-0211
Contents
16.2.1
16.2.2
16.2.3
16.2.4
16.2.5
16.2.6
16.2.7
16.2.8
16.2.9
16.2.10
16.3
16.3.1
16.3.2
16.3.3
16.4
16.4.1
16.4.2
16.4.3
16.4.4
16.4.5
16.4.6
16.4.7
16.4.8
16.4.9
16.4.10
16.4.11
16.4.12
16.4.13
16.5
16.5.1
16.5.2
16.5.3
16.5.4
16.5.5
16.5.6
16.5.7
16.5.8
16.5.9
16.5.10
16.5.11
16.5.12
About cleansing data............................................................................................................367
Workflow for developing, deploying and maintaining a cleansing package............................368
Configuring the Data Cleanse transform..............................................................................370
Ranking and prioritizing parsing engines...............................................................................371
About parsing data...............................................................................................................372
About standardizing data......................................................................................................377
About assigning gender descriptions and prenames.............................................................378
Prepare records for matching...............................................................................................378
Region-specific data.............................................................................................................379
Japanese data......................................................................................................................381
Geocoding...........................................................................................................................382
POI and address geocoding ................................................................................................383
POI and address reverse geocoding ....................................................................................389
Understanding your output...................................................................................................400
Match..................................................................................................................................402
Matching strategies..............................................................................................................402
Match components..............................................................................................................402
Match Wizard.......................................................................................................................404
Transforms for match data flows..........................................................................................411
Working in the Match and Associate editors........................................................................412
Physical and logical sources.................................................................................................413
Match preparation................................................................................................................417
Match criteria.......................................................................................................................438
Post-match processing.........................................................................................................454
Association matching...........................................................................................................472
Unicode matching................................................................................................................472
Phonetic matching................................................................................................................475
Set up for match reports .....................................................................................................477
Address Cleanse..................................................................................................................478
How address cleanse works.................................................................................................478
Prepare your input data........................................................................................................481
Determine which transform(s) to use...................................................................................482
Identify the country of destination.........................................................................................485
Set up the reference files.....................................................................................................486
Define the standardization options.......................................................................................488
Process Japanese addresses .............................................................................................489
Process Chinese addresses.................................................................................................498
Supported countries (Global Address Cleanse)....................................................................503
New Zealand Certification....................................................................................................505
Global Address Cleanse Suggestion List.............................................................................509
Global Suggestion List.........................................................................................................510
2010-12-0212
Contents
16.6
16.6.1
16.6.2
16.6.3
16.6.4
16.6.5
16.6.6
16.6.7
16.6.8
16.6.9
16.6.10
16.6.11
16.6.12
16.7
16.7.1
16.8
17.1
17.1.1
17.1.2
17.1.3
17.2
17.2.1
17.2.2
17.2.3
17.2.4
17.2.5
17.3
17.3.1
17.3.2
17.3.3
17.3.4
17.3.5
17.3.6
17.3.7
17.4
17.4.1
17.4.2
17.4.3
17.4.4
Beyond the basic address cleansing.....................................................................................511
USPS DPV®.........................................................................................................................511
USPS DSF2®.......................................................................................................................521
NCOALink (USA Regulatory Address Cleanse)...................................................................531
LACSLink®...........................................................................................................................549
SuiteLink™............................................................................................................................560
USPS eLOT® .......................................................................................................................563
Early Warning System (EWS)...............................................................................................564
GeoCensus (USA Regulatory Address Cleanse).................................................................565
USPS RDI®..........................................................................................................................569
Z4Change (USA Regulatory Address Cleanse)....................................................................573
Suggestion lists overview.....................................................................................................574
Multiple data source statistics reporting...............................................................................577
Data Quality support for native data types............................................................................595
Data Quality data type definitions.........................................................................................596
Data Quality support for NULL values..................................................................................596
Design and Debug..............................................................................................................599Chapter 17
Using View Where Used......................................................................................................599
Accessing View Where Used from the object library............................................................600
Accessing View Where Used from the workspace...............................................................602
Limitations...........................................................................................................................603
Using View Data..................................................................................................................604
Accessing View Data...........................................................................................................605
Viewing data in the workspace.............................................................................................605
View Data Properties...........................................................................................................607
View Data tool bar options...................................................................................................611
View Data tabs....................................................................................................................612
Using the interactive debugger.............................................................................................616
Before starting the interactive debugger...............................................................................616
Starting and stopping the interactive debugger.....................................................................619
Panes...................................................................................................................................621
Debug menu options and tool bar.........................................................................................628
Viewing data passed by transforms......................................................................................630
Push-down optimizer............................................................................................................630
Limitations...........................................................................................................................631
Comparing Objects..............................................................................................................632
To compare two different objects.........................................................................................632
To compare two versions of the same object.......................................................................633
Overview of the Difference Viewer window..........................................................................633
Navigating through differences.............................................................................................637
2010-12-0213
Contents
17.5
17.5.1
17.5.2
18.1
18.1.1
18.1.2
18.2
18.2.1
18.2.2
18.2.3
18.2.4
19.1
19.2
19.2.1
19.2.2
19.2.3
19.2.4
19.2.5
19.2.6
19.2.7
19.3
19.4
19.4.1
19.4.2
19.4.3
Calculating column mappings...............................................................................................637
To automatically calculate column mappings ........................................................................638
To manually calculate column mappings ..............................................................................638
Exchanging Metadata..........................................................................................................639Chapter 18
Metadata exchange..............................................................................................................639
Importing metadata files into the software............................................................................640
Exporting metadata files from the software...........................................................................640
Creating SAP universes.......................................................................................................641
To create universes using the Tools menu ...........................................................................641
To create universes using the object library..........................................................................642
Mappings between repository and universe metadata..........................................................642
Attributes that support metadata exchange..........................................................................643
Recovery Mechanisms........................................................................................................645Chapter 19
Recovering from unsuccessful job execution........................................................................645
Automatically recovering jobs...............................................................................................646
Enabling automated recovery...............................................................................................646
Marking recovery units.........................................................................................................647
Running in recovery mode....................................................................................................648
Ensuring proper execution path............................................................................................648
Using try/catch blocks with automatic recovery...................................................................649
Ensuring that data is not duplicated in targets.......................................................................651
Using preload SQL to allow re-executable data flows ..........................................................652
Manually recovering jobs using status tables........................................................................654
Processing data with problems.............................................................................................655
Using overflow files..............................................................................................................655
Filtering missing or bad values .............................................................................................656
Handling facts with missing dimensions................................................................................657
20.1
20.1.1
20.1.2
20.1.3
20.2
20.2.1
20.2.2
20.2.3
20.2.4
Techniques for Capturing Changed Data............................................................................659Chapter 20
Understanding changed-data capture...................................................................................659
Full refresh...........................................................................................................................659
Capturing only changes........................................................................................................659
Source-based and target-based CDC..................................................................................660
Using CDC with Oracle sources..........................................................................................662
Overview of CDC for Oracle databases...............................................................................662
Setting up Oracle CDC........................................................................................................666
To create a CDC datastore for Oracle.................................................................................667
Importing CDC data from Oracle..........................................................................................667
2010-12-0214
Contents
20.2.5
20.2.6
20.2.7
20.2.8
20.2.9
20.3
20.3.1
20.3.2
20.3.3
20.3.4
20.3.5
20.3.6
20.4
20.4.1
20.4.2
20.4.3
20.4.4
20.4.5
20.4.6
20.5
20.5.1
20.5.2
20.5.3
20.5.4
20.5.5
20.6
Viewing an imported CDC table...........................................................................................670
To configure an Oracle CDC source table............................................................................672
To create a data flow with an Oracle CDC source................................................................675
Maintaining CDC tables and subscriptions...........................................................................676
Limitations...........................................................................................................................677
Using CDC with Attunity mainframe sources.......................................................................677
Setting up Attunity CDC......................................................................................................678
Setting up the software for CDC on mainframe sources......................................................679
Importing mainframe CDC data............................................................................................681
Configuring a mainframe CDC source..................................................................................682
Using mainframe check-points.............................................................................................684
Limitations...........................................................................................................................685
Using CDC with Microsoft SQL Server databases ..............................................................685
Overview of CDC for SQL Server databases.......................................................................685
Setting up Microsoft SQL Server for CDC...........................................................................687
Setting up the software for CDC on SQL Server.................................................................689
Importing SQL Server CDC data..........................................................................................690
Configuring a SQL Server CDC source...............................................................................692
Limitations...........................................................................................................................695
Using CDC with timestamp-based sources..........................................................................696
Processing timestamps........................................................................................................697
Overlaps..............................................................................................................................699
Types of timestamps............................................................................................................705
Timestamp-based CDC examples........................................................................................706
Additional job design tips.....................................................................................................712
Using CDC for targets.........................................................................................................713
21.1
21.2
21.2.1
21.2.2
21.2.3
21.2.4
21.2.5
21.2.6
22.1
22.2
Monitoring Jobs..................................................................................................................715Chapter 21
Administrator.......................................................................................................................715
SNMP support.....................................................................................................................715
About the SNMP agent........................................................................................................715
Job Server, SNMP agent, and NMS application architecture...............................................716
About SNMP Agent's Management Information Base (MIB)................................................717
About an NMS application...................................................................................................719
Configuring the software to support an NMS application......................................................720
Troubleshooting...................................................................................................................729
Multi-user Development......................................................................................................731Chapter 22
Central versus local repository.............................................................................................731
Multiple users......................................................................................................................732
2010-12-0215
Contents
22.3
23.1
23.2
23.3
23.3.1
23.3.2
23.3.3
23.3.4
24.1
24.1.1
24.1.2
24.1.3
24.2
24.2.1
24.2.2
24.3
24.4
24.5
24.6
24.6.1
Security and the central repository.......................................................................................734
Multi-user Environment Setup............................................................................................735Chapter 23
Create a nonsecure central repository.................................................................................735
Define a connection to a nonsecure central repository.........................................................736
Activating a central repository..............................................................................................736
To activate a central repository............................................................................................737
To open the central object library.........................................................................................737
To change the active central repository................................................................................737
To change central repository connections............................................................................738
Implementing Central Repository Security.........................................................................739Chapter 24
Overview..............................................................................................................................739
Group-based permissions....................................................................................................739
Permission levels.................................................................................................................740
Process summary................................................................................................................740
Creating a secure central repository.....................................................................................741
To create a secure central repository...................................................................................741
To upgrade a central repository from nonsecure to secure...................................................741
Adding a multi-user administrator (optional)..........................................................................742
Setting up groups and users................................................................................................742
Defining a connection to a secure central repository............................................................743
Working with objects in a secure central repository..............................................................743
Viewing and modifying permissions......................................................................................743
25.1
25.2
25.2.1
25.2.2
25.3
25.3.1
25.3.2
25.3.3
25.4
25.4.1
25.4.2
25.5
25.5.1
25.5.2
Working in a Multi-user Environment.................................................................................745Chapter 25
Filtering................................................................................................................................745
Adding objects to the central repository...............................................................................746
To add a single object to the central repository....................................................................746
To add an object and its dependent objects to the central repository...................................747
Checking out objects...........................................................................................................747
Check out single objects or objects with dependents...........................................................748
Check out single objects or objects with dependents without replacement..........................749
Check out objects with filtering............................................................................................750
Undoing check out...............................................................................................................750
To undo single object check out...........................................................................................751
To undo check out of an object and its dependents..............................................................751
Checking in objects..............................................................................................................751
Checking in single objects, objects with dependents............................................................752
Checking in an object with filtering.......................................................................................753
2010-12-0216
Contents
25.6
25.6.1
25.7
25.7.1
25.7.2
25.7.3
25.8
25.9
25.9.1
25.9.2
25.9.3
25.10
26.1
26.2
26.2.1
26.3
Index 765
Labeling objects...................................................................................................................753
To label an object and its dependents..................................................................................755
Getting objects....................................................................................................................755
To get a single object...........................................................................................................755
To get an object and its dependent objects..........................................................................756
To get an object and its dependent objects with filtering......................................................756
Comparing objects...............................................................................................................756
Viewing object history..........................................................................................................757
To examine the history of an object......................................................................................757
To get a previous version of an object..................................................................................758
To get an object with a particular label..................................................................................758
Deleting objects...................................................................................................................758
Migrating Multi-user Jobs...................................................................................................761Chapter 26
Application phase management............................................................................................761
Copying contents between central repositories....................................................................762
To copy the contents of one central repository to another central repository.......................762
Central repository migration.................................................................................................763
2010-12-0217
Contents
2010-12-0218
Introduction
Introduction
1.1 Welcome to SAP BusinessObjects Data Services
1.1.1 Welcome
SAP BusinessObjects Data Services delivers a single enterprise-class solution for data integration,
data quality, data profiling, and text data processing that allows you to integrate, transform, improve,
and deliver trusted data to critical business processes. It provides one development UI, metadata
repository, data connectivity layer, run-time environment, and management console—enabling IT
organizations to lower total cost of ownership and accelerate time to value. With SAP BusinessObjects
Data Services, IT organizations can maximize operational efficiency with a single solution to improve
data quality and gain access to heterogeneous sources and applications.
1.1.2 Documentation set for SAP BusinessObjects Data Services
You should become familiar with all the pieces of documentation that relate to your SAP BusinessObjects
Data Services product.
What this document providesDocument
Administrator's Guide
Customer Issues Fixed
Designer Guide
Information about administrative tasks such as monitoring,
lifecycle management, security, and so on.
Information about customer issues fixed in this release.
Information about how to use SAP BusinessObjects Data
Services Designer.
Documentation Map
Information about available SAP BusinessObjects Data Services books, languages, and locations.
2010-12-0219
Introduction
What this document providesDocument
Installation Guide for Windows
Installation Guide for UNIX
Integrator's Guide
Management Console Guide
Performance Optimization Guide
Reference Guide
Release Notes
Technical Manuals
Information about and procedures for installing SAP BusinessObjects Data Services in a Windows environment.
Information about and procedures for installing SAP BusinessObjects Data Services in a UNIX environment.
Information for third-party developers to access SAP BusinessObjects Data Services functionality using web services and
APIs.
Information about how to use SAP BusinessObjects Data
Services Administrator and SAP BusinessObjects Data Services Metadata Reports.
Information about how to improve the performance of SAP
BusinessObjects Data Services.
Detailed reference material for SAP BusinessObjects Data
Services Designer.
Important information you need before installing and deploying
this version of SAP BusinessObjects Data Services.
A compiled “master” PDF of core SAP BusinessObjects Data
Services books containing a searchable master table of contents and index:
•
Administrator's Guide
•
Designer Guide
•
Reference Guide
•
Management Console Guide
•
Performance Optimization Guide
•
Supplement for J.D. Edwards
•
Supplement for Oracle Applications
•
Supplement for PeopleSoft
•
Supplement for Salesforce.com
•
Supplement for Siebel
•
Supplement for SAP
Text Data Processing Extraction Customization Guide
Text Data Processing Language Reference
Guide
Information about building dictionaries and extraction rules to
create your own extraction patterns to use with Text Data
Processing transforms.
Information about the linguistic analysis and extraction processing features that the Text Data Processing component provides, as well as a reference section for each language supported.
2010-12-0220
Introduction
What this document providesDocument
Tutorial
Upgrade Guide
What's New
In addition, you may need to refer to several Adapter Guides and Supplemental Guides.
Supplement for J.D. Edwards
Supplement for Oracle Applications
Supplement for PeopleSoft
A step-by-step introduction to using SAP BusinessObjects
Data Services.
Release-specific product behavior changes from earlier versions of SAP BusinessObjects Data Services to the latest release. This manual also contains information about how to
migrate from SAP BusinessObjects Data Quality Management
to SAP BusinessObjects Data Services.
Highlights of new key features in this SAP BusinessObjects
Data Services release. This document is not updated for support package or patch releases.
What this document providesDocument
Information about interfaces between SAP BusinessObjects Data Services
and J.D. Edwards World and J.D. Edwards OneWorld.
Information about the interface between SAP BusinessObjects Data Services
and Oracle Applications.
Information about interfaces between SAP BusinessObjects Data Services
and PeopleSoft.
Supplement for Salesforce.com
Supplement for SAP
Supplement for Siebel
Information about how to install, configure, and use the SAP BusinessObjects
Data Services Salesforce.com Adapter Interface.
Information about interfaces between SAP BusinessObjects Data Services,
SAP Applications, and SAP NetWeaver BW.
Information about the interface between SAP BusinessObjects Data Services
and Siebel.
We also include these manuals for information about SAP BusinessObjects Information platform services.
Information platform services Administrator's Guide
Information platform services Installation Guide for
UNIX
What this document providesDocument
Information for administrators who are responsible for
configuring, managing, and maintaining an Information
platform services installation.
Installation procedures for SAP BusinessObjects Information platform services on a UNIX environment.
2010-12-0221
Introduction
What this document providesDocument
Information platform services Installation Guide for
Windows
1.1.3 Accessing documentation
You can access the complete documentation set for SAP BusinessObjects Data Services in several
places.
1.1.3.1 Accessing documentation on Windows
After you install SAP BusinessObjects Data Services, you can access the documentation from the Start
menu.
1.
Choose Start > Programs > SAP BusinessObjects Data Services XI 4.0 > Data Services
Documentation.
Installation procedures for SAP BusinessObjects Information platform services on a Windows environment.
Note:
Only a subset of the documentation is available from the Start menu. The documentation set for this
release is available in <LINK_DIR>\Doc\Books\en.
2.
Click the appropriate shortcut for the document that you want to view.
1.1.3.2 Accessing documentation on UNIX
After you install SAP BusinessObjects Data Services, you can access the online documentation by
going to the directory where the printable PDF files were installed.
1.
Go to <LINK_DIR>/doc/book/en/.
2.
Using Adobe Reader, open the PDF file of the document that you want to view.
1.1.3.3 Accessing documentation from the Web
2010-12-0222
Introduction
You can access the complete documentation set for SAP BusinessObjects Data Services from the SAP
BusinessObjects Business Users Support site.
1.
Go to http://help.sap.com.
2.
Click SAP BusinessObjects at the top of the page.
3.
Click All Products in the navigation pane on the left.
You can view the PDFs online or save them to your computer.
1.1.4 SAP BusinessObjects information resources
A global network of SAP BusinessObjects technology experts provides customer support, education,
and consulting to ensure maximum information management benefit to your business.
Useful addresses at a glance:
2010-12-0223
Introduction
ContentAddress
Customer Support, Consulting, and Education
services
http://service.sap.com/
SAP BusinessObjects Data Services Community
http://www.sdn.sap.com/irj/sdn/ds
Forums on SCN (SAP Community Network )
http://forums.sdn.sap.com/forum.jspa?foru
mID=305
Blueprints
http://www.sdn.sap.com/irj/boc/blueprints
Information about SAP Business User Support
programs, as well as links to technical articles,
downloads, and online forums. Consulting services
can provide you with information about how SAP
BusinessObjects can help maximize your information management investment. Education services
can provide information about training options and
modules. From traditional classroom learning to
targeted e-learning seminars, SAP BusinessObjects
can offer a training package to suit your learning
needs and preferred learning style.
Get online and timely information about SAP BusinessObjects Data Services, including tips and tricks,
additional downloads, samples, and much more.
All content is to and from the community, so feel
free to join in and contact us if you have a submission.
Search the SAP BusinessObjects forums on the
SAP Community Network to learn from other SAP
BusinessObjects Data Services users and start
posting questions or share your knowledge with the
community.
Blueprints for you to download and modify to fit your
needs. Each blueprint contains the necessary SAP
BusinessObjects Data Services project, jobs, data
flows, file formats, sample data, template tables,
and custom functions to run the data flows in your
environment with only a few modifications.
http://help.sap.com/businessobjects/
Supported Platforms (Product Availability Matrix)
https://service.sap.com/PAM
1.2 Overview of this guide
SAP BusinessObjects product documentation.Product documentation
Get information about supported platforms for SAP
BusinessObjects Data Services.
Use the search function to search for Data Services.
Click the link for the version of Data Services you
are searching for.
2010-12-0224
Introduction
Welcome to the
development environment in which you define data application logic to extract, transform, and load data
from databases and applications into a data warehouse used for analytic and on-demand queries. You
can also use the Designer to define logical paths for processing message-based queries and transactions
from Web-based, front-office, and back-office applications.
Designer Guide
1.2.1 About this guide
The guide contains two kinds of information:
• Conceptual information that helps you understand the Data Services Designer and how it works
• Procedural information that explains in a step-by-step manner how to accomplish a task
You will find this guide most useful:
• While you are learning about the product
• While you are performing tasks in the design and early testing phase of your data-movement projects
• As a general source of information during any phase of your projects
. The Data Services Designer provides a graphical user interface (GUI)
1.2.2 Who should read this guide
This and other Data Services product documentation assumes the following:
• You are an application developer, consultant, or database administrator working on data extraction,
data warehousing, data integration, or data quality.
• You understand your source data systems, RDBMS, business intelligence, and messaging concepts.
• You understand your organization's data needs.
• You are familiar with SQL (Structured Query Language).
• If you are interested in using this product to design real-time processing, you should be familiar with:
• DTD and XML Schema formats for XML files
• Publishing Web Services (WSDL, HTTP, and SOAP protocols, etc.)
• You are familiar Data Services installation environments—Microsoft Windows or UNIX.
2010-12-0225
Introduction
2010-12-0226
Logging into the Designer
Logging into the Designer
You must have access to a local repository to log into the software. Typically, you create a repository
during installation. However, you can create a repository at any time using the Repository Manager,
and configure access rights within the Central Management Server.
Additionally, each repository must be associated with at least one Job Server before you can run
repository jobs from within the Designer. Typically, you define a Job Server and associate it with a
repository during installation. However, you can define or edit Job Servers or the links between
repositories and Job Servers at any time using the Server Manager.
When you log in to the Designer, you must log in as a user defined in the Central Management Server
(CMS).
1.
Enter your user credentials for the CMS.
• System
Specify the server name and optionally the port for the CMS.
• User name
Specify the user name to use to log into CMS.
• Password
Specify the password to use to log into the CMS.
• Authentication
Specify the authentication type used by the CMS.
2.
Click Log on.
The software attempts to connect to the CMS using the specified information. When you log in
successfully, the list of local repositories that are available to you is displayed.
3.
Select the repository you want to use.
4.
If you want the software to remember connection information for future use, click Remember.
If you choose this option, your CMS connection information and repository selection are encrypted
and stored locally, and will be filled in automatically the next time you log into the Designer.
5.
Click OK to log in using the selected repository.
2.1 Version restrictions
2010-12-0227
Logging into the Designer
Your repository version must be associated with the same major release as the Designer and must be
less than or equal to the version of the Designer.
During login, the software alerts you if there is a mismatch between your Designer version and your
repository version.
After you log in, you can view the software and repository versions by selecting Help > About Data
Services.
Some features in the current release of the Designer might not be supported if you are not logged in
to the latest version of the repository.
2.2 Resetting users
Occasionally, more than one person may attempt to log in to a single repository. If this happens, the
Reset Users window appears, listing the users and the time they logged in to the repository.
From this window, you have several options. You can:
• Reset Users to clear the users in the repository and set yourself as the currently logged in user.
• Continue to log in to the system regardless of who else might be connected.
• Exit to terminate the login attempt and close the session.
Note:
Only use Reset Users or Continue if you know that you are the only user connected to the repository.
Subsequent changes could corrupt the repository.
2010-12-0228
Designer User Interface
Designer User Interface
This section provides basic information about the Designer's graphical user interface.
3.1 Objects
All "entities" you define, edit, or work with in Designer are called objects. The local object library shows
objects such as source and target metadata, system functions, projects, and jobs.
Objects are hierarchical and consist of:
• Options, which control the operation of objects. For example, in a datastore, the name of the database
to which you connect is an option for the datastore object.
• Properties, which document the object. For example, the name of the object and the date it was
created are properties. Properties describe an object, but do not affect its operation.
The software has two types of objects: Reusable and single-use. The object type affects how you define
and retrieve the object.
3.1.1 Reusable objects
You can reuse and replicate most objects defined in the software.
After you define and save a reusable object, the software stores the definition in the local repository.
You can then reuse the definition as often as necessary by creating calls to the definition. Access
reusable objects through the local object library.
A reusable object has a single definition; all calls to the object refer to that definition. If you change the
definition of the object in one place, you are changing the object in all other places in which it appears.
A data flow, for example, is a reusable object. Multiple jobs, like a weekly load job and a daily load job,
can call the same data flow. If the data flow changes, both jobs use the new version of the data flow.
The object library contains object definitions. When you drag and drop an object from the object library,
you are really creating a new reference (or call) to the existing object definition.
2010-12-0229
Designer User Interface
3.1.2 Single-use objects
Some objects are defined only within the context of a single job or data flow, for example scripts and
specific transform definitions.
3.1.3 Object hierarchy
Object relationships are hierarchical. The following figure shows the relationships between major object
types:
2010-12-0230
Loading...