Business objects DATA FEDERATOR XI 3.0 User Manual

Data Federator User Guide

BusinessObjects Data Federator XI 3.0

© 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-10-09

Introduction to Data Federator 25Chapter 1

The Data Federator application.................................................................26

Fundamental notions in Data Federator....................................................29

Data Federator user interface....................................................................31

Overview of the methodology....................................................................33

Starting a Data Federator project 39Chapter 2

Working with Data Federator.....................................................................40

Adding new users......................................................................................40

Starting a project........................................................................................41

An answer to a common business problem.........................................26

Data Federator Designer: design time.................................................30

Data Federator Query Server: run time................................................30

Important terms....................................................................................30

Adding the targets................................................................................35

Adding the datasources........................................................................35

Mapping datasources to targets...........................................................36

Checking if data passes constraints.....................................................36

Deploying the project............................................................................37

Adding a project...................................................................................41

Opening a project.................................................................................41

Deleting Data Federator projects.........................................................42

Closing Data Federator projects...........................................................42

Unlocking projects................................................................................43

Data Federator User Guide 3

Contents

Creating target tables 45Chapter 3

Managing target tables..............................................................................46

Adding a target table manually.............................................................46

Adding a target table from a DDL script...............................................47

Adding a target table from an existing table.........................................47

Changing the name of a target table....................................................48

Displaying the impact and lineage of target tables...............................48

Details on configuring target table schemas........................................49

Determining the status of a target table.....................................................50

How to read the Impact and lineage pane in Data Federator Designer.....52

Testing targets...........................................................................................53

Testing a target.....................................................................................53

Managing domain tables............................................................................54

Adding a domain table to enumerate values in a target column..........55

Examples of domain tables..................................................................56

Adding a domain table by importing data from a file............................59

Dereferencing a domain table from your target table...........................60

Exporting a domain table as CSV........................................................61

Deleting a domain table........................................................................61

Using domain tables in your target tables.................................................61

Using a domain table as the domain of a column................................62

Defining sources of data 65Chapter 4

About datasources.....................................................................................66

Datasource user interface....................................................................67

Draft and Final datasources.................................................................67

About configuration resources..............................................................70

Generic and pre-defined datasources..................................................71

Creating database datasources using resources......................................72

4 Data Federator User Guide

Contents

Adding Access datasources.................................................................72

Adding DB2 datasources......................................................................76

Adding Informix datasources................................................................81

Adding MySQL datasources.................................................................86

Adding Oracle datasources..................................................................91

Adding Netezza datasources...............................................................96

Adding Progress datasources............................................................101

Adding SAS datasources...................................................................107

Adding SQL Server datasources........................................................113

Adding Sybase datasources...............................................................119

Adding Sybase IQ datasources..........................................................124

Adding Teradata datasources............................................................128

Creating JDBC datasources from custom resources.........................133

Creating generic database datasources..................................................138

Creating generic JDBC or ODBC datasources..................................138

Managing database datasources............................................................154

Using deployment contexts ...............................................................154

Defining deployment parameters for a project ..................................155

Defining a connection with deployment context parameters .............156

Adding tables to a relational database datasource............................157

Updating the tables of a relational database datasource...................158

Creating text file datasources..................................................................158

About text file formats.........................................................................159

Setting a text file datasource name and description..........................159

Selecting a text data file.....................................................................160

Configuring file extraction parameters...............................................161

Automatically extracting the schema of your datasource table..........168

Indicating a primary key in a text file datasource...............................170

Managing text file datasources................................................................170

Editing the schema of an existing table..............................................170

Using a schema file to define a text file datasource schema.............171

Data Federator User Guide 5

Contents

Generating a schema when a text file has no header row.................171

Defining the schema of a text file datasource manually.....................172

Selecting multiple text files as a datasource .....................................173

Numeric formats used in text files......................................................174

Date formats used in text files............................................................176

Modifying the data extraction parameters of a text file.......................177

Using a remote text file as a datasource............................................178

Creating XML and web service datasources...........................................179

About XML file datasources...............................................................179

Adding an XML file datasource..........................................................179

Choosing and configuring a source file of type XML..........................180

Adding datasource tables to an XML datasource..............................181

About web service datasources.........................................................182

Adding a web service datasource......................................................183

Extracting the available operations from a web service.....................183

Selecting the operations you want to access from a web service......184

Authenticating on a web service datasource......................................185

Authenticating on a server that hosts web services used as

datasources........................................................................................185

Using the SOAP header to pass parameters to web services...........186

Selecting which response elements to convert to tables in a web service

datasource..........................................................................................187

Assigning constant values to parameters of web service operations..188

Assigning dynamic values to parameters of web service operations..188

Propagating values to parameters of web service operations...........189

Managing XML and web service datasources.........................................189

Using the elements and attributes pane.............................................189

Selecting multiple XML files as datasources .....................................199

Using a remote XML file as a datasource..........................................200

Testing web service datasources.......................................................201

Creating remote Query Server datasources............................................202

6 Data Federator User Guide

Contents

Configuring a remote Query Server datasource................................202

Managing datasources............................................................................204

Defining the schema of a datasource.................................................204

Authentication methods for database datasources............................207

Displaying the impact and lineage of datasource tables....................208

Restricting access to columns using input columns...........................209

Changing the source type of a datasource........................................209

Deleting a datasource........................................................................210

Testing and finalizing datasources...........................................................210

Running a query on a datasource......................................................211

Making your datasource final.............................................................212

Editing a final datasource...................................................................213

Mapping datasources to targets 215Chapter 5

Mapping datasources to targets process overview.................................216

The user interface for mapping..........................................................216

Adding a mapping rule for a target table............................................217

Selecting a datasource table for the mapping rule.............................218

Writing mapping formulas...................................................................219

Determining the status of a mapping.......................................................220

Mapping values using formulas...............................................................222

Mapping formula syntax.....................................................................222

Filling in mapping formulas automatically..........................................223

Setting a constant in a column of a target table.................................224

Testing mapping formulas..................................................................226

Writing aggregate formulas................................................................227

Writing case statement formulas........................................................230

Testing case statement formulas........................................................232

Mapping values to input columns............................................................233

Assigning constant values to input columns using pre-filters.............233

Assigning dynamic values to input columns using table relationships.234

Data Federator User Guide 7

Contents

Propagating values to input columns using input value functions......234

Adding filters to mapping rules................................................................235

The precedence between filters and formulas...................................235

Adding a pre-filter on a column of a datasource table........................236

Editing a pre-filter...............................................................................239

Deleting a pre-filter.............................................................................241

Using lookup tables.................................................................................242

What is a lookup table?......................................................................242

The process of adding a lookup table between columns...................243

Adding a lookup table.........................................................................244

Referencing a datasource table in a lookup table..............................246

Referencing a domain table in a lookup table....................................247

Mapping values between a datasource table and a domain table.....248

Adding a lookup table by importing data from a file...........................249

Dereferencing a domain table from a lookup table............................251

Deleting a lookup table.......................................................................252

Exporting a lookup table as CSV.......................................................252

Using a target as a datasource................................................................252

Managing relationships between datasource tables................................253

The precedence between formulas and relationships........................253

Finding incomplete relationships........................................................254

Adding a relationship..........................................................................256

Editing a relationship..........................................................................259

Deleting a relationship........................................................................260

Choosing a core table........................................................................261

Configuring meanings of table relationships using core tables..........261

Using a domain table to constrain possible values............................263

The process of mapping multiple datasource tables to one target

table....................................................................................................264

Adding multiple datasource tables to a mapping...............................265

Writing mapping formulas when mapping multiple datasource tables.265

8 Data Federator User Guide

Contents

Adding a relationship when mapping multiple datasource tables......267

Interpreting the results of a mapping of multiple datasource tables....270

Combining mappings and case statements.......................................274

Managing a set of mapping rules............................................................276

Viewing all the mapping rules.............................................................276

Opening a mapping rule.....................................................................276

Copying a mapping rule.....................................................................277

Printing a mapping rule......................................................................278

Deleting a mapping rule.....................................................................279

Displaying the impact and lineage of mappings.................................279

Activating and deactivating mapping rules..............................................279

Deactivating a mapping rule...............................................................280

Activating a mapping rule...................................................................280

Testing mappings.....................................................................................280

Testing a mapping rule.......................................................................281

Managing datasource, lookup and domain tables in a mapping rule......282

Adding a table to a mapping rule.......................................................282

Replacing a table in a mapping rule...................................................284

Deleting a table from a mapping rule.................................................286

Viewing the columns of a table in a mapping rule..............................286

Setting the alias of a table in a mapping rule.....................................287

Restricting rows to distinct values......................................................289

Details on functions used in formulas......................................................292

Managing constraints 293Chapter 6

Testing mapping rules against constraints...............................................294

Defining constraints on a target table......................................................294

Types of constraints...........................................................................294

Defining key constraints for a target table..........................................295

Defining not-null constraints for a target table....................................295

Defining custom constraints on a target table....................................296

Data Federator User Guide 9

Contents

Syntax of constraint formulas.............................................................296

Configuring a constraint check...........................................................297

Checking constraints on a mapping rule.................................................299

The purpose of analyzing constraint violations..................................299

Computing constraint violations.........................................................300

Computing constraint violations for a group of mapping rules...........301

Filtering constraint violations..............................................................302

Marking a mapping rule as validated.................................................303

Viewing constraint violations..............................................................303

The Constraint checks pane...............................................................304

Reports....................................................................................................306

Managing projects 307Chapter 7

Managing a project and its versions........................................................308

The user interface for projects............................................................308

The life cycle of a project....................................................................309

Editing the configuration of a project..................................................310

Storing the current version of a project..............................................311

Storing the current version of selected target tables..........................312

Downloading a version of a project....................................................313

Loading a version of a project stored on the server...........................314

Loading a version of a project stored on your file system..................315

Including a project in your current project..........................................315

Opening multiple projects...................................................................319

Exporting all projects..........................................................................320

Importing a set of projects..................................................................321

Deploying projects...................................................................................321

Servers on which projects are deployed............................................322

User rights on deployed catalogs and tables.....................................322

Storage of deployed projects..............................................................323

Version control of deployed projects..................................................323

10 Data Federator User Guide

Contents

Deploying a version of a project.........................................................324

Using deployment contexts ...............................................................325

Reference of project deployment options...........................................327

Managing changes 329Chapter 8

Overview..................................................................................................330

Verifying if changes are valid..............................................................330

Modifying the schema of a final datasource............................................331

Deleting an installed datasource.............................................................333

Modifying a target....................................................................................335

Adding a mapping....................................................................................337

Modifying a mapping................................................................................337

Adding a constraint check........................................................................338

Modifying a constraint check...................................................................338

Modifying a domain table.........................................................................338

Deleting a domain table...........................................................................339

Modifying a lookup table..........................................................................341

Deleting a lookup table............................................................................341

Chapter 10

Introduction to Data Federator Query Server 343Chapter 9

Data Federator Query Server overview...................................................344

Data Federator Query Server architecture..............................................344

How Data Federator Query Server accesses sources of data...........345

Key functions of Data Federator Administrator..................................347

Security recommendations......................................................................348

Connecting to Data Federator Query Server using JDBC/ODBC drivers 349

Connecting to Data Federator Query Server using JDBC.......................350

Installing the JDBC driver with the Data Federator installer...............350

Data Federator User Guide 11

Contents

Installing the JDBC driver without the Data Federator installer..........351

Connecting to the server using JDBC................................................352

Example Java code for connecting to Data Federator Query Server using

JDBC..................................................................................................353

Connecting to Data Federator Query Server using ODBC......................354

Installing the ODBC driver for Data Federator (Windows only)..........354

Connecting to the server using ODBC...............................................355

Using ODBC when your application already uses another JVM........357

Accessing data........................................................................................357

JDBC URL syntax....................................................................................358

Parameters in the JDBC connection URL..........................................361

JDBC and ODBC Limitations...................................................................378

JDBC and ODBC Limitations.............................................................378

SQL Constraints......................................................................................380

Using Data Federator Administrator 383Chapter 11

Data Federator Administrator overview...................................................384

Starting Data Federator Administrator.....................................................384

To end your Data Federator Administrator session.................................384

Server configuration.................................................................................384

Exploring the user interface ....................................................................385

Objects tab.........................................................................................385

My Query Tool tab..............................................................................386

Administration tab...............................................................................387

The Server Status menu item.............................................................389

The Connector Settings menu item....................................................390

The User Rights menu item................................................................391

The Configuration menu item.............................................................392

The Statistics menu item....................................................................393

Managing statistics with Data Federator Administrator...........................394

Using the Statistics tab to refresh statistics automatically..................395

12 Data Federator User Guide

Contents

Selecting the tables for which you want to display statistics..............395

Recording statistics that Query Server recently requested................395

List of options for the Global Refresh of Statistics pane....................396

Managing queries with Data Federator Administrator.............................397

Executing SQL queries using the My Query Tool tab.........................397

Configuring connectors to sources of data 399Chapter 12

About connectors in Data Federator........................................................400

Configuring Access connectors...............................................................400

Configuring Access connectors..........................................................400

Configuring DB2 connectors....................................................................401

Configuring DB2 connectors..............................................................401

Configuring Informix connectors..............................................................402

Supported versions of Informix...........................................................402

Configuring Informix connectors........................................................402

List of Informix resource properties....................................................403

Configuring MySQL connectors...............................................................410

Configuring MySQL connectors.........................................................410

Specific collation parameters for MySQL...........................................411

Configuring Oracle connectors................................................................412

Configuring Oracle connectors...........................................................412

Specific collation parameters for Oracle............................................412

How Data Federator transforms wildcards in names of Oracle tables.413

Configuring Netezza connectors.............................................................414

Supported versions of Netezza..........................................................414

Configuring Netezza connectors........................................................414

List of Netezza resource properties...................................................415

Configuring Progress connectors............................................................422

Configuring connectors for Progress..................................................422

Installing OEM SequeLink Server for Progress connections.............423

Configuring middleware for Progress connections.............................423

Data Federator User Guide 13

Contents

Configuring SAS connectors...................................................................426

Configuring connectors for SAS.........................................................426

Supported versions of SAS................................................................427

Installing drivers for SAS connections................................................427

Optimizing SAS queries by ordering tables in the from clause by their

cardinality...........................................................................................428

List of JDBC resource properties for SAS..........................................428

Configuring SQL Server connectors........................................................430

Configuring SQL Server connectors..................................................430

Specific collation parameters for SQL Server....................................431

Configuring Sybase connectors...............................................................432

Supported versions of Sybase...........................................................432

Configuring Sybase connectors.........................................................432

Installing middleware to let Data Federator connect to Sybase.........433

List of Sybase resource properties.....................................................434

Configuring Sybase IQ connectors..........................................................442

Supported versions of Sybase IQ......................................................442

Configuring Sybase IQ connectors....................................................442

List of Sybase IQ resource properties................................................443

Configuring Teradata connectors.............................................................451

Supported versions of Teradata.........................................................451

Configuring Teradata connectors.......................................................451

List of Teradata resource properties...................................................452

Default values of capabilities in connectors.............................................459

Configuring connectors that use JDBC...................................................460

Pointing a resource to an existing JDBC driver..................................460

List of JDBC resource properties.......................................................461

List of JDBC resource properties for connection pools......................472

List of common JDBC classes............................................................475

List of pre-defined JDBC URL templates...........................................476

transactionIsolation property..............................................................478

14 Data Federator User Guide

Contents

urlTemplate.........................................................................................479

Configuring connectors to web services..................................................479

List of resource properties for web service connectors......................480

Managing resources and properties of connectors.................................483

Managing resources using Data Federator Administrator..................483

Creating and configuring a resource using Data Federator

Administrator......................................................................................486

Copying a resource using Data Federator Administrator...................488

List of pre-defined resources..............................................................489

Managing resources using SQL.........................................................491

Creating a resource using SQL..........................................................491

Deleting a resource using SQL..........................................................492

Modifying a resource property using SQL..........................................493

Deleting a resource property using SQL............................................494

System tables for resource management..........................................494

Collation in Data Federator......................................................................495

Supported Collations in Data Federator.............................................496

Setting string sorting and string comparison behavior for Data Federator

SQL queries.......................................................................................497

How Data Federator decides how to push queries to sources when using

binary collation...................................................................................500

Managing user accounts and roles 503Chapter 13

About user accounts, roles, and privileges..............................................504

About user accounts...........................................................................504

Creating a Data Federator administrator user account...........................505

Creating a Data Federator Designer user account..................................506

Creating a Data Federator Query Server user account...........................506

Managing user accounts with Data Federator Administrator...................507

Properties of user accounts.....................................................................511

Managing roles with Data Federator Administrator.................................511

Data Federator User Guide 15

Contents

Granting privileges to a user account or role...........................................514

Managing privileges with Data Federator Administrator..........................514

Managing user accounts with SQL statements.......................................516

Creating a user account with SQL.....................................................516

Dropping a user account with SQL....................................................516

Modifying a user password with SQL.................................................517

Modifying properties of a user account with SQL...............................517

Listing user accounts using SQL........................................................517

Managing privileges using SQL statements ...........................................518

About grantees...................................................................................519

Granting a privilege with SQL.............................................................519

Revoking a privilege with SQL...........................................................520

Checking a privilege with SQL...........................................................520

Verifying privileges using system tables.............................................521

List of privileges..................................................................................522

Managing roles with SQL statements......................................................523

Creating a role with SQL....................................................................523

Dropping a role with SQL...................................................................524

Granting roles with SQL.....................................................................524

Verifying roles using system tables....................................................524

Managing login domains..........................................................................525

Adding a login domain........................................................................525

Modifying a login domain description.................................................525

Deleting login domains.......................................................................526

Mapping user accounts to login domains...........................................526

System tables for user management.......................................................527

Using a system table to check the properties of a user.....................527

Controlling query execution 529Chapter 14

Query execution overview.......................................................................530

Auditing and monitoring the system........................................................530

16 Data Federator User Guide

Contents

Viewing target tables..........................................................................530

Viewing datasource tables.................................................................530

Querying metadata.............................................................................532

Cancelling a query...................................................................................534

Cancelling a query..............................................................................534

Cancelling all running queries............................................................535

Data types................................................................................................535

Configuring the precision and scale of DECIMAL values returned from

Data Federator Query Server.............................................................535

Viewing system configuration..................................................................543

Statistics on query execution..............................................................543

Statistics on the buffer manager.........................................................543

Queries registered for buffer manager...............................................543

Detailed buffer allocation for operators..............................................543

Statistics on wrapper management....................................................544

Optimizing queries 545Chapter 15

Tuning the performance of Data Federator Query Server.......................546

Updating statistics..............................................................................546

Optimizing access to the swap file.....................................................546

Optimizing memory............................................................................547

Operators that consume memory.......................................................548

Guidelines for using system and session parameters to optimize queries

on large tables....................................................................................548

Managing system and session parameters 553Chapter 16

About system and session parameters...................................................554

Managing parameters using Data Federator Administrator.....................554

Managing parameters using SQL statements.........................................556

List of parameters....................................................................................557

Configuring the working directory............................................................573

Data Federator User Guide 17

Contents

Backing up and restoring data 575Chapter 17

About backing up and restoring data.......................................................576

Starting the Data Federator Backup and Restore tool.............................576

Starting the Backup and Restore tool................................................576

Backing up your Data Federator data......................................................578

Restoring your Data Federator data........................................................578

Deploying Data Federator servers 581Chapter 18

About deploying Data Federator servers.................................................582

Deploying a project on a single remote Query Server.............................582

Possibilities for deploying a project on a single remote instance of Query

Server.................................................................................................583

Configuring Data Federator Designer to connect to a remote Query

Server.................................................................................................584

Sharing Query Server between multiple instances of Designer.........585

Deploying a project on a cluster of remote instances of Query Server....586

Possibilities for deploying a project on a cluster of remote instances of

Query Server......................................................................................587

Starting and stopping Connection Dispatcher.........................................589

Starting Connection Dispatcher when Data Federator Windows Services

are installed........................................................................................589

Starting Connection Dispatcher when Data Federator Windows Services

are not installed..................................................................................590

Starting Connection Dispatcher on AIX, Solaris or Linux...................590

Shutting down Connection Dispatcher when Data Federator Windows

Services are installed.........................................................................591

Shutting down Connection Dispatcher when Data Federator Windows

Services are not installed...................................................................591

Shutting down Connection Dispatcher on AIX, Solaris or Linux........592

Configuring Connection Dispatcher.........................................................593

Setting parameters for Connection Dispatcher..................................593

18 Data Federator User Guide

Contents

Guidelines for using Connection Dispatcher parameters to configure

validity times of references to servers................................................593

Configuring logging for Connection Dispatcher..................................595

Parameters for Connection Dispatcher..............................................596

Managing the set of servers for Connection Dispatcher....................599

Format of the Connection Dispatcher servers configuration file........600

Configuring fault tolerance for Data Federator........................................601

Data Federator Designer reference 603Chapter 19

Using data types and constants in Data Federator Designer..................604

Date formats in Data Federator Designer................................................607

Data extraction parameters for text files..................................................607

Formats of files used to define a schema................................................608

Running a query to test your configuration..............................................614

Query configuration.................................................................................615

Printing a data sheet................................................................................617

Inserting rows in tables............................................................................618

The syntax of filter formulas.....................................................................619

The syntax of case statement formulas...................................................620

The syntax of relationship formulas.........................................................621

Function reference 623Chapter 20

Function reference...................................................................................624

Aggregate functions...........................................................................624

Numeric functions...............................................................................628

Date/Time functions...........................................................................637

String functions...................................................................................647

System functions................................................................................667

Conversion functions..........................................................................671

Data Federator User Guide 19

Contents

SQL syntax reference 687Chapter 21

SQL syntax overview...............................................................................688

Data Federator Query Server query language........................................688

Identifiers and naming conventions....................................................688

Data Federator data types..................................................................696

Expressions........................................................................................701

Comments..........................................................................................706

Statements.........................................................................................706

Data Federator SQL grammar.................................................................713

Syntax key..........................................................................................714

Grammar for the SELECT clause.......................................................715

Grammar for managing users............................................................721

Grammar for managing resources.....................................................725

System table reference 727Chapter 22

System table reference............................................................................728

Metadata system tables.....................................................................729

Function system tables.......................................................................733

User system tables.............................................................................735

Resource system tables.....................................................................739

Other system tables...........................................................................740

Stored procedure reference 743Chapter 23

List of stored procedures.........................................................................744

getTables............................................................................................744

getCatalogs........................................................................................746

getKeys..............................................................................................746

getFunctionsSignatures......................................................................748

getColumns........................................................................................750

20 Data Federator User Guide

Contents

getSchemas.......................................................................................751

getForeignKeys..................................................................................752

refreshTableCardinality.......................................................................754

clearMetrics........................................................................................755

addLoginDomain................................................................................755

delLoginDomains................................................................................756

alterLoginDomain...............................................................................756

getLoginDomains...............................................................................757

addCredential.....................................................................................757

delCredentials....................................................................................758

alterCredential....................................................................................760

getCredentials....................................................................................761

Using patterns in stored procedures........................................................762

Glossary 765Chapter 24

Glossary...................................................................................................766

Terms and descriptions............................................................................766

Troubleshooting 777Chapter 25

Installation................................................................................................778

Installing from remote machine..........................................................778

Input line is too long: error on Windows 2000....................................778

Input line is too long: error on Windows 2000....................................779

McAfee's On-Access Scan.................................................................779

Errors like missing method due to uncleared browser cache after

installation..........................................................................................779

Finding the Connection Dispatcher servers configuration file when running

Connection Dispatcher as a Windows service...................................780

Datasources.............................................................................................781

File on SMB share..............................................................................781

Separators not working......................................................................781

Data Federator User Guide 21

Contents

Cannot edit an existing datasource....................................................782

Connection parameters......................................................................782

Targets.....................................................................................................783

Cannot see any datasources in targets windows...............................783

Mappings.................................................................................................783

Cannot reference lookup table in existing mapping...........................783

Error in formula that uses a BOOLEAN value....................................784

Source relationships introduce cycles in the graph............................784

Table used in mapping rule is no longer available.............................785

Table added to the mapping rule should be core...............................786

Table added to the mapping rule should not be core.........................786

At least one table should be core.......................................................787

Domain tables..........................................................................................787

Cannot remove domain table.............................................................787

Data Federator Designer.........................................................................788

Cannot select a table..........................................................................788

Cannot find column names.................................................................788

Cannot use column or table after changing the source......................789

Data Federator connectors......................................................................789

Exception for entity expansion limit....................................................789

On Teradata V2R6, error Datatype mismatch in the Then/Else

expression..........................................................................................790

Accessing data........................................................................................790

Target tables not accessible on Data Federator Query Server..........790

Target tables not accessible from deployed project on Data Federator

Query Server......................................................................................791

Cannot access CSV files on a remote machine using a generic ODBC

connection..........................................................................................791

Data Federator services..........................................................................792

Starting and stopping services...........................................................792

Networking...............................................................................................792

22 Data Federator User Guide

Contents

Network Connections.........................................................................792

Data Federator logs 795Chapter 26

About Data Federator logs.......................................................................796

Data Federator Designer logs.................................................................796

Data Federator Query Server logs..........................................................796

Activating Data Federator Query Server logs....................................796

Get More Help 799Appendix A

Index 803

Data Federator User Guide 23

Contents

24 Data Federator User Guide

Introduction to Data Federator

The Data Federator application

Data Federator is an Enterprise Information Integration (EII) application that provides a uniform, coherent and integrated view of distributed and heterogeneous data sources. The data sources can be spread across a network, managed by different data management systems and administered under different areas of the organization.

This tool differs in its architecture to ETL (Extract, Transform, Load) tools in that the data it manages is not replicated in another system but optimized in the form of virtual data tables. The virtual database is a collection of relational tables that are manipulated with SQL but do not hold stored data.

Data Federator allows you to consolidate your various data sources into one coherent set of target tables. From these consolidated, virtual, target tables, reporting tools can perform queries and be confident that the data are reliable, trustworthy and up-to-date. For example, you can create a universe using BusinessObjects Designer or create a query directly against the virtual target tables using Crystal Reports.

An answer to a common business problem

Most businesses maintain several data sources that are spread across different departments or sites. Often, duplicate information appears within the various data sources but is cataloged in such a way that makes it difficult to use the data to make strategic decisions or perform statistical analysis.

The following diagram illustrates the classic approach to consolidating data.

26 Data Federator User Guide

What are the challenges?

When your task involves consolidating several disparate data sources, you most likely face the following challenges.

• simplicity and productivity - you want to develop a solution once

Introduction to Data Federator

The Data Federator application

• quality control - you want to ensure that the consolidated data can be

trusted and is correct

• performance - you want to make sure that access to the data is optimized

to produce results quickly

• maintenance - you want to develop a solution that requires little or no

maintenance as new source of data are added or as existing sources change

How can the problem be defined?

When faced with the above challenges, you can define the problem in terms of the following needs.

• need to retrieve the content of each source

• need to aggregate the information relative to the same customer

Data Federator User Guide 27

Introduction to Data Federator

The Data Federator application

• need to reconcile or transform the data to follow a uniform representation

How does Data Federator solve this problem?

The following diagrams illustrate how Data Federator addresses the above needs.

Data Federator operates between your sources of data and your applications. The communication between the data and the Data Federator Query Server takes place by means of "connectors." In turn, external applications query data from Data Federator Query Server by using SQL.

The following diagram shows where Data Federator operates in relation to your sources of data and your applications.

28 Data Federator User Guide

Introduction to Data Federator

Fundamental notions in Data Federator

Internally, Data Federator uses virtual tables and mappings to present the data from your sources of data in a single virtual form that is accessible to and optimized for your applications.

The following diagram shows the internal operation of Data Federator and how it can aggregate your sources of data into a form usable by your applications.

Fundamental notions in Data Federator

You work with Data Federator in two phases:

• design time

• run time

Data Federator User Guide 29

Introduction to Data Federator

Fundamental notions in Data Federator

Design time is the phase of defining a representation of your data, and run time is the phase where you use that representation to query your data.

Data Federator Designer: design time

At design time, you use Data Federator Designer to define a data model, composed of datasource tables and target tables. Mapping rules, domain tables and lookup tables help you to achieve this goal.

The outcome of this phase is a mapping from your datasources to your targets. Your target tables are virtual tables that live inside Data Federator, and they can be queried at run time.

Data Federator Query Server: run time

Once your data model and its associated metadata are in place, your applications can query these virtual tables as a single source of data. Your applications connect to and launch queries against Data Federator Query Server.

Behind the scenes at run time, the Data Federator Query Server knows how to query your distributed data sources optimally to reduce data transfers.

Important terms

The following table lists some of the fundamental terms when working with Data Federator. For a full list of definitions, see Glossary on page 766.

target

30 Data Federator User Guide

DescriptionTerm

This is the database that you create using Data Federator Designer: it consolidates the data of multiple sources into a form that can be used by your applications.

Introduction to Data Federator

Data Federator user interface

DescriptionTerm

target table

datasource

connector

lookup table

A target table is one of the tables that you define in your target.

A datasource is representation of a source of your data, in tabular form. You define a datasource in Data Federator Designer.

A connector is a file that defines your sources of data in a format that Data Federator Query Server understands.

When you use Data Federator Designer to add a datasource, the definition that you make is stored in a configuration file for a connector.

This is a table that typically maps values from one column to a different column. You define it in Data Federator Designer, and you use it when adding mappings.

A mapping is a set of rules that define

mapping

a correspondence between a set of datasource tables and a target table.

Data Federator user interface

The following diagram shows the layout and elements of a Data Federator Designer window.

Data Federator User Guide 31

Introduction to Data Federator

Data Federator user interface

Data Federator Designer maintains a consistent interface for all the components of a Data Federator project.

The main components of the Data Federator Designer user interface are:

• (A) the breadcrumb, showing you the position of the current window in

the tree view

• (B) the tabs, where you navigate among your open projects

• (C) the project toolbar, where you add, import or export projects

• (D) the tree view, where you navigate among the components in your

project

• (E) the main view, where you define your components

• (F) the Save button, which saves the changes you made on the current

window

• (G) the Open button, which lets you open a project from the project

Configuration window

• (H) the Reset button, which resets the changes you made on the current

window

32 Data Federator User Guide

Overview of the methodology

This section introduces the methodology that you can follow to work with Data Federator effectively.

You complete the following steps when working with Data Federator.

1. Add the targets.

2. Add the datasources.

3. Map the datasources to the targets.

4. Check the target data against constraints.

5. Deploy the project.

The following diagram summarizes steps 1-3 above. These steps represent the construction phase in Data Federator Designer, at the end of which Data Federator understands your source data and can present it as a federated view.

Introduction to Data Federator

Overview of the methodology

Data Federator User Guide 33

Introduction to Data Federator

Overview of the methodology

34 Data Federator User Guide

Adding the targets

Adding the targets is a matter of designing the schemas of the tables that your applications will query.

This design is driven by the needs of your applications. You define the target schema by examining what data your applications require, and by implementing this schema as a target table in Data Federator Designer.

Related Topics

• Managing target tables on page 46

Adding the datasources

In Data Federator, you reference your existing sources of data by adding "datasources".

Introduction to Data Federator

Overview of the methodology

Data Federator accepts database management systems and CSV files as sources of data. These sources can be located on different servers at different locations and use different protocols for access.

Depending on the type of source, you define the data access system in which it is stored, you select the capabilities of the data access system, or you describe the data extraction parameters if your source is a text file.

Once you have referenced either a text or database system as a source, Data Federator names it a "datasource". The term "datasource" refers to Data Federator representation of actual source of data. It is this abstraction that lets Data Federator understand the data and perform real-time queries on it.

Related Topics

• About datasources on page 66

• Creating text file datasources on page 158

• Creating generic JDBC or ODBC datasources on page 138

• Configuring a remote Query Server datasource on page 202

Data Federator User Guide 35

Introduction to Data Federator

Overview of the methodology

Mapping datasources to targets

The mapping phase links your datasources to your targets.

During the mapping phase, you can use filters, relationships and formulas to convert values from the ones in your datasources to the ones expected by your targets.

Mapping formulas let you make computations on existing data, in order to convert it to its target form. Data Federator Designer lets you add additional data that does not exist in your datasource by creating lookup tables and domain tables. You can also describe additional logic by adding filters and relationships between datasource tables.

Once the mappings are in place, Data Federator Query Server knows how to transform, in real-time, the data in your datasources into the form required by your targets.

Related Topics

• Mapping datasources to targets process overview on page 216

Checking if data passes constraints

Once your mappings are defined, Data Federator Designer helps you check the validity of the data that results from the mappings.

Data Federator Designer defines several default constraint checks, such as checking that a primary key column never produces duplicate values, or checking that a column marked as "NOT-NULL" does not have any NULLS in it. You can also add custom constraints.

Once your constraints are defined, Data Federator Designer lets you check each mapping, and mark the ones that are producing valid results, in order to refine the rules so that they are ready for production.

Related Topics

• Testing mapping rules against constraints on page 294

• Defining constraints on a target table on page 294

36 Data Federator User Guide

Deploying the project

When your mappings are tested in Data Federator Designer, you can deploy your project on Data Federator Query Server.

When you deploy your project, its tables are usable by applications that send queries to Data Federator Query Server.

Related Topics

• Managing a project and its versions on page 308

Introduction to Data Federator

Overview of the methodology

Data Federator User Guide 37

Introduction to Data Federator

Overview of the methodology

38 Data Federator User Guide

Starting a Data Federator project

Working with Data Federator

To start working with Data Federator, you create a "project" in Data Federator Designer.

A "project" is a workspace containing all the components used by Data Federator: targets, datasources, mappings, lookup tables, domain tables, and constraint checks. Each project has versions, and each version is either:

• in development

• deployed

While you work on a project, it is considered to be in development. When you are ready to put your work into production, you deploy the project. Once you deploy a project, it becomes a catalog on Data Federator Query Server, and other applications can send queries to it.

Login and passwords for Data Federator

The default user name is sysadmin.

The default password is sysadmin.

You should use Data Federator Administrator to change the login parameters after installation.

Related Topics

• Starting Data Federator Administrator on page 384

Adding new users

You can add new users to Data Federator using Data Federator Administrator.

Related Topics

• Data Federator Administrator overview on page 384

• Starting Data Federator Administrator on page 384

40 Data Federator User Guide

Starting a project

To start a project in Data Federator Designer, you add a project and then open the project.

Related Topics

• Adding a project on page 41

• Opening a project on page 41

• Managing a project and its versions on page 308

Adding a project

To define targets, datasources and mappings, you must add a project to the Data Federator list of projects.

When you add a project, it appears in the Data Federator list of projects, and you can switch between different projects.

1. At the top of the window, click Projects.

2. Click Add project.

The New project window appears.

Starting a Data Federator project

Starting a project

3. Enter a name and description for the project in the Project name and

Description fields, and click Save.

Data Federator adds the project to the list of projects.

Opening a project

You can only open a project that is not locked by another user account. If it is locked, wait for the other user account to unlock the project, or wait until the other user account's session expires.

In order to work on your targets, datasources and mappings, you must open a project. You open projects from the Projects tab.

1. At the top of the window, click the Projects tab.

2. In the tree list, click your-project-name.

The "Configuration" window appears.

Data Federator User Guide 41

Starting a Data Federator project

Starting a project

3. Click Open.

The your-project-name tab appears.

4. Click the your-project-name tab.

The latest version of your project opens.

Once your project is open, you can add targets, datasources and mappings to it.

Related Topics

• Unlocking projects on page 43

• Managing target tables on page 46

• About datasources on page 66

• Creating database datasources using resources on page 72

• Mapping datasources to targets process overview on page 216

• Opening multiple projects on page 319

Deleting Data Federator projects

You can only open a project that is not locked by another user account. If it is locked, wait for the other user account to unlock the project, or wait until the other user account's session expires.

1. Click the Projects tab.

2. Click the Delete this project icon.

Related Topics

• Unlocking projects on page 43

• Managing a project and its versions on page 308

Closing Data Federator projects

1. Click the your-project-name tab.

2. Click the Close this project icon.

42 Data Federator User Guide

The project closes and becomes unlocked for other user accounts.

Related Topics

• Unlocking projects on page 43

• Managing a project and its versions on page 308

Unlocking projects

When you open a project, Data Federator Designer locks it. When other user accounts try to access the project, Data Federator refuses, and indicates that it is locked by your user account.

To unlock a project, the user account that locked it must log in and close the project.

If the password for the user account that locked the project is lost, the system administrator can reset it. You can the log in using the user account that locked the project, and unlock it.

Starting a Data Federator project

Starting a project

If you open the same project on two machines with the same user account, the last machine will lock the project. If you return to the first machine, the project will be open, but you will not be able to save your changes. In this case, you will have to decide if you want to keep the changes you made on the first machine or on the second machine.

Data Federator also automatically unlocks the project after the session timeout value expires. This value is set to 30 minutes.

Related Topics

• Closing Data Federator projects on page 42

• Managing a project and its versions on page 308

Data Federator User Guide 43

Starting a Data Federator project

Starting a project

44 Data Federator User Guide

Creating target tables

Managing target tables

Target tables are the Data Federator tables that you create to present data in the correct format to your external applications.

You define target tables in the Data Federator Designer user interface. Once you have defined the target tables and deployed your project, the Data Federator server (Data Federator Query Server) exposes your tables to your other applications.

Adding a target table manually

1. Select Add a new target table from the Add drop-down arrow.

The New target table window appears.

2. Type a name for the table in the Table name field, and a description in

the Description field.

3. Click Add columns, then click the number of columns that you want to

add.

Empty rows appear in the Table schema pane. Each row lets you define one column.

You can add rows repeatedly.

4. Fill in each row in the Table schema pane with the name and type of the

column that you want to add.

5. Click Save.

Your target table appears in the Target tables tree list.

Related Topics

• Inserting rows in tables on page 618

• Adding a mapping rule for a target table on page 217

• Using data types and constants in Data Federator Designer on page 604

46 Data Federator User Guide

Adding a target table from a DDL script

This procedure shows you how to add a target table by opening a file that contains a DDL script.

1. Select Add target tables from DDL script from the Add drop-down

arrow.

The Import a DDL script window appears.

2. Import a DDL script in one of the following ways:

• Click Import a DDL script, then click Browse and select a file that

contains a DDL script that defines a table.

• Click Manual input, then type in a DDL script in the text box.

3. Click Save.

Data Federator Designer executes your DDL script and adds a new table.

Related Topics

• Adding a mapping rule for a target table on page 217

• Formats of files used to define a schema on page 608

Creating target tables

Managing target tables

Adding a target table from an existing table

This procedure shows you how to create a new target table by copying an existing table or datasource.

1. Select Add target table from existing table from the Add drop-down

arrow.

The Add target table from exising table window appears.

2. Expand the Tables tree list and select the target table to be added.

The name of the selected table appears in the Replace with table field.

3. Click the table that you want to use.

The name of the selected table appears in the Selected table field, with copyOf_your-table-name in the New target table's name field.

Data Federator User Guide 47

Creating target tables

Managing target tables

4. If you want to create a default mapping rule for your new target table,

select the Create default mapping rule check box. A default mapping rule maps each column of your new table to its

corresponding column in the original table.

5. Click Save.

The Target tables window is displayed showing all created target tables.

6. Click copyOf_your-table-name in the tree list.

The copyOf_your-table-name window appears, showing the columns copied from your original table to your new target table.

7. Modify the columns as required, and click Save when done.

Related Topics

• Adding a mapping rule for a target table on page 217

Changing the name of a target table

You can change the name of a target table from the Target tables > your- target-table-name window.

1. In the tree list, click your-target-table-name.

2. In the General pane, type the new name of your target table.

3. Click Save.

The name of the target table changes.

Displaying the impact and lineage of target tables

1. Open your target table.

2. Click Impact and Lineage.

The Impact and lineage pane for your target table expands and appears.

Related Topics

• How to read the Impact and lineage pane in Data Federator Designer on

page 52

48 Data Federator User Guide

Managing target tables

Details on configuring target table schemas

This section describes the options you have when you are defining the schema of a target table. You can use this information while adding a target table manually.

Table 3-1: Description of the Table schema pane

DescriptionOption

allows you to select a row, if you want

Select

to move it or add a new row before it (see Inserting rows in tables on page 618)

Creating target tables

Column name

Type

Domain table

Key icon

lets you enter the name of a column of the target table

the data type of the column (see Us-

ing data types and constants in Data Federator Designer on page 604)

if the data type is enumerated, this box lets you choose the domain table that contains the allowed values for this column (see Managing domain

tables on page 54)

specifies if the column is the key, or part of the key, of the target table

Data Federator User Guide 49

Creating target tables

Determining the status of a target table

DescriptionOption

Not-null

Input column icon

Description icon

Delete icon

Related Topics

• Adding a target table manually on page 46

specifies if the values in this column must not be NULL

if checked, Data Federator refuses to answer queries on this table unless the querying application supplies a value for this column

allows you to enter a description of the column

deletes the row

Determining the status of a target table

Data Federator displays the current status of each of your targets. You can use this status to learn if you have entered all of the information that Data Federator needs to use the target.

Each target goes through the statuses:

• incomplete

(Data Federator does not show this status in the interface. All new targets are put in this status.)

50 Data Federator User Guide

Creating target tables

Determining the status of a target table

• mapped

The status is shown in the Target tables > your-target-table- name>your-mapping-rule-name window.

This table shows what to do for each status of the target life cycle.

you can do this...means...The status...

Deactivate the mapping

incomplete

Not all of the active mapping rules in the target are complete.

rules.

Make the active mapping rules complete.

All of the active mapping

mapped

Related Topics

• Deactivating a mapping rule on page 280

• Mapping values using formulas on page 222

• Testing mappings on page 280

• Deploying a version of a project on page 324

rules in the target are complete.

•

Test the mapping rules.

Data Federator User Guide 51

Creating target tables

How to read the Impact and lineage pane in Data Federator Designer

Table 3-3: How to read the Impact and lineage pane in Data Federator Designer

DescriptionElement

Each box represents a component of your Data Federator Designer project.

boxes

A box can be a target table, a final datasource table, a mapping rule, a domain table or a lookup table.

Lineage tab

arrows

Impact tab

arrows

52 Data Federator User Guide

On the Lineage tab, the arrows show where data comes from.

Each arrow points to the component from which the first component gets its data.

On the Impact tab, the arrows show where data goes.

Each arrow points to the component to which the first component provides data.

Testing targets

To test a target, you must verify if the information you entered allows Data Federator to correctly populate the target tables.

You can encounter the following problems:

• You have written mapping formula that maps the wrong value.

• Your mapping formulas do not result in sufficient information for your

target columns.

• Your mapping formulas result in null values in columns that must not be

NULL.

Data Federator lets you test a target by using the Target table test tool pane.

Testing a target

The target must have the status "mapped" (see Determining the status of a

target table on page 50).

Creating target tables

Testing targets

You can run a query on a target to test that all of its mapping rules are mapping values correctly and consistently.

1. In the tree list, click your-target-table-name.

The Target tables > your-target-table-name window appears.

2. In the Target table test tool pane, click View data to see the query

results.

For details on running the query, see Running a query to test your

configuration on page 614.

For details on printing the results of the query, see Printing a data sheet on page 617.

Data Federator displays the data in columns in the Data sheet frame.

3. Verify that the values appear correctly.

Otherwise, try adjusting the mapping rules in the target again.

Data Federator User Guide 53

Creating target tables

Managing domain tables

Example tests to run on a mapping rule

Tip:

Example tests to perform on your mapping rule

• Fetch the first 100 rows.

Run a query, as in Testing a mapping rule on page 281, and select the Show total number of rows only check box.

The number of rows will appear above the query results.

• Fetch a single row.

For example, if you have a target table with a primary key of client_id in the range 6000000-6009999, type:

client_id=6000114

in the Filter box.

Click View data, and verify the value of each column with the data in your datasource table.

• Verify that the primary key columns are never NULL.

Type the formula:

client_id <> NULL

If any of the returned columns are NULL, verify that your mapping rule does not insert NULL values.

Managing domain tables

In Data Federator, domain tables are tables that have the following properties:

• Like datasource tables, domain tables hold columns of data.

• Unlike datasource tables, the data in a domain table is stored on the Data

Federator Query Server.

54 Data Federator User Guide

Creating target tables

Managing domain tables

• Unlike datasource tables, you can use a domain table to create an

enumeration to be used in a target table (see Using a domain table as

the domain of a column on page 62).

• Domain tables support up to 5000 rows.

• You can combine a domain table with a lookup table to map the values

in a datasource column to the values in a domain table (see Using lookup

tables on page 242).

You create domain tables when you want make an enumeration available for a column in one of your target tables.

You can also use a domain table to constrain the values in the column of a target table. See Using a domain table to constrain possible values on page 263.

Adding a domain table to enumerate values in a target column

The following procedure is an example of a domain table that you can use to enumerate a list of values for a column called marital_status. The list in this example contains a code for each marital status.

In this example, the list contains:

• SE (to represent single)

• MD (married)

• DD (divorced)

• WD (widowed)

1. Click Add > Add domain table.

The "New domain table" window appears.

2. In the Table name field, type a name for your new domain table.

3. In the "Table schema" pane, click Add columns, then click 1 column to

add one column.

One empty row appears in the "Table schema" pane.

4. Complete the row with the following values:

Data Federator User Guide 55

Creating target tables

Managing domain tables

In the Column name box, type marital_status.•

• In the Type box, type String.

•

In the key column (key icon ), select the key check box.

5. Click Save.

The your-domain-table-name window appears.

6. In the "Table contents" pane, click Add, then click Add again.

The "Add rows" window appears, showing one empty row with the columns that you defined.

7. Click Add rows, then click 3 rows to add three more rows.

8. In the field that you named marital_status, enter the values:

• SE

• MD

• DD

• WD

9. Click Save.

The "Update report" window appears.

10. Click Close.

The your-domain-table-name window appears, showing your new table with the values you entered. You can now use this domain table to define a set of values for a column in a target table.

Related Topics

• Using data types and constants in Data Federator Designer on page 604

Examples of domain tables

This section shows some examples of domain tables that you can use in different cases.

56 Data Federator User Guide

Creating target tables

Managing domain tables

Example: Single-column domain table used as an enumeration

You can use this type of table to enumerate the values in the column of a target table (see Using a domain table as the domain of a column on page 62)

marital_status

Example: Two-column domain table used as an enumeration, with

descriptions

You can use this type of table to enumerate the values in the column of a target table, and add a description to each value. You can use the descriptions to make the corresponding values easier to remember.

See Using a domain table as the domain of a column on page 62.

marital_status_descriptionmarital_status

singleSE

marriedMD

Data Federator User Guide 57

Creating target tables

Managing domain tables

marital_status_descriptionmarital_status

divorcedDD

widowedWD

Example: Four-column domain table with a relationship between the

columns

You can use this type of table in the following situation:

•

You want to enumerate the values of one column of a target table.

•

The column is related to another column, and you want to represent this relationship.

In this example, you could use department_code as the domain of a column called "department" in your target table, and you could populate the first column called "division" based on the value of department_code.

See Using a domain table as the domain of a column on page 62.

division_code

58 Data Federator User Guide

division_code_ description

research and development

department_ code

department_ code_ description

benefitsD101human resourcesHR

new hiresD102human resourcesHR

serversD111

Creating target tables

Managing domain tables

department_ code_ description

workstationsD112

North AmericaD121marketingMKTG

North AmericaD231salesSLS

GlobalD241purchasingPRCH

division_code

division_code_ description

research and development

department_ code

Adding a domain table by importing data from a file

• You must have created a text file containing the domain data. The file

must be in comma-separated value (CSV) format, as in the example above.

•

For details on data types that you can use, see Using data types and

constants in Data Federator Designer on page 604.

If you have a lot of domain data, you can enter it into your domain table quickly by importing the data from a text file.

For example, Data Federator can import domain data such as the following.

file: my-domain-data.csv "1";"single" "2";"married" "3";"divorced" "4";"widowed"

1. Add a domain table.

Data Federator User Guide 59

Creating target tables

Managing domain tables

2. Add a datasource that points to the file from which you want to import.

3. When the Domain tables > your-domain-tablewindow appears, click

Add, then click Add from datasource table.

The Domain tables > your-domain-table > Add rows from a datasource window appears.

4. Refer to the Select a datasource table field and select the datasource

table to be added to the domain table.

The columns of the selected datasource table are displayed in the Select a subset of columns field on the right. You can, if required, select one or all of the columns in this field and click View Data to display the contents of the selected columns.

5. Refer to the Domain columns mapping pane and map the required

datasource column from each domain table column's drop-down list-box.

6. Click Save.

The Domain tables > your-domain-table-name > Update report window is displayed and your file's imported data is added to your domain table.

Related Topics

• Creating text file datasources on page 158

• Adding a domain table to enumerate values in a target column on page 55

Dereferencing a domain table from your target table

1. Edit your target table.

2. In the Table Schema pane, find a column that references your domain

table and select String under the Type column.

Do this for each column that is of type Enumerated, and that references your domain table.

3. Click Save.

Related Topics

• Adding a target table manually on page 46

60 Data Federator User Guide

Exporting a domain table as CSV

• You must have added a domain table.

See Managing domain tables on page 54.

1. In the tree list, click Domain tables.

The Domain tables window appears.

2. Select the table you want to export as CSV.

The Domain tables > your-domain-table-name window appears.

3. Click Export.

The File download window appears giving you the option of opening or saving your Domain_your-domain-table-name.csv file.

4. Click Save and save the .csv file to a location of your choosing.

Deleting a domain table

Creating target tables

Using domain tables in your target tables

To delete a domain table, you must first remove references to it from any lookup and target tables.

1. In the tree list, click Domain tables.

2. Select the tables that you want to delete.

3. Click Delete, and click OK to confirm.

Related Topics

• Dereferencing a domain table from your target table on page 60

• Dereferencing a domain table from a lookup table on page 251

Using domain tables in your target tables

This section describes how to use domain tables. You can use domain tables to enumerate the values of a column in your target table.

Data Federator User Guide 61

Creating target tables

Using domain tables in your target tables

Using a domain table as the domain of a column

•

You must have created a domain table as described in Managing domain

tables on page 54.

This procedure shows how to use the values that you entered in a domain table as the values that can appear in a column of your target table.

Add a target table. See Managing target tables on page 46.

2. When the Target tables > New target table window appears, click Add

columns, then, from the list, click 1.

An empty row appears in the Target schema pane.

3. In the Column name box, type a name for your column.

4. In the Type list, click Enumerated.

An edit icon appears beside the Type box.

5. Click the Edit icon

The Target tables > New target table > Domain constraint table 'your- column-name' window appears.

This window shows a list of your domain tables.

6. In the list, expand the name of your domain table, then click the column

that you want to use as the domain.

For example, if your domain table contains the columns marital_code, and marital_code_description, click marital_code.

The name of the domain table appears in the Selected table box. The name of the column appears in the Selected column box.

7. Click Save.

The "Target tables > New target table" window appears.

62 Data Federator User Guide

Creating target tables

Using domain tables in your target tables

The name of the domain table and domain column that you selected appears in the Domain table box in the row that defines your new target column.

When you choose values for this column in Data Federator Designer, only the values in domain table will appear.

To associate a set of enumerated values in your datasource to a set of enumerated values in your target, see The process of adding a lookup

table between columns on page 243.

To constraing rows in your datasource to those whose values match a set of enumerated values in your target, see Using a domain table to

constrain possible values on page 263.

Data Federator User Guide 63

Creating target tables

Using domain tables in your target tables

64 Data Federator User Guide

Defining sources of data

About datasources

Data Federator projects use datasources to access a project's sources of data. A datasource is a pointer that points to and represents the data that is kept in a source. For example, this could be a relational database in which you store customer data. A datasource can also point to a text file, for example in which you keep sales information.

Datasources are a basic component of Data Federator. A datasource consists of a table, or a set of tables. Once you define a datasource, you can connect your project to the datasource, and populate your target tables with the data.

Datasources that you can create fall into the following categories:

• Databases are datasources that represent databases such as Oracle,

Access and DB2. Data Federator includes pre-defined resources that you can use to help configure your datasource to achieve the best performance.

This category includes relational databases that use JDBC drivers, ODBC drivers, and openclient drivers.

• Text file datasources provide access to data held in text files, for example

comma-separated value (.csv) files.

• XML/web datasources provide access to data held in XML files, or data

provided by web services.

• A Remote Query Server datasource uses a remote Data Federator Query

Server as a source of data.

Related Topics

• Creating generic JDBC or ODBC datasources on page 138

• Generic and pre-defined datasources on page 71

• Creating remote Query Server datasources on page 202

• Creating text file datasources on page 158

• Adding an XML file datasource on page 179

• Adding a web service datasource on page 183

66 Data Federator User Guide

Datasource user interface

The following diagram shows what you see in Data Federator Designer when you work with datasources.

Defining sources of data

About datasources

The main components of the datasource user interface are:

• (A) the tree view, where you navigate among your datasources

• (B) the main view, where you define your datasources

• (C) collapsed nodes in the tree, each representing one datasource

• (D) an expanded node, showing a datasource with two statuses: a draft

and a final

• (E) a pane, showing parameters for a datasource

Draft and Final datasources

When you create a new datasource, Data Federator marks its status as Draft, to indicate that the definition is incomplete. In order to use your datasource in a mapping, when you finalize the definition, you must make it Final.

• Draft: A datasource is a draft when you first create it. When a datasource

is a draft, you can modify it, but you cannot use it in a mapping.

Data Federator User Guide 67

Defining sources of data

About datasources

The datasource appears under Draft in the tree list.

A draft has two statuses: Incomplete and Complete.

• Incomplete: Certain configuration parameters have not been filled in.

The values are either not complete or they are invalid.

• Complete: All necessary configuration parameters are filled in and

are valid.

• The datasource passes automatically from Incomplete to

Complete as soon as you fill in the required configuration parameters correctly.

• The datasource passes automatically from Complete to

Incomplete if you replace a correct value with an incorrect one. This is also the case when you add a new table in which the required parameters are not filled in correctly.

• Final: A datasource is final when you click Make Final.

When a datasource is Final, you cannot modify it, but you can use it in a mapping.

The datasource appears under Final in the tree list.

68 Data Federator User Guide

Table 4-1: Summary of the life cycle of a datasource

Draft,

Incomplete

Some datasource definition and schema definition parameters are invalid.

Defining sources of data

About datasources

you can do this...means...The version...

Modify the datasource configuration.

The symbols

indicate invalid parameters.

The datasource table schema is incomplete.

Draft,

Complete

Final

Related Topics

• Setting a text file datasource name and description on page 159

• Creating generic JDBC or ODBC datasources on page 138

Datasource definition and schema definition parameters are complete and valid.

All the datasource table schemas are defined.

The datasource appears in the datasource tree list under Final.

Define the datasource table schema.

Test the datasource configuration.

If all the datasource tables have been added, make the datasource Final.

If you need to change a Final datasource, you must copy it to a Draft first.

Data Federator User Guide 69

Defining sources of data

About datasources

• Defining the schema of a text file datasource manually on page 172

• Running a query on a datasource on page 211

• Making your datasource final on page 212

• Editing a final datasource on page 213

About configuration resources

The Data Federator software includes pre-defined configuration resources that you can use to create datasources. For example, the Data Federator software includes resources for databases including the following:

• Oracle

• MySQL

• SQL Server

• DB2

• Microsoft Access

Using Data Federator Administrator, you can:

• Modify a pre-defined resource to change the configurations of all

datasources that use it

• Copy a pre-defined resource, and use the copy as the base for a new

resource

• Create a new resource

Once you have created a resource in Administrator, you can use it to create datasources in Designer.

In addition to using pre-defined resources, for a datasource, you can configure a generic JDBC connection. Unlike resources, this can only be used by the datasource for which it is created.

There are three types of resources:

• JDBC resources provide access through JDBC. These are used for

databases such as Access, Oracle, and DB2.

• ODBC resources provide access through ODBC. These are used for

databases such as Netezza, Terradata, and Informix.

70 Data Federator User Guide

• Openclient resources are used for databases such as Sybase.

Generic and pre-defined datasources

A generic datasource is a connection configuration that you create in Data Federator Designer. You do not require Administrator access to define a generic datasource.

A generic datasource differs from a pre-defined resource in the following ways:

• Performance: The performance of a generic datasource is not as efficient

as with a pre-defined resource:

• With a generic datasource, a large degree of data processing is

performed by the Data Federator application software.

• For pre-defined resources, as much processing as possible is handled

by the database software. This results in better performance. In addition, for pre-defined datasources, the connection parameters have been optimized and tested for maximum performance.

Defining sources of data

About datasources

• Datasource availability:

• A generic datasource does not use a configured resource. You have

to re-enter all the configuration parameters every time you create a new generic JDBC datasource.

• You can use a pre-defined resource configuration for multiple

datasources.

Related Topics

• About datasources on page 66

• Configuration parameters for generic JDBC and ODBC datasources on

page 150

• Creating generic JDBC or ODBC datasources on page 138

• Managing resources using Data Federator Administrator on page 483

Data Federator User Guide 71

Defining sources of data

Creating database datasources using resources

To create a database datasource, you can:

• Use a resource definition to set the configuration parameters. A resource

can be used in multiple datasources across multiple projects.

Pre-defined resource definitions are supplied with the Data Federator sortware, and you can create custom resources to use with Data Federator Administrator

• Configure a new, generic JDBC or ODBC datasource. Unlike resources,

the configuration can only be used with the datasource for which it is created.

Related Topics

• Creating JDBC datasources from custom resources on page 133

Adding Access datasources

To create a datasource for Access:

• Ensure that the connector for Access is configured. Usually, your Data

Federator administrator configures the connectors.

• Ensure that you have the necessary drivers installed for Access. Installing

drivers is the minimal part of configuring connectors. It is also done by your Data Federator administrator.

• Ensure that you have the necessary parameters to indicate how to connect

to the database, for example the name of the machine where the database is running. These are also available from your Data Federator administrator.

1. Open the project to which you want to add the datasource, and at the top

of the Data Federator Designer screen, click Add, and from the pull-down list, click Add datasource.

The "New Datasource" screen is displayed.

72 Data Federator User Guide

Defining sources of data

Creating database datasources using resources

2. Enter a name and description for your datasource, and expand the

Datasource Type pull-down list.

The datasource options are displayed

3. From the list, select Access, and click Save.

The "Draft" configuration screen is displayed.

4. In the Connection parameters pane, from the Defined resource

drop-down list, select the name of the resource that defines the parameters for your Access database system.

The resource that you choose depends on the parameters that your Data Federator administrator configured for your Access database. If you are not sure which resource to choose, ask your Data Federator administrator.

5. On the "Draft" screen, configure the parameters. Refer to the information

about connection parameters for Access datasources for details.

You can use the parameters defined in a deployment context as values in these fields.

6. Add the datasource tables to your datasource. Refer to the information

on adding tables to database datasources for details.

7. Click Save.

Your Access datasource is added.

Related Topics

• Managing login domains on page 525

• Adding tables to a relational database datasource on page 157

• Defining a connection with deployment context parameters on page 156

• Testing and finalizing datasources on page 210

Data Federator User Guide 73

Defining sources of data

Creating database datasources using resources

Connection parameters for Access datasources

DescriptionParameter

The method to use to authenticate users' login credentials:

•

Authentication mode

Use a specific database logon for all Data Federator users

Data Federator connects to the database using the username and password that you enter. For each user, Data Federator uses the same username and password.

Use the Data Federator logon

Data Federator connects to the datasource using the username and password used to log in to Data Federator.

Defined resource

74 Data Federator User Guide

•

Use a Data Federator login domain

Data Federator connects to the datasource by mapping Data Federator users to database users.

Data Federator uses potentially different usernames and passwords for all Data Federator users, depending on how you or your administrator have set up the login domains.

The Data Federator resource that holds the configuration information that you want to use.

Defining sources of data

Creating database datasources using resources

DescriptionParameter

The name that your Data Federator installation uses to refer to a database server or set of servers on which you can log in. Your Data Federator administrator chose this name when adding login domains.

ODBC DSN

Password

Table types

The ODBC Data Source Name to use.

The password that Data Federator enters for the username.

•

TABLE and VIEW

Choose this to see both tables and views when you click View tables.

•

TABLE

Choose this to see only tables when you click View tables.

•

VIEW

Choose this to see only views when you click View tables.

•

ALL

Choose this to avoid filtering the objects that you see in the database. When you click View tables, you will see all objects.

Data Federator User Guide 75

Defining sources of data

Creating database datasources using resources

DescriptionParameter

User Name

Related Topics

• Managing login domains on page 525

• Mapping user accounts to login domains on page 526

The username that Data Federator uses to connect to the source of data

Connection parameters in Access datasources that can use deployment context parameters

For this datasource type, you can use deployment context parameters for the following fields.

• ODBC DSN

• Password

• User Name

To use a deployment context parameter in a datasource definition field, use the syntax:

${parameter}

where parameter is the deployment context parameter that you want to use.

Related Topics

• Defining a connection with deployment context parameters on page 156

Adding DB2 datasources

To create a datasource for DB2:

• Ensure that the connector for DB2 is configured. Usually, your Data

Federator administrator configures the connectors.

76 Data Federator User Guide

Defining sources of data

Creating database datasources using resources

• Ensure that you have the necessary drivers installed for DB2. Installing

drivers is the minimal part of configuring connectors. It is also done by your Data Federator administrator.

• Ensure that you have the necessary parameters to indicate how to connect

to the database, for example the name of the machine where the database is running. These are also available from your Data Federator administrator.

1. Open the project to which you want to add the datasource, and at the top

of the Data Federator Designer screen, click Add, and from the pull-down list, click Add datasource.

The "New Datasource" screen is displayed.

2. Enter a name and description for your datasource, and expand the

Datasource Type pull-down list.

The datasource options are displayed

3. From the list, select DB2, and click Save.

The "Draft" configuration screen is displayed.

4. In the Connection parameters pane, from the Defined resource

drop-down list, select the name of the resource that defines the parameters for your DB2 database system.

The resource that you choose depends on the parameters that your Data Federator administrator configured for your DB2 database. If you are not sure which resource to choose, ask your Data Federator administrator.

5. On the "Draft" screen, configure the parameters. Refer to the information

about connection parameters for DB2 datasources for details.

You can use the parameters defined in a deployment context as values in these fields.

6. Add the datasource tables to your datasource. Refer to the information

on adding tables to database datasources for details.

7. Click Save.

Your DB2 datasource is added.

Related Topics

• Managing login domains on page 525

• Adding tables to a relational database datasource on page 157

Data Federator User Guide 77

Defining sources of data

Creating database datasources using resources

• Defining a connection with deployment context parameters on page 156

• Testing and finalizing datasources on page 210

Connection parameters for DB2 datasources

DescriptionParameter

The method to use to authenticate users' login credentials:

•

Use a specific database logon for all Data Federator users

Data Federator connects to the database using the username and password that you enter. For each user, Data Federator uses the same username and password.

•

Use the Data Federator logon

Authentication mode

78 Data Federator User Guide

Data Federator connects to the datasource using the username and password used to log in to Data Federator.

•

Use a Data Federator login domain

Data Federator connects to the datasource by mapping Data Federator users to database users.

Data Federator uses potentially different usernames and passwords for all Data Federator users, depending on how you or your administrator have set up the login domains.

Defining sources of data

Creating database datasources using resources

DescriptionParameter

Database name

Defined resource

Host name

Password

Port

The name of the database to which to connect.

The Data Federator resource that holds the configuration information that you want to use.

The name of the host where the database is located.

The password that Data Federator enters for the username.

The port to which to connect.

Prefix table names with the schema name

Specifies if Data Federator should add the name of the schema in its SQL queries to this JDBC data source.

You can select this option only if you are using a JDBC data source that can use the schema name in queries, such as Oracle.

Data Federator User Guide 79

Defining sources of data

Creating database datasources using resources

Schema

DescriptionParameter

The names of the schemas of tables that you want to use, separated by commas.

The % character (percent) means "all schemas".

If you use multiple schemas, you should use the option Prefix table names with the schema name to distinguish tables from different schemas.

•

TABLE and VIEW

Choose this to see both tables and views when you click View

tables.

Table types

User Name

80 Data Federator User Guide

•

TABLE

Choose this to see only tables when you click View tables.

•

VIEW

Choose this to see only views when you click View tables.

•

ALL

Choose this to avoid filtering the objects that you see in the database. When you click View tables, you will see all objects.

The username that Data Federator uses to connect to the source of data

Defining sources of data

Creating database datasources using resources

Related Topics

• Managing login domains on page 525

• Mapping user accounts to login domains on page 526

Connection parameters in DB2 datasources that can use deployment context parameters

For this datasource type, you can use deployment context parameters for the following fields.

• Database name

• Host name

• Password

• Port

• Schema

• User Name

To use a deployment context parameter in a datasource definition field, use the syntax:

${parameter}

where parameter is the deployment context parameter that you want to use.

Related Topics

• Defining a connection with deployment context parameters on page 156

Adding Informix datasources

To create a datasource for Informix:

• Ensure that the connector for Informix is configured. Usually, your Data

Federator administrator configures the connectors.

• Ensure that you have the necessary drivers installed for Informix. Installing

drivers is the minimal part of configuring connectors. It is also done by your Data Federator administrator.

Data Federator User Guide 81

Defining sources of data

Creating database datasources using resources

• Ensure that you have the necessary parameters to indicate how to connect

to the database, for example the name of the machine where the database is running. These are also available from your Data Federator administrator.

1. Open the project to which you want to add the datasource, and at the top

of the Data Federator Designer screen, click Add, and from the pull-down list, click Add datasource.

The "New Datasource" screen is displayed.

2. Enter a name and description for your datasource, and expand the

Datasource Type pull-down list.

The datasource options are displayed

3. From the list, select Informix, and click Save.

The "Draft" configuration screen is displayed.

4. In the Connection parameters pane, from the Defined resource

drop-down list, select the name of the resource that defines the parameters for your Informix database system.

The resource that you choose depends on the parameters that your Data Federator administrator configured for your Informix database. If you are not sure which resource to choose, ask your Data Federator administrator.

5. On the "Draft" screen, configure the parameters. Refer to the information

about connection parameters for Informix datasources for details.

You can use the parameters defined in a deployment context as values in these fields.

6. Add the datasource tables to your datasource. Refer to the information

on adding tables to database datasources for details.

7. Click Save.

Your Informix datasource is added.

Related Topics

• Managing login domains on page 525

• Adding tables to a relational database datasource on page 157

• Defining a connection with deployment context parameters on page 156

• Testing and finalizing datasources on page 210

82 Data Federator User Guide

Creating database datasources using resources

Connection parameters for Informix datasources

DescriptionParameter

The method to use to authenticate users' login credentials:

•

Use a specific database logon for all Data Federator users

Data Federator connects to the database using the username and password that you enter. For each user, Data Federator uses the same username and password.

•

Use the Data Federator logon

Data Federator connects to the datasource using the username

Authentication mode

and password used to log in to Data Federator.

Defining sources of data

Defined resource

•

Use a Data Federator login domain

Data Federator connects to the datasource by mapping Data Federator users to database users.

Data Federator uses potentially different usernames and passwords for all Data Federator users, depending on how you or your administrator have set up the login domains.

The Data Federator resource that holds the configuration information that you want to use.

Data Federator User Guide 83

Defining sources of data

Creating database datasources using resources

DescriptionParameter

The middleware type, for example JDBC or ODBC.

Network layer

ODBC DSN

Password

Prefix table names with the schema name

Note:

Data Federator inserts this value when you select the Defined Re- source, and it cannot be changed.

The ODBC Data Source Name to use.

The password that Data Federator enters for the username.

Specifies if Data Federator should add the name of the schema in its SQL queries to this JDBC data source.

You can select this option only if you are using a JDBC data source that can use the schema name in queries, such as Oracle.

84 Data Federator User Guide

Schema

Defining sources of data

Creating database datasources using resources

DescriptionParameter

The names of the schemas of tables that you want to use, separated by commas.

The % character (percent) means "all schemas".

If you use multiple schemas, you should use the option Prefix table names with the schema name to distinguish tables from different schemas.

User Name

Related Topics

• Managing login domains on page 525

• Mapping user accounts to login domains on page 526

The username that Data Federator uses to connect to the source of data

Connection parameters in Informix datasources that can use deployment context parameters

For this datasource type, you can use deployment context parameters for the following fields.

• ODBC DSN

• Password

• Schema

• User Name

To use a deployment context parameter in a datasource definition field, use the syntax:

${parameter}

Data Federator User Guide 85

Defining sources of data

Creating database datasources using resources

where parameter is the deployment context parameter that you want to use.

Related Topics

• Defining a connection with deployment context parameters on page 156

Adding MySQL datasources

To create a datasource for MySQL:

• Ensure that the connector for MySQL is configured. Usually, your Data

Federator administrator configures the connectors.

• Ensure that you have the necessary drivers installed for MySQL. Installing

drivers is the minimal part of configuring connectors. It is also done by your Data Federator administrator.

• Ensure that you have the necessary parameters to indicate how to connect

to the database, for example the name of the machine where the database is running. These are also available from your Data Federator administrator.

1. Open the project to which you want to add the datasource, and at the top

of the Data Federator Designer screen, click Add, and from the pull-down list, click Add datasource.

The "New Datasource" screen is displayed.

2. Enter a name and description for your datasource, and expand the

Datasource Type pull-down list.

The datasource options are displayed

3. From the list, select MySQL, and click Save.

The "Draft" configuration screen is displayed.

4. In the Connection parameters pane, from the Defined resource

drop-down list, select the name of the resource that defines the parameters for your MySQL database system.

The resource that you choose depends on the parameters that your Data Federator administrator configured for your MySQL database. If you are not sure which resource to choose, ask your Data Federator administrator.

5. On the "Draft" screen, configure the parameters. Refer to the information

about connection parameters for MySQL datasources for details.

86 Data Federator User Guide

Defining sources of data

Creating database datasources using resources

You can use the parameters defined in a deployment context as values in these fields.

6. Add the datasource tables to your datasource. Refer to the information

on adding tables to database datasources for details.

7. Click Save.

Your MySQL datasource is added.

Related Topics

• Managing login domains on page 525

• Adding tables to a relational database datasource on page 157

• Defining a connection with deployment context parameters on page 156

• Testing and finalizing datasources on page 210

Data Federator User Guide 87

Defining sources of data

Creating database datasources using resources

Connection parameters for MySQL datasources

DescriptionParameter

The method to use to authenticate users' login credentials:

•

Authentication mode

Use a specific database logon for all Data Federator users

Data Federator connects to the database using the username and password that you enter. For each user, Data Federator uses the same username and password.

Use the Data Federator logon

Data Federator connects to the datasource using the username and password used to log in to Data Federator.

Database name

88 Data Federator User Guide

•

Use a Data Federator login domain

Data Federator connects to the datasource by mapping Data Federator users to database users.

Data Federator uses potentially different usernames and passwords for all Data Federator users, depending on how you or your administrator have set up the login domains.

The name of the database to which to connect.

Defined resource

Defining sources of data

Creating database datasources using resources

DescriptionParameter

The Data Federator resource that holds the configuration information that you want to use.

Host name

Password

Port

Prefix table names with the database name

The name of the host where the database is located.

The password that Data Federator enters for the username.

The port to which to connect.

Specifies if Data Federator should add the name of the database in its SQL queries to this JDBC source of data.

You can select this option only if you are using a JDBC data source that can use the database name in queries.

Data Federator User Guide 89

Defining sources of data

Creating database datasources using resources

Table types

DescriptionParameter

•

TABLE and VIEW

Choose this to see both tables and views when you click View tables.

•

TABLE

Choose this to see only tables when you click View tables.

•

VIEW

Choose this to see only views when you click View tables.

•

ALL

Choose this to avoid filtering the objects that you see in the database. When you click View

tables, you will see all objects.

User Name

Related Topics

• Managing login domains on page 525

• Mapping user accounts to login domains on page 526

The username that Data Federator uses to connect to the source of data

Connection parameters in MySQL datasources that can use deployment context parameters

For this datasource type, you can use deployment context parameters for the following fields.

• Database name

• Host name

90 Data Federator User Guide

• Password

• Port

• User Name

To use a deployment context parameter in a datasource definition field, use the syntax:

${parameter}

where parameter is the deployment context parameter that you want to use.

Related Topics

• Defining a connection with deployment context parameters on page 156

Adding Oracle datasources

To create a datasource for Oracle:

• Ensure that the connector for Oracle is configured. Usually, your Data

Federator administrator configures the connectors.

Defining sources of data

Creating database datasources using resources

• Ensure that you have the necessary drivers installed for Oracle. Installing

drivers is the minimal part of configuring connectors. It is also done by your Data Federator administrator.

• Ensure that you have the necessary parameters to indicate how to connect

to the database, for example the name of the machine where the database is running. These are also available from your Data Federator administrator.

1. Open the project to which you want to add the datasource, and at the top

of the Data Federator Designer screen, click Add, and from the pull-down list, click Add datasource.

The "New Datasource" screen is displayed.

2. Enter a name and description for your datasource, and expand the

Datasource Type pull-down list.

The datasource options are displayed

3. From the list, select Oracle, and click Save.

The "Draft" configuration screen is displayed.

Data Federator User Guide 91

Defining sources of data

Creating database datasources using resources

4. In the Connection parameters pane, from the Defined resource

drop-down list, select the name of the resource that defines the parameters for your Oracle database system.

The resource that you choose depends on the parameters that your Data Federator administrator configured for your Oracle database. If you are not sure which resource to choose, ask your Data Federator administrator.

5. On the "Draft" screen, configure the parameters. Refer to the information

about connection parameters for Oracle datasources for details.

You can use the parameters defined in a deployment context as values in these fields.

6. Add the datasource tables to your datasource. Refer to the information

on adding tables to database datasources for details.

7. Click Save.

Your Oracle datasource is added.

Related Topics

• Managing login domains on page 525

• Adding tables to a relational database datasource on page 157

• Defining a connection with deployment context parameters on page 156

• Testing and finalizing datasources on page 210

92 Data Federator User Guide

Creating database datasources using resources

Connection parameters for Oracle datasources

DescriptionParameter

The method to use to authenticate users' login credentials:

•

Use a specific database logon for all Data Federator users

Data Federator connects to the database using the username and password that you enter. For each user, Data Federator uses the same username and password.

•

Use the Data Federator logon

Data Federator connects to the datasource using the username

Authentication mode

and password used to log in to Data Federator.

Defining sources of data

Defined resource

•

Use a Data Federator login domain

Data Federator connects to the datasource by mapping Data Federator users to database users.

Data Federator uses potentially different usernames and passwords for all Data Federator users, depending on how you or your administrator have set up the login domains.

The Data Federator resource that holds the configuration information that you want to use.

Data Federator User Guide 93

Defining sources of data

Creating database datasources using resources

DescriptionParameter

Host name

Password

Port

Prefix table names with the schema name

The name of the host where the database is located.

The password that Data Federator enters for the username.

The port to which to connect.

Specifies if Data Federator should add the name of the schema in its SQL queries to this JDBC data source.

You can select this option only if you are using a JDBC data source that can use the schema name in queries, such as Oracle.

94 Data Federator User Guide

Schema

Defining sources of data

Creating database datasources using resources

DescriptionParameter

The names of the schemas of tables that you want to use, separated by commas.

The % character (percent) means "all schemas".

If you use multiple schemas, you should use the option Prefix table names with the schema name to distinguish tables from different schemas.

SID

Table types

The system identifier for the Oracle database.

•

TABLE and VIEW

Choose this to see both tables and views when you click View tables.

•

TABLE

Choose this to see only tables when you click View tables.

•

VIEW

Choose this to see only views when you click View tables.

•

ALL

Choose this to avoid filtering the objects that you see in the database. When you click View tables, you will see all objects.

Data Federator User Guide 95

Defining sources of data

Creating database datasources using resources

DescriptionParameter

User Name

Related Topics

• Managing login domains on page 525

• Mapping user accounts to login domains on page 526

The username that Data Federator uses to connect to the source of data

Connection parameters in Oracle datasources that can use deployment context parameters

For this datasource type, you can use deployment context parameters for the following fields.

• Host name

• Password

• Port

• Schema

• SID

• User Name

To use a deployment context parameter in a datasource definition field, use the syntax:

${parameter}

where parameter is the deployment context parameter that you want to use.

Related Topics

• Defining a connection with deployment context parameters on page 156

Adding Netezza datasources

To create a datasource for Netezza:

96 Data Federator User Guide

Defining sources of data

Creating database datasources using resources

• Ensure that the connector for Netezza is configured. Usually, your Data

Federator administrator configures the connectors.

• Ensure that you have the necessary drivers installed for Netezza. Installing

drivers is the minimal part of configuring connectors. It is also done by your Data Federator administrator.

• Ensure that you have the necessary parameters to indicate how to connect

to the database, for example the name of the machine where the database is running. These are also available from your Data Federator administrator.

1. Open the project to which you want to add the datasource, and at the top

of the Data Federator Designer screen, click Add, and from the pull-down list, click Add datasource.

The "New Datasource" screen is displayed.

2. Enter a name and description for your datasource, and expand the

Datasource Type pull-down list.

The datasource options are displayed

3. From the list, select Netezza, and click Save.

The "Draft" configuration screen is displayed.

4. In the Connection parameters pane, from the Defined resource

drop-down list, select the name of the resource that defines the parameters for your Netezza database system.

The resource that you choose depends on the parameters that your Data Federator administrator configured for your Netezza database. If you are not sure which resource to choose, ask your Data Federator administrator.

5. On the "Draft" screen, configure the parameters. Refer to the information

about connection parameters for Netezza datasources for details.

You can use the parameters defined in a deployment context as values in these fields.

6. Add the datasource tables to your datasource. Refer to the information

on adding tables to database datasources for details.

7. Click Save.

Your Netezza datasource is added.

Data Federator User Guide 97

Defining sources of data

Creating database datasources using resources

Related Topics

• Managing login domains on page 525

• Adding tables to a relational database datasource on page 157

• Defining a connection with deployment context parameters on page 156

• Testing and finalizing datasources on page 210

98 Data Federator User Guide

Creating database datasources using resources

Connection parameters for Netezza datasources

DescriptionParameter

The method to use to authenticate users' login credentials:

•

Use a specific database logon for all Data Federator users

Data Federator connects to the database using the username and password that you enter. For each user, Data Federator uses the same username and password.

•

Use the Data Federator logon

Data Federator connects to the datasource using the username

Authentication mode

and password used to log in to Data Federator.

Defining sources of data

Defined resource

•

Use a Data Federator login domain

Data Federator connects to the datasource by mapping Data Federator users to database users.

Data Federator uses potentially different usernames and passwords for all Data Federator users, depending on how you or your administrator have set up the login domains.

The Data Federator resource that holds the configuration information that you want to use.

Data Federator User Guide 99

Defining sources of data

Creating database datasources using resources

DescriptionParameter

The middleware type, for example JDBC or ODBC.

Network layer

ODBC DSN

Password

Port

User Name

Related Topics

• Managing login domains on page 525

• Mapping user accounts to login domains on page 526

Note:

Data Federator inserts this value when you select the Defined Re- source, and it cannot be changed.

The ODBC Data Source Name to use.

The password that Data Federator enters for the username.

The port to which to connect.

The username that Data Federator uses to connect to the source of data

100 Data Federator User Guide

Business objects DATA FEDERATOR XI 3.0 User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual

Contents

Introduction to Data Federator

The Data Federator application

An answer to a common business problem

What are the challenges?

How can the problem be defined?

How does Data Federator solve this problem?

Fundamental notions in Data Federator

Data Federator Designer: design time

Data Federator Query Server: run time

Important terms

Data Federator user interface

Overview of the methodology

Adding the targets

Adding the datasources

Mapping datasources to targets

Checking if data passes constraints

Deploying the project

Starting a Data Federator project

Working with Data Federator

Login and passwords for Data Federator

Adding new users

Starting a project

Adding a project

Opening a project

Deleting Data Federator projects

Closing Data Federator projects

Unlocking projects

Creating target tables

Managing target tables

Adding a target table manually

Adding a target table from a DDL script

Adding a target table from an existing table

Changing the name of a target table

Displaying the impact and lineage of target tables

Details on configuring target table schemas

Determining the status of a target table

How to read the Impact and lineage pane in Data Federator Designer

Testing targets

Testing a target

Managing domain tables

Example tests to run on a mapping rule

Adding a domain table to enumerate values in a target column

Examples of domain tables

Adding a domain table by importing data from a file

Dereferencing a domain table from your target table

Exporting a domain table as CSV

Deleting a domain table

Using domain tables in your target tables

Using a domain table as the domain of a column

Defining sources of data

About datasources

Datasource user interface

Draft and Final datasources

About configuration resources

Generic and pre-defined datasources

Creating database datasources using resources

Adding Access datasources

Connection parameters for Access datasources

Connection parameters in Access datasources that can use deployment context parameters

Adding DB2 datasources

Connection parameters for DB2 datasources

Connection parameters in DB2 datasources that can use deployment context parameters

Adding Informix datasources

Connection parameters for Informix datasources

Connection parameters in Informix datasources that can use deployment context parameters

Adding MySQL datasources

Connection parameters for MySQL datasources

Connection parameters in MySQL datasources that can use deployment context parameters

Adding Oracle datasources

Connection parameters for Oracle datasources

Connection parameters in Oracle datasources that can use deployment context parameters

Adding Netezza datasources

Connection parameters for Netezza datasources