Setting Up Core PeopleSoft Data Transformer Features

This chapter discusses how to:

Define remote database connections.
Define installation options.
Define subject areas.

Note. These activities are typically one-time events and require minimal maintenance.

Defining Remote Database Connections

This section provides an overview of remote database connections and discusses how to define remote database connections.

Understanding Remote Database Connections

Data sources represent the location of the source data that is extracted, transformed, and loaded to the target. Remote data source data is extracted from a separate (remote) database and migrated into the local database. You must define remote database connections to source data from a database other than your local PeopleSoft database instance.

The Remote Database Access Management page enables you to define connectivity information for relational databases to be used for sourcing data for PeopleSoft Data Transformer. You can define connectivity information only for databases of the same type as the Target PeopleSoft database instance. For example, if the PeopleSoft database instance is Oracle, then you can define only Oracle remote connections. Remote database connections are established once for a local connect and once for each database that will be sourced. You must define one local connection for the current PeopleSoft instance. This connection is used during remote database access to retrieve information for the target database.

Note. If you are accessing data from a local database, then you do not need to set up remote database connections.

Page Used to Define Remote Database Connections

Page Name	Object Name	Navigation	Usage
Remote Database Access Management	REMOTEDB	Enterprise Components, Data Transformation, Data Transformation Home, Setup Remote Database Connection, Remote Database Access Management.	Define remote database connections.

Defining Remote Database Connections

Access the Remote Database Access Management page.

Important! Different fields may appear on the page based on the database DB type. The preceding page shows fields for Microsoft, DB2 (OS/390), DB2/UNIX, and Sybase.

This table illustrates the page variance by database type:

Database Type	Required Information
Microsoft, DB2 (OS/390), DB2/UNIX, and Sybase	Enter a description, server, database, userID, and password.
Informix	Enter a description, server, user ID, password, and Inf Svr (Informix Server) name.
Oracle	For Oracle and a Connection Type of TNSNames, enter a description, TNS Entry, user ID and password. Note. TNS Names is a preconfigured file (tnsnames.ora) with previously defined database connection information. Select a connection type of Specific if you want to set up a database that does not already have a TNS Entry defined, or when you don't want to use the TNS entry.

Name	Displays the remote DB name entered on the Search page for your database.
DB Type	Displays the database type of your PeopleSoft database. Note. Since your remote database must be of the same type, this field is defaulted to your current local database type.
Local Connect	Select if you are defining the remote connection information for your local database. All others must be specified as remote databases. An error displays if you save a page with a connection checked as a Local Connect and there is already one identified as such (in the PSREMOTEDBDEFN table). Important! One connection must be defined as the Local Connect, the connection information for the current PeopleSoft instance (the local database). This connection is used during remote database access to retrieve connection information for the source and as the target database for the sourced data.
Server	Enter the name of the server where the database resides. Note. This field is platform specific.
DB Server Port	The DB Server Port number is automatically populated with a default value based on the database type. It may need to be changed depending upon your database server configuration.
User ID and Password	Enter the user ID and password that are required to access the database. The password is not displayed. Note. This field is platform specific.
Test Connect	Click to access the remote database by using the information that you have entered. This will verify that all of the connection information is correct.

Defining Installation Options

This section discusses how to define installation options.

Page Used to Define Installation Options

Page Name	Object Name	Navigation	Usage
Data Transformation Installation Options	EOEW_OPTIONS	Enterprise Components, Data Transformation, Data Transformation Home, Data Transformation Installation Options	Define installation options.

Defining Installation Options

Access the Data Transformation Installation Options page.

System administrators may use PeopleSoft Data Transformers' installation options to set certain high-level Data Transformer defaults such as the maximum number of rows to return on a preview of sourced or transformed data, a table space name to be used to dynamically build temporary tables, and the default message set number assignment for transformation error correction.

Maximum Rows to Preview	Enter the maximum number of rows of sourced or transformed data to preview. Keep this value relatively low, for example 100 rows, as larger values significantly increase the amount of time it takes to retrieve data and render the preview pages.
Tablespace Name	Enter the tablespace name for the temporary table that is built dynamically at runtime. This tablespace name is applied to all of the temporary tables unless overridden at a lower level. Note. This name can be overridden at the subject area and map option levels.
Create Temporary Table	Select to create temporary tables dynamically. A temporary table is created at runtime—it's needed by the engine during processing only, then it's deleted at completion. It is not available in PeopleSoft metadata. If the checkbox is cleared, you must specify a temporary table record name on the map for use at runtime. This temporary table will be available in PeopleSoft metadata and remain after the engine processing. Note. Determining which of these options to use needs to be considered carefully. A dynamic temporary table will automatically change when the map changes, conversely, a non-dynamic temporary table will not change and may require more maintenance.
Message Set Number	Select a message set number that corresponds to a text error message set. When adding a new transformation and associating a specific error message with an error, this default message set number is used as a starting point.

Defining Subject Areas

This section provides an overview of subject areas and discusses how to define subject areas.

Understanding Subject Areas

Use subject areas as a grouping mechanism on translation sets, transformations, map rules and map definitions. These objects can only be shared within a subject area. An enterprise subject area can be used for objects that are to be shared by all subject areas.

You need to carefully plan subject areas when you implement PeopleSoft Data Transformer. For example, you need to consider how the Data Transformer objects (translation sets, transformations, map rules, map definitions, and so on) can be grouped most efficiently. Some objects need to be available to all users, while some only to a specific user or functional group.

Note. Subject areas affect only prompting and object selection and do not reflect object security.

You can set up default tablespaces, or you can dynamically create temporary tables by subject area. If an implementation requires that only groups of maps dynamically generate temporary tables, or are built within different tablespaces, you can specify this at the subject area level.

Page Used to Define Subject Areas

Page Name	Object Name	Navigation	Usage
Subject Areas	EOEW_OBJ_OWNER	Enterprise Components, Data Transformation, Data Transformation Home, Setup Subject Areas	Define subject areas to group translation sets, transformations, map rules, and map definitions.

Defining Subject Areas

Access the Subject Areas page.

Default Subject Area

Select to use this subject area as your default subject area.

Note. Only one subject area can be selected as the default, which becomes available to all users—it is your enterprise subject area.

Tablespace Name

Select the tablespace name to use if you want to override the Data Transformation installation options selection for this subject area.

Note. This is needed only when an implementation shares dynamically generated temporary tables over multiple tablespaces.

For example, the CEW EO temporary tables may be assigned to a special tablespace because the table size in this area is expected to be larger than other tables. For that subject area, an override tablespace name would be entered. The CEW CM subject area contains unique tables that also need special segmentation. For that subject area, an override tablespace name would be entered. All other subject areas would have a blank tablespace name therefore defaulting to the Data Transformation installation options tablespace name.

Create Temporary Table

Select if you want to override the Data Transformation installation options selection for this subject area.

This is needed only when an implementation allows or disallows the dynamic temporary table creation per subject area.