Business objects DATA INTEGRATOR 11.5.1.5 User Manual

Download

Data Integrator Supplement for PeopleSoft

Data Integrator 11.5.1.5

for Windows and UNIX

Trademarks

Third-party contributors

Patents

Date

If you find any problems with this documentation, please report them to Business Objects S.A. in writing at documentation@businessobjects.com.

Business Objects, the Business Objects logo, Crystal Reports, and Crystal Enterprise are trademarks or registered trademarks of Business Objects SA or its affiliated companies in the United States and other countries. All other names mentioned herein may be trademarks of their respective owners.

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

Business Objects owns the following U.S. patents, which may cover products that are offered and sold by Business Objects: 5,555,403, 6,247,008 B1, 6,578,027 B2, 6,490,593 and 6,289,352.

February 28, 2006

2 Data Integrator Supplement for PeopleSoft

Chapter 1 Introduction 5

Using Data Integrator in a PeopleSoft environment . . . . . . . . . . . . . . . . . . . 6

About this document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

More Data Integrator product documentation . . . . . . . . . . . . . . . . . . . . . . . 7

Chapter 2 PeopleSoft Datastores 11

Defining PeopleSoft datastores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

Browsing PeopleSoft metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

Importing PeopleSoft metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

Metadata for PeopleSoft domains . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

What is a domain? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

Using PeopleSoft domains in Data Integrator . . . . . . . . . . . . . . . . . . . 17

Metadata for PeopleSoft trees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

Chapter 3 Data Flows with PeopleSoft Data 19

Using effective dates from PeopleSoft data . . . . . . . . . . . . . . . . . . . . . . . . 20

Selecting a subset of source columns . . . . . . . . . . . . . . . . . . . . . . . . . 20

Filtering based on status . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

Creating effective date ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

Retrieving records with current effective dates . . . . . . . . . . . . . . . . . . 22

Using PeopleSoft domains in data flows . . . . . . . . . . . . . . . . . . . . . . . . . . 23

Using the Picker window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

Validating domains . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

Restrictions using domains . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

Extracting PeopleSoft tree data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

Chapter 4 Reference Information 31

Datastore . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33

Domain . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

Data Integrator Supplement for PeopleSoft 3

Contents

Hierarchy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36

Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42

4 Data Integrator Supplement for PeopleSoft

Data Integrator Supplement for PeopleSoft

Introduction

chapter

Introduction

Overview

Welcome to the Data Integrator Supplement for PeopleSoft. This guide contains information about how to use Data Integrator with PeopleSoft.

This chapter contains the following topics:

• Using Data Integrator in a PeopleSoft environment

• About this document

• More Data Integrator product documentation

Using Data Integrator in a PeopleSoft environment

If you install the PeopleSoft interface, you can use PeopleSoft HRMS and ERP application data as a data source in Data Integrator.

Through the PeopleSoft datastore you define in Data Integrator, you can navigate PeopleSoft metadata external to Data Integrator (using standard PeopleSoft panels and menus) and import metadata for PeopleSoft source tables into the Data Integrator repository.

The following diagram shows PeopleSoft tables, hierarchies, and domains that have been imported into Data Integrator.

6 Data Integrator Supplement for PeopleSoft

In addition, capabilities in Data Integrator that support PeopleSoft include the following:

• You can view valid domain values for a column from within the query

transform. This allows you to filter on specific domain values for a column.

• Data Integrator can determine whether a given value for a column is valid

within the domain of the column. Data Integrator flags those that are not valid.

• Data Integrator allows extraction of data based on ef fective dates without

requiring you to write a secondary query. This functionality is particularly useful in implementing data warehouses containing HR data—it significantly reduces the complexity and increases the performance of extracting effective-dated data.

• Data Integrator allows extraction of the hierarchical data in PeopleSoft

trees.

About this document

This document describes how Data Integrator interacts with PeopleSoft in the following chapters:

• Chapter 3: PeopleSoft Datastores — Describes how to define a

PeopleSoft datastore so that you can use PeopleSoft as a data source in Data Integrator

• Chapter 4: Data Flows with PeopleSoft Data — Describes information

unique to data flows that extract, transform, and load PeopleSoft data

• Chapter 5: Reference Information — Describes Data Integrator objects

specific to PeopleSoft and Data Integrator objects with supplemental information for the PeopleSoft interface

Introduction

About this document

PeopleSoft Datastores

chapter

PeopleSoft Datastores

Overview of PeopleSoft datastores

With the PeopleSoft interface, you can use a PeopleSoft system as a Data Integrator source. To use a PeopleSoft data source, you must:

• Define a Data Integrator datastore that will serve as the logical link to

your PeopleSoft system

• Import your PeopleSoft metadata into the Data Integrator datastore

This chapter describes the steps to complete these tasks. This chapter contains the following topics:

• Defining PeopleSoft datastores

• Browsing PeopleSoft metadata

• Importing PeopleSoft metadata

• Metadata for PeopleSoft domains

• Metadata for PeopleSoft trees

For generic information about Data Integrator datastores, see “Datastores” on

page 79 of the Data Integrator Designer Guide .

Defining PeopleSoft datastores

You must define a PeopleSoft datastore in Data Integrator when you are extracting data from or loading data to PeopleSoft.

T o define a PeopleSoft datastore



1. Go to the Datastores tab of the object library.

2. Right-click inside the object library window and choose New.

The Create New Datastore window appears.

3. Enter a name for the new datastore in the Datastore Name box.

You can give the datastore any name you want, and you can change the name later if necessary. The name can contain any alpha or numeric characters or underscores (_). It cannot contain spaces.

4. In the Datastore type box, choose PeopleSoft.

5. In the Database type box, choose the appropriate database.

6. Enter the appropriate information for the database type you selected.

12 Data Integrator Supplement for PeopleSoft

PeopleSoft Datastores

Browsing PeopleSoft metadata

For detailed information about the options, see “Datastore” on page 53 of

the Data Integrator Reference Guide .

7. Click OK.

Data Integrator creates the datastore and it appears in the object library window.

Browsing PeopleSoft metadata

After you create the datastore, you can access the PeopleSoft metadata via the Data Integrator datastore explorer, which displays information in a tree format.

The information displayed consists of metadata related directly to database data—no information about calculation fields, images, buttons, or other GUIrelated items is included.

Browsing PeopleSoft data in Data Integrator is a lot like browsing in PeopleTools—icons in the display represent PeopleSoft menu groups, menus, menu bars, menu items, panels, and panel fields. Each panel field displays the caption, table column, and table name.

Icon Metadata type Description

Menu Group The entire set of commands available in

PeopleSoft applications for a specific database.

Menu A set of commands for a specific application.

Data Integrator Supplement for PeopleSoft 13

PeopleSoft Datastores

Importing PeopleSoft metadata

Icon Metadata type Description

Menu Bar The top level of the menu.

Menu Item The commands that make up each menu bar. Panel A vehicle for capturing and displaying data. Each

panel can have multiple sub-panels, each represented by the same icon.

Panel Fields Represents a column on a table or in a specific

view.



T o browse PeopleSo ft metadata

1. In the object library, go to the Datastores tab.

2. Right-click the PeopleSoft datastore name and select Open.

The datastore explorer window opens.

Importing PeopleSoft metadata

To access PeopleSoft metadata from Data Integrator, you must import the metadata into the object library.

You can import tables, PeopleSoft trees (called hierarchies in Data Integrator), and domains.

You can import PeopleSoft metadata in one of three ways:

• By name

• By browsing

14 Data Integrator Supplement for PeopleSoft

PeopleSoft Datastores

Importing PeopleSoft metadata

• By searching

T o import PeopleSof t metadata by name



1. In the object library, go to the Datastore tab.

2. Right-click the datastore name and select Import By Name.

3. In the Import by Name dialog box, specify the Type of the item to import.

To import a PeopleSoft tree, select Hierarchy.

4. In the Name box, enter the name of the item to import.

The default is to import domain information automatically with any tables you import. You can choose not to import the associated domains.

To import a hierarchy, enter the tree name in the Name box and enter the Category and Structure associated with the hierarchy.

5. Click OK.

Data Integrator Supplement for PeopleSoft 15

PeopleSoft Datastores

Metadata for PeopleSof t domains



You can search for PeopleSoft items as you would any items to be imported into Data Integrator. That is, right-click the datastore name in the object library and choose Search.

For more information, see “Importing metadata through a database dat astore”

on page 97 of the Data Integrator Designer Guide .

The information appears in the object library.

T o import PeopleSoft metadata by searching

T o import PeopleSoft metadata by browsing



1. In the object library, go to the Datastores tab.

2. Right-click the datastore name and choose Open.

The datastore explorer opens in the workspace and lists the available tables, domains, and hierarchies. See “Browsing PeopleSoft metadata”

on page 13 for information about the icons that appear in the list.

3. Right-click the names of the items you want to import and choose Import.

To import a table, select a name at the panel field level. To import a hierarchy, select a name at the tree level.

Metadata for PeopleSoft domains

This section discusses metadata for PeopleSoft domains. It includes the following topics:

16 Data Integrator Supplement for PeopleSoft

Metadata for PeopleSoft domains

• What is a domain?

• Using PeopleSoft domains in Data Integrator

What is a domain?

A domain is a lookup table that pairs a coded value with a textual description of the value. Domain values are typically used to specify column data where the possible range of values is constrained within a particular set.

Using a domain value instead of the text description can save space when the number of records is large. However, when working with the table to create queries, you might prefer to see a text description as opposed to looking for the appropriate code for the source data.

In PeopleSoft, domain values play a major role as the category name (or link) between a data value and its description.

Using PeopleSoft domains in Data Integrator

Domain values in PeopleSoft are available to end users only through PeopleSoft panels.

Data Integrator has built-in capabilities that make it much easier to deal with domain values in data movement applications.

You browse, search, and import domain metadata in the same way you browse, search, and import any PeopleSoft metadata. For specific instructions, see “Browsing PeopleSoft metadata” on page 13 and “Importing

PeopleSoft metadata” on page 14.

PeopleSoft tables have columns that should be constrained to contain only values from a particular set (that is, a set within a domain). This set of possible domain values is in another table in the PeopleSoft database called XLATT ABLE.

When importing table metadata from a PeopleSoft database, Data Integrator can automatically import associated domain data to make it easier to work with the values that appear in rows from the imported table. By default, Data Integrator imports associated domain data, but you can prevent this by deselecting the Import associated domains check box on the Import By Name window.

PeopleSoft Datastores

Data Integrator Supplement for PeopleSoft 17

PeopleSoft Datastores

Metadata for PeopleSof t trees

When importing domain values, the repository only stores the currently effective value. The currently effective value is determined using the date when the import takes place.

Imported domains appear nested under the datastore in the object library. For more information about PeopleSoft domains, see “Using PeopleSoft

domains in data flows” on page 23.

Metadata for PeopleSoft trees

You can perform the same kind of business model navigation and data browsing on PeopleSoft trees (called hierarchies in Data Integrator) that you can on other objects.

You browse, search, and import hierarchy metadata similarly to the way you browse, search, and import any PeopleSoft metadata. For specific instructions, see “Browsing PeopleSoft metadata” on page 13 and “Importing

PeopleSoft metadata” on page 14.

Imported hierarchies appear nested under the datastore in the object library. For more information about PeopleSoft trees, see “Extracting PeopleSoft tree

data” on page 26.

18 Data Integrator Supplement for PeopleSoft

Data Integrator Supplement for PeopleSoft

Data Flows with PeopleSoft Data

chapter

Data Flows with PeopleSoft Data

About this chapter

Data flows extract, transform, and load data. This chapter describes information unique to data flows that extract, transform, and load PeopleSoft data.

This chapter contains the following topics:

• Using effective dates from PeopleSoft data

• Using PeopleSoft domains in data flows

• Extracting PeopleSoft tree data

For information about Data Integrator data flows that is not PeopleSoftspecific, see “Data Flows” on page 167 of the Data Integrator Designer Guide.

Using effective dates from PeopleSoft data

This section provides an example that shows how you can use effective dates when populating a dimension table in the product group. This example includes several operations:

• Extracts data from table PS_PRODUCT_TBL

• Selects a subset of columns for the target

• Filters the data based on status

• Creates effective date ranges using the Effective Date transform

• Retrieves only the rows effective on a particular date

• Loads the data into the target

One data flow completes these operations.

Selecting a subset of source columns

The first query selects a subset of the columns.

20 Data Integrator Supplement for PeopleSoft

Filtering based on status

Data Flows with PeopleSoft Data

Using effective dates from PeopleSoft data

The first query includes a WHERE clause that limits the products selected to those with a status of values in the domains, see “Using PeopleSoft domains in data flows” on page 23.

To display the relevant domain values for the



1. Click Domains in the WHERE clause of the query editor. The Picker

window opens and shows a list of domains and descriptions.

EFF_STATUS column in the input. For general information about

Active. The status information comes from the domain

EFF_STATUS

Data Integrator Supplement for PeopleSoft 21

column

Data Flows with PeopleSoft Data

Using effective dates from PeopleSoft data

2. Select the EFF_STATUS domain and click the Show Domain Values

button (second button at the top of the Picker window) to see the available domain values. The domain values appear to the right of the descriptions.

3. To put the domain value in the a. Drag the

WHERE clause.

b. Enter an equal sign (=) after the column name. c. Place the cursor where the value needs to appear in the Where tab

and click the description name Active in the Picker window.

EFF_STATUS column from the source schema into the

WHERE clause,

Creating effective date ranges

This example retrieves only those product groups that are valid today. There are three steps involved:

1. Retrieve the effective-from date (

2. Generate effective-to dates for the source rows using the Effective Date

transform.

3. Filter out rows that are not effective on today’s date.

The Effective Date transform is described in detail in Chapter 5, “Transforms,”

in the Data Integrator Reference Guide.

EFFDT column) from the source.

Retrieving records with current effective dates

The second query in the data flow contains a WHERE clause that selects:

22 Data Integrator Supplement for PeopleSoft

Data Flows with PeopleSoft Data

Using PeopleSoft domains in data flows

• Those rows where the effective-from date from the source (EFFDT) is less

than or equal to the system date AND

• Those rows where the effective-to date from the results of the Effective

Date transform is greater than the system date

The query editor contains the

WHERE clause.

Using PeopleSoft domains in data flows

For general information about domains, see “Metadata for PeopleSoft

domains” on page 16.

Data Integrator helps you:

• Build queries containing domain values

• Substitute domain descriptions for cryptic domain values

• Build queries where you need to know some domain values, for example

while filtering

• Validate imported data that contains domain values

The properties for a given column enable you to explicitly associate a given column with a given domain.

You can assign any available domain to any column regardless of data type.



To assign a domain to a column

1. Right-click a table name in the object library and select Open.

Data Integrator Supplement for PeopleSoft 23

Data Flows with PeopleSoft Data

Using PeopleSoft domains in data flows

2. To open the column properties, right-click a column name in the table

metadata and select Properties.

3. The drop-down list in the Associated domain text box displays the

available domains.

Using the Picker window

To open the domain Picker window, click the Domains button in a query editor that has a table as a source.

24 Data Integrator Supplement for PeopleSoft

Data Flows with PeopleSoft Data

Using PeopleSoft domains in data flows

The four buttons at the top of the Picker window control the content of the window and initiate actions.

Click to toggle between displaying “all” and “relevant” domains. All domains (default) are listed in alphabetical order; relevant

domains are shown in the order in which they are used in the table or tables you have selected.

Click to display the domain values in the window. Click again to hide the domain values.

Click to toggle between pasting only the domain value (default) and both the value and a commented description in the

WHERE clause when you select the value.

Pastes all selected values in the are not included. To enable this button, use Ctrl-clicks to select more than one domain description.



To include a column and a related domain value in a WHERE clause

1. Drag the column from the source schema into the WHERE clause.

2. Enter an equal sign (=) after the column name.

3. Enter a value or paste the value from the Picker window.

Click Domains to open the Picker window.

Validating domains

WHERE clause. Descriptions

Capabilities built into Data Integrator allow you to validate data that contains domain values:

• value IN domain clause (part of a WHERE clause)

Data Integrator Supplement for PeopleSoft 25

Data Flows with PeopleSoft Data

Extracting PeopleSoft tree data

This clause enables you to ensure that all rows in a table have a valid value for a domain column.

Syntax:

value

This clause returns TRUE if the value is a member of the domain specified.

If the clause is part of a job being executed, Data Integrator searches the domain values in the

• get_domain_description function

This function returns the description for a domain name. The description is returned as a quoted string.

Syntax:

get_domain_description

(

'datastore_name..domain_name',

table_name.domain_name

Restrictions using domains

Some restrictions involving domains include:

• Prompt tables are not supported. (A prompt table is a user-defined table

similar to the XLATTABLE in a database.)

• Prior to validation at execution time, there is no type checking when a

domain is associated with a column.

datastore_name.owner.domain_name

XLATTABLE.

)

Extracting PeopleSoft tree data

In this example, Data Integrator extracts data from a hierarchy (tree) and loads it into an Oracle table.

You can browse hierarchy information when you open a datastore. Opening hierarchy groups in the workspace shows the tree levels.

26 Data Integrator Supplement for PeopleSoft

Reference Information

chapter

Reference Information

Overview

This chapter contains reference information specific to the PeopleSoft interface for Data Integrator.

Specifically, this section describes Data Integrator objects specific to PeopleSoft and Data Integrator objects with supplemental information for the PeopleSoft interface.

This chapter contains information about the following objects:

Object Class Description

Datastore Single-use Specifies the connection information Data

Integrator needs to access a database or other data source. Cannot be dropped.

Domain Reusable A lookup table that pairs a coded value with a

textual description of the value. Domain values are typically used to specify column data where the possible range of values is constrained within a particular set.

Hierarchy Reusable Browse, search, and import hierarchy

metadata.

Query Single-use Retrieves a data set that satisfies conditions

that you specify.

32 Data Integrator Supplement for PeopleSoft

Reference Information

Datastore

Class

Reusable

Access

In the object library, click the Datastores tab.

Description

A datastore provides a connection to a data source such as a database. Through the datastore connection, Data Integrator is able to import descriptions of the data source such as its metadat a. When you specify t ables as sources or targets in a data flow, Data Integrator uses the datastore to determine how to read data from or load data to those tables. In addition, some transforms and functions require a datastore name to qualify the tables they access.

If you delete a datastore from the object library, you must remove references to the datastore from the following locations:

• Source or target tables using this datastore in your diagrams

• The lookup and key_generation functions and Key_Generation,

History_Preserving, Table_Comparison, and SQL transform references

Datastores have the following properties:

Attribute Description

Name The name of the object. This name appears on the object

in the object library and in the calls to the object.

Description Text that you enter to describe and document the

datastore.

After you create a datastore, you can import metadat a about the objects, such as tables and functions, in that datastore. See Chapter 5, “Datastores,” in the Data Integrator Designer Guide.

Data Integrator Supplement for PeopleSoft 33

Reference Information

Datastore

PeopleSoft datastores

Set the following options to define a PeopleSoft datastore:

Option Description

Name Specify the datastore name. Data Integrator uses this

name to reference the datastore from other object definitions.

Datastore type Choose PeopleSoft to display the options for

PeopleSoft datastores. You cannot edit this option after creating the datastore.

Database type Select either Microsoft_SQL_Server or Oracle to

indicate the database type used as the database layer of your PeopleSoft application server.

The remaining options in the datastore definition are specific to the database type. Refer to the “Database

datastores” on page 65 of the Data Integrator

Reference Guide for details.

34 Data Integrator Supplement for PeopleSoft

Reference Information

Domain

Class

Reusable

Access

• To view the list of imported domains and domain values from the object

library, click the Datastores tab.

• Use domain values from inside query transforms with inputs from tables

associated with domains. Click Domains on the Where tab to open a window that helps you pick domain values.

Description

A domain is a lookup table that pairs a coded value with a text description of the value. You will typically use domain values to specify column data where the possible range of values is constrained to a particular set.

Domains have two built-in attributes.

Attribute Description

Name The name of the object. This name appears on the object

in the object library and in the calls to the object.

Description The description of the domain imported from the

application or database.

View domain values, descriptions of the values, and the effective dates for each value by opening the domain from the object library.

Data Integrator Supplement for PeopleSoft 35

Reference Information

Hierarchy

Class

Reusable

Access

In the object library, click the Datastores tab and expand a datastore listing (click the plus sign next to the datastore name).

Description

A hierarchy is a PeopleSoft tree. The Data Integrator datastore explorer lists the hierarchy types for a PeopleSoft datastore.

Imported tree

After you import the tree, it appears as a hierarchy object in the object library nested under the datastore name. The following sample object library shows the datastore psft1 and three trees under Hierarchies.

Data Integrator Supplement for PeopleSoft 37

Reference Information

Hierarchy

The hierarchy object has the following properties:

Property Description

Name The name of the hierarchy object. The name is constructed

as follows:

category-structure-tree(datastore)

The name (except the datastore name) is case sensitive.

Description The description of the tree as included in the PeopleSoft

table.

The hierarchy object has the following attributes, the values of which are included in the metadata imported for the hierarchy.

Attribute Description

Category Tree_Node_Description Structure Tree_Node_Record_Primpary_Key1 to Set_ID Snapshot_Date Tree_Leaf_Record_Name Tree_Name Tree_Leaf_Field_Name Tree_with_Leaf? Tree_Leaf_Description Tree_Node_Record_Name Tree_Leaf_Record_Primary_Key1 to Tree_Node_Field_Name

Tree_Node_Record_Primpary_Key9

Tree_Leaf_Record_Primary_Key9

Selecting the hierarchy in the object library and choosing Open displays the object properties and the source schema that results from the hierarchy extraction.

Source column name

NodeID The parent value in the relationship described by this row. Description The parent description. ChildID The child value in the relationship described by this row. ChildDesc The child description. MaxDepth The maximum number of nodes between the root node and

TreeLevel The level in the hierarchy that this row describes. Category The category in which this hierarchy is included. Structure The tree structure in which this hierarchy is included.

38 Data Integrator Supplement for PeopleSoft

Description

the lowest node in the hierarchy.

Reference Information

Hierarchy

Source column name

SETID The identification of the subset of the tree. Each set groups

SnapshotDate EFFDT The date when the data in a set became or will become

Description

together data that have similar values or rules.

The date when the hierarchy data was extracted.

effective.

Hierarchy instance

Y ou can drag a hierarchy from the object library into a dat a flow definition. The hierarchy object appears in the data flow definition as follows:

Data Integrator Supplement for PeopleSoft 39

Reference Information

Hierarchy

The hierarchy editor displays the datastore information for the hierarchy and provides options for the instance of the hierarchy object:

Option Description

All set IDs or Set ID

Current date or All dates or Snapshot date

Set ID selects one or more trees out of the structure

imported into Data Integrator. Use commas to separate multiple set IDs.

The set ID identifies a subset of the tree that groups together data that have similar values or rules. It appears as part of the name of the level below the level of hierarchy structure imported into Data Integrator.

All set IDs selects all versions of a tree.

Current date selects only the data that has an

effective date that is equal to the date returned by the

sysdate function.

All dates selects data with all effective dates. Snapshot date filters the extracted values by the

effective date you specify in one of the following ways:

• Date allows you to specify the date as a four-digit

year, a period, a two digit month, a period, and a two-digit day (YYYY.MM.DD). You can select each part of the date and use the arrows to increment or decrement the digits.

• Variable allows you to specify a varchar variable

name that contains a valid date in the yyyy.mm.dd format

You can select one of the variable names in the drop-down list. Alternatively, can type in the name of a new variable, but you must define it in the Variables and Parameters window (Tools > Variables).

If you do not enter a variable name, the

Snapshot date defaults to the current date.

Note: You can use a local or global variable for the

date. Because the variable can be a global variable, validating the data flow or work flow does not check for invalid data types for this variable. Be sure to validate the Job to check for invalid data types for this date variable.

40 Data Integrator Supplement for PeopleSoft

Reference Information

Hierarchy

In the above hierarchy editor, the Set ID option specifies the set, BNUSA, from which to extract data.

When you open the PeopleSoft datastore, you can see the Set ID values in the Datastore Explorer. The above Datastore Explorer shows:

• The tree DEPT_SECURITY, which is the level of the hierarchy that you

import the metadata from a PeopleSoft source.

• The Set IDs AUS01, BEL01, BNCAN, BNUSA, and CAN01 which appear as

part of the names in the level below the tree imported into Data Integrator. Each Set ID represents a different version of the tree.

• Multiple effective dates (01/01/1996, 01/01/1994, 01/01/1990, and

01/01/1996) which follow each Set ID in the names in this level.

Data Integrator Supplement for PeopleSoft 41

Reference Information

Query

Class

Single-use

Access

With a data flow diagram in the workspace, click the query icon in the tool palette, then click in the workspace.

Description

A Query transform, like a SQL SELECT statement, retrieves a data set that satisfies the conditions you specify. With a Query transform, you can:

• Map columns from input to output schema

• Add new columns, nested schemas, and functions to the output schema

• Choose the data to extract

• Perform operations on the data

• Join data from multiple sources

For a complete description of the Query transform, see “Query” on page 331

of the Data Integrator Reference Guide.

Editor

Where tab — Use the Where tab to set conditions that determine which rows

Data Integrator outputs. With the PeopleSoft interface, you can use the Domains button. Domains

constrain retrieved data sets. Click the Domains button to open the Picker window, which helps you build an expression using a domain. For more information about domains, see “Metadata for PeopleSoft domains” on

page 16.

42 Data Integrator Supplement for PeopleSoft

Index

customer support 8

Data Integrator

PeopleSoft environment 6 support 8

databases

connecting to 33

datastores

defining, PeopleSoft 12 description 33

defining

datastores, PeopleSoft 12

deleting

datastores 33

domains

definition 17 description 35 displaying values 23 filtering based on 23 including in where clause 25 restrictions 26 validating 25 values, viewing 7

effective date, PeopleSoft

definition 39

effective dates, PeopleSoft

example of 20 extracting by 29 using, overview 7

viewing 41 Effective_Date transform 22 extracting data

hierarchical in PeopleSoft 26

get_domain_description function 26

hierarchies

description 36 levels within 36 viewing types 36

importing metadata

PeopleSoft 14

metadata

importing PeopleSoft 14

objects

list of 32

PeopleSoft

domains 17, 35 EFF_STATUS column 21 effective dates 20 environment 6 extracting hierarchical data 26 filtering on EFF_STATUS column 21, 22 metadata 13, 14 sets 29 trees 36

Picker window 21, 24

Data Integrator Supplement for PeopleSoft 43

Index

query transforms

description 42

where tab 22, 35, 42

set IDs, PeopleSoft

definition 39, 40

specifying 28, 41

viewing 41

set, PeopleSoft

definition 37

snapshot date, PeopleSoft

default value 29

definition 39, 40

specifying 29, 40

support, Data Integrator 8

value IN domain clause 25

WHERE clause

domain values, including 25

where tab, query transform 22, 35, 42

44 Data Integrator Supplement for PeopleSoft

Business objects DATA INTEGRATOR 11.5.1.5 User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual

Contents

Introduction

Overview

Using Data Integrator in a PeopleSoft environment

About this document

More Data Integrator product documentation

PeopleSoft Datastores

Overview of PeopleSoft datastores

Defining PeopleSoft datastores

Browsing PeopleSoft metadata

Importing PeopleSoft metadata

Metadata for PeopleSof t domains

What is a domain?

Using PeopleSoft domains in Data Integrator

Metadata for PeopleSof t trees

Data Flows with PeopleSoft Data

About this chapter

Using effective dates from PeopleSoft data

Selecting a subset of source columns

Filtering based on status

Creating effective date ranges

Retrieving records with current effective dates

Using PeopleSoft domains in data flows

Using the Picker window

Validating domains

Extracting PeopleSoft tree data

Restrictions using domains

Reference Information

Overview

Datastore

Class

Access

Description

PeopleSoft datastores

Domain

Class

Access

Description

Hierarchy

Class

Access

Description

Imported tree

Hierarchy instance

Query

Class

Access

Description

Editor

Index