Creating Source Data Objects

This chapter provides an overview of source data objects and discusses how to:

Create basic source data objects.
Create expressions.
Create filters.
Join records.

Understanding Source Data Objects

A source data object is the input to every map definition and defines the data to be extracted from the source. The business requirement of the map definition dictates the appropriate structure of the source data object, which can be as basic as requiring only an object definition and the fields to be used in the extract SQL specified, or quite complex containing expressions, multiple table joins, and filtering criteria.

Common Elements Used to Create Source Data Objects

Data Source

One or more record definitions joined by common fields.

Source Data Object

Enter a unique value of up to 30 characters, with no spaces or special characters. The system automatically converts the value to uppercase.

This value, along with the description from the definition page, appears at the top of each page of the Source Data Object component.

Creating Basic Source Data Objects

This section discusses how to:

Define source data objects.
Specify source data object fields.
Preview results.
Preview SQL.

Note. All remote source databases must be of the same database type as the local PeopleSoft database instance. For example, if your PeopleSoft application is running on DB2/UNIX then you may only define DB2/UNIX source databases.

Pages Used to Create Basic Source Data Objects

Page Name	Object Name	Navigation	Usage
Source Data Object	EOEW_SRCDO	Enterprise Components, Data Transformation, Data Transformation Home, Define Source Data Objects, Source Data Object	Create source data object definitions.
Fields	EOEW_SRCFIELD	Enterprise Components, Data Transformation, Data Transformation Home, Define Source Data Objects, Source Data Object, Fields	Specify the fields to include in the source data object.
Preview	EOEW_SRCPREVIEW	Enterprise Components, Data Transformation, Data Transformation Home, Define Source Data Objects, Source Data Object, Preview	Review the results and check the validity of the source data object.
View Query	EOEW_SRCQUERY	Enterprise Components, Data Transformation, Data Transformation Home, Define Source Data Objects, Source Data Object, View Query	Generate and view the SQL based on the source data object definition.

Defining Source Data Objects

Access the Source Data Object page.

Subject Area

Select a subject area.

For new source data objects, the subject area will be set to the default as defined on the Subject Area page.

Data Source Type

Local: Select to use PeopleSoft-defined records from the current database.

Remote: Select to access the remote database connection information and use records from a remote database. Select the desired data source for remote.

Note. The Data Source Type can only be selected in Add mode. If you want to change this value, you will need to create a new Source Data Object.

Source Record

Select the source records from which to extract data. This record is placed in the From clause in the extract SQL.

If multiple records are selected, join conditions are required.

Note. To change a record that is associated with a source data object, you must delete the existing record and then add the new record.

Save As

Click to save an existing source data object under a new name.

Use this function to create a new source data object that will only slightly differ from the original object. Once saved, you can make the necessary changes to the new object. The new object is maintained separately.

Specifying Source Data Object Fields

Access the Fields page.

The source field information is populated from the source records that you selected on the Source Data Object page. The selected fields become the select list in the extract SQL.

Source Field Name	Select the fields to include in the source data object. Note. At least one field must be selected. Use the Select All and Deselect all icons to make the field selection more efficient.
Field Alias	Change the field alias of any of the selected field names, if needed. The field alias is displayed when defining the map, so descriptive names are important. Note. All fields in the source data object must have an alias, and each alias must be unique within the source data object.
Synchronize Fields	Click to synchronize fields with the database record. It is possible over the life span of a source data object that customizations are made. If a field is added or removed from the physical table, the field list must be synchronized.
Select All	Click to select all of the fields that are listed.
Clear All	Click to clear all field selections.

Previewing Results

Access the Preview page.

Click the Preview button to view a subset of the source data object based on the record and fields that are selected.

Use this feature to check the validity of the defined source data object. It is especially helpful in determining if your expressions are defined correctly.

Note. Attempting to preview complex data sources can quickly consume processing and memory resources on your application server.

The preview maximum row count is set using the Data Transformer Installation Options page. Large row counts take more time and resources to preview. Consider setting your preview row count relatively low.

If no data is returned, verify the SQL that it being used to extract the data is accurate.

Previewing SQL

Access the View Query page.

Click the Generate SQL button to generate SQL text in the Query text box.

Use this to preview the SQL generated by the defined source data object.

Note. The enterprise administrator can verify if the extract SQL is correct and adjust the source data object definition as necessary.

(Optional) Creating Expressions

This section provides an overview of expressions and discusses how to create expression statements.

Understanding Expressions

Expressions enable you to create virtual columns that are made up of mathematical calculations based on actual fields in a record. Expressions are resolved at runtime so that duplicate information is not stored in the database. Expressions can be numeric, string, date, time, or datetime. Numeric expressions can combine any number of fields, constant values, and math operators, and results are rounded based on the decimal position that is selected. String expressions can be concatenations of any number of character fields and constant values.

Built-in Functions

PeopleSoft delivers built-in functions that you can use to make expressions powerful and flexible. You can also nest built-in functions and expressions; for example:

Concatenate two fields, separate them with a comma, drop trailing blanks, and limit this field to 40 characters:

Substring(RTRIM(FirstName) | , | RTRIM(LastName),1,40)
Sum the multiplication of two fields:

SUM(PROD_COST * PROD_RATE)

This table lists all of the delivered built-in functions with supported meta-SQL and aggregate functions:

Function	Explanation	Use
ABS(Number)	Returns the absolute value of the specified number.	ABS(-5) returns 5.
AddMonth(Date, Number)	Adds a number of months to the specified date and returns the calculated date.	AddMonth(“2002–01–10”,2) returns 2002–03–10.
Average (Number)	Returns the average value of the specified number (typically a numeric column name).	Average(PRICE) returns the average price of your source data set.
Beginning of Month (Date)	Returns a date representing the first day of the month for the specified date.	Beginning of Month(“2002–10–05”) returns 2002–10–01.
Beginning of Year (Date)	Returns a date representing the first day of the year for the specified date.	Beginning of Month(“2002–10–05”) returns 2002–01–01.
Count (col)	Returns the number of rows that are found.	Count(PRODUCT_ID) returns the number of rows on your source data set.
DateAdd(Date, Days)	Adds a number of days to the specified date and returns the calculated date.	DateAdd(“2002–02–02”,5) returns 2002–02–07.
DateDiff(Date, Date)	Returns the number of days that have elapsed between the first and second specified dates.	DateDiff(“2002–02–02”,”2002–02–10”) returns 8.
DtTmDiff(Datetime,Datetime)	Returns the number of minutes that have elapsed between the first and second specified datetimes.	DtTmDiff(”'2002-01-01 00:00:00.000”,”2002-01-01 10:00:00.000”) returns 600 (10 hours in minutes).
DIFF_H(Datetime, Datetime)	Returns the number of hours that have elapsed between the first and second specified datetimes.	DIFF_H(”'2002-01-01 00:00:00.000”,”2002-01-01 10:00:00.000”) returns 10.
DIFF_S(Datetime, Datetime)	Returns the number of seconds that have elapsed between the first and second specified datetimes.	DIFF_S(”'2002-01-01 00:00:00.000”,”2002-01-01 10:00:00.000”) returns 36000 (10 hours in seconds).
End of Month (Date)	Returns a date representing the last day of the month for the specified date.	End of Month(“2002–10–05”) returns 2002–10–31.
End of Year (Date)	Returns a date representing the last day of the year for the specified date.	End of Year(“2002–10–05”) returns 2002–12–31.
GetDay(Date)	Returns the day of the month for the specified date.	GetDay(“2002–10–05”) returns 5.
GetMonth(Date)	Returns the month of the specified date.	GetMonth(“2002–10–05”) returns 10.
GetYear(Date)	Returns the year of the specified date.	GetYear(“2002–10–05”) returns 2002.
MOD(Number, Number)	Returns the modulus (or remainder) of the first number when it is divided by the second number.	MOD(14,3) returns 2 (14 divides into 3 four times with a remainder of 2).
Max (Number)	Returns the maximum value of the specified number (typically a numeric column name).	Max(PRICE) returns the largest price of your source data set.
Min (Number)	Returns the minimum value of the specified number (typically a numeric column name).	Min(PRICE) returns the smallest price of your source data set.
Sum (Number)	Returns the sum of the specified number (typically a numeric column name).	Sum(PRICE) returns the sum of all prices of your source data set.
RTRIM(String)	Returns the specified string, and trims trailing blank characters.	RTRIM(“Hello “) returns Hello.
Round (Number, Factor)	Returns a number representing the specified number after being rounded to the given factor or precision.	Round(5.582, 1) returns 5.6 Round(5.512, 0) returns 6
Substring(String, StartPos, Length)	Returns a section of the specified string starting at the specified position and continuing for the specified length.	Substring(“My String Expression“, 4,6) returns String.
TO_CHAR(Number)	Returns a string representation of the specified numeric expression.	TO_CHAR(59) returns 59.
TO_NUM(String)	Returns a numeric representation of the specified string expression.	TO_NUM(“59”) returns 59. Note. Trying to apply a TO_NUM built-in function on a character that does not translate to a numeric value aborts the processing of your map.
TrimSubStr (String, Start, Length)	Returns a section of the specified string starting at the specified position and continuing for the specified length, and trims trailing blank characters.	Substring(“My String Expression“, 4,7) returns String.
Truncate(Number, Factor)	Returns a number representing the specified number after being truncated to the given factor or precision.	Truncate(5.582, 1) returns 5.5. Truncate(5.512, 0) returns 5.
UPPER(String)	Returns the specified string in uppercase characters.	UPPER(“My String Expression“, 4,7) returns MY STRING EXPRESSION.
YYMMDD(Date)	Returns the string representation of a specified date in YYMMDD format.	YYMMDD(“2002–10–01”) returns 021001.

Page Used to Create Expressions

Page Name	Object Name	Navigation	Usage
Expressions	EOEW_SRCFIELDEXPR	Enterprise Components, Data Transformation, Data Transformation Home, Define Source Data Objects, Source Data Object, Expressions	Create expression statements by selecting operators, fields, and constant values.

Creating Expression Statements

Access the Expressions page.

Name

Enter a unique name for this expression.

Expression Field Type

Select to control the set of operators, record fields, and value objects that can be selected when building the expression.

Numeric: Select to combine any number of record fields, value objects, and math operators.

String: Select to concatenate any number of character record fields and value objects.

Date: Select to define a constant value or to create a date field from the source data object or built-in functions.

Expression Statement

Displays your expression as you build it. You cannot type directly into this edit box. Use operators, data source options, and the constant values to create your expressions.

You can see what element is being specified by the arrows surrounding it; the selection is also displayed in the Current Element group box.

Clear

Click to delete all entries in the Expression Statement edit box.

Operators

Select from the operator buttons to add operators to the expression.

Data Source

Data Object Column

Select this option to display a list of data object fields. Select a field and click Insert to have the selected data object appear in the expression statement.

Built-in Function

Select this option to display a list of built-in functions. Select a function and click Insert to have the selected built-in function appear in the expression statement.

Built-in functions let you define numeric calculations like Sum, Average, or End of Month, and Minimum/Maximum values.

Note. All parameters of the selected built-in function must be defined.

Constant Value

Select this option to enter your own character, numeric, or date value. Click Insert to have the value appear in the expression statement.

Note. Consider how an expression will be used when you want to specify date related value objects. If the expression will be in a WHERE clause, then use the %CurrentDateIn value object. If the expression will be in a SELECT clause, then use the %CurrentDateOut value object.

Current Element

, , ,	Click to move through the expression statement and identify the text you want to replace or delete or to move to the position in the statement where you want to insert text. Click the Previous or Next button to move one element forward or backward. Click the First or Last button to move to the beginning or end of the expression statement. Note. The element name will be shown in the Current Element group box as well as surrounded by arrows in the Expression Statement edit box.
Replace	Click to replace the current element item in the expression statement with the selected data source.
Insert	Click to insert the selected data source into the expression statement before the current element item.
Delete	Click to delete the current element item in the expression statement.

Example: Creating Expressions by Using Data Object Column

This section provides an example of creating an expression by using a data object column.

The company wants to award its employees with a bonus of $1000 for each year that they have been employed by the company. To calculate the bonus:

Enter BONUS for the expression name, and then enter a description.
Select the Number expression type.
Select integer and decimal positions.
Select Data Object Column in the Data Source group box.
Select the YEARS_OF_EMP data object column, and click Insert.

The field appears in the Expression Statement edit box.
Click the Multiplication (*) operator to insert it at the end of the expression statement.
Select Constant Value in the Data Source group box.
Enter a numeric value of 1000 , and click Insert.

This creates the following expression statement:

YEARS_OF_EMP * 1000
Click Save.

Example: Creating Expressions Using Built-in Functions

This section provides an example of creating an expression by using a built-in function.

You want to define a field called SHORT_DESCR that uses the first 10 characters of the DESCR field.

To define the SHORT_DESCR field:

Enter SHORT_DESCR for the expression name, and then enter a description.
Select the Char expression type.
Enter the length.
Select Built-in Function in the Data Source group box.
Select the Substring (String,Start,Length) built-in function, and click Insert.

The function appears as SUBSTRING ( PARAM1 , PARAM2 , PARAM3 ) in the Expression Statement edit box.
Use the arrow buttons to navigate through the expression statement and identify the PARAM1 element.
Select Data Object Column in the Data Source group box.
Select DESCR, and click Replace.

The DESCR field appears in place of PARAM1 in the expression statement.
Navigate to PARAM2.
Select Constant Value in the Data Source group box, and enter a numeric value of 1.
Click Replace.
Navigate to PARAM3, and replace it with a numeric value of 10.

This creates the following expression statement:

SUBSTRING ( DESCR , 1 , 10 )
Click Save.

(Optional) Creating Filters

This section provides an overview of filters and discusses how to create filters.

Understanding Filters

Filters serves as criteria that the system applies to each row of data in the data source. If the row passes the criteria, the system uses the data as an input to the data transformer map; if the row doesn't pass, the system doesn't use the data. A filter compares the value in one of the row's fields to a reference value. For example, suppose that you need only products whose selling price is greater than $10.00. You would compare the value in the Price field to the constant value of $10.00. The filter criteria is placed in the WHERE clause in the extract SQL.

The following table describes the operators that you can use to compare values:

Operator	Description
Like	The value in the selected field matches a specified string pattern. The comparison value may be a string that contains wild card characters. The wild card characters that PeopleSoft Data Transformer recognizes are percent (%) and underscore (_). The % matches any string of zero or more characters. For example, C% matches any string starting with C, including C alone. The _ matches any single character. For example, _ones matches any 5-character string ending with ones, such as Jones or bones. Note. To use one of the wild card characters as a literal character, (that is, to include % in your string), precede the character with a backslash (\). For example, percent\%\.
=	The value in the selected record field exactly matches the comparison value.
<>	The value in the selected record field is not equal to the comparison value.
>	The value in the record field is greater than the comparison value.
<=	The value in the record field is less than or equal to the comparison value.
<	The value in the record field is less than the comparison value.
>=	The value in the record field is greater than or equal to the comparison value.

When you include multiple filters, link them using AND or OR. When you link two filters with AND, a row must meet the first and second filter to pass the test. When you link with OR, a row must meet the first or second filter, but not necessarily both.

Page Used to Create Filters

Page Name	Object Name	Navigation	Usage
Filters	EOEW_SRCFILTER	Enterprise Components, Data Transformation, Data Transformation Home, Define Source Data Objects, Source Data Object, Filters	Create filters.

Creating Filters

Access the Filters page.

To define a filter:

Select the record that you want to filter against from the list of records that are defined in the source data object.
Select the field on which to base your filter.
Select an operator.
Enter a comparison value.
Click Save.
Click Refresh Filter to save any changes that you made to existing filters and reflect those changes in the source data object. Refresh Filter also captures changes made for Save and updates dropdown lists to show your selections on the page.

Note. If the filter begins to get complicated, use the arrow keys to indent subconditions. Subconditions will be surrounded by parentheses and fully resolved before comparing results with other level results.

(Optional) Joining Records

This section provides an overview of joins and discusses how to define join conditions.

Understanding Joins

PeopleSoft Data Transformer enables you to create source data objects that include multiple-table joins. Joins retrieve data from more than one table, presenting the data as if it came from one table. PeopleSoft Data Transformer links the tables, based on common record fields, and links the rows on the two tables by common values in the shared record fields.

Page Used to Join Records

Page Name	Object Name	Navigation	Usage
Join Conditions	EOEW_SRCJOIN	Enterprise Components, Data Transformation, Data Transformation Home, Define Source Data Objects, Source Data Object, Join Conditions	Define join conditions.

Defining Join Conditions

Access the Join Conditions page.

Note. This page appears only when two or more records are defined in the source data object.

Enter the record name and associated field name for the left and right sides of the join.

The content of the field name dropdown list box is filtered to exclude some fields with data types that cannot be qualified (for example, image columns). You need to fully qualify the join criteria of the selected records; otherwise, processing may return unexpected results. The join criteria will become part of the WHERE clause in the extract SQL.

Subject Area	Select a subject area. For new source data objects, the subject area will be set to the default as defined on the Subject Area page.
Data Source Type	Local: Select to use PeopleSoft-defined records from the current database. Remote: Select to access the remote database connection information and use records from a remote database. Select the desired data source for remote. Note. The Data Source Type can only be selected in Add mode. If you want to change this value, you will need to create a new Source Data Object.
Source Record	Select the source records from which to extract data. This record is placed in the From clause in the extract SQL. If multiple records are selected, join conditions are required. Note. To change a record that is associated with a source data object, you must delete the existing record and then add the new record.
Save As	Click to save an existing source data object under a new name. Use this function to create a new source data object that will only slightly differ from the original object. Once saved, you can make the necessary changes to the new object. The new object is maintained separately.

Name	Enter a unique name for this expression.
Expression Field Type	Select to control the set of operators, record fields, and value objects that can be selected when building the expression. Numeric: Select to combine any number of record fields, value objects, and math operators. String: Select to concatenate any number of character record fields and value objects. Date: Select to define a constant value or to create a date field from the source data object or built-in functions.
Expression Statement	Displays your expression as you build it. You cannot type directly into this edit box. Use operators, data source options, and the constant values to create your expressions. You can see what element is being specified by the arrows surrounding it; the selection is also displayed in the Current Element group box.
Clear	Click to delete all entries in the Expression Statement edit box.
Operators	Select from the operator buttons to add operators to the expression.