Page 1
Data Services Designer Guide
BusinessObjects Data Services XI 3.1 (12.1.1)
Page 2
Copyright
© 2008 Business Objects, an SAP company. All rights reserved. Business Objects
owns the following U.S. patents, which may cover products that are offered and
licensed by Business Objects: 5,295,243; 5,339,390; 5,555,403; 5,590,250;
5,619,632; 5,632,009; 5,857,205; 5,880,742; 5,883,635; 6,085,202; 6,108,698;
6,247,008; 6,289,352; 6,300,957; 6,377,259; 6,490,593; 6,578,027; 6,581,068;
6,628,312; 6,654,761; 6,768,986; 6,772,409; 6,831,668; 6,882,998; 6,892,189;
6,901,555; 7,089,238; 7,107,266; 7,139,766; 7,178,099; 7,181,435; 7,181,440;
7,194,465; 7,222,130; 7,299,419; 7,320,122 and 7,356,779. Business Objects and
its logos, BusinessObjects, Business Objects Crystal Vision, Business Process
On Demand, BusinessQuery, Cartesis, Crystal Analysis, Crystal Applications,
Crystal Decisions, Crystal Enterprise, Crystal Insider, Crystal Reports, Crystal
Vision, Desktop Intelligence, Inxight and its logos , LinguistX, Star Tree, Table
Lens, ThingFinder, Timewall, Let There Be Light, Metify, NSite, Rapid Marts,
RapidMarts, the Spectrum Design, Web Intelligence, Workmail and Xcelsius are
trademarks or registered trademarks in the United States and/or other countries
of Business Objects and/or affiliated companies. SAP is the trademark or registered
trademark of SAP AG in Germany and in several other countries. All other names
mentioned herein may be trademarks of their respective owners.
Third-party
Contributors
Business Objects products in this release may contain redistributions of software
licensed from third-party contributors. Some of these individual components may
also be available under alternative licenses. A partial listing of third-party
contributors that have requested or permitted acknowledgments, as well as required
notices, can be found at: http://www.businessobjects.com/thirdparty
2008-11-28
Page 3
Contents
Introduction 21Chapter 1
Welcome to Data Services........................................................................22
Overview of this guide...............................................................................28
Logging into the Designer 31Chapter 2
Creating a Data Services repository..........................................................32
Associating the repository with a Job Server.............................................32
Entering repository login information.........................................................33
Resetting users..........................................................................................36
Welcome..............................................................................................22
Documentation set for Data Services...................................................22
Accessing documentation....................................................................25
Business Objects information resources..............................................26
About this guide....................................................................................29
Who should read this guide..................................................................29
Version restrictions...............................................................................33
Oracle login .........................................................................................34
Microsoft SQL Server login..................................................................34
IBM DB2 login......................................................................................35
Sybase ASE login.................................................................................35
Designer User Interface 37Chapter 3
Objects.......................................................................................................38
Reusable objects..................................................................................39
Single-use objects................................................................................39
Object hierarchy...................................................................................39
Data Services Designer Guide 3
Page 4
Contents
Designer window.......................................................................................41
Menu bar....................................................................................................42
Project menu........................................................................................42
Edit menu.............................................................................................43
View menu............................................................................................43
Tools menu...........................................................................................44
Debug menu.........................................................................................45
Validation menu....................................................................................46
Dictionary menu...................................................................................46
Window menu.......................................................................................47
Help menu............................................................................................47
Toolbar.......................................................................................................48
Project area ..............................................................................................51
Tool palette................................................................................................52
Workspace.................................................................................................55
Moving objects in the workspace area.................................................56
Connecting objects...............................................................................56
Disconnecting objects..........................................................................56
Describing objects ...............................................................................56
Scaling the workspace.........................................................................57
Arranging workspace windows.............................................................57
Closing workspace windows................................................................57
Local object library.....................................................................................58
To open the object library.....................................................................59
To display the name of each tab as well as its icon.............................60
To sort columns in the object library.....................................................60
Object editors.............................................................................................61
Working with objects..................................................................................62
Creating new reusable objects.............................................................62
Changing object names........................................................................64
Viewing and changing object properties...............................................64
4 Data Services Designer Guide
Page 5
Contents
Creating descriptions............................................................................67
Creating annotations ...........................................................................69
Saving and deleting objects.................................................................70
Searching for objects............................................................................72
General and environment options.............................................................75
Designer — Environment.....................................................................75
Designer — General.............................................................................76
Designer — Graphics...........................................................................78
Designer — Central Repository Connections......................................78
Data — General...................................................................................78
Job Server — Environment..................................................................79
Job Server — General..........................................................................79
Projects and Jobs 81Chapter 4
Projects......................................................................................................82
Objects that make up a project.............................................................82
Creating a new project.........................................................................83
Opening existing projects.....................................................................83
Saving projects.....................................................................................84
Jobs...........................................................................................................84
Creating jobs........................................................................................85
Naming conventions for objects in jobs................................................86
Datastores 89Chapter 5
What are datastores?................................................................................90
Database datastores..................................................................................91
Mainframe interface..............................................................................92
Defining a database datastore.............................................................96
Changing a datastore definition...........................................................99
Browsing metadata through a database datastore............................100
Data Services Designer Guide 5
Page 6
Contents
Importing metadata through a database datastore............................104
Memory datastores.............................................................................111
Persistent cache datastores...............................................................116
Linked datastores...............................................................................120
Adapter datastores..................................................................................122
Defining an adapter datastore............................................................123
Browsing metadata through an adapter datastore.............................125
Importing metadata through an adapter datastore.............................126
Web service datastores...........................................................................126
Defining a web service datastore.......................................................127
Browsing WSDL metadata through a web service datastore.............128
Importing metadata through a web service datastore........................129
Creating and managing multiple datastore configurations......................130
Definitions...........................................................................................131
Why use multiple datastore configurations?......................................132
Creating a new configuration..............................................................133
Adding a datastore alias.....................................................................135
Functions to identify the configuration................................................135
Portability solutions............................................................................137
Job portability tips...............................................................................142
Renaming table and function owner...................................................144
Defining a system configuration.........................................................149
File Formats 153Chapter 6
What are file formats?..............................................................................154
File format editor......................................................................................154
Creating file formats.................................................................................157
To specify a source or target file.........................................................157
To create a new file format.................................................................157
Modeling a file format on a sample file...............................................159
Replicating and renaming file formats................................................161
6 Data Services Designer Guide
Page 7
Contents
To create a file format from an existing flat table schema..................162
To create a specific source or target file.............................................162
Editing file formats...................................................................................163
To edit a file format template..............................................................163
To edit a source or target file..............................................................163
File format features..................................................................................164
Reading multiple files at one time......................................................164
Identifying source file names .............................................................165
Number formats..................................................................................165
Ignoring rows with specified markers.................................................167
Date formats at the field level.............................................................168
Parallel process threads.....................................................................169
Error handling for flat-file sources......................................................170
Creating COBOL copybook file formats..................................................173
To create a new COBOL copybook file format...................................174
To create a new COBOL copybook file format and a data file...........175
To create rules to identify which records represent which schemas...176
To identify the field that contains the length of the schema's record...176
Creating Microsoft Excel workbook file formats on UNIX platforms .......177
To create a Microsoft Excel workbook file format on UNIX ...............177
File transfers............................................................................................179
Custom transfer system variables for flat files...................................179
Custom transfer options for flat files...................................................181
Setting custom transfer options..........................................................182
Design tips..........................................................................................184
Web log support.......................................................................................185
Word_ext function..............................................................................186
Concat_date_time function.................................................................187
WL_GetKeyValue function.................................................................187
Data Services Designer Guide 7
Page 8
Contents
Data Flows 191Chapter 7
What is a data flow?................................................................................192
Naming data flows..............................................................................192
Data flow example..............................................................................192
Steps in a data flow............................................................................193
Data flows as steps in work flows.......................................................193
Intermediate data sets in a data flow.................................................194
Operation codes.................................................................................194
Passing parameters to data flows......................................................196
Creating and defining data flows.............................................................196
To define a new data flow using the object library..............................197
To define a new data flow using the tool palette................................197
To change properties of a data flow...................................................197
Source and target objects........................................................................199
Source objects....................................................................................199
Target objects.....................................................................................200
Adding source or target objects to data flows....................................201
Template tables..................................................................................203
Converting template tables to regular tables......................................205
Transforms...............................................................................................206
Transform configurations....................................................................209
To add transforms to data flows.........................................................212
Transform editors...............................................................................213
Query transform overview .......................................................................214
Query editor........................................................................................214
To change the current schema...........................................................215
To modify output schema contents.....................................................215
To add a Query transform to a data flow............................................217
Data Quality transforms overview ...........................................................217
Data Quality editors............................................................................218
8 Data Services Designer Guide
Page 9
Contents
Associate, Match, and User-Defined transform editors......................220
Ordered options editor........................................................................221
To add a Data Quality transform to a data flow..................................221
Lookup tables and the lookup_ext function.............................................223
Accessing the lookup_ext editor........................................................225
Example: Defining a simple lookup_ext function................................226
Example: Defining a complex lookup_ext function ............................229
Data flow execution.................................................................................232
Push down operations to the database server...................................232
Distributed data flow execution..........................................................233
Load balancing...................................................................................234
Caches...............................................................................................235
Audit Data Flow overview........................................................................236
Work Flows 237Chapter 8
What is a work flow?................................................................................238
Steps in a work flow.................................................................................238
Order of execution in work flows.............................................................239
Example of a work flow............................................................................240
Creating work flows.................................................................................241
To create a new work flow using the object library.............................241
To create a new work flow using the tool palette ...............................241
To specify that a job executes the work flow one time.......................242
Conditionals.............................................................................................242
To define a conditional........................................................................244
While loops..............................................................................................245
Design considerations........................................................................245
Defining a while loop..........................................................................246
Using a while loop with View Data.....................................................247
Try/catch blocks.......................................................................................248
Defining a try/catch block...................................................................249
Data Services Designer Guide 9
Page 10
Contents
Categories of available exceptions....................................................251
Example: Catching details of an error................................................251
Scripts......................................................................................................252
To create a script................................................................................253
Debugging scripts using the print function.........................................254
Nested Data 255Chapter 9
What is nested data?...............................................................................256
Representing hierarchical data................................................................257
Formatting XML documents.....................................................................259
Importing XML Schemas....................................................................260
Specifying source options for XML files ............................................265
Mapping optional schemas.................................................................269
Using Document Type Definitions (DTDs) ........................................270
Generating DTDs and XML Schemas from an NRDM schema.........273
Operations on nested data......................................................................274
Overview of nested data and the Query transform............................274
FROM clause construction.................................................................276
Nesting columns ................................................................................280
Using correlated columns in nested data...........................................282
Distinct rows and nested data............................................................283
Grouping values across nested schemas..........................................283
Unnesting nested data ......................................................................284
Transforming lower levels of nested data...........................................287
XML extraction and parsing for columns.................................................288
Sample Scenarios..............................................................................289
Real-time Jobs 295Chapter 10
Request-response message processing.................................................296
What is a real-time job?...........................................................................297
10 Data Services Designer Guide
Page 11
Contents
Real-time versus batch.......................................................................297
Messages...........................................................................................298
Real-time job examples......................................................................300
Creating real-time jobs.............................................................................302
Real-time job models..........................................................................302
Using real-time job models.................................................................303
To create a real-time job.....................................................................305
Real-time source and target objects........................................................308
To view an XML message source or target schema..........................310
Secondary sources and targets.........................................................310
Transactional loading of tables...........................................................311
Design tips for data flows in real-time jobs.........................................313
Testing real-time jobs...............................................................................314
Executing a real-time job in test mode...............................................314
Using View Data.................................................................................314
Using an XML file target.....................................................................314
Building blocks for real-time jobs.............................................................316
Supplementing message data............................................................316
Branching data flow based on a data cache value.............................319
Calling application functions...............................................................325
Designing real-time applications..............................................................325
Reducing queries requiring back-office application access...............326
Messages from real-time jobs to adapter instances...........................327
Real-time service invoked by an adapter instance.............................327
Embedded Data Flows 329Chapter 11
Overview of embedded data flows..........................................................330
Example of when to use embedded data flows.......................................331
Creating embedded data flows................................................................332
Using the Make Embedded Data Flow option....................................332
Creating embedded data flows from existing flows............................335
Data Services Designer Guide 11
Page 12
Contents
Using embedded data flows...............................................................336
Separately testing an embedded data flow........................................338
Troubleshooting embedded data flows..............................................339
Variables and Parameters 341Chapter 12
Overview of variables and parameters....................................................342
The Variables and Parameters window...................................................344
To view the variables and parameters in each job, work flow, or data
flow.....................................................................................................344
Using local variables and parameters.....................................................347
Parameters.........................................................................................348
Passing values into data flows...........................................................348
Defining local variables......................................................................349
Defining parameters...........................................................................350
Using global variables .............................................................................351
Creating global variables....................................................................352
Viewing global variables ....................................................................352
Setting global variable values.............................................................353
Local and global variable rules................................................................358
Naming...............................................................................................359
Replicating jobs and work flows.........................................................359
Importing and exporting......................................................................359
Environment variables.............................................................................359
Setting file names at run-time using variables.........................................360
To use a variable in a flat file name....................................................360
Substitution parameters...........................................................................362
Overview of substitution parameters..................................................362
Using the Substitution Parameter Editor............................................364
Associating a substitution parameter with a system configuration.....367
Overriding a substitution parameter in the Administrator...................368
Executing a job with substitution parameters ....................................369
12 Data Services Designer Guide
Page 13
Contents
Exporting and importing substitution parameters...............................370
Executing Jobs 371Chapter 13
Overview of Data Services job execution................................................372
Preparing for job execution......................................................................373
Validating jobs and job components...................................................373
Ensuring that the Job Server is running.............................................374
Setting job execution options.............................................................375
Executing jobs as immediate tasks.........................................................376
To execute a job as an immediate task..............................................376
Monitor tab ........................................................................................377
Log tab ..............................................................................................377
Debugging execution errors....................................................................378
Using Data Services logs...................................................................378
Examining target data........................................................................382
Changing Job Server options..................................................................383
To change option values for an individual Job Server........................387
To use mapped drive names in a path...............................................390
Data Assessment 391Chapter 14
Using the Data Profiler.............................................................................393
Data sources that you can profile.......................................................394
Connecting to the profiler server........................................................394
Profiler statistics.................................................................................396
Executing a profiler task.....................................................................400
Monitoring profiler tasks using the Designer......................................407
Viewing the profiler results.................................................................409
Using View Data to determine data quality..............................................417
Data tab..............................................................................................417
Profile tab...........................................................................................418
Data Services Designer Guide 13
Page 14
Contents
Relationship Profile or Column Profile tab.........................................419
Using the Validation transform.................................................................419
Analyze column profile.......................................................................419
Defining validation rule based on column profile................................421
Using Auditing .........................................................................................422
Auditing objects in a data flow............................................................423
Accessing the Audit window...............................................................428
Defining audit points, rules, and action on failure..............................429
Guidelines to choose audit points .....................................................433
Auditing embedded data flows...........................................................434
Resolving invalid audit labels.............................................................437
Viewing audit results .........................................................................438
Data Quality 441Chapter 15
Overview of data quality..........................................................................442
Address Cleanse.....................................................................................442
How address cleanse works...............................................................443
Prepare your input data......................................................................446
Determine which transform(s) to use.................................................448
Identify the country of destination......................................................451
Set up the reference files...................................................................452
Define the standardization options.....................................................454
Beyond the basics..............................................................................455
Process Japanese addressees .........................................................506
Supported countries (Global Address Cleanse).................................518
Address Server...................................................................................520
New Zealand Certification..................................................................525
Data Cleanse...........................................................................................529
What is Data Cleanse?.......................................................................530
Parse data..........................................................................................530
Prepare records for matching.............................................................533
14 Data Services Designer Guide
Page 15
Contents
Data parsing overview........................................................................534
Parsing dictionaries............................................................................541
Dictionary entries................................................................................544
Classifications....................................................................................554
International data................................................................................559
Universal Data Cleanse......................................................................561
Rank and prioritize parsing engines...................................................579
Geocoding...............................................................................................580
Prepare records for geocoding...........................................................580
Understanding your output.................................................................581
Match.......................................................................................................581
Matching strategies............................................................................581
Match components.............................................................................582
Match Wizard.....................................................................................585
Transforms for match data flows........................................................592
Working in the Match and Associate editors......................................594
Match preparation..............................................................................595
Match criteria......................................................................................614
Post-match processing.......................................................................631
Association matching.........................................................................650
Unicode matching...............................................................................651
Phonetic matching..............................................................................653
Set up for match reports ....................................................................656
Design and Debug 659Chapter 16
Using View Where Used..........................................................................660
Accessing View Where Used from the object library.........................661
Accessing View Where Used from the workspace.............................664
Limitations..........................................................................................665
Using View Data......................................................................................666
Accessing View Data..........................................................................667
Data Services Designer Guide 15
Page 16
Contents
Viewing data in the workspace...........................................................668
View Data Properties..........................................................................670
View Data tool bar options.................................................................675
View Data tabs...................................................................................676
Using the interactive debugger................................................................682
Before starting the interactive debugger............................................682
Starting and stopping the interactive debugger..................................686
Panes.................................................................................................688
Debug menu options and tool bar......................................................697
Viewing data passed by transforms...................................................700
Push-down optimizer..........................................................................701
Limitations..........................................................................................702
Comparing Objects..................................................................................702
To compare two different objects........................................................703
To compare two versions of the same object.....................................703
Overview of the Difference Viewer window........................................704
Navigating through differences...........................................................708
Calculating column mappings..................................................................709
To automatically calculate column mappings ....................................710
To manually calculate column mappings ...........................................710
Exchanging Metadata 711Chapter 17
Metadata exchange.................................................................................712
Importing metadata files into Data Services.......................................713
Exporting metadata files from Data Services.....................................713
Creating Business Objects universes......................................................714
To create universes using the Tools menu ........................................715
To create universes using the object library.......................................715
Mappings between repository and universe metadata.......................716
Attributes that support metadata exchange.............................................718
16 Data Services Designer Guide
Page 17
Contents
Recovery Mechanisms 719Chapter 18
Recovering from unsuccessful job execution..........................................720
Automatically recovering jobs..................................................................721
Enabling automated recovery............................................................721
Marking recovery units.......................................................................722
Running in recovery mode.................................................................723
Ensuring proper execution path.........................................................724
Using try/catch blocks with automatic recovery.................................725
Ensuring that data is not duplicated in targets...................................727
Using preload SQL to allow re-executable data flows .......................728
Manually recovering jobs using status tables..........................................730
Processing data with problems................................................................731
Using overflow files............................................................................732
Filtering missing or bad values ..........................................................733
Handling facts with missing dimensions.............................................734
Techniques for Capturing Changed Data 735Chapter 19
Understanding changed-data capture.....................................................736
Full refresh.........................................................................................736
Capturing only changes......................................................................736
Source-based and target-based CDC................................................737
Using CDC with Oracle sources..............................................................738
Overview of CDC for Oracle databases.............................................739
Setting up Oracle CDC.......................................................................743
To create a CDC datastore for Oracle................................................744
Importing CDC data from Oracle........................................................745
Viewing an imported CDC table.........................................................749
To configure an Oracle CDC source table..........................................750
To create a data flow with an Oracle CDC source.............................754
Data Services Designer Guide 17
Page 18
Contents
Maintaining CDC tables and subscriptions........................................755
Limitations..........................................................................................756
Using CDC with Attunity mainframe sources...........................................757
Setting up Attunity CDC.....................................................................758
Setting up Data Services for CDC on mainframe sources.................759
Importing mainframe CDC data..........................................................761
Configuring a mainframe CDC source...............................................763
Using mainframe check-points...........................................................765
Limitations..........................................................................................766
Using CDC with Microsoft SQL Server databases .................................767
Overview of CDC for SQL Server databases.....................................767
Setting up Microsoft SQL Server for CDC..........................................769
Setting up Data Services for CDC on SQL Server.............................771
Importing SQL Server CDC data........................................................773
Configuring a SQL Server CDC source.............................................775
Limitations..........................................................................................778
Using CDC with timestamp-based sources.............................................779
Processing timestamps......................................................................780
Overlaps.............................................................................................784
Types of timestamps..........................................................................791
Timestamp-based CDC examples.....................................................793
Additional job design tips....................................................................800
Using CDC for targets..............................................................................802
Monitoring Jobs 803Chapter 20
Administrator............................................................................................804
SNMP support.........................................................................................804
About the Data Services SNMP agent...............................................804
Job Server, SNMP agent, and NMS application architecture.............805
About SNMP Agent's Management Information Base (MIB)..............806
About an NMS application..................................................................810
18 Data Services Designer Guide
Page 19
Contents
Configuring Data Services to support an NMS application................811
Troubleshooting..................................................................................822
Index 825
Data Services Designer Guide 19
Page 20
Contents
20 Data Services Designer Guide
Page 21
Introduction
1
Page 22
Introduction
1
Welcome to Data Services
Welcome to Data Services
Welcome
Data Services XI Release 3 provides data integration and data quality
processes in one runtime environment, delivering enterprise performance
and scalability.
The data integration processes of Data Services allow organizations to easily
explore, extract, transform, and deliver any type of data anywhere across
the enterprise.
The data quality processes of Data Services allow organizations to easily
standardize, cleanse, and consolidate data anywhere, ensuring that end-users
are always working with information that's readily available, accurate, and
trusted.
Documentation set for Data Services
You should become familiar with all the pieces of documentation that relate
to your Data Services product.
What this document providesDocument
Documentation Map
Release Summary
Release Notes
Getting Started Guide
Installation Guide for Windows
22 Data Services Designer Guide
Information about available Data Services books,
languages, and locations
Highlights of key features in this Data Services release
Important information you need before installing and
deploying this version of Data Services
An introduction to Data Services
Information about and procedures for installing Data
Services in a Windows environment.
Page 23
Introduction
Welcome to Data Services
What this document providesDocument
1
Installation Guide for UNIX
Advanced Development Guide
Designer Guide
Integrator's Guide
Management Console: Administrator
Guide
Management Console: Metadata Reports Guide
Migration Considerations Guide
Information about and procedures for installing Data
Services in a UNIX environment.
Guidelines and options for migrating applications including information on multi-user functionality and
the use of the central repository for version control
Information about how to use Data Services Designer
Information for third-party developers to access Data
Services functionality. Also provides information about
how to install, configure, and use the Data Services
Adapter for JMS.
Information about how to use Data Services Administrator
Information about how to use Data Services Metadata
Reports
Information about:
• Release-specific product behavior changes from
earlier versions of Data Services to the latest release
• How to migrate from Data Quality to Data Services
Performance Optimization Guide
Reference Guide
Information about how to improve the performance
of Data Services
Detailed reference material for Data Services Designer
Data Services Designer Guide 23
Page 24
Introduction
1
Welcome to Data Services
Technical Manuals
What this document providesDocument
A compiled “master” PDF of core Data Services books
containing a searchable master table of contents and
index:
•
Getting Started Guide
•
Installation Guide for Windows
•
Installation Guide for UNIX
•
Designer Guide
•
Reference Guide
•
Management Console: Metadata Reports Guide
•
Management Console: Administrator Guide
•
Performance Optimization Guide
•
Advanced Development Guide
•
Supplement for J.D. Edwards
•
Supplement for Oracle Applications
•
Supplement for PeopleSoft
•
Supplement for Siebel
•
Supplement for SAP
Tutorial
In addition, you may need to refer to several Adapter Guides and
Supplemental Guides.
What this document providesDocument
Salesforce.com Adapter
Interface
Supplement for J.D. Edwards
Supplement for Oracle Applications
Supplement for PeopleSoft
24 Data Services Designer Guide
Information about how to install, configure, and use the Data
Services Salesforce.com Adapter Interface
Information about license-controlled interfaces between Data
Services and J.D. Edwards World and J.D. Edwards OneWorld
Information about the license-controlled interface between Data
Services and Oracle Applications
Information about license-controlled interfaces between Data
Services and PeopleSoft
A step-by-step introduction to using Data Services
Page 25
Introduction
Welcome to Data Services
What this document providesDocument
1
Supplement for SAP
Supplement for Siebel
Information about license-controlled interfaces between Data
Services, SAP ERP, and SAP BI/BW
Information about the license-controlled interface between Data
Services and Siebel
Accessing documentation
You can access the complete documentation set for Data Services in several
places.
Accessing documentation on Windows
After you install Data Services, you can access the documentation from the
Start menu.
1. Choose Start > Programs > BusinessObjects XI 3.1 >
BusinessObjects Data Services > Data Services Documentation.
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.
Accessing documentation on UNIX
After you install 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.
Data Services Designer Guide 25
Page 26
Introduction
1
Welcome to Data Services
Accessing documentation from the Web
You can access the complete documentation set for Data Services from the
Business Objects Customer Support site.
1.
Go to http://help.sap.com.
2. Cick Business Objects at the top of the page.
You can view the PDFs online or save them to your computer.
Business Objects information resources
A global network of Business Objects technology experts provides customer
support, education, and consulting to ensure maximum business intelligence
benefit to your business.
Useful addresses at a glance:
ContentAddress
26 Data Services Designer Guide
Page 27
Introduction
Welcome to Data Services
ContentAddress
1
Customer Support, Consulting, and Education
services
http://service.sap.com/
Data Services Community
https://www.sdn.sap.com/irj/sdn/businessob
jects-ds
Forums on SCN (SAP Community Network)
https://www.sdn.sap.com/irj/sdn/businessob
jects-forums
Blueprints
http://www.sdn.sap.com/irj/boc/blueprints
Information about Customer Support programs,
as well as links to technical articles, downloads,
and online forums. Consulting services can
provide you with information about how Business Objects can help maximize your business
intelligence investment. Education services can
provide information about training options and
modules. From traditional classroom learning
to targeted e-learning seminars, Business Objects can offer a training package to suit your
learning needs and preferred learning style.
Get online and timely information about 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 Business Objects forums on the
SAP Community Network to learn from other
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 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.
Data Services Designer Guide 27
Page 28
Introduction
1
Overview of this guide
http://help.sap.com/
ContentAddress
Business Objects product documentation.Product documentation
Documentation mailbox
documentation@businessobjects.com
Supported platforms documentation
https://service.sap.com/bosap-support
Send us feedback or questions about your
Business Objects documentation. Do you have
a suggestion on how we can improve our documentation? Is there something that you particularly like or have found useful? Let us know,
and we will do our best to ensure that your
suggestion is considered for the next release
of our documentation.
Note:
If your issue concerns a Business Objects
product and not the documentation, please
contact our Customer Support experts.
Get information about supported platforms for
Data Services.
In the left panel of the window, navigate to
Documentation > Supported Platforms >
BusinessObjects XI 3.1. Click the BusinessObjects Data Services link in the main window.
Overview of this guide
Welcome to the Designer Guide. The Data Services Designer provides a
graphical user interface (GUI) 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.
28 Data Services Designer Guide
Page 29
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
Who should read this guide
Introduction
Overview of this guide
1
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.
Data Services Designer Guide 29
Page 30
Introduction
Overview of this guide
1
30 Data Services Designer Guide
Page 31
Logging into the Designer
2
Page 32
Logging into the Designer
2
Creating a Data Services repository
This section describes how to log in to the Data Services Designer. When
you log in to the Data Services Designer, you are actually logging in to the
database you defined for the Data Services repository.
Data Services repositories can reside on Oracle, Microsoft SQL Server, IBM
DB2, Sybase ASE.
Related Topics
• Creating a Data Services repository on page 32
• Associating the repository with a Job Server on page 32
• Entering repository login information on page 33
• Resetting users on page 36
Creating a Data Services repository
You must configure a local repository to log in to Data Services. Typically,
you create a repository during installation. However, you can create a
repository at any time using the Data Services Repository Manager.
To create a local repository:
1. Define a database for the local repository using your database
management system.
2. From the Start menu, choose Programs > Business Objects XI 3.1 >
BusinessObjects Data Services > Data Services Repository Manager.
3. In the Repository Manager window, enter the database connection
information for the repository and select Local for repository type.
4. Click Create. This adds the Data Services repository schema to the
specified database.
Associating the repository with a Job Server
Each repository must be associated with at least one Job Server, which is
the process that starts jobs. When running a job from a repository, you select
one of the associated repositories. In production environments, you can
balance loads appropriately. You can link any number of repositories to a
single Job Server. The same Job Server can run jobs stored on multiple
repositories.
32 Data Services Designer Guide
Page 33
Logging into the Designer
Entering repository login information
Typically, you define a Job Server and link it to a repository during installation.
However, you can define or edit Job Servers or links between repositories
and Job Servers at any time using the Data Services Server Manager.
To create a Job Server for your local repository
• Open the Data Services Server Manager.
From the Start menu, choose Programs > BusinessObjects XI 3.1 >
BusinessObjections Data Services > Data Services Server Manager
(assuming you installed Data Services in the Data Services program
group).
Entering repository login information
To log in, enter the connection information for your Data Services repository.
The required information varies with the type of database containing the
repository.
Related Topics
• Version restrictions on page 33
• Oracle login on page 34
• Microsoft SQL Server login on page 34
• IBM DB2 login on page 35
• Sybase ASE login on page 35
2
Version restrictions
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, Data Services alerts you if there is a mismatch between your
Designer version and your repository version.
After you log in, you can view Data Services 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.
Data Services Designer Guide 33
Page 34
Logging into the Designer
2
Entering repository login information
Oracle login
From the Windows Start menu, select Programs > BusinessObjects XI
3.1 > BusinessObjects Data Services > Data Services Designer.
In the Repository Login window, complete the following fields:
• Database type — Select Oracle.
• Database connection name — The TNSnames.ora entry or Net Service
Name of the database.
• User name and Password —The user name and password for a Data
Services repository defined in an Oracle database.
• Remember — Check this box if you want the Designer to store this
information for the next time you log in.
Microsoft SQL Server login
From the Windows Start menu, select Programs > Business Objects XI
3.1 > BusinessObjects Data Services > Data Services Designer.
For a Microsoft SQL Server repository, you must complete the following
fields:
1. Database type — Select Microsoft_SQL_Server.
2. Database server name —The database server name.
3. Database name — The name of the specific database to which you are
connecting.
4. Windows authentication — Select to have Microsoft SQL Server validate
the login account name and password using information from the Windows
operating system; clear to authenticate using the existing Microsoft SQL
Server login account name and password and complete the User name
and Password fields.
5. User name and Password — The user name and password for a Data
Services repository defined in a Microsoft SQL Server database.
34 Data Services Designer Guide
Page 35
6. Remember — Check this box if you want the Designer to store this
information for the next time you log in.
IBM DB2 login
From the Windows Start menu, select Programs > Business Objects >
Data Services > Data Services Designer.
For a DB2 repository, you must complete the following fields:
• Database type — Select DB2.
• DB2 datasource — The data source name.
• User name and Password — The user name and password for a Data
Services repository defined in a DB2 database.
• Remember — Check this box if you want the Designer to store this
information for the next time you log in.
Sybase ASE login
Logging into the Designer
Entering repository login information
2
From the Windows Start menu, select Programs > Business Objects XI
3.1 > BusinessObjects Data Services > Data Services Designer.
For a Sybase ASE repository, you must complete the following fields:
• Database type — Select Sybase ASE.
• Database server name — Enter the database's server name.
Note:
For UNIX Job Servers, when logging in to a Sybase repository in the
Designer, the case you type for the database server name must match
the associated case in the SYBASE_Home\interfaces file. If the case
does not match, you might receive an error because the Job Server cannot
communicate with the repository.
• Database name — Enter the name of the specific database to which you
are connecting.
• User name and Password — Enter the user name and password for
this database.
Data Services Designer Guide 35
Page 36
Logging into the Designer
2
Resetting users
• Remember — Check this box if you want the Designer to store this
information for the next time you log in.
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.
36 Data Services Designer Guide
Page 37
Designer User Interface
3
Page 38
Designer User Interface
3
Objects
Objects
This section provides basic information about the Designer's graphical user
interface.
Related Topics
• Objects on page 38
• Designer window on page 41
• Menu bar on page 42
• Toolbar on page 48
• Project area on page 51
• Tool palette on page 52
• Workspace on page 55
• Local object library on page 58
• Object editors on page 61
• Working with objects on page 62
• General and environment options on page 75
All "entities" you define, edit, or work with in Data Services 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.
Data Services has two types of objects: Reusable and single-use. The object
type affects how you define and retrieve the object.
Related Topics
• Reusable objects on page 39
• Single-use objects on page 39
38 Data Services Designer Guide
Page 39
Reusable objects
You can reuse and replicate most objects defined in Data Services.
After you define and save a reusable object, Data Services 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.
Designer User Interface
Objects
3
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.
Object hierarchy
Data Services object relationships are hierarchical. The following figure
shows the relationships between major Data Services object types:
Data Services Designer Guide 39
Page 40
Designer User Interface
3
Objects
40 Data Services Designer Guide
Page 41
Designer window
The Data Services Designer user interface consists of a single application
window and several embedded supporting windows.
Designer User Interface
Designer window
3
In addition to the Menu bar on page 42 and Toolbar on page 48, the key
areas of the Data Services application window are:
1.
Project area on page 51—Contains the current project (and the job(s)
and other objects within it) available to you at a given time. In Data
Services, all entities you create, modify, or work with are objects.
2.
Workspace on page 55—The area of the application window in which
you define, display, and modify objects.
3.
Local object library on page 58—Provides access to local repository
objects including built-in system objects, such as transforms, and the
objects you build and save, such as jobs and data flows.
4.
Tool palette on page 52 — Buttons on the tool palette enable you to add
new objects to the workspace.
Data Services Designer Guide 41
Page 42
Designer User Interface
3
Menu bar
Menu bar
Project menu
This section contains a brief description of the Designer's menus:
•
Project menu on page 42
•
Edit menu on page 43
•
View menu on page 43
•
Tools menu on page 44
•
Debug menu on page 45
•
Validation menu on page 46
•
Window menu on page 47
•
Help menu on page 47
The project menu contains standard Windows as well as Data
Services-specific options.
• New — Define a new project, batch job, real-time job, work flow, data
flow, transform, datastore, file format, DTD, XML Schema, or custom
function.
• Open — Open an existing project.
• Close — Close the currently open project.
• Delete — Delete the selected object.
• Save — Save the object open in the workspace.
• Save All — Save all changes to objects in the current Designer session.
• Print — Print the active workspace.
• Print Setup — Set up default printer information.
• Compact Repository — Remove redundant and obsolete objects from
the repository tables.
• Exit — Exit Data Services Designer.
42 Data Services Designer Guide
Page 43
Edit menu
Designer User Interface
Menu bar
The Edit menu provides standard Windows commands with a few restrictions.
• Undo — Undo the last operation (text edits only).
• Cut — Cut the selected object or text and place it on the clipboard.
• Copy — Copy the selected object or text to the clipboard.
Note:
You cannot copy reusable objects using the Copy command; instead,
use Replicate in the object library to make an independent copy of an
object.
• Paste — Paste the contents of the clipboard into the active workspace
or text box.
Note:
You can only paste clipboard contents once. To paste again, you must
cut or copy the objects again.
3
• Delete — Delete the selected object.
• Clear All — Clear all objects in the active workspace (no undo).
View menu
A check mark indicates that the tool is active.
• Toolbar — Display or remove the toolbar in the Designer window.
• Status Bar — Display or remove the status bar in the Designer window.
• Palette — Display or remove the floating tool palette.
• Enabled Descriptions — View descriptions for objects with enabled
• Refresh — Redraw the display.
descriptions.
Use this command to ensure the content of the workspace represents
the most up-to-date information from the repository.
Data Services Designer Guide 43
Page 44
Designer User Interface
3
Menu bar
Tools menu
An icon with a different color background indicates that the tool is active.
• Object Library — Open or close the object library window.
• Project Area — Display or remove the project area from the Data Services
window.
• Variables — Open or close the Variables and Parameters window.
• Output — Open or close the Output window. The Output window shows
errors that occur such as during job validation or object export.
• Profilier Monitor — Display the status of Profiler tasks.
• Run match Wizard — Display the Match Wizard to create a match data
flow. Select a transform in a data flow to activate this menu item. The
transform(s) that the Match Wizard generates will be placed downstream
from the transform you selected.
• Match Editor — Display the Match Editor to edit Match transform options.
• Associate Editor — Display the Associate Editor to edit Associate
transform options.
• User-Defined Editor — Display the User-Defined Editor to edit
User-Defined transform options.
• Custom Functions — Display the Custom Functions window.
• System Configurations — Display the System Configurations editor.
• Substitution Parameter Configurations — Display the Substitution
Parameter Editor to create and edit substitution paramters and
configurations.
• Profiler Server Login — Connect to the Profiler Server.
• Export — Export individual repository objects to another repository or
file. This command opens the Export editor in the workspace. You can
drag objects from the object library into the editor for export. To export
your whole repository, in the object library right-click and select
Repository > Export to file.
• Import From File — Import objects into the current repository from a file.
The default file types are ATL, XML, DMT, and FMT. For more information
on importing objects, see the Advanced Development Guide. For more
information on DMT and FMT files, see the Data Quality to Data Services
Migration Guide.
• Metadata Exchange — Import and export metadata to third-party systems
via a file.
44 Data Services Designer Guide
Page 45
Designer User Interface
Menu bar
• BusinessObjects Universes — Export (create or update) metadata in
Business Objects Universes.
• Central Repositories — Create or edit connections to a central repository
for managing object versions among multiple users.
• Options — Display the Options window.
• Data Services Management Console — Display the Management
Console.
• Assess and Monitor — Open Data Insight to profile, examine and report
on the quality of your data. This menu item is only available if you have
purchased and installed Data Insight.
Related Topics
• Advanced Development Guide: Multi-user environment setup
• Advanced Development Guide: Export/Import, Importing from a file
• Advanced Development Guide: Export/Import, Exporting/importing objects
in Data Services
• Reference Guide: Functions and Procedures, Custom functions
• Local object library on page 58
• Project area on page 51
• Variables and Parameters on page 341
• Using the Data Profiler on page 393
• Creating and managing multiple datastore configurations on page 130
• Connecting to the profiler server on page 394
• Metadata exchange on page 712
• Creating Business Objects universes on page 714
• General and environment options on page 75
3
Debug menu
The only options available on this menu at all times are Show
Filters/Breakpoints and Filters/Breakpoints. The Execute and Start Debug
options are only active when a job is selected. All other options are available
as appropriate when a job is running in the Debug mode.
• Execute - Opens the Execution Properties window which allows you to
execute the selected job.
• Start Debug - Opens the Debug Properties window which allows you to
run a job in the debug mode.
Data Services Designer Guide 45
Page 46
Designer User Interface
3
Menu bar
• Show Filters/Breakpoints - Shows and hides filters and breakpoints in
workspace diagrams.
• Filters/Breakpoints - Opens a window you can use to manage filters
and breakpoints.
Related Topics
• Using the interactive debugger on page 682
• Filters and Breakpoints window on page 696
Validation menu
The Designer displays options on this menu as appropriate when an object
is open in the workspace.
• Validate — Validate the objects in the current workspace view or all
objects in the job before executing the application.
• Show ATL — View a read-only version of the language associated with
the job.
• Display Optimized SQL — Display the SQL that Data Services generated
for a selected data flow..
Related Topics
• Performance Optimization Guide: Maximizing Push-Down Operations,
To view SQL
Dictionary menu
The Dictionary menu contains options for interacting with the dictionaries
used by cleansing packages and the Data Cleanse transform.
• Search — Search for existing dictionary entries.
• Add New Dictionary Entry — Create a new primary dictionary entry.
• Bulk Load — Import a group of dictionary changes from an external file.
• View Bulk Load Conflict Logs — Display conflict logs generated by the
Bulk Load feature.
• Export Dictionary Changes — Export changes from a dictionary to an
XML file.
46 Data Services Designer Guide
Page 47
• Universal Data Cleanse — Dictionary-related options specific to the
Universal Data Cleanse feature.
• Add New Classification — Add a new dictionary classification.
• Edit Classification — Edit an existing dictionary classification.
• Add Custom Output — Add custom output categories and fields to
a dictionary.
• Create Dictionary — Create a new dictionary in the repository.
• Delete Dictionary — Delete a dictionary from the repository.
• Manage Connection — Update the connection information for the
dictionary repository connection.
Window menu
The Window menu provides standard Windows options.
• Back — Move back in the list of active workspace windows.
• Forward — Move forward in the list of active workspace windows.
• Cascade — Display window panels overlapping with titles showing.
Designer User Interface
Menu bar
3
• Tile Horizontally — Display window panels side by side.
• Tile Vertically — Display window panels one above the other.
• Close All Windows — Close all open windows.
• A list of objects open in the workspace also appears on the Windows
Help menu
• Contents — Display the Data Services Technical Manuals.
• Technical Manuals— Display the Data Services Technical Manuals.
• Release Notes — Display release notes for this release.
• Release Summary — Display a summary of new features for this release.
menu. The name of the currently-selected object is indicated by a check
mark. Navigate to another open object by selecting its name in the list.
You can also access the same documentation from the
<LINKDIR>\Doc\Books directory.
Data Services Designer Guide 47
Page 48
Designer User Interface
3
Toolbar
Toolbar
• About Data Services — Display information about Data Services
including versions of the Designer, Job Server and engine, and copyright
information.
In addition to many of the standard Windows tools, Data Services provides
application-specific tools, including:
DescriptionToolIcon
Close all windows
Local Object Library
Central Object Library
Variables
Project Area
Output
Closes all open windows in the workspace.
Opens and closes the
local object library window.
Opens and closes the
central object library
window.
Opens and closes the
variables and parameters creation window.
Opens and closes the
project area.
Opens and closes the
output window.
48 Data Services Designer Guide
Page 49
View Enabled Descriptions
Validate Current View
Validate All Objects in
View
Designer User Interface
Toolbar
DescriptionToolIcon
Enables the system level setting for viewing
object descriptions in
the workspace.
Validates the object definition open in the
workspace. Other objects included in the
definition are also validated.
Validates the object definition open in the
workspace. Objects included in the definition
are also validated.
3
Audit Objects in Data
Flow
Data Services Designer Guide 49
Opens the Audit window
to define audit labels
and rules for the data
flow.
Page 50
Designer User Interface
3
Toolbar
View Where Used
DescriptionToolIcon
Opens the Output window, which lists parent
objects (such as jobs) of
the object currently
open in the workspace
(such as a data flow).
Use this command to
find other jobs that use
the same data flow, before you decide to make
design changes. To see
if an object in a data
flow is reused elsewhere, right-click one
and select View Where
Used.
50 Data Services Designer Guide
Go Back
Go Forward
Data Services Management Console
Assess and Monitor
Move back in the list of
active workspace windows.
Move forward in the list
of active workspace
windows.
Opens and closes the
Management Console
window.
Opens the Data Insight
application where you
can profile, examine and
report on the quality of
your data.
Page 51
Related Topics
• Debug menu options and tool bar on page 697
Project area
The project area provides a hierarchical view of the objects used in each
project. Tabs on the bottom of the project area support different tasks. Tabs
include:
Contents
Designer User Interface
Project area
DescriptionToolIcon
Opens the Data Services Technical Manuals
PDF for information
about using Data Services.
3
Create, view and manage projects.
Provides a hierarchical view of all
objects used in each project.
View the status of currently executing
jobs. Selecting a specific job execution displays its status, including
which steps are complete and which
steps are executing. These tasks can
also be done using the Data Services
Administrator.
View the history of complete jobs.
Logs can also be viewed with the
Data Services Administrator.
Data Services Designer Guide 51
Page 52
Designer User Interface
3
Tool palette
To control project area location, right-click its gray border and select/deselect
Allow Docking, or select Hide from the menu.
• When you select Allow Docking, you can click and drag the project area
to dock at and undock from any edge within the Designer window. When
you drag the project area away from a Designer window edge, it stays
undocked. To quickly switch between your last docked and undocked
locations, just double-click the gray border.
When you deselect Allow Docking, you can click and drag the project
area to any location on your screen and it will not dock inside the Designer
window.
• When you select Hide, the project area disappears from the Designer
window. To unhide the project area, click its toolbar icon.
Here's an example of the Project window's Designer tab, which shows the
project hierarchy:
As you drill down into objects in the Designer workspace, the window
highlights your location within the project hierarchy.
Tool palette
The tool palette is a separate window that appears by default on the right
edge of the Designer workspace. You can move the tool palette anywhere
on your screen or dock it on any edge of the Designer window.
52 Data Services Designer Guide
Page 53
Designer User Interface
Tool palette
The icons in the tool palette allow you to create new objects in the workspace.
The icons are disabled when they are not allowed to be added to the diagram
open in the workspace.
To show the name of each icon, hold the cursor over the icon until the tool
tip for the icon appears, as shown.
When you create an object from the tool palette, you are creating a new
definition of an object. If a new object is reusable, it will be automatically
available in the object library after you create it.
For example, if you select the data flow icon from the tool palette and define
a new data flow, later you can drag that existing data flow from the object
library, adding a call to the existing definition.
The tool palette contains the following icons:
3
ToolIcon
Pointer
Work flow
Data flow
R/3 data flow
Description
(class)
Returns the tool
pointer to a selection pointer for
selecting and
moving objects in
a diagram.
Creates a new
work flow.
(reusable)
Creates a new
data flow.
(reusable)
Used only with
the SAP licensed
extension.
Available
Everywhere
Jobs and work
flows
Jobs and work
flows
Data Services Designer Guide 53
Page 54
Designer User Interface
3
Tool palette
ToolIcon
Query transform
Template table
Template XML
Data transport
Description
(class)
Creates a template for a query.
Use it to define
column mappings
and row selections. (single-use)
Creates a table
for a target. (single-use)
Creates an XML
template. (singleuse)
Used only with
the SAP Licensed
extension.
Available
Data flows
Data flows
Data flows
54 Data Services Designer Guide
Script
Conditional
Try
Creates a new
script object. (single-use)
Creates a new
conditional object. (single-use)
Creates a new try
object. (singleuse)
Jobs and work
flows
Jobs and work
flows
Jobs and work
flows
Page 55
Designer User Interface
Workspace
3
Workspace
When you open or select a job or any flow within a job hierarchy, the
workspace becomes "active" with your selection. The workspace provides
a place to manipulate system objects and graphically assemble data
movement processes.
These processes are represented by icons that you drag and drop into a
workspace to create a workspace diagram. This diagram is a visual
representation of an entire data movement application or some part of a data
movement application.
ToolIcon
Catch
Annotation
Description
(class)
Creates a new
catch object. (single-use)
Creates an annotation. (singleuse)
Available
Jobs and work
flows
Jobs, work flows,
and data flows
Related Topics
• Moving objects in the workspace area on page 56
• Connecting objects on page 56
• Describing objects on page 56
• Scaling the workspace on page 57
• Arranging workspace windows on page 57
• Closing workspace windows on page 57
Data Services Designer Guide 55
Page 56
Designer User Interface
3
Workspace
Moving objects in the workspace area
Use standard mouse commands to move objects in the workspace.
To move an object to a different place in the workspace area:
1. Click to select the object.
2. Drag the object to where you want to place it in the workspace.
Connecting objects
You specify the flow of data through jobs and work flows by connecting
objects in the workspace from left to right in the order you want the data to
be moved.
To connect objects:
1. Place the objects you want to connect in the workspace.
2. Click and drag from the triangle on the right edge of an object to the
triangle on the left edge of the next object in the flow.
Disconnecting objects
To disconnect objects
1. Click the connecting line.
2. Press the Delete key.
Describing objects
You can use descriptions to add comments about objects. You can use
annotations to explain a job, work flow, or data flow. You can view object
descriptions and annotations in the workspace. Together, descriptions and
annotations allow you to document a Data Services application. For example,
you can describe the incremental behavior of individual jobs with numerous
annotations and label each object with a basic description.
56 Data Services Designer Guide
Page 57
This job loads current categories and expenses and produces tables for
analysis.
For more information, see Creating descriptions on page 67 and Creating
annotations on page 69.
Scaling the workspace
You can control the scale of the workspace. By scaling the workspace, you
can change the focus of a job, work flow, or data flow. For example, you
might want to increase the scale to examine a particular part of a work flow,
or you might want to reduce the scale so that you can examine the entire
work flow without scrolling.
To change the scale of the workspace
1. In the drop-down list on the tool bar, select a predefined scale or enter a
custom value (for example, 100%).
2. Alternatively, right-click in the workspace and select a desired scale.
Note:
You can also select Scale to Fit and Scale to Whole:
Designer User Interface
Workspace
3
• Select Scale to Fit and the Designer calculates the scale that fits the
entire project in the current view area.
• Select Scale to Whole to show the entire workspace area in the current
view area.
Arranging workspace windows
The Window menu allows you to arrange multiple open workspace windows
in the following ways: cascade, tile horizontally, or tile vertically.
Closing workspace windows
When you drill into an object in the project area or workspace, a view of the
object's definition opens in the workspace area. The view is marked by a tab
at the bottom of the workspace area, and as you open more objects in the
Data Services Designer Guide 57
Page 58
Designer User Interface
3
Local object library
workspace, more tabs appear. (You can show/hide these tabs from the Tools
> Options menu. Go to Designer > General options and select/deselect
Show tabs in workspace. For more information, see the General and
environment options on page 75 section.)
Note:
These views use system resources. If you have a large number of open
views, you might notice a decline in performance.
Close the views individually by clicking the close box in the top right corner
of the workspace. Close all open views by selecting Window > Close All
Windows or clicking the Close All Windows icon on the toolbar.
Local object library
The local object library provides access to reusable objects. These objects
include built-in system objects, such as transforms, and the objects you build
and save, such as datastores, jobs, data flows, and work flows.
The local object library is a window into your local Data Services repository
and eliminates the need to access the repository directly. Updates to the
repository occur through normal Data Services operation. Saving the objects
you create adds them to the repository. Access saved objects through the
local object library.
To control object library location, right-click its gray border and select/deselect
Allow Docking, or select Hide from the menu.
• When you select Allow Docking, you can click and drag the object library
to dock at and undock from any edge within the Designer window. When
you drag the object library away from a Designer window edge, it stays
undocked. To quickly switch between your last docked and undocked
locations, just double-click the gray border.
When you deselect Allow Docking, you can click and drag the object
library to any location on your screen and it will not dock inside the
Designer window.
• When you select Hide, the object library disappears from the Designer
window. To unhide the object library, click its toolbar icon.
Related Topics
• Advanced Development: Central versus local repository
58 Data Services Designer Guide
Page 59
To open the object library
• Choose Tools > Object Library, or click the object library icon in the icon
bar.
The object library gives you access to the object types listed in the following
table. The table shows the tab on which the object type appears in the object
library and describes the Data Services context in which you can use each
type of object.
Designer User Interface
Local object library
DescriptionTab
Projects are sets of jobs available at
a given time.
Jobs are executable work flows.
There are two job types: batch jobs
and real-time jobs.
3
Work flows order data flows and the
operations that support data flows,
defining the interdependencies between them.
Data flows describe how to process
a task.
Transforms operate on data, producing output data sets from the sources
you specify. The object library lists
both built-in and custom transforms.
Data Services Designer Guide 59
Page 60
Designer User Interface
3
Local object library
DescriptionTab
Datastores represent connections to
databases and applications used in
your project. Under each datastore
is a list of the tables, documents, and
functions imported into Data Services.
Formats describe the structure of a
flat file, XML file, or XML message.
Custom Functions are functions written in the Data Services Scripting
Language. You can use them in Data
Services jobs.
To display the name of each tab as well as its icon
1. Make the object library window wider until the names appear.
or
2. Hold the cursor over the tab until the tool tip for the tab appears.
To sort columns in the object library
• Click the column heading.
For example, you can sort data flows by clicking the Data Flow column
heading once. Names are listed in ascending order. To list names in
descending order, click the Data Flow column heading again.
60 Data Services Designer Guide
Page 61
Object editors
To work with the options for an object, in the workspace click the name of
the object to open its editor. The editor displays the input and output schemas
for the object and a panel below them listing options set for the object. If
there are many options, they are grouped in tabs in the editor.
A schema is a data structure that can contain columns, other nested schemas,
and functions (the contents are called schema elements). A table is a schema
containing only columns.
A common example of an editor is the editor for the query transform, as
shown in the following illustration:
Designer User Interface
Object editors
3
For specific information about the query editor, see Query editor on page 214.
In an editor, you can:
• Undo or redo previous actions performed in the window (right-click and
choose Undo or Redo)
• Find a string in the editor (right-click and choose Find)
• Drag-and-drop column names from the input schema into relevant option
boxes
Data Services Designer Guide 61
Page 62
Designer User Interface
3
Working with objects
• Use colors to identify strings and comments in text boxes where you can
edit expressions (keywords appear blue; strings are enclosed in quotes
and appear pink; comments begin with a pound sign and appear green)
Note:
You cannot add comments to a mapping clause in a Query transform.
For example, the following syntax is not supported on the Mapping tab:
table.column # comment
The job will not run and you cannot successfully export it. Use the object
description or workspace annotation feature instead.
Working with objects
This section discusses common tasks you complete when working with
objects in the Designer. With these tasks, you use various parts of the
Designer—the toolbar, tool palette, workspace, and local object library.
Tasks in this section include:
•
Creating new reusable objects on page 62
•
Changing object names on page 64
•
Viewing and changing object properties on page 64
•
Creating descriptions on page 67
•
Creating annotations on page 69
•
Saving and deleting objects on page 70
•
Searching for objects on page 72
Creating new reusable objects
You can create reusable objects from the object library or by using the tool
palette. After you create an object, you can work with the object, editing its
definition and adding calls to other objects.
62 Data Services Designer Guide
Page 63
To create a reusable object (in the object library)
1. Open the object library by choosing Tools > Object Library.
2. Click the tab corresponding to the object type.
3. Right-click anywhere except on existing objects and choose New.
4. Right-click the new object and select Properties. Enter options such as
name and description to define the object.
To create a reusable object (using the tool palette)
1. In the tool palette, left-click the icon for the object you want to create.
2. Move the cursor to the workspace and left-click again.
The object icon appears in the workspace where you have clicked.
To open an object's definition
Designer User Interface
Working with objects
3
You can open an object's definition in one of two ways:
1. From the workspace, click the object name. Data Services opens a blank
workspace in which you define the object.
2. From the project area, click the object.
You define an object using other objects. For example, if you click the name
of a batch data flow, a new workspace opens for you to assemble sources,
targets, and transforms that make up the actual flow.
To add an existing object (create a new call to an existing object)
1. Open the object library by choosing Tools > Object Library.
2. Click the tab corresponding to any object type.
3. Select an object.
4. Drag the object to the workspace.
Note:
Objects dragged into the workspace must obey the hierarchy logic explained
in Object hierarchy on page 39. For example, you can drag a data flow into
a job, but you cannot drag a work flow into a data flow.
Data Services Designer Guide 63
Page 64
Designer User Interface
3
Working with objects
Changing object names
You can change the name of an object from the workspace or the object
library. You can also create a copy of an existing object.
Note:
You cannot change the names of built-in objects.
1. To change the name of an object in the workspace
a. Click to select the object in the workspace.
b. Right-click and choose Edit Name.
c. Edit the text in the name text box.
d. Click outside the text box or press Enter to save the new name.
2. To change the name of an object in the object library
a. Select the object in the object library.
b. Right-click and choose Properties.
c. Edit the text in the first text box.
d. Click OK.
3. To copy an object
a. Select the object in the object library.
b. Right-click and choose Replicate.
c. Data Services makes a copy of the top-level object (but not of objects
that it calls) and gives it a new name, which you can edit.
Viewing and changing object properties
You can view (and, in some cases, change) an object's properties through
its property page.
To view, change, and add object properties
1. Select the object in the object library.
2. Right-click and choose Properties. The General tab of the Properties
window opens.
64 Data Services Designer Guide
Page 65
General tab
Designer User Interface
Working with objects
3. Complete the property sheets. The property sheets vary by object type,
but General, Attributes and Class Attributes are the most common and
are described in the following sections.
4. When finished, click OK to save changes you made to the object
properties and to close the window.
Alternatively, click Apply to save changes without closing the window.
The General tab contains two main object properties: name and description.
From the General tab, you can change the object name as well as enter or
edit the object description. You can add object descriptions to single-use
objects as well as to reusable objects. Note that you can toggle object
descriptions on and off by right-clicking any object in the workspace and
selecting/clearing View Enabled Descriptions.
Depending on the object, other properties may appear on the General tab.
Examples include:
• Execute only once
• Recover as a unit
• Degree of parallelism
• Use database links
• Cache type
3
Related Topics
• Performance Optimization Guide: Using Caches
• Linked datastores on page 120
• Performance Optimization Guide: Using Parallel Execution
• Recovery Mechanisms on page 719
• Creating and defining data flows on page 196
Attributes tab
The Attributes tab allows you to assign values to the attributes of the current
object.
Data Services Designer Guide 65
Page 66
Designer User Interface
3
Working with objects
To assign a value to an attribute, select the attribute and enter the value in
the Value box at the bottom of the window.
Some attribute values are set by Data Services and cannot be edited. When
you select an attribute with a system-defined value, the Value field is
unavailable.
Class Attributes tab
The Class Attributes tab shows the attributes available for the type of object
selected. For example, all data flow objects have the same class attributes.
To create a new attribute for a class of objects, right-click in the attribute list
and select Add. The new attribute is now available for all of the objects of
this class.
66 Data Services Designer Guide
Page 67
To delete an attribute, select it then right-click and choose Delete. You cannot
delete the class attributes predefined by Data Services.
Creating descriptions
Use descriptions to document objects. You can see descriptions on
workspace diagrams. Therefore, descriptions are a convenient way to add
comments to workspace objects.
Designer User Interface
Working with objects
3
A description is associated with a particular object. When you import or export
that repository object (for example, when migrating between development,
test, and production environments), you also import or export its description.
The Designer determines when to show object descriptions based on a
system-level setting and an object-level setting. Both settings must be
activated to view the description for a particular object.
The system-level setting is unique to your setup. The system-level setting
is disabled by default. To activate that system-level setting, select
ViewEnabled Descriptions, or click the View Enabled Descriptions button
on the toolbar.
The object-level setting is saved with the object in the repository. The
object-level setting is also disabled by default unless you add or edit a
description from the workspace. To activate the object-level setting, right-click
the object and select Enable object description.
An ellipses after the text in a description indicates that there is more text. To
see all the text, resize the description by clicking and dragging it. When you
move an object, its description moves as well. To see which object is
Data Services Designer Guide 67
Page 68
Designer User Interface
3
Working with objects
associated with which selected description, view the object's name in the
status bar.
To add a description to an object
1. In the project area or object library, right-click an object and select
Properties.
2. Enter your comments in the Description text box.
3. Click OK.
The description for the object displays in the object library.
To display a description in the workspace
1. In the project area, select an existing object (such as a job) that contains
an object to which you have added a description (such as a work flow).
2. From the View menu, select Enabled Descriptions.
Alternately, you can select the View Enabled Descriptions button on the
toolbar.
3. Right-click the work flow and select Enable Object Description.
The description displays in the workspace under the object.
To add a description to an object from the workspace
1. From the View menu, select Enabled Descriptions.
2. In the workspace, right-click an object and select Properties.
3. In the Properties window, enter text in the Description box.
4. Click OK.
The description displays automatically in the workspace (and the object's
Enable Object Description option is selected).
To hide a particular object's description
1. In the workspace diagram, right-click an object.
68 Data Services Designer Guide
Page 69
Alternately, you can select multiple objects by:
• Pressing and holding the Control key while selecting objects in the
workspace diagram, then right-clicking one of the selected objects.
• Dragging a selection box around all the objects you want to select,
then right-clicking one of the selected objects.
2. In the pop-up menu, deselect Enable Object Description.
The description for the object selected is hidden, even if the View Enabled
Descriptions option is checked, because the object-level switch overrides
the system-level switch.
To edit object descriptions
1. In the workspace, double-click an object description.
2. Enter, cut, copy, or paste text into the description.
3. In the Project menu, select Save.
Alternately, you can right-click any object and select Properties to open
the object's Properties window and add or edit its description.
Designer User Interface
Working with objects
3
Note:
If you attempt to edit the description of a reusable object, Data Services
alerts you that the description will be updated for every occurrence of the
object, across all jobs. You can select the Do not show me this again check
box to avoid this alert. However, after deactivating the alert, you can only
reactivate the alert by calling Technical Support.
Creating annotations
Annotations describe a flow, part of a flow, or a diagram in a workspace. An
annotation is associated with the job, work flow, or data flow where it appears.
When you import or export that job, work flow, or data flow, you import or
export associated annotations.
To annotate a workspace diagram
1. Open the workspace diagram you want to annotate.
Data Services Designer Guide 69
Page 70
Designer User Interface
3
Working with objects
You can use annotations to describe any workspace such as a job, work
flow, data flow, catch, conditional, or while loop.
2. In the tool palette, click the annotation icon.
3. Click a location in the workspace to place the annotation.
An annotation appears on the diagram.
You can add, edit, and delete text directly on the annotation. In addition,
you can resize and move the annotation by clicking and dragging. You
can add any number of annotations to a diagram.
To delete an annotation
1. Right-click an annotation.
2. Select Delete.
Alternately, you can select an annotation and press the Delete key.
Saving and deleting objects
"Saving" an object in Data Services means storing the language that
describes the object to the repository. You can save reusable objects;
single-use objects are saved only as part of the definition of the reusable
object that calls them.
You can choose to save changes to the reusable object currently open in
the workspace. When you save the object, the object properties, the
definitions of any single-use objects it calls, and any calls to other reusable
objects are recorded in the repository. The content of the included reusable
objects is not saved; only the call is saved.
Data Services stores the description even if the object is not complete or
contains an error (does not validate).
To save changes to a single reusable object
1. Open the project in which your object is included.
2. Choose Project > Save.
70 Data Services Designer Guide
Page 71
This command saves all objects open in the workspace.
Repeat these steps for other individual objects you want to save.
To save all changed objects in the repository
1. Choose ProjectSave All.
Data Services lists the reusable objects that were changed since the last
save operation.
2. (optional) Deselect any listed object to avoid saving it.
3. Click OK.
Note:
Data Services also prompts you to save all objects that have changes
when you execute a job and when you exit the Designer. Saving a
reusable object saves any single-use object included in it.
To delete an object definition from the repository
Designer User Interface
Working with objects
3
1. In the object library, select the object.
2. Right-click and choose Delete.
• If you attempt to delete an object that is being used, Data Services
provides a warning message and the option of using the View Where
Used feature.
For more information, see Using View Where Used on page 660.
• If you select Yes, Data Services marks all calls to the object with a
red "deleted" icon to indicate that the calls are invalid. You must
remove or replace these calls to produce an executable job.
Note:
Built-in objects such as transforms cannot be deleted from the object
library.
Data Services Designer Guide 71
Page 72
Designer User Interface
3
Working with objects
To delete an object call
1. Open the object that contains the call you want to delete.
2. Right-click the object call and choose Delete.
If you delete a reusable object from the workspace or from the project area,
only the object call is deleted. The object definition remains in the object
library.
Searching for objects
From within the object library, you can search for objects defined in the
repository or objects available through a datastore.
To search for an object
1. Right-click in the object library and choose Search.
Data Services displays the Search window.
2. Enter the appropriate values for the search.
Options available in the Search window are described in detail following
this procedure.
3. Click Search.
The objects matching your entries are listed in the window. From the
search results window you can use the context menu to:
• Open an item
• View the attributes (Properties)
• Import external tables as repository metadata
You can also drag objects from the search results window and drop them
in the desired location.
The Basic tab in the Search window provides you with the following options:
72 Data Services Designer Guide
Page 73
Name
Description
Designer User Interface
Working with objects
DescriptionOption
The object name to find.
If you are searching in the repository, the name is not
case sensitive. If you are searching in a datastore and
the name is case sensitive in that datastore, enter the
name as it appears in the database or application and
use double quotation marks (") around the name to
preserve the case.
You can designate whether the information to be located Contains the specified name or Equals the specified name using the drop-down box next to the Name
field.
The object description to find.
Objects imported into the Data Services repository
have a description from their source. By default, objects
you create in the Designer have no description unless
you add a one.
3
Type
The search returns objects whose description attribute
contains the value entered.
The type of object to find.
When searching the repository, choose from Tables,
Files, Data flows, Work flows, Jobs, Hierarchies,
IDOCs, and Domains.
When searching a datastore or application, choose
from object types available through that datastore.
Data Services Designer Guide 73
Page 74
Designer User Interface
3
Working with objects
DescriptionOption
Where to search.
Choose from the repository or a specific datastore.
Look in
When you designate a datastore, you can also choose
to search the imported data (Internal Data) or the entire datastore (External Data).
The Search window also includes an Advanced tab. From the Advanced
tab, you can choose to search for objects based on their Data Services
attribute values. You can search by attribute values only when searching in
the repository.
The Advanced tab provides the following options:
DescriptionOption
Attribute
Match
74 Data Services Designer Guide
The object attribute in which to search.
The attributes are listed for the object type specified on
the Basic tab.
The attribute value to find.Value
The type of search performed.
Select Contains to search for any attribute that contains
the value specified. Select Equals to search for any attribute that contains only the value specified.
Page 75
Designer User Interface
General and environment options
General and environment options
To open the Options window, select Tools > Options. The window displays
option groups for Designer, Data, and Job Server options.
Expand the options by clicking the plus icon. As you select each option group
or option, a description appears on the right.
SAP options appear if you install these licensed extensions. See the Data
Services Supplement for SAP for more information about these options.
Related Topics
• Designer — General on page 76
• Designer — Environment on page 75
• Designer — Graphics on page 78
• Data — General on page 78
• Job Server — Environment on page 79
• Job Server — General on page 79
• Designer — Central Repository Connections on page 78
3
Designer — Environment
Default Administrator for Metadata Reporting:
Administrator: Select the Administrator that the metadata reporting tool
uses. An Administrator is defined by host name and port.
Default Job Server:
If a repository is associated with several Job Servers, one Job Server must
be defined as the default Job Server to use at login.
Current: Displays the current value of the default Job Server.
New: Allows you to specify a new value for the default Job Server from a
drop-down list of Job Servers associated with this repository. Changes are
effective immediately.
Data Services Designer Guide 75
Page 76
Designer User Interface
3
General and environment options
Note:
Job-specific options and path names specified in Designer refer to the current
default Job Server. If you change the default Job Server, modify these options
and path names.
Designer Communication Ports:
Allow Designer to set the port for Job Server communication: If checked,
Designer automatically sets an available port to receive messages from the
current Job Server. The default is checked. Uncheck to specify a listening
port or port range.
Specify port range: Only activated when you deselect the previous control.
Allows you to specify a range of ports from which the Designer can choose
a listening port.
You may choose to constrain the port used for communication between
Designer and Job Server when the two components are separated by a
firewall.
Enter port numbers in the From port and To port text boxes. To specify a
specific listening port, enter the same port number in both the From port and
To port text boxes. Changes will not take effect until you restart Data Services.
Interactive Debugger: Allows you to set a communication port for the
Designer to communicate with a Job Server while running in Debug mode.
For more information, see Changing the interactive debugger port on page 686.
Server group for local repository: If the local repository that you logged
in to when you opened the Designer is associated with a server group, the
name of the server group appears.
Designer — General
View data sampling size (rows): Controls the sample size used to display
the data in sources and targets in open data flows in the workspace. View
data by clicking the magnifying glass icon on source and target objects.
Number of characters in workspace icon name: Controls the length of
the object names displayed in the workspace. Object names are allowed to
exceed this number, but the Designer only displays the number entered here.
The default is 17 characters.
76 Data Services Designer Guide
Page 77
Designer User Interface
General and environment options
Maximum schema tree elements to auto expand: The number of elements
displayed in the schema tree. Element names are not allowed to exceed this
number. Enter a number for the Input schema and the Output schema.
The default is 100.
Default parameters to variables of the same name: When you declare a
variable at the work-flow level, Data Services automatically passes the value
as a parameter with the same name to a data flow called by a work flow.
Automatically import domains: Select this check box to automatically
import domains when importing a table that references a domain.
Perform complete validation before job execution: If checked, Data
Services performs a complete job validation before running a job. The default
is unchecked. If you keep this default setting, you should validate your design
manually before job execution.
Open monitor on job execution: Affects the behavior of the Designer when
you execute a job. With this option enabled, the Designer switches the
workspace to the monitor view during job execution; otherwise, the workspace
remains as is. The default is on.
3
Automatically calculate column mappings: Calculates information about
target tables and columns and the sources used to populate them. Data
Services uses this information for metadata reports such as impact and
lineage, auto documentation, or custom reports. Column mapping information
is stored in the AL_COLMAP table (ALVW_MAPPING view) after you save
a data flow or import objects to or export objects from a repository. If the
option is selected, be sure to validate your entire job before saving it because
column mapping calculation is sensitive to errors and will skip data flows that
have validation problems.
Show dialog when job is completed: Allows you to choose if you want to
see an alert or just read the trace messages.
Show tabs in workspace: Allows you to decide if you want to use the tabs
at the bottom of the workspace to navigate.
Related Topics
• Using View Data on page 666
• Management Console Metadata Reports Guide: Refresh Usage Data tab
Data Services Designer Guide 77
Page 78
Designer User Interface
3
General and environment options
Designer — Graphics
Choose and preview stylistic elements to customize your workspaces. Using
these options, you can easily distinguish your job/work flow design workspace
from your data flow design workspace.
• Workspace flow type: Switch between the two workspace flow types
(Job/Work Flow and Data Flow) to view default settings. Modify settings
for each type using the remaining options.
• Line Type: Choose a style for object connector lines.
• Line Thickness: Set the connector line thickness.
• Background style: Choose a plain or tiled background pattern for the
selected flow type.
• Color scheme: Set the background color to blue, gray, or white.
• Use navigation watermark: Add a watermark graphic to the background
of the flow type selected. Note that this option is only available with a
plain background style.
Designer — Central Repository Connections
Displays the central repository connections and the active central repository.
To activate a central repository, right-click one of the central repository
connections listed and select Activate.
Reactivate automatically: Select if you want the active central repository
to be reactivated whenever you log in to Data Services using the current
local repository.
Data — General
Century Change Year: Indicates how Data Services interprets the century
for two-digit years. Two-digit years greater than or equal to this value are
interpreted as 19##. Two-digit years less than this value are interpreted as
20##. The default value is 15.
78 Data Services Designer Guide
Page 79
Designer User Interface
General and environment options
For example, if the Century Change Year is set to 15:
Interpreted asTwo-digit year
199999
191616
191515
201414
Convert blanks to nulls for Oracle bulk loader: Converts blanks to NULL
values when loading data using the Oracle bulk loader utility and:
• the column is not part of the primary key
3
• the column is nullable
Job Server — Environment
Maximum number of engine processes: Sets a limit on the number of
engine processes that this Job Server can have running concurrently.
Job Server — General
Use this window to reset Job Server options (see Changing Job Server
options on page 383) or with guidance from Business Objects Customer
Support. For contact information, visit http://www.businessobjects.com/sup
port/.
Data Services Designer Guide 79
Page 80
Designer User Interface
General and environment options
3
80 Data Services Designer Guide
Page 81
Projects and Jobs
4
Page 82
Projects and Jobs
4
Projects
Projects
Project and job objects represent the top two levels of organization for the
application flows you create using the Designer.
Related Topics
• Projects on page 82
• Jobs on page 84
A project is a reusable object that allows you to group jobs. A project is the
highest level of organization offered by Data Services. Opening a project
makes one group of objects easily accessible in the user interface.
You can use a project to group jobs that have schedules that depend on one
another or that you want to monitor together.
Projects have common characteristics:
• Projects are listed in the object library.
• Only one project can be open at a time.
• Projects cannot be shared among multiple users.
Objects that make up a project
The objects in a project appear hierarchically in the project area. If a plus
sign (+) appears next to an object, expand it to view the lower-level objects
contained in the object. Data Services shows you the contents as both names
in the project area hierarchy and icons in the workspace.
In the following example, the Job_KeyGen job contains two data flows, and
the DF_EmpMap data flow contains multiple objects.
82 Data Services Designer Guide
Page 83
Each item selected in the project area also displays in the workspace:
Creating a new project
1. Choose Project > New > Project.
2. Enter the name of your new project.
Projects and Jobs
Projects
4
The name can include alphanumeric characters and underscores (_). It
cannot contain blank spaces.
3. Click Create.
The new project appears in the project area. As you add jobs and other
lower-level objects to the project, they also appear in the project area.
Opening existing projects
To open an existing project
1. Choose Project > Open.
2. Select the name of an existing project from the list.
3. Click Open.
Data Services Designer Guide 83
Page 84
Projects and Jobs
4
Jobs
Note:
If another project was already open, Data Services closes that project and
opens the new one.
Saving projects
To save all changes to a project
1. Choose Project > Save All.
Data Services lists the jobs, work flows, and data flows that you edited
since the last save.
2. (optional) Deselect any listed object to avoid saving it.
3. Click OK.
Note:
Data Services also prompts you to save all objects that have changes
when you execute a job and when you exit the Designer. Saving a
reusable object saves any single-use object included in it.
Jobs
A job is the only object you can execute. You can manually execute and test
jobs in development. In production, you can schedule batch jobs and set up
real-time jobs as services that execute a process when Data Services
receives a message request.
A job is made up of steps you want executed together. Each step is
represented by an object icon that you place in the workspace to create a
job diagram. A job diagram is made up of two or more objects connected
together. You can include any of the following objects in a job definition:
• Data flows
• Sources
• Targets
• Transforms
84 Data Services Designer Guide
Page 85
Projects and Jobs
Jobs
• Work flows
• Scripts
• Conditionals
• While Loops
• Try/catch blocks
If a job becomes complex, organize its content into individual work flows,
then create a single job that calls those work flows.
Real-time jobs use the same components as batch jobs. You can add work
flows and data flows to both batch and real-time jobs. When you drag a work
flow or data flow icon into a job, you are telling Data Services to validate
these objects according the requirements of the job type (either batch or
real-time).
There are some restrictions regarding the use of some Data Services features
with real-time jobs.
Related Topics
• Work Flows on page 237
• Real-time Jobs on page 295
4
Creating jobs
To create a job in the project area
1. In the project area, select the project name.
2. Right-click and choose New BatchJob or Real Time Job.
3. Edit the name.
The name can include alphanumeric characters and underscores (_). It
cannot contain blank spaces.
Data Services opens a new workspace for you to define the job.
Data Services Designer Guide 85
Page 86
Projects and Jobs
4
Jobs
To create a job in the object library
1. Go to the Jobs tab.
2. Right-click Batch Jobs or Real Time Jobs and choose New.
3. A new job with a default name appears.
4. Right-click and select Properties to change the object's name and add a
description.
The name can include alphanumeric characters and underscores (_). It
cannot contain blank spaces.
5. To add the job to the open project, drag it into the project area.
Naming conventions for objects in jobs
We recommend that you follow consistent naming conventions to facilitate
object identification across all systems in your enterprise. This allows you to
more easily work with metadata across all applications such as:
• Data-modeling applications
• ETL applications
• Reporting applications
• Adapter software development kits
Examples of conventions recommended for use with jobs and other objects
are shown in the following table.
86 Data Services Designer Guide
ExampleObjectSuffixPrefix
DF_CurrencyData flown/aDF_
EDF_Example_InputEmbedded data flow_InputEDF_
EDF_Example_OutputEmbedded data flow_OutputEDF_
RTJob_OrderStatusReal-time jobn/aRTJob_
WF_SalesOrgWork flown/aWF_
Page 87
Projects and Jobs
Jobs
ExampleObjectSuffixPrefix
JOB_SalesOrgJobn/aJOB_
ORA_DSDatastore_DSn/a
DC_DB2_productionDatastore configurationn/aDC_
SC_ORA_testSystem configurationn/aSC_
Catalog_Memory_DSMemory datastore_Memory_DSn/a
PROC_SalesStatusStored proceduren/aPROC_
Although Data Services Designer is a graphical user interface with icons
representing objects in its windows, other interfaces might require you to
identify object types by the text alone. By using a prefix or suffix, you can
more easily identify your object's type.
In addition to prefixes and suffixes, you might want to provide standardized
names for objects that identify a specific action across all object types. For
example: DF_OrderStatus, RTJob_OrderStatus.
4
In addition to prefixes and suffixes, naming conventions can also include
path name identifiers. For example, the stored procedure naming convention
can look like either of the following:
<datastore>.<owner>.<PROC_Name>
<datastore>.<owner>.<package>.<PROC_Name>
Data Services Designer Guide 87
Page 88
Projects and Jobs
Jobs
4
88 Data Services Designer Guide
Page 89
Datastores
5
Page 90
Datastores
5
What are datastores?
Related Topics
• What are datastores? on page 90
• Database datastores on page 91
• Adapter datastores on page 122
• Creating and managing multiple datastore configurations on page 130
What are datastores?
Datastores represent connection configurations between Data Services and
databases or applications. These configurations can be direct or through
adapters. Datastore configurations allow Data Services to access metadata
from a database or application and read from or write to that database or
application while Data Services executes a job.
Data Services datastores can connect to:
• Databases and mainframe file systems.
• Applications that have pre-packaged or user-written Data Services
adapters.
• J.D. Edwards One World and J.D. Edwards World, Oracle Applications,
PeopleSoft, SAP ERP and R/3 and SAP BI/BW, and Siebel Applications.
See the appropriate Data Services Supplement.
Note:
Data Services reads and writes data stored in flat files through flat file formats.
Data Services reads and writes data stored in XML documents through DTDs
and XML Schemas.
The specific information that a datastore object can access depends on the
connection configuration. When your database or application changes, make
corresponding changes in the datastore information in Data Services—Data
Services does not automatically detect the new information.
Note:
Objects deleted from a datastore connection are identified in the project area
and workspace by a red "deleted" icon. This visual flag allows you to
find and update data flows affected by datastore changes.
You can create multiple configurations for a datastore. This allows you to
plan ahead for the different environments your datastore may be used in and
limits the work involved with migrating jobs. For example, you can add a set
90 Data Services Designer Guide
Page 91
of configurations (DEV, TEST, and PROD) to the same datastore name.
These connection settings stay with the datastore during export or import.
You can group any set of datastore configurations into a system configuration.
When running or scheduling a job, select a system configuration, and thus,
the set of datastore configurations for your current environment.
Related Topics
• Database datastores on page 91
• Adapter datastores on page 122
• File Formats on page 153
• Formatting XML documents on page 259
• Creating and managing multiple datastore configurations on page 130
Database datastores
Database datastores can represent single or multiple Data Services
connections with:
• Legacy systems using Attunity Connect
Datastores
Database datastores
5
• IBM DB2, HP Neoview, Informix, Microsoft SQL Server, Oracle, Sybase
ASE, Sybase IQ, MySQL, Netezza, Business Objects Data Federator,
and Teradata databases (using native connections)
• Other databases (through ODBC)
• A Data Services repository, using a memory datastore or persistent cache
datastore
This section discusses:
•
Mainframe interface on page 92
•
Defining a database datastore on page 96
•
Browsing metadata through a database datastore on page 100
•
Importing metadata through a database datastore on page 104
•
Memory datastores on page 111
•
Persistent cache datastores on page 116
•
Linked datastores on page 120
Data Services Designer Guide 91
Page 92
Datastores
5
Database datastores
Mainframe interface
Data Services provides the Attunity Connector datastore that accesses
mainframe data sources through Attunity Connect. The data sources that
Attunity Connect accesses are in the following list. For a complete list of
sources, refer to the Attunity documentation.
• Adabas
• DB2 UDB for OS/390 and DB2 UDB for OS/400
• IMS/DB
• VSAM
• Flat files on OS/390 and flat files on OS/400
Prerequisites for an Attunity datastore
Attunity Connector accesses mainframe data using software that you must
manually install on the mainframe server and the local client (Job Server)
computer. Data Services connects to Attunity Connector using its ODBC
interface.
It is not necessary to purchase a separate ODBC driver manager for UNIX
and Windows platforms.
Servers
Install and configure the Attunity Connect product on the server (for example,
an zSeries computer).
Clients
To access mainframe data using Attunity Connector, install the Attunity
Connect product. The ODBC driver is required. Attunity also offers an optional
tool called Attunity Studio, which you can use for configuration and
administration.
Configure ODBC data sources on the client (Data Services Job Server).
92 Data Services Designer Guide
Page 93
When you install a Data Services Job Server on UNIX, the installer will prompt
you to provide an installation directory path for Attunity connector software.
In addition, you do not need to install a driver manager, because Data
Services loads ODBC drivers directly on UNIX platforms.
For more information about how to install and configure these products, refer
to their documentation.
Configuring an Attunity datastore
To use the Attunity Connector datastore option, upgrade your repository to
Data Services version 6.5.1 or later.
To create an Attunity Connector datastore:
1. In the Datastores tab of the object library, right-click and select New.
2. Enter a name for the datastore.
3. In the Datastore type box, select Database.
4. In the Database type box, select Attunity Connector.
5. Type the Attunity data source name, location of the Attunity daemon (Host
location), the Attunity daemon port number, and a unique Attunity server
workspace name.
Datastores
Database datastores
5
For information about how to specify multiple data sources in one Attunity
datastore, see Specifying multiple data sources in one Attunity datastore
on page 94.
For information about how to specify Attunity tables in Data Services, see
Data Services naming convention for Attunity tables on page 94.
6. To change any of the default options (such as Rows per Commit or
Language), click the Advanced button. For general information about
these options see, Defining a database datastore on page 96.
7. Click OK.
You can now use the new datastore connection to import metadata tables
into the current Data Services repository.
Data Services Designer Guide 93
Page 94
Datastores
5
Database datastores
Specifying multiple data sources in one Attunity datastore
You can use the Attunity Connector datastore to access multiple Attunity
data sources on the same Attunity Daemon location. If you have several
types of data on the same computer, for example a DB2 database and VSAM,
you might want to access both types of data using a single connection. For
example, you can use a single connection to join tables (and push the join
operation down to a remote server), which reduces the amount of data
transmitted through your network.
To specify multiple sources in the Datastore Editor:
1. Separate data source names with semicolons in the Attunity data source
box using the following format:
AttunityDataSourceName;AttunityDataSourceName
For example, if you have a DB2 data source named DSN4 and a VSAM
data source named Navdemo, enter the following values into the Data
source box:
DSN4;Navdemo
2. If you list multiple data source names for one Attunity Connector datastore,
ensure that you meet the following requirements:
• All Attunity data sources must be accessible by the same user name
and password.
• All Attunity data sources must use the same workspace. When you
setup access to the data sources in Attunity Studio, use the same
workspace name for each data source.
Data Services naming convention for Attunity tables
Data Services' format for accessing Attunity tables is unique to Data Services.
Because a single datastore can access multiple software systems that do
not share the same namespace, the name of the Attunity data source must
be specified when referring to a table. With an Attunity Connector, precede
94 Data Services Designer Guide
Page 95
Datastores
Database datastores
the table name with the data source and owner names separated by a colon.
The format is as follows:
AttunityDataSource:OwnerName.TableName
When using the Designer to create your jobs with imported Attunity tables,
Data Services automatically generates the correct SQL for this format.
However, when you author SQL, be sure to use this format. You can author
SQL in the following constructs:
• SQL function
• SQL transform
• Pushdown_sql function
• Pre-load commands in table loader
• Post-load commands in table loader
Note:
For any table in Data Services, the maximum size of the owner name is 64
characters. In the case of Attunity tables, the maximum size of the Attunity
data source name and actual owner name is 63 (the colon accounts for 1
character). Data Services cannot access a table with an owner name larger
than 64 characters.
5
Limitations
All Data Services features are available when you use an Attunity Connector
datastore except the following:
• Bulk loading
• Imported functions (imports metadata for tables only)
• Template tables (creating tables)
• The datetime data type supports up to 2 sub-seconds only
• Data Services cannot load timestamp data into a timestamp column in a
table because Attunity truncates varchar data to 8 characters, which is
not enough to correctly represent a timestamp value.
Data Services Designer Guide 95
Page 96
Datastores
5
Database datastores
• When running a job on UNIX, the job could fail with following error:
[D000] Cannot open file /usr1/attun/navroot/def/sys System
error 13: The file access permissions do not allow the
specified action.; (OPEN)
This error occurs because of insufficient file permissions to some of the
files in the Attunity installation directory. To avoid this error, change the
file permissions for all files in the Attunity directory to 777 by executing
the following command from the Attunity installation directory:
$ chmod -R 777 *
Defining a database datastore
Define at least one database datastore for each database or mainframe file
system with which you are exchanging data.
To define a datastore, get appropriate access privileges to the database or
file system that the datastore describes.
For example, to allow Data Services to use parameterized SQL when reading
or writing to DB2 databases, authorize the user (of the datastore/database)
to create, execute and drop stored procedures. If a user is not authorized to
create, execute and drop stored procedures, jobs will still run. However, they
will produce a warning message and will run less efficiently.
To define a Database datastore
1. In the Datastores tab of the object library, right-click and select New.
2. Enter the name of the new datastore in the Datastore Name field.
The name can contain any alphabetical or numeric characters or
underscores (_). It cannot contain spaces.
3. Select the Datastore type.
Choose Database. When you select a Datastore Type, Data Services
displays other options relevant to that type.
4. Select the Database type.
96 Data Services Designer Guide
Page 97
Datastores
Database datastores
Note:
If you select Data Federator, you must also specify the catalog name and
the schema name in the URL. If you do not, you may see all of the tables
from each catalog.
a. Select ODBC Admin and then the System DSN tab.
b. Highlight Data Federator, and then click Configure.
c. In the URL option, enter the catalog name and the schema name, for
example, jdbc:leselect://localhost/catalogname;schema=sche
maname.
5. Enter the appropriate information for the selected database type.
6. The Enable automatic data transfer check box is selected by default
when you create a new datastore and you chose Database for Datastore
type. This check box displays for all databases except Attunity Connector,
Data Federator, Memory, and Persistent Cache.
Keep Enable automatic data transfer selected to enable transfer tables
in this datastore that the Data_Transfer transform can use to push down
subsequent database operations.
7. At this point, you can save the datastore or add more information to it:
• To save the datastore and close the Datastore Editor, click OK.
5
• To add more information, select Advanced.
To enter values for each configuration option, click the cells under
each configuration name.
For the datastore as a whole, the following buttons are available:
Data Services Designer Guide 97
Page 98
Datastores
5
Database datastores
Import unsupported data types as
VARCHAR of size
Edit
DescriptionButtons
The data types that Data Services
supports are documented in the
Reference Guide. If you want Data Services to convert a data type
in your source that it would not
normally support, select this option and enter the number of
characters that you will allow.
Opens the Configurations for
Datastore dialog. Use the tool bar
on this window to add, configure,
and manage multiple configurations for a datastore.
Show ATL
OK
Cancel
8. Click OK.
98 Data Services Designer Guide
Opens a text window that displays
how Data Services will code the
selections you make for this
datastore in its scripting language.
Saves selections and closes the
Datastore Editor (Create New
Datastore) window.
Cancels selections and closes the
Datastore Editor window.
Saves selections.Apply
Page 99
Note:
On versions of Data Integrator prior to version 11.7.0, the correct database
type to use when creating a datastore on Netezza was ODBC. Data Services
11.7.1 provides a specific Netezza option as the Database type instead of
ODBC. When using Netezza as the database with Data Services, we
recommend that you choose Data Services' Netezza option as the Database
type rather than ODBC.
Related Topics
• Performance Optimization Guide: Data Transfer transform for push-down
operations
• Reference Guide: Datastore
• Creating and managing multiple datastore configurations on page 130
• Ways of importing metadata on page 107
Changing a datastore definition
Like all Data Services objects, datastores are defined by both options and
properties:
Datastores
Database datastores
5
Options control the operation of objects. For example, the name of the
database to connect to is a datastore option.
Properties document the object. For example, the name of the datastore and
the date on which it was created are datastore properties. Properties are
merely descriptive of the object and do not affect its operation.
To change datastore options
1. Go to the Datastores tab in the object library.
2. Right-click the datastore name and choose Edit.
The Datastore Editor appears (the title bar for this dialog displays Edit
Datastore). You can do the following tasks:
• Change the connection information for the current datastore
configuration.
• Click Advanced and change properties for the current configuration,
Data Services Designer Guide 99
Page 100
Datastores
5
Database datastores
• Click Edit to add, edit, or delete additional configurations. The
Configurations for Datastore dialog opens when you select Edit in the
Datastore Editor. Once you add a new configuration to an existing
datastore, you can use the fields in the grid to change connection
values and properties for the new configuration.
3. Click OK.
The options take effect immediately.
Related Topics
• Reference Guide: Data Services Objects, Database datastores
To change datastore properties
1. Go to the datastore tab in the object library.
2. Right-click the datastore name and select Properties.
The Properties window opens.
3. Change the datastore properties.
4. Click OK.
Related Topics
• Reference Guide: Datastore
Browsing metadata through a database datastore
Data Services stores metadata information for all imported objects in a
datastore. You can use Data Services to view metadata for imported or
non-imported objects and to check whether the metadata has changed for
objects already imported.
To view imported objects
1. Go to the Datastores tab in the object library.
2. Click the plus sign (+) next to the datastore name to view the object types
in the datastore. For example, database datastores have functions, tables,
and template tables.
100 Data Services Designer Guide
Loading...