Page 1
Supplement for PeopleSoft
■ SAP BusinessObjects Data Services XI 4.0 (14.0.0)
2010-12-02
Page 2
Copyright
© 2010 SAP AG. All rights reserved.SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP
Business ByDesign, and other SAP products and services mentioned herein as well as their respective
logos are trademarks or registered trademarks of SAP AG in Germany and other countries. Business
Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web
Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well
as their respective logos are trademarks or registered trademarks of Business Objects S.A. in the
United States and in other countries. Business Objects is an SAP company.All other product and
service names mentioned are the trademarks of their respective companies. Data contained in this
document serves informational purposes only. National product specifications may vary.These materials
are subject to change without notice. These materials are provided by SAP AG and its affiliated
companies ("SAP Group") for informational purposes only, without representation or warranty of any
kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The
only warranties for SAP Group products and services are those that are set forth in the express
warranty statements accompanying such products and services, if any. Nothing herein should be
construed as constituting an additional warranty.
2010-12-02
Page 3
Contents
Introduction.............................................................................................................................5Chapter 1
1.1
2.1
2.2
2.3
2.4
2.5
2.6
2.7
2.8
2.8.1
2.8.2
2.9
3.1
3.1.1
3.1.2
3.1.3
3.1.4
3.1.5
3.1.6
3.1.7
3.1.8
3.1.9
3.1.10
3.2
Using Data Services in a PeopleSoft environment....................................................................5
PeopleSoft Datastores............................................................................................................7Chapter 2
Defining PeopleSoft datastore.................................................................................................7
Browsing PeopleSoft metadata................................................................................................8
To browse PeopleSoft metadata..............................................................................................8
Importing PeopleSoft metadata................................................................................................9
To import PeopleSoft metadata by name..................................................................................9
To import PeopleSoft metadata by searching...........................................................................9
To import PeopleSoft metadata by browsing..........................................................................10
Metadata for PeopleSoft domains..........................................................................................10
What is a domain?..................................................................................................................10
Using PeopleSoft domains in SAP BusinessObjects Data Services.......................................11
Metadata for PeopleSoft trees...............................................................................................11
Data Flows with PeopleSoft Data.........................................................................................13Chapter 3
Using effective dates from PeopleSoft data...........................................................................13
Selecting a subset of source columns....................................................................................13
Filtering based on status........................................................................................................14
To display the relevant domain values for the EFF_STATUS column.......................................14
Creating effective date ranges...............................................................................................14
Retrieving records with current effective dates.......................................................................15
Using PeopleSoft domains in data flows ................................................................................15
Using the Picker window........................................................................................................16
To include a column and a related domain value in a WHERE clause.......................................16
Validating domains.................................................................................................................16
Restrictions using domains.....................................................................................................17
Extracting PeopleSoft tree data..............................................................................................17
4.1
Reference Information...........................................................................................................21Chapter 4
Datastore...............................................................................................................................21
2010-12-023
Page 4
Contents
4.2
4.3
4.4
Index 31
Domain..................................................................................................................................23
Hierarchy...............................................................................................................................23
Query.....................................................................................................................................29
2010-12-024
Page 5
Introduction
Introduction
Welcome to the
information about how to use Data Services with PeopleSoft.
SAP BusinessObjects Data Services Supplement for PeopleSoft
1.1 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 SAP BusinessObjects Data Services.
Through the PeopleSoft datastore you define, you can navigate PeopleSoft metadata external to SAP
BusinessObjects Data Services (using standard PeopleSoft panels and menus) and import metadata
for PeopleSoft source tables into the repository.
The following diagram shows PeopleSoft tables, hierarchies, and domains that have been imported
into the software.
. This guide contains
In addition, capabilities in the software 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.
2010-12-025
Page 6
Introduction
• The software can determine whether a given value for a column is valid within the domain of the
column.
The software flags those that are not valid.
• The software 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.
• The software allows extraction of the hierarchical data in PeopleSoft trees.
2010-12-026
Page 7
PeopleSoft Datastores
PeopleSoft Datastores
With the PeopleSoft interface, you can use a PeopleSoft system as an SAP BusinessObjects Data
Services source. To use a PeopleSoft data source, you must:
• Define a datastore that will serve as the logical link to your PeopleSoft system
• Import your PeopleSoft metadata into the datastore
Related Topics
• Designer Guide: Datastores
2.1 Defining PeopleSoft datastore
You must define a PeopleSoft datastore in SAP BusinessObjects 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.
7.
Click OK.
The software creates the datastore and it appears in the object library window.
Related Topics
• Reference Guide: Datastore
2010-12-027
Page 8
PeopleSoft Datastores
2.2 Browsing PeopleSoft metadata
After you create the datastore, you can access the PeopleSoft metadata via the SAP BusinessObjects
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 the software 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.
DescriptionMetadata typeIcon
Menu Group
Panel
The entire set of commands available in PeopleSoft applications for
a specific database.
A set of commands for a specific application.Menu
The top level of the menu.Menu Bar
The commands that make up each menu bar.Menu Item
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.Panel Fields
2.3 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.
2010-12-028
Page 9
PeopleSoft Datastores
2.4 Importing PeopleSoft metadata
To access PeopleSoft metadata from SAP BusinessObjects Data Services, you must import the metadata
into the object library.
You can import tables, PeopleSoft trees (called “hierarchies” in SAP BusinessObjects Data Services),
and domains.
You can import PeopleSoft metadata in one of three ways:
• By name
• By browsing
• By searching
2.5 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.
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 by deselecting the Import associated domains option.
5.
To import a hierarchy, enter the tree name in the Name box and enter the Category and Structure
associated with the hierarchy.
6.
Click OK.
The information appears in the object library.
2.6 To import PeopleSoft metadata by searching
You can search for PeopleSoft items as you would any items to be imported into the software. That is,
right-click the datastore name in the object library and choose Search.
2010-12-029
Page 10
PeopleSoft Datastores
Related Topics
• Designer Guide: Importing metadata through a database datastore
2.7 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
2.8 Metadata for PeopleSoft domains
This section discusses metadata for PeopleSoft domains.
Related Topics
• What is a domain?
• Using PeopleSoft domains in SAP BusinessObjects Data Services
2.8.1 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.
2010-12-0210
Page 11
PeopleSoft Datastores
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.
2.8.2 Using PeopleSoft domains in SAP BusinessObjects Data Services
Domain values in PeopleSoft are available to end users only through PeopleSoft panels.
SAP BusinessObjects Data Services 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.
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 XLATTABLE.
When importing table metadata from a PeopleSoft database, the software 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, the software imports associated domain data, but you can prevent this by deselecting
the Import associated domains check box on the Import By Name window.
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.
Related Topics
• Browsing PeopleSoft metadata
• Importing PeopleSoft metadata
• Using PeopleSoft domains in data flows
2.9 Metadata for PeopleSoft trees
You can perform the same kind of business model navigation and data browsing on PeopleSoft trees
(called hierarchies in Data Services) 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.
2010-12-0211
Page 12
PeopleSoft Datastores
Imported hierarchies appear nested under the datastore in the object library.
Related Topics
• Browsing PeopleSoft metadata
• Importing PeopleSoft metadata
• Extracting PeopleSoft tree data
2010-12-0212
Page 13
Data Flows with PeopleSoft Data
Data Flows with PeopleSoft Data
Data flows extract, transform, and load data. This section describes information unique to data flows
that extract, transform, and load PeopleSoft data.
Related Topics
• Designer Guide: Data Flows
3.1 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.
3.1.1 Selecting a subset of source columns
The first query selects a subset of the columns:
PS_PRODUCT_TBL.EFF_STATUS = 'A'
2010-12-0213
Page 14
Data Flows with PeopleSoft Data
3.1.2 Filtering based on status
The first query includes a WHERE clause that limits the products selected to those with a status of Active.
The status information comes from the domain values in the EFF_STATUS column in the input.
Related Topics
• Using PeopleSoft domains in data flows
3.1.3 To display the relevant domain values for the EFF_STATUS column
1.
Click Domains in the WHERE clause of the query editor. The Picker window opens and shows a list
of domains and descriptions.
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 WHERE clause,
a. Drag the EFF_STATUS column from the source schema into 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.
3.1.4 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 (EFFDT column) from the source.
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.
Related Topics
• Reference Guide: Transforms
2010-12-0214
Page 15
Data Flows with PeopleSoft Data
3.1.5 Retrieving records with current effective dates
The second query in the data flow contains a WHERE clause that selects:
• 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.
Effective_Date.EFFDT <= sysdate( ) and
Effective_Date.EFFECTIVE_TO_COLUMN > sysdate ( )
3.1.6 Using PeopleSoft domains in data flows
SAP BusinessObjects Data Services 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.
Related Topics
• Metadata for PeopleSoft domains
3.1.6.1 To assign a domain to a column
1.
Right-click a table name in the object library and select Open.
2.
To open the column properties, right-click a column name in the table metadata and select Properties.
2010-12-0215
Page 16
Data Flows with PeopleSoft Data
3.
The drop-down list in the Associated domain text box displays the available domains.
Any available domain can be assigned to any column regardless of data type.
3.1.7 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.
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 WHERE clause. Descriptions are not included. To enable
this button, use Ctrl-clicks to select more than one domain description.
3.1.8 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.
3.1.9 Validating domains
2010-12-0216
Page 17
Data Flows with PeopleSoft Data
Capabilities built into SAP BusinessObjects Data Services allow you to validate data that contains
domain values:
• value IN domain clause (part of a WHERE clause)
This clause enables you to ensure that all rows in a table have a valid value for a domain column.
Syntax:
value IN datastore_name.owner.domain_name
This clause returns TRUE if the value is a member of the domain specified.
If the clause is part of a job being executed, the software searches the domain values in the
XLATTABLE.
• 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)
3.1.10 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.
3.2 Extracting PeopleSoft tree data
In this example, SAP BusinessObjects Data Services 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.
2010-12-0217
Page 18
Data Flows with PeopleSoft Data
1.
Category
2.
Structure
3.
Tree
4.
Set
To import the tree into SAP BusinessObjects Data Services, select the tree, right-click, and choose
Import.
After you import the tree, it appears as a hierarchy in the object library.
The job consists of a single data flow that extracts the data and loads it into the Oracle table.
Double-clicking the hierarchy object opens the editor to show the options available for the object.
On the hierarchy editor, you must specify:
• All set IDs, a specific set ID, or multiple set IDs
Select the All set IDs check box to obtain all the set IDs associated with this hierarchy. Each set ID
identifies a subset of a tree that groups data that have similar values or rules. In this example, the
tree contains a set of data for Austria (AUS01), a set for Belgium (BEL01), a set for Canada (BNCAN),
and so forth.
Clear the All set IDs check box to open the Set ID box where you can:
• Enter the name of the specific set ID for which you want to extract data.
• Enter multiple set IDs, separated by commas. For example,
AUS01, BEL01, BNCAN
2010-12-0218
Page 19
Data Flows with PeopleSoft Data
• Date options
• Current date
Select Current date to extract data that is effective as of the date returned by the sysdate function.
• All dates
Select All dates to extract data with all effective dates for the set IDs that you specified.
• Snapshot date
Select Snapshot date to extract data that was effective on a specific date. Specify a date in the
past or future in one of the following ways:
• Date
Select Date in the drop-down list 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 each value.
• Variable
Select Variable in the drop-down list 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, you 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.
Before you load the tree data into the table, you can flatten it with the Hierarchy_Flattening transform.
On the transform editor, you:
• Choose to flatten the tree horizontally or vertically.
• Must specify the Parent column and Child column to identify the columns in the source data that
contain the parent identifier and child identifier in each parent-child relationship.
• Must select View > Refresh or press F5 to refresh the target schema after you identify the source
columns.
Related Topics
• PeopleSoft Datastores
• Hierarchy
2010-12-0219
Page 20
Data Flows with PeopleSoft Data
2010-12-0220
Page 21
Reference Information
Reference Information
This section contains reference information specific to the PeopleSoft interface for SAP BusinessObjects
Data Services.
Specifically, this section describes SAP BusinessObjects Data Services objects specific to PeopleSoft
and SAP BusinessObjects Data Services objects with supplemental information for the PeopleSoft
interface.
This section contains information about the following objects:
DescriptionClassObject
4.1 Datastore
Class
Reusable
Single-useDatastore
ReusableDomain
Specifies the connection information Data Services needs to access a
database or other data source. Cannot be dropped.
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.
Browse, search, and import hierarchy metadata.ReusableHierarchy
Retrieves a data set that satisfies conditions that you specify.Single-useQuery
Access
In the object library, click the Datastores tab.
2010-12-0221
Page 22
Reference Information
Description
A datastore provides a connection to a data source such as a database. Through the datastore
connection, SAP BusinessObjects Data Services is able to import descriptions of the data source such
as its metadata. When you specify tables as sources or targets in a data flow, the software 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_Com
parison, and SQL transform references
Datastores have the following properties:
DescriptionAttribute
Name
The name of the object. This name appears on the object in the object
library and in the calls to the object.
Text that you enter to describe and document the datastore.Description
After you create a datastore, you can import metadata about the objects, such as tables and functions,
in that datastore.
PeopleSoft datastores
Set the following options to define a PeopleSoft datastore:
DescriptionOption
Name
Datastore type
Specify the datastore name. The software uses this name to reference
the datastore from other object definitions.
Choose PeopleSoft to display the options for PeopleSoft datastores.
You cannot edit this option after creating the datastore.
Select either Microsoft_SQL_Server or Oracle to indicate the
database type used as the database layer of your PeopleSoft appli-
Database type
cation server.
The remaining options in the datastore definition are specific to the
database type.
2010-12-0222
Page 23
Reference Information
Related Topics
• Designer Guide: Datastores
• Reference Guide: Database datastores
4.2 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.
Name
View domain values, descriptions of the values, and the effective dates for each value by opening the
domain from the object library.
4.3 Hierarchy
DescriptionAttribute
The name of the object. This name appears on the object in the object library and
in the calls to the object.
The description of the domain imported from the application or database.Description
2010-12-0223
Page 24
Reference Information
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 SAP BusinessObjects Data Services datastore explorer lists the
hierarchy types for a PeopleSoft datastore.
A hierarchy contains the following levels:
1.
Category: A category represents a major business organization. The above datastore explorer
displays two categories: DEFAULT and HR.
2.
Tree structure: A tree structure represents a department or group within a category. The above
datastore explorer displays three tree structures: COMPENSATION, COMPETENCY, and DEPARTMENT.
3.
Tree: A tree represents a specific type of data within a tree structure. The above datastore explorer
shows one tree, DEPT_SECURITY. You import hierarchical data at the tree level.
4.
Set: A set is a subset of the tree that contains data that have similar values or rules. Each set is
identified by a set ID. The above datastore explorer shows set IDs APR, BEL, CAN, and so forth, and
each set contains data specific to an organization.
To extract hierarchical information, select a hierarchy type or tree, import its metadata into your repository,
then drag the hierarchy icon from the object library into a data flow.
From the datastore explorer, you can choose the hierarchy to import.
You can also import a tree by specifying the tree by name. Select the datastore in the object library,
right-click, and choose Import By Name.
Choose Hierarchy from the Type drop-down menu.
2010-12-0224
Page 25
Reference Information
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.
Structure
Set_ID
The hierarchy object has the following properties:
DescriptionProperty
The name of the hierarchy object. The name is constructed as follows:
Name
category-structure-tree(datastore)
The name (except the datastore name) is case sensitive.
The description of the tree as included in the PeopleSoft table.Description
The hierarchy object has the following attributes, the values of which are included in the metadata
imported for the hierarchy.
DescriptionAttribute
Tree_Node_DescriptionCategory
Tree_Node_Record_Primary_Key1 -Tree_Node_Record_Primary_Key9
Snapshot_Date
Tree_Leaf_Field_NameTree_Name
Tree_Leaf_DescriptionTree_with_Leaf?
2010-12-0225
Page 26
Reference Information
Tree_Node_Record_Name
Tree_Node_Field_Name
Selecting the hierarchy in the object library and choosing Open displays the object properties and the
source schema that results from the hierarchy extraction.
DescriptionAttribute
Tree_Leaf_Record_Primary_Key1 - Tree_Leaf_Record_Primary_Key9
DescriptionSource column name
The parent value in the relationship described by this row.NodeID
The parent description.Description
The child value in the relationship described by this row.ChildID
The child description.ChildDesc
MaxDepth
The maximum number of nodes between the root node and the lowest
node in the hierarchy.
The level in the hierarchy that this row describes.TreeLevel
The category in which this hierarchy is included.Category
The tree structure in which this hierarchy is included.Structure
SETID
The identification of the subset of the tree. Each set groups together data
that have similar values or rules.
The date when the hierarchy data was extracted.SnapshotDate
The date when the data in a set became or will become effective.EFFDT
Hierarchy instance
You can drag a hierarchy from the object library into a data flow definition.
The hierarchy editor displays the datastore information for the hierarchy and provides options for the
instance of the hierarchy object:
2010-12-0226
Page 27
Reference Information
DescriptionOption
Set ID selects one or more trees out of the structure imported into
SAP BusinessObjects Data Services. Use commas to separate
multiple set IDs.
All set IDs or Set ID
Current date or All dates or Snapshot
date
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
the software.
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 peri-
od, 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.
2010-12-0227
Page 28
Reference Information
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 the software. 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.
Related Topics
• Importing PeopleSoft metadata
• Extracting PeopleSoft tree data
• Importing PeopleSoft metadata
2010-12-0228
Page 29
Reference Information
4.4 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
Editor
From tab: Use the From tab to specify joins and set join conditions.
Where tab: Use the Where tab to restrict the result set.
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.
Related Topics
• Reference Guide: Query
• Metadata for PeopleSoft domains
2010-12-0229
Page 30
Reference Information
2010-12-0230
Page 31
Index
D
Data Services
PeopleSoft environment 5
databases, connecting to 21
datastores
defining, PeopleSoft 7
description 21
defining datastores, PeopleSoft 7
deleting datastores 21
domains
definition 10
description 23
displaying values 15
filtering based on 15
including in where clause 16
restrictions 17
validating 17
values, viewing 5
E
effective date, PeopleSoft, definition
11
effective dates, PeopleSoft
example of 13
extracting by 17
using, overview 5
viewing 23
Effective_Date transform 14
extracting data
hierarchical in PeopleSoft 17
G
get_domain_description function 17
H
hierarchies
description 23
levels within 23
viewing types 23
I
importing metadata, PeopleSoft 9
M
metadata, importing PeopleSoft 9
O
objects, list of 21
P
PeopleSoft
domains 10, 23
EFF_STATUS column 14
effective dates 13
environment 5
extracting hierarchical data 17
filtering on EFF_STATUS column
14
PeopleSoft
metadata 9
sets 17
trees 23
Picker window 14, 16
(continued)
Q
query transforms
description 29
where tab 14, 23, 29
S
set IDs PeopleSoft
viewing 23
set IDs, PeopleSoft
definition 11
specifying 17, 23
set, PeopleSoft, definition 23
snapshot date, PeopleSoft
default value 17
definition 11
specifying 11, 17
V
value IN domain clause 17
W
WHERE clause, domain values,
including 16
where tab, query transform 14, 23, 29
2010-12-0231
Page 32
Index
2010-12-0232
Loading...