SAP Business objects DATA SERVICES Supplement for Peoplesoft

Data Services Supplement for Peoplesoft

BusinessObjects Data Services XI 3.0 (12.0.0)

© 2008 Business Objects. All rights reserved. Business Objects owns the following U.S. patents, which may cover products that are offered and licensed by Business Objects: 5,555,403; 5,857,205; 6,289,352; 6,247,008; 6,490,593; 6,578,027; 6,831,668; 6,768,986; 6,772,409; 6,882,998; 7,139,766; 7,299,419; 7,194,465; 7,222,130; 7,181,440 and 7,181,435. Business Objects and the Business Objects logo, BusinessObjects, Business Objects Crystal Vision, Business Process On Demand, BusinessQuery, Crystal Analysis, Crystal Applications, Crystal Decisions, Crystal Enterprise, Crystal Insider, Crystal Reports, Desktop Intelligence, Inxight, the Inxight Logo, 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. 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-03-12

Introduction 5Chapter 1

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

PeopleSoft Datastores 9Chapter 2

Defining PeopleSoft datastore...................................................................10

Browsing PeopleSoft metadata.................................................................11

To browse PeopleSoft metadata................................................................12

Importing PeopleSoft metadata.................................................................13

To import PeopleSoft metadata by name..................................................13

To import PeopleSoft metadata by searching............................................15

To import PeopleSoft metadata by browsing.............................................15

Metadata for PeopleSoft domains.............................................................15

Metadata for PeopleSoft trees...................................................................17

Data Flows with PeopleSoft Data 19Chapter 3

What is a domain?................................................................................16

Using PeopleSoft domains in Data Services........................................16

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

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

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

To display the relevant domain values for the EFF_STATUS column...21

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

Retrieving records with current effective dates....................................23

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

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

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

Data Services Supplement for Peoplesoft 3

Contents

Validating domains...............................................................................26

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

Extracting PeopleSoft tree data.................................................................27

Reference Information 33Chapter 4

Datastore...................................................................................................34

Domain......................................................................................................36

Hierarchy...................................................................................................38

Query.........................................................................................................45

Index 47

4 Data Services Supplement for Peoplesoft

Introduction

Using Data Services in a PeopleSoft environment

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

Related Topics

• Using Data Services in a PeopleSoft environment on page 6

Using Data Services 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 Services.

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

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

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

6 Data Services Supplement for Peoplesoft

Introduction

Using Data Services in a PeopleSoft environment

• 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 Services can determine whether a given value for a column is valid

within the domain of the column.

Data Services flags those that are not valid.

• Data Services allows extraction of data based on effective 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 Services allows extraction of the hierarchical data in PeopleSoft

trees.

Data Services Supplement for Peoplesoft 7

Introduction

Using Data Services in a PeopleSoft environment

8 Data Services Supplement for Peoplesoft

PeopleSoft Datastores

Defining PeopleSoft datastore

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

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

PeopleSoft system

• Import your PeopleSoft metadata into the Data Services datastore

Related Topics

• Designer Guide: Datastores

Defining PeopleSoft datastore

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

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 DatastoreName 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.

10 Data Services Supplement for Peoplesoft

PeopleSoft Datastores

Browsing PeopleSoft metadata

7. Click OK.

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

Related Topics

• Reference Guide: Datastore

Browsing PeopleSoft metadata

After you create the datastore, you can access the PeopleSoft metadata via the Data Services 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 GUI-related items is included.

Browsing PeopleSoft data in Data Services 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.

Data Services Supplement for Peoplesoft 11

PeopleSoft Datastores

To browse PeopleSoft metadata

Menu Group

DescriptionMetadata typeIcon

The entire set of commands available in PeopleSoft applications for a specific database.

Menu Item

Panel

Panel Fields

A set of commands for a specific application.

The top level of the menu.Menu Bar

The commands that make up each menu bar.

A vehicle for capturing and displaying data. Each panel can have multiple sub-panels, each represented by the same icon.

Represents a column on a table or in a specific view.

To browse PeopleSoft 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.

12 Data Services Supplement for Peoplesoft

PeopleSoft Datastores

Importing PeopleSoft metadata

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

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

You can import PeopleSoft metadata in one of three ways:

• By name

• By browsing

• By searching

To import PeopleSoft metadata by name

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

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

Data Services Supplement for Peoplesoft 13

PeopleSoft Datastores

To import PeopleSoft metadata 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.

The information appears in the object library.

14 Data Services Supplement for Peoplesoft

PeopleSoft Datastores

To import PeopleSoft metadata by searching

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

Related Topics

• Designer Guide: Importing metadata through a database datastore

To 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.

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.

Related Topics

• Browsing PeopleSoft metadata on page 11

Metadata for PeopleSoft domains

This section discusses metadata for PeopleSoft domains.

Related Topics

• What is a domain? on page 16

• Using PeopleSoft domains in Data Services on page 16

Data Services Supplement for Peoplesoft 15

+ 33 hidden pages