This chapter provides an overview of metadata, lists common elements, and discusses how to:
Find metadata objects.
Apply the hidden flag to metadata objects.
Set up record metadata.
Set up rule metadata.
Set up tablemaps.
Set up datamaps.
Set up expressions.
Use data sets.
Set up filters.
Set up constraints.
Create user-defined functions.
Set up and flatten tree metadata.
Set up report metadata.
Set up metric metadata.
Define and set up dimensions.
Clone metadata.
Use the Metadata Mover utility.
Delete metadata.
Archive PeopleSoft Performance Management Warehouse data.
The PeopleSoft Performance Management Warehouse is supported by a framework of metadata. Metadata defines everything from table and data structures to rules for running processes. Metadata is central to the entire PeopleSoft Enterprise Performance Management (PeopleSoft EPM) product line.
When you run PeopleSoft EPM processes, you require two types of input: your business data and the appropriate EPM Foundation metadata. Metadata provides an abstraction layer, enabling technical users to establish dynamic relationships between warehouse tables, business users to easily identify the data that interests them without having to know the database structure, and warehouse administrators to manage warehouse processes.
Metadata is information that is used by processes and application engines to define rules and physical objects such as tables or trees. For instance, you may want an application engine to process certain columns from certain tables. Instead of entering a long SQL statement, you can define a datamap.
This diagram shows the relationship between data and metadata in PeopleSoft EPM.
EPM data and metadata
This section discusses:
Metadata terms and objects.
SQL object ID.
Metadata utilities.
When we discuss metadata, we discuss:
Technical metadata defines the physical structures of PeopleSoft Performance Management Warehouse and enables users to work with PeopleSoft Performance Management Warehouse applications without the need to understand the underlying structures. Examples are record metadata, tablemaps, datamaps, filters, and constraints.
Business intelligence metadata.
Business intelligence metadata defines how information is to be used to provide end users with the information they need to work most productively. An example of business intelligence metadata is the KPI Manager.
Operational metadata provides information about the flow of data through PeopleSoft Performance Management Warehouse. An example of operational metadata is extract, transform, and load (ETL) jobs.
The metadata structure that is used by the PeopleSoft EPM engines is built in several layers or levels, each dependent on the layer below it. The following table lists the metadata layers that are found in PeopleSoft EPM in order from the lowest to the highest level:
Metadata Object |
Includes |
Record metadata |
Defines the lowest level of PeopleSoft Performance Management Warehouse metadata table objects. This is the foundation on which all other metadata is defined. Record metadata defines and identifies PeopleSoft Performance Management Warehouse data tables. |
Tablemaps |
Define the physical relationships (joins) between tables and are foundations for datamaps. |
Datamaps |
Built on tablemaps, datamaps are similar to table definitions in that they describe a logical view of the tables themselves. They enable you to select information from different tables that are specified in a tablemap and define it as if it were one entity or table. |
Constraints |
Built on datamaps and can use one or more filters to define your business processing rules. |
Filters |
Enable you to define what subset of data gets processed by or uses a specific business rule. |
This diagram illustrates how PeopleSoft EPM Foundation metadata is nested to define a SQL statement.
EPM Foundation metadata, SQL
The resulting SQL is SELECT (Datamap) FROM (Tablemap) WHERE (Constraints/Filters).
Other types of metadata and terms are:
ERP metadata |
Examples are calendars, fiscal year, accounting period, business unit, and setID. |
Data sets |
Used as input for various engines. |
Expressions |
Enable you to create virtual columns that are made up of mathematical calculations based on actual fields on a table. |
Tree metadata |
Captures information about the trees that you have set up in PeopleSoft EPM. It is mainly used for reporting. |
Rule metadata |
Gathers the rules for PeopleSoft EPM engines based on the standard keys of setID, business unit, model ID, and effective date. Rule metadata is used by PeopleSoft EPM engines to recursively determine inheritance rules between models. |
Balancing rules |
Enable you to track before and after amounts in the system. |
Job totals |
Define flash totals for data to be used as input or output to the different source or target tables in the system. |
PF_RECONCILIATION engine |
PF_RECONCILIATION uses the job totals and balancing rules metadata that you set up to validate balances. |
Engine and job metadata |
Delivered metadata for running jobs and jobstreams. |
Not all the metadata objects are described in this chapter. Refer to the following chapters for information about rule and engine metadata and balancing rules and job totals metadata.
See Also
Using Balancing and Reconciliation Features
The SQL object ID is a system-generated number that identifies a SQL object that is generated by a metadata component and is stored in the PeopleSoft SQL repository. The prefix identifies the PeopleSoft EPM product, metadata object, and sequential number for each metadata object respectively, for example PF$_MR_140. Many setup pages within PeopleSoft EPM have a SQL object ID associated with the page definition.
Various types of SQL object ID prefixes exist, depending on the metadata object that you set up:
SQL Object ID Prefix |
Metadata Object |
PF$_MR_# |
Record Metadata |
PF$_RS_# |
Record summary metadata |
PF$_TM_# |
Tablemaps |
PF$_DM_# |
Datamaps |
PF$_EX_# |
Expressions |
PF$_CN_# |
Constraints |
PF$_RL_# |
Rule Metadata |
PF$_DS_# |
Data sets |
MD$_xxx_# |
Data Manager. Where xxx is the rule ID. |
AB$_yyy_xxx_# |
ABM. Where yyy = setID, and xxx = rule ID. |
Several utilities are available that you can use to search for and validate your metadata objects. These utilities include :
Metadata Search engine: Enables you to search for metadata objects based on the description.
Mass Validate: Enables you to check the validity of your metadata objects before running any PeopleSoft EPM engines or processes that depend on it.
Impact Analysis tool: Enables you to determine the interdependencies of metadata before you change objects.
These utilities are described later in this PeopleBook.
Warning! Only an experienced user should make changes to existing metadata.
See Also
Hidden Object |
This is a check box that enables power users to edit metadata objects while ensuring that everyday users can only view the objects. It enables another level of security to be applied to metadata. |
Owner ID |
Assign an owner ID to a particular metadata object. The owner represents an EPM functional area (such as ABM or Budgeting) that is associated with a metadata object. Assigning an owner ID to the metadata organizes and groups the metadata, making it easier to locate and audit. After an owner ID is defined for record metadata, any tablemap, datamap, and constraint built on this record inherits the owner ID of the primary table. Note. You can add owner ID values by updating the translate values in the Owner table (PF_OWNER). However, the added values represent a configuration and are not supported and must be migrated on upgrade. Warning! When an owner ID is changed for parent metadata (for example, record metadata), the owner IDs for child metadata (for example tablemaps and datamaps) are not updated. |
SQL Object ID Prefix |
A system-generated number that identifies the prefix of the SQL that is built by this component in the SQL repository. |
|
Click the Compile button on any of the metadata pages to build the metadata. Remember that you need to recompile any metadata that you change using this button. |
Description |
The Metadata search engine uses the description that you enter to find metadata objects. |
This section describes how to search for metadata objects.
Page Name |
Object Name |
Navigation |
Usage |
Metadata Search |
PF_SRCH_PANEL |
EPM Foundation, Foundation Metadata, Other Metadata Operations, Metadata Search |
Search for delivered and created metadata objects. |
Access the Metadata Search page.
Search Keywords |
Enter any keywords that you want to search by. You can use the AND, OR, and parentheses buttons to create a search string. Alternatively, you can leave this field blank and just search on a setID. |
Metadata Types |
You can further narrow your search by specifying the metadata types to search on. |
|
Click the All Metadata button to select all the metadata types that are listed. |
|
Click the Clear all flags button to clear all the current selections in the Metadata Types group box. |
Restrict SetID |
Select Yes to restrict the search to a specific setID. This applies only to data sets, constraints, and filters. |
Search |
After you have entered your criteria, click the Search button to activate the search. Depending on your criteria and the amount of metadata in your system, the search may take a while. The search returns metadata matching your criteria to the grid at the bottom of the page. |
|
Click the Go to Setup Page button to go directly to the metadata setup page of any of the metadata objects that are returned. |
To fully secure your metadata objects, it is recommended that you establish hidden flags. Hidden flags enable power users to edit metadata objects while ensuring that everyday users can only view the objects. Hidden flags provide you with an extra level of security for your metadata. Hidden flags use the PeopleTools My Personalizations feature to determine how to render the page for a user. If the user does not have access to the hidden flag through the personalization feature, then the page will be rendered as display only. This ensures that daily users are not able to modify or delete the metadata object
Using this flag, system administrators can control access to metadata objects. System administrators may also assign power users access on an as-needed basis.
This flag acts as an attribute for the following metadata objects:
Record metadata
Tablemaps
Datamaps
Expressions
Filters
Constraints
Data sets
Dimension metadata
This section discuses how to:
Apply the hidden flag to metadata objects.
Grant access to hidden metadata objects.
On any of the metadata setup pages for record metadata, tablemaps, datamaps, expressions, filters, constraints, or data sets, select the Hidden Flag check box to activate the hidden flag for that metadata object.
System administrators may grant access to hidden metadata objects for day-to-day users (power users) by enabling them to access the My Personalizations, EPM Expert User Settings page.
On this page, users can click the Personalize Option button for EPM Expert User Settings to access the Option Category: EPM Expert User Settings page. On this page, power users can set the override value for Display Hidden Objects to Yes to enable them to modify metadata objects that are hidden from day-to-day users.
This section provides an overview of record metadata and discusses how to:
Define record metadata.
Review record column properties.
Create and review related metadata objects.
Record metadata defines the first level of metadata; that is, it defines the tables that are part of EPM Foundation. Your PeopleSoft database contains several types of tables:
PeopleTools tables.
PeopleSoft Performance Management Warehouse business rules tables.
PeopleSoft Performance Management Warehouse data tables.
Record metadata defines and identifies the PeopleSoft Performance Management Warehouse data tables only.
PeopleSoft delivers permanent data tables in the PeopleSoft Performance Management Warehouse and the corresponding record metadata that identifies them as fact tables, fact reference tables, dimension tables, dimension reference tables, or transaction-dated tables.
If you add new tables, you must set up record metadata for each table that you add.
If you change a table, you must recompile record metadata for that table. If you add a non-key column to a table, you must recompile the record metadata. If you add a key column, you must recompile the record metadata and any tablemaps, datamaps, constraints, filters, or other metadata objects that are associated with it.
Every permanent data table that is defined within the PeopleSoft Performance Management Warehouse requires a shadow temporary table, known as the record stub. Shadow temporary tables have a similar record layout that generally matches the permanent tables. One exception to this is long varchar, long binary columns are removed. The temporary tables are defined as temporary tables on the PeopleTools record definition.
Page Name |
Object Name |
Navigation |
Usage |
Record Metadata |
PF_META_REC_TBL1 |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Record Metadata, Record Metadata |
Define record metadata for any new tables that you add to the PeopleSoft Performance Management Warehouse. |
Record Metadata - Field Properties |
PF_META_REC_SEQ |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Record Metadata, Field Properties |
Verify that your permanent and temporary tables are in sync. |
Record Metadata - Table Description |
PF_META_REC_NOTE |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Record Metadata, Table Description |
Enter any notes that you want to associate with the record metadata. |
Record Metadata - Related Metadata |
PF_META_RELMD |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Record Metadata, Related Metadata |
Create and review tablemaps, datamaps, and constraints that are related to specific record metadata. |
Access the Record Metadata page.
Record Details
Temporary Table |
Select a temporary table to use in parallel processing. When the system uses temporary tables for parallel processing, it uses the temporary table in conjunction with the record suite table. Note. Temporary tables need to be selected only for Operational Warehouse - Enriched (OWE) tables. |
Table Type |
Select the type of table for the selected record. You can select Dimension Reference Table, Dimension Table, Fact Reference Table, Fact Table, Security Join Table, or Transaction-Dated Fact Table. |
Merge Allowed |
Select this check box if the table name needs to be merged from a temporary table to a final table by the Merge (PF_MERGE) application engine. Generally, this check box is used for engine output tables only. Warning! The following tables must never be marked for Merge: PF_LEDGER_F00, PF_JRNL_F00, PF_ADB_JRNL_F00, PF_LED_ADB_F00, LEDGER, or LEDGER_ADB. |
Selective Merge |
Select this check box to use in the selective merge delete clause. Temporary tables that are created during the last step of a jobstream run are merged into permanent tables (F00 tables). To avoid duplicates in the permanent tables, a delete must occur before the merge. Typically, the run control parameters are used to delete the necessary rows from the permanent tables and then the temporary tables are merged in. For some isolated cases, a more restrictive delete is necessary. The Selective Merge process performs a delete with even more criteria than just the run control parameters. Selective Merge bases its delete on the run control parameters plus the SQL Object ID that is entered in the record metadata settings for each specific table and settings within the Application Engine (AE). Note. You generally do not use Selective Merge unless your application uses KPI Manager. |
SQL Object ID |
Select the ID of the SQL object for the selective merge. |
Related Warehouse Tables
Error Table |
Select the OWE error table related to the selected record. The error table contains the error data that fails as part of the edit and modification process. |
TSE Table Name |
Select the TSE table related to the selected record. The TSE (transaction editor set) table contains error message detail information. |
Owner Details
Component |
Select the component to which the record metadata belongs. Select either Multi-Dimensional Warehouse or Operational Warehouse - Enrich. |
Sub Component |
Select the sub component, or type of data, associated with the selected record. Select either Enriched Data orInput Data. |
When you have completed your record metadata setup, click the Compile button to generate the SQL objects. You must also build your SQL anytime you make changes to the record metadata.
Access the Field Properties page.
Key Field |
Select this check box if the field is a key on the permanent table. |
Field Name |
Lists all the fields on the permanent table. |
Field Type |
Lists the field type for each column. |
Prompt Table |
Column in which you can enable the system to prompt for criteria and default value fields. |
Field on Temp Table (field on temporary table) |
Select this check box for all fields that also appear on the temporary table. In general, this should be all fields, with the exception of transaction-dated tables. |
Measure |
(Transaction-dated tables only). All numbers (DBFIELDTYPE = 2) and signed numbers (DBFIELDTYPE = 3) that are defined on the temporary table. When the SQL is generated for these fields in a list, they are enclosed in a sum construct, for example, sum (REPORTED_HRS). |
Resolve By |
Select a key for use in the rule resolver process. The Rule Resolver is an application engine program that is called by most PeopleSoft EPM engines to gather the rule sets that are used in processing. The main function of the Rule Resolver is to gather the rules for a given process run based on the standard keys of setID, business unit, model ID, fiscal year, accounting period, and effective date. Resolver fields are used for SQL object generation that enables applications to select data based on the run control parameters. The Resolver reduces the amount of data that an application engine needs to process by populating tables with only the data necessary for the engine to run. Individual application engines call the Resolver as part of their run process. Note. If an OWE table is selected, the resolve by field is automatically selected and you cannot edit this field. If a MDW table is selected, you can select a resolve by field. |
Click the Table Description tab to enter a more detailed description of the record metadata.
Record Columns for Transaction-Dated Tables
At resolution time, data is selected from the permanent transaction-dated fact table by transaction date and stored in the temporary table in a fiscal year and period format so that it is processed in the same way as in any other table.
For example, if an engine runs for 2002 and is based on a monthly calendar, all transaction dates between January 1, 2002 and January 31, 2002 are selected from the transaction-dated fact table and inserted into the temporary table. Because more than one transaction with the same key information may exist, some aggregation must occur. A group by clause is generated that includes all fields on the temporary table that are not resolved or are not measure fields. You use the Record Columns page to see how the fields are defined.
Access the Record Metadata - Related Metadata page.
The Related Metadata page enables you to create and review tablemaps and datamaps that are related to particular record metadata. The fist time you define record metadata, you can use this page to create related tablemaps and datamaps at the same time that you create the record. Simply enter a name and description for the objects and click the add button—the objects are created automatically with the same name as the related record metadata. The SQL IDs for each object are also created automatically.
If you have already defined record metadata, you can use this page to review the tablemaps and datamaps that are built on top of the record metadata. Click any of the metadata objects to access their primary page (for example, the Tablemap page).
This section provides an overview of rule metadata and discusses how to define rule metadata.
Rule metadata is used in conjunction with the Rule Resolver to specify the relationship between parent and child models. Unless you create a new PeopleSoft EPM engine, you will not likely need to create any rule metadata. PeopleSoft EPM engines use rule metadata to recursively determine inheritance rules between models.
Understanding the Rule Resolver
The Rule Resolver is an application engine program that is called by most PeopleSoft EPM engines to gather the rule sets that are used in processing. The main function of the Rule Resolver is to gather the rules for a given process run based on the standard keys of setID, business unit, model ID, fiscal year, accounting period, and effective date. The system does this by passing the Rule Resolver a list of parent tables that need to be resolved. The related child tables (as defined in the Rule Metadata page) are also resolved as part of the Rule Resolver. In addition, if a table is keyed by model ID, the Rule Resolver determines what the parent models are and resolves rules for them.
Page Name |
Object Name |
Navigation |
Usage |
Rule Metadata |
PF_METARULE_TBL |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Rule Metadata |
Define rule metadata. Rule metadata is delivered with EPM and generates the SQL that is needed to resolve rules. |
Access the Rule Metadata page.
Resolve |
The settings in this group box are set by default based on the table name that you select. You cannot update this information. |
Child Record Name |
Select the appropriate record name. This includes all child tables that must be resolved along with the parent table. |
Record Stub |
Select the appropriate record stub. |
When you have completed your rule metadata setup, click the Compile button to generate the SQL objects. You must also build your SQL anytime you make changes to the rule metadata.
This section provides and overview of tablemaps, lists common elements, and discusses how to:
Define tablemaps.
Define reference tables.
View SQL IDs.
Tablemaps define the physical relationships between your PeopleSoft data warehouse tables. Tablemaps enable you to define families of related data warehouse tables and the columns that define the key relationships (or joins) between the tables. Tablemaps describe the parent-child relationships between tables by defining the common fields that join them together.
A tablemap can be defined as identifying a primary table only, or you may specify any combination of child tables, reference tables, and reference child tables:
The types of tables are:
Often a fact table, the primary table, from a logical point of view, to which the other tables are related. |
|
A hierarchical child of the primary table (all the same keys, plus one). Child tables enable you to have a one-to-many relationship between a primary table and its child tables and to define the relationship between these tables through the tablemaps. You specify a child table only if you need fields from that table in your tablemap. |
|
A table that defines the properties for one or more keys in the primary table. Reference tables contain additional attributes, or properties, of the transactional fact information. Some examples of reference tables are Customers, Products, Channels, Geography, and Time. A reference table always has one or more key fields that it shares with the primary table. These fields are defined on this page and matched with the corresponding fact field to enable engines to access data through the join. Each reference table may be the parent table to one or more reference child tables. |
|
Reference child table |
A hierarchical child of the reference table (all the same keys, plus one). Reference child tables are similar to child tables in that they allow for multiple types or rows of data per related dimension table. An example of a reference child and its related dimension is a customer demographic table's relationship to a customer table. That is, you may want to track various demographic values for each customer, such as salary range and geographic codes |
The relationship between these tables is often referred to as a star schema. A star schema is a group of tables that are related to a central fact table. A single fact table can have numerous fact references and dimensions and each dimension can have numerous dimension references.
Example of relationship between tables (star schema)
Tablemaps are used as a basis for defining datamaps, filters, and ultimately constraints. After a tablemap is set up, you will likely keep it as it is. You therefore have many datamaps defined for each tablemap.
We deliver a set of tablemaps with the PeopleSoft Performance Management Warehouse.
Warning! If you plan at the leaf level instead of the node level for a dimension, you must remove the reference table for that dimension. When you set up your data set that uses this dimension, you must select to group by dimension ID and not dimension node.
Join Mapping |
Depending on the key field type, you may or may not be able to edit this field. The system automatically resolves certain fields. Fields that you can edit are in white. If you change the method to Map One to One =, then you can specify the primary field. Map One to One = is the default if the reference key field has the same name as on the primary table. |
Primary Field |
If you change the join mapping to Map One to One =, then you can specify the primary field using the valid values that are provided by the system. |
Additional Field Lookup Table |
If join mapping is set to Additional Key, you can specify that key value in this field using the valid values that are provided by the system. Additional Key is the default when the key cannot be resolved and if it does not have the same name on the primary table. In a valid tablemap, every child has only one additional key. |
Page Name |
Object Name |
Navigation |
Usage |
TableMap |
PF_TBLMAP_DEFN |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, TableMap, TableMap |
Define tablemap and tablemap child tables. |
Reference |
PF_TBLMAP_REF |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, TableMap, Reference |
Define reference and reference child tables. |
SQL IDs |
PF_TBLMAP_REF |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, TableMap, SQL IDs |
View the generated SQL object ID prefixes for the tables that are defined in this tablemap. |
Access the TableMap page.
Target Table |
Select this check box to define the primary table as a target table for Data Manager, Allocation Manager, or another application engine. You must select this check box if you are using this table as a target for Data Manager or Allocation. If you select this check box, the Reference page is disabled. Note. This check box is unavailable for editing when input MDW tables are selected. |
Compile |
Click to generate the SQL objects for the tablemap metadata. You must rebuild your SQL anytime you make changes to the tablemap, such as when you delete or add a key field. Note. If you need to recompile a tablemap, you must also recompile any datamaps or other metadata objects that are associated with the tablemap. |
Primary Table
Primary Table |
Select the table that you want to use as the primary table for this tablemap. Only tables that have been defined in record metadata appear as valid values. A primary table is the center of the tablemap. Generally, a primary table will be a fact table. Fact tables contain the data that is relevant to a single business transaction, and they also have a unique key structure that can identify other related fact reference or child tables. A tablemap can contain only one primary table, but it may be related to many child and reference tables. |
View Primary Table Fields |
Click to go to the Field Properties page and review the fields that are associated with the underlying record metadata. |
List Only Fact Records |
Select this check box if you want to view only the tables that are defined in record metadata as fact table type. |
Child Fact Tables
Child Table |
Select the appropriate child table to join with the primary table for this tablemap. Only tables that have been defined in record metadata appear as valid values. |
List Only Fact Reference Recs (list only fact reference records) |
Select this check box if you want to view only the tables that are defined in record metadata as fact table type. |
Child Key Field |
Displays the key field of the child table used to join to the fact table key field. This key field relates the child table to the primary table. |
Join Operator |
Select a method to join the child key field and the fact table key field. If you select Additional Key to use in the join, you must specify the record in which the additional key resides. |
Fact Table Key Field |
Select the key field of the primary fact table used to join to the child key field. This field relates the fact table to the child table. In some cases, this field is display only. |
Additional Field Lookup Table |
Select the record in which the additional key for the join resides. This field is available only when you select Additional Key in the Join Operatorfield. |
Access the TableMap - Reference page.
Reference Tables
Reference Table |
Select the appropriate reference table that you want to join to the primary table. Only tables that have been defined in record metadata appear as valid values. |
View Only Dimension Records |
Select this check box to view only the tables that are defined in record metadata as dimension table type. |
View Reference Table Fields |
Click to access the Field Properties page and view the record metadata fields related to the selected reference table. |
Reference Key Field |
Displays the key field of the reference table used to join to the primary table key field. |
Join Mapping |
Select a method to join the reference key field and the primary table key field. |
Primary Field |
Select the key field of the primary table used to join to the reference key field. |
Child Reference Tables
Child Table |
Select the appropriate reference child table that you want to join to the primary table. Only tables that have been defined in record metadata appear as valid values. |
View Only Dimension Ref Recs (view only dimension reference records) |
Select this check box to view only the tables that are defined in record metadata as dimension table type. |
Reference Child Key Field |
Displays the key field of the child reference table used to join to the reference table key field. This key field relates the child reference table to the primary reference table. |
Join Mapping |
Select a method to join the reference child key field and the reference table key field. If you select Additional Key to use in the join, you must specify the record in which the additional key resides. |
Reference Key Field |
Select the key field of the reference table used to join to the reference child key field. |
Additional Field Lookup Table |
Select the record in which the additional key for the join resides. This field is available only when you select Additional Key in the Join Mappingfield. |
Access the TableMap - SQL IDs page.
After the SQL has been compiled, you can view the generated SQL object ID prefixes for the tables that are defined in this tablemap.
This section provides an overview of datamaps and discusses how to: define datamaps.
Define datamaps.
View datamap fields.
View or add constraints for a datamap.
A datamap is the third level of metadata that builds upon the information that you captured in the tablemap and enables you to define a logical view of the physical PeopleSoft Performance Management Warehouse tables. Datamaps bring together information from the different tables that can be specified in a tablemap and defines it as if it were one entity or table. Not every column of every table that is defined in a tablemap is necessary; datamaps enable you to select only those columns that you want to use
You can group fields that are related to common processes so that processing can be done on one data set. In addition, datamaps enable you to give columns more intuitive, meaningful names, making data retrieval and review easier for business users.
Warning! If you plan at the leaf level instead of the node level for a dimension, you must remove the row that contains this dimension. When you set up your data set that uses this dimension, you must select to group by dimension ID and not dimension node.
Page Name |
Object Name |
Navigation |
Usage |
General Properties |
PF_DATAMAP_DEFN |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, DataMap, General Properties |
Define datamaps. |
DataMap Fields |
PF_DATAMAP_DEFN2 |
Click DataMap Fields on the General Properties page. |
View DataMap fields. |
Constraints |
PF_DMAP_CONSTRNTS |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, DataMap, Constraints |
View or add constraints for a specific datamap. |
Access the DataMap - General Properties page.
TableMap Code |
Select a tablemap code. The code selected here limits the records that you can include in your datamap to the ones that are defined in the tablemap. When you add or change this value, the page automatically inserts each field from the tablemap's primary table into the grid. Generally, you include all fields from the primary table and add only a few from the reference tables. You can add or remove rows from the grid below. If your tablemap is a target table, you cannot insert or delete rows. |
View/Edit TableMap |
Click to access the TableMap page and view the tablemap associated with the selected datamap. |
Target |
This check box is controlled by the tablemap that the datamap is based on. If the tablemap that you select is a target, the check box is selected. |
DataMap Fields |
Click to access the DataMap - DataMap Fields page and view or edit the fields that are associated with this datamap. |
When you have completed your datamap setup, click the Compile button to generate the SQL objects. You must also build your SQL anytime you make changes to the datamap, such as when you delete or add a key field.
Note. If you need to recompile a datamap, you must also recompile any tablemaps or other metadata objects that are associated with the datamap.
Access the DataMap - DataMap Fields page.
Expr (expression) |
To create a virtual column, add a new blank row, select the Expr check box, and click the Expression button. This accesses the Expressions page. After you have defined and saved your expression, all the changes made to the DataMap page are also saved. You cannot change an existing row into an expression. |
Record |
Contains a list of all the records that are defined in the tablemap on which this datamap is based. |
Field Name |
Lists all the possible fields for the selected record. When you add or change a field, the description is automatically populated with the column field name that you select. You may then edit the description to be anything you want under 30 characters long. |
Description |
Change the name of a field to enable you to create more intuitive names that provide more meaning than the original column name when creating your datamap. The names that you select here will show up on reports and are used in filters, constraints, and by the Data Manager. |
Type |
Select Attribute, Dimension, or Measure. The system's default is Attribute. The type field is important when you are defining datamaps for the Data Manager to verify rule definitions. Note. The types mentioned here are different from the types that are used by PeopleSoft EPM Foundation metadata and OLAP objects. |
Key Value |
Required for fields from child tables. Enables you to define more than one column from a single column based on different values in the lookup table code on the child table. |
Lookup Table |
This field is available for character fields and is used when you are defining filters to limit the field to valid values that are contained in the Lookup table. |
Field Type |
Displays the field type that is associated with the field. Its value is based on the field type that is defined in PSDBFIELD. Number fields consist of the following types: 1 (Basis Points), 2 (Rate), 3 (Amount), and 5 (Number). These types represent ways in which the field will be summed. |
Access the Constraints page.
You can use this page to view constraints associated with a datamap. You can also add new constraints to the datamap.
Expressions enable you to create virtual columns that are made up of mathematical calculations based on actual fields on a table. Because expressions are resolved at runtime, duplicate information is not stored in the database. Expressions are user-defined columns that you add to a datamap. After you add them as columns to the underlying datamap, you can use them in filters just like record fields. An expression can be either numeric or a string. Numeric expressions can combine any number of record fields, value objects, and math operators. String expressions can be concatenations of any number of character record fields and value objects.
Note. On DB2 UDB for OS/390 and z/OS, you should compose your expressions so that multiplication occurs before division; otherwise, decimal precision may be affected as values may be truncated. Use parentheses where necessary to control the order of calculation to ensure correct decimal precision.
This section discusses how to define expressions.
Page Name |
Object Name |
Navigation |
Usage |
Expression |
PF_EXPR_DEFN |
EPM Foundation, Business Metadata, Constraint and Expressions, Expression |
Define an expression. |
Access the Expression page.
DataMap Code |
Select the datamap that you want to build your expression on. You cannot change the datamap code after you have associated a datamap with an expression. |
Rounding |
For numeric expressions, the result of the expression is rounded based on the precision that is selected. For all the other types of expressions, the rounding factor is not applicable. |
Type |
Controls the set of operators, record fields, and value objects that can be selected. Values are: Numeric: These expressions can combine any number of record fields, value objects, and math operators. String: These expressions can be concatenations of any number of character record fields and value objects. Date: These expressions can be a constant data or a date field that is derived from the datamap or built-in functions. |
Expression Statement |
Displays the expression that you build by selecting operators, fields, or value objects in the group boxes below. This is where you build your expression logic. You cannot type directly in the Expression Statement field. First, select a data source. The data source that you select presents you with different options below it. To add an element to the expression statement, select it from the data source, and then click the Insert button. The system inserts your selection into the text area. Use the arrow buttons at the bottom to move in the text area. You can see what element is being specified by the arrows surrounding it, for example >>Effective Date<<. Note. >>Effective Date<< is also displayed in the Current Element group box. |
Operators |
Select from the operators buttons to add the indicated value to the expression. |
DataMap Column |
Select to display a datamap column drop-down list box. Select the appropriate datamap column from the list and use the Insert button to place your selection in the text area of the page. |
Built-In Function |
Select to display a built-in function drop-down list box. Select the appropriate function from the list and use the Insert button to place your selection in the text area of the page. Built-in functions enable you to define numeric calculations, for example sum, average, or end of month, and minimum and maximum values. |
Constant Value |
Select to make available the constants options and, depending on your choice, a drop-down list box. The Ad Hoc option enables you to enter your own values for the character, numeric, and date field. The From List drop-down list box contains valid value objects. Use the Insert button to place your selection in the text area of the page. |
Note. It is important to consider how an expression will be used when you want to specify date-related value objects. If the expression is to be used in a WHERE clause, then the %CurrentDateIn value object must be used. If the expression is to be used in a SELECT clause, then the %CurrentDateOut value object must be used.
Clear |
Click to erase all of the text in the Expression Statement box. |
|
Use these buttons to move in the Expression Statement area of the page and identify the text that you want to replace, insert, or delete. When you click either > or < , you move one element forward or backwards. If you use the double arrows >> or <<, you move either to the beginning of the expression text or to the end. You can see what element is being specified by the arrows surrounding it, for example >>Effective Date<<. |
Replace |
Use the arrow keys << >> or < > to specify the element that you want to replace, select another element, and then click Replace. |
Insert |
After you have selected a data source, use this button to insert it into the Expression Statement text area. The system inserts the element at the position designated by the work area, pushing everything else to the back. |
Delete |
Use the arrow keys << >> or < > to specify the element that you want to delete, and then click Delete. |
When you have completed your expression, click the Compile button to compile the expression.
SQL Functions That Are Available to the Expression Builder
The following table lists the SQL functions that are available to build expressions.
SQL Object ID |
Expression Function |
Return Value |
Description |
PF_FUNCLIB_DATE_DAYADD_UPD |
AddtoDays (Date, Integer) |
Date |
Increase date by adding days (Integer). |
PF_FUNCLIB_DATE_DAYDIFF_UPD |
DiffDates (Date, Date) |
Integer |
Calculate difference between two dates. |
PF_FUNCLIB_DATE_DAY_UPD |
GetDay(Date) |
Integer |
Returns numeric day of the month from date. |
PF_FUNCLIB_DATE_MONADD_UPD |
AddtoMonth(Date, Integer) |
Date |
Increase date by adding months (Integer). |
PF_FUNCLIB_DATE_MONBEG_UPD |
BOM(Date) |
Date |
Returns the date value for the beginning of the month. |
PF_FUNCLIB_DATE_MONTH_UPD |
GetMonth(Date) |
Integer |
Returns numeric month from date. |
PF_FUNCLIB_DATE_YEARBEG_UPD |
BOY(Date) |
Date |
Returns date for the beginning of the year. |
PF_FUNCLIB_DATE_YEAREND_UPD |
EOY(Date) |
Date |
Returns date for the end of the year. |
PF_FUNCLIB_DATE_YEAR_UPD |
GetYear(Date) |
Integer |
Returns numeric year from date. |
PF_FUNCLIB_DATE_YYMMDD_UPD |
YYMMDD(Date) |
Character |
Formats date YYMMDD. |
PF_FUNCLIB_DIFF_H(DTTM,DTTM) |
DTTMDIFF_H(Date, Date) |
Integer |
Date time difference in hours. |
PF_FUNCLIB_DTTM_DTTMDIFF_M_UPD |
DTTMDIFF_M(Date, Date) |
Integer |
Date time difference in minutes. |
PF_FUNCLIB_DIFF_S(DTTM,DTTM) |
DTTMDIFF_S(Date, Date) |
Integer |
Date time difference in seconds. |
PF_FUNCLIB_MATH_ABS_UPD |
ABS(Integer) |
Integer |
Absolute value. |
PF_FUNCLIB_MATH_MOD_UPD |
MOD(Integer, Integer) |
Integer |
Modulus. |
PF_FUNCLIB_MATH_TO_NUMB_UPD |
TO_NUM(Character) |
Integer |
Convert to number. |
PF_FUNCLIB_RTRIM |
RTRIM(Character) |
Character |
RTrim blanks. |
PF_FUNCLIB_TO_CHAR_UPD |
TO_CHAR(Integer) |
Character |
Convert to character. |
Note. All expression functions are operating system independent and database dependent.
Data sets are used as input for various engines, for instance, the Forecasting engine, user-defined functions, drivers in PeopleSoft Activity-Based Management (PeopleSoft ABM), and data elements in PeopleSoft KPI Manager. Data sets provide a user-defined set of information to the engines. Data sets restrict used columns and returned rows using constraints.
When you create a data element in PeopleSoft KPI Manager, for example, you are actually creating a data set. Though each data set is created by a process-specific setup, the underlying logic is the same, enabling you to more easily understand the functional aspects of the process.
Note. You will most likely not need to create a data set using the DataSet page because data sets are created behind the scenes. However, you may have to rebuild or recompile a data set if you change an underlying table. Data sets that are created in PeopleSoft EPM analytic applications do not appear on this page.
This section provides an overview of data sets and discusses how to recompile or rebuild data sets.
Page Name |
Object Name |
Navigation |
Usage |
DataSet |
PF_DATASET_DEFN |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, DataSet |
Recompile or rebuild a data set. |
Access the DataSet page.
Constraint Code |
The constraint code for this data set. The selected constraint code populates the grid at the bottom of the page. |
Select |
Select this check box to indicate that this datamap column is included in the data set's select clause. |
Aggregate Type |
The aggregate type choices correspond to the standard SQL aggregate functions. If you select an aggregate type, all the other selected columns are populated with the group by default. Values are: Avg (average): Returns the average of all the records retrieved. Avg Distinct: Returns the average of all the unique records retrieved. For example, average (5, 8, 9, 12, 9, 7, 5) = 55/7 but average distinct (5, 8, 9, 12, 9, 7, 5) = 41/5. Count: Returns the count of all the records retrieved. Count Distinct: Returns the count of all the unique records retrieved. For example, count (A, B, A, C) = 4 but count distinct (A, B, A, C) = 3. Group By: Groups the records retrieved by the measure field. Min (minimum): Returns the minimum of all the records retrieved. Max (maximum): Returns the maximum of all the records retrieved. Sum: Returns the sum of all the records retrieved. Sum Distinct: Returns the sum of all the unique records retrieved. For example, sum (1,1) = 2 but sum distinct (1,1) = 1. |
Select the Compile button to compile the data set. This compilation sets the as of date of the data set to the effective date. If you want to change the as of date, you must recompile after updating the Effective Date field.
Use the Mass Validate utility to validate against as of dates.
Note. If a data set is sent to Resolver and has an inactive constraint, the system returns an error message.
PF_FETCH is an application engine process that is used by PeopleSoft EPM analytic applications to retrieve and display data by running the SQL behind the data set for a set of run control parameters after the data set is built (SQL is generated). PF_FETCH selects and inserts data into an output table that is specified by the application. Output table data is automatically deleted if it is more than two days old based on the date and time stamp.
This section provides an overview of filters and discusses how to:
Define filters.
Specify filter selection criteria.
Use the tree viewer.
Filters are used extensively by the PeopleSoft EPM engines to define subsets of data to perform operations or calculations on. In your datamap, you define which columns from the tablemap to use in processing. Filters enable you to specify which rows to use from those columns, similar to a WHERE clause in a query.
In general, the PeopleSoft EPM engines use set-based processing to process large amounts of data as efficiently as possible. Filters enable you to define what subset of data gets processed by or uses a specific business rule. Not every row of data may be necessary to process your data. Filters enable you to select only those rows you want.
You can create multiple filters based on a single datamap. In addition, your filters, and thus your business rules, can be different for each setID, making the enrichment engines behave differently for the same rules in a different business unit.
Page Name |
Object Name |
Navigation |
Usage |
Filter |
PF_FILTER_DEFN |
EPM Foundation, Business Metadata, Constraint and Expressions, Filter, Filter |
Define a filter and specify the datamap. |
Filter - Selection Criteria |
PF_FILTER_SEQ |
EPM Foundation, Business Metadata, Constraint and Expressions, Filter, Selection Criteria |
Define selection criteria for business rules. |
Tree Viewer |
PSTREEVIEWER |
Click the Tree button on the Filter - Selection Criteria page. This appears only if you select an operation involving trees. |
View all tree nodes. Select a node to be displayed in the value field on the Selection Criteria page by double-clicking the node. |
Access the Filter page.
DataMap Code |
Select the datamap for which you want to define filters. |
View/Edit Datamap |
Click to transfer directly to the DataMap page for the selected DataMap to review it or make changes. |
Enter any notes to further describe the filter. |
When you have finished defining your filter, click the Compile button to compile the filter. This compilation sets the as of date of the filter equal to the effective date. If you want to change the as of date, you must recompile after updating the Effective Date field. Use the Mass Validate utility to validate against as of dates
Access the Filter - Selection Criteria page.
On this page, you can add or delete actual data values that make up your filter. You can have multiple filter rules.
Open |
Select the number of left parentheses needed for the selection criteria. You may have multiple parentheses in a case such as ((X or Y) and Z). |
DataMap Column |
Select the columns from the datamap for which you want to define filter criteria. |
Oper (operation) |
Select the operation to be used as selection criteria. The current operations available are equal to, greater than, less than, greater than or equal to, less than or equal to, not equal to, In Tree, Not in Tree, In MetaTree, and NotInMetaTree. Like is valid only for character type columns that have no associated lookup tables in the datamap. The value column for this operator must have a pattern search wild card, for example % or _. If you specify an operation of In Tree, click the Tree icon that appears alongside the Oper field to specify the tree name and tree node that you want to use as selection criteria. You can search in the Lookup Tree ID page for your tree if it is not displayed. If you still can't find the tree, verify that when you added the tree in the Tree Manager, you also created a record in the Tree Metadata page. This record identifies the engines where the flattened tree data is stored. On selecting the tree name, other information that is related to the tree is retrieved and then sent to the tree viewer (PSTREEVIEWERWRK). All the nodes can be seen here. The node you select here will be displayed in the Value field. |
Obj (object) |
Select this check box to select from a list of predefined value objects for the value setting. The Obj check box appears if you select any operand other than the tree choices. |
Value |
Select the value that the column is to be evaluated against. If you've defined a lookup table for the column in the datamap, you are prompted to select from a list of valid values. |
Close |
Select the number of right parentheses that are needed for the selection criteria. |
And/Or |
Select either an AND or an OR join for the filter criteria. |
Aggr (aggregate) |
Select to aggregate the values. |
Note. Remember that the Resolver resolves the following fields, so you do not have to include them as selection criteria: setID, business unit, scenario ID, effective date, as of date, fiscal year, and period.
Access the Tree Viewer page.
You can expand all levels of the tree to view detailed information. Double-click the folder icon to expand specific levels. Select the node of the tree that you want to use and click the Select button to return to the Filter - Selection Criteria page. The value field is populated with the node that you selected.
Constraints can be made up of one or more filters linked together using AND, OR, and NOT logic. This means that you can keep your filters simple and dedicated to a single purpose, and link them together in constraints to form complex business logic.
Constraints enable you to define business rules for processing and also enable you to create and reuse filters.
This section discusses how to:
Define constraints.
Specify constraint criteria.
Specify constraint details.
Page Name |
Object Name |
Navigation |
Usage |
Constraint |
PF_CONSTRAINT_DEFN |
EPM Foundation, Business Metadata, Constraint and Expressions, Constraint, Constraint |
Define a constraint. |
Constraint - Criteria |
PF_CONSTRAINT_SEQ |
EPM Foundation, Business Metadata, Constraint and Expressions, Constraint, Criteria |
Specify your constraint criteria. This is where you can combine filters to create complex business logic. |
Constraint - Details |
PF_FILTER_EXPLODE |
EPM Foundation, Business Metadata, Constraint and Expressions, Constraint, Details |
Display the filter contents of a given constraint. Modify the filter, as needed. |
Access the Constraint page.
Save As |
Click to replicate existing constraint metadata. You are prompted to enter the new constraint code name and effective date. This is available only in correction mode. |
DataMap Code |
Select the datamap for the constraint. |
View/Edit Datamap |
Click to transfer directly to the DataMap setup page where, you can view and edit the selected datamap. |
No Criteria |
You are not required to link filters to your constraint. By selecting this check box, you indicate to the system that you want all the values from the datamap. The system adds a 0= 0 WHERE clause to any other join criteria that the datamap needs (thus hiding the criteria page). The FROM clause is the same as the from field on the datamap. |
Notes |
Enter any notes to further describe the constraint. |
When you have completed setup of the constraint, click the Build Constraint SQL button to compile the constraint. This compilation sets the as of date of the constraint equal to the effective date. If you want to change the as of date, you must recompile after updating the Effective Date field. Use the Mass Validate utility to validate against as of dates.
Access the Constraint - Criteria page.
Use this page to add or delete the filters that you want to make up the constraint. You can link one or more filter codes. This page is not accessible if you select the No Criteria check box on the Constraint page.
Not |
Select to signify the negative of the condition. |
Open |
Select the number of left parentheses that are needed for the selection criteria. You may have multiple parentheses in a case such as ((X or Y) and Z). |
Filter Code |
Select the filter to use. You define filters using the Filter component. |
Close |
Select the number of right parentheses needed. |
And/Or |
Select either AND or OR to relate one line to the next of the filter criteria. |
Three filter types are available: WHERE, HAVING, and a combination of the two. Filter types relate lines of constraint rules.
Note. After a filter is created, the filter type cannot be changed.
Access the Constraint - Details page.
Here you can view the data elements that are targeted based on the filter selection criteria.
Modify Filter |
Click to access the Filter setup page, where you can modify the filter. |
User-defined functions enable you to define functions one time through a common interface, then use them throughout many of the analytic applications. The options that are available to you when defining functions are based on predefined modules that are provided with your system.
Refer to the PeopleSoft Application Fundamentals for Financial Services Industry Applications for details on working with user-defined functions.
See Also
Creating User-Defined Functions
Record summary metadata specifies the TSE views and pages, as well as the flash total fields that are associated with a table. Record summary metadata is delivered for fact tables. If you change the record structure of a table that uses record summary metadata, or change the totals to be summarized, you must click the Rebuild button to regenerate the SQL. This process is associated with Profit Manager.
See Also
Setting Up and Using Profit Manager
Page Name |
Object Name |
Navigation |
Usage |
Record Summary |
PF_SUMM_REC_TBL1 |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Record Summary |
Create new record summary metadata or rebuild delivered record summary metadata. |
PF Record Summary |
RUN_PF_SUMM |
EPM Foundation, Data Enhancement Tools, Profit Manager, Record Summary, Summarize Error Statistics, PF Record Summary |
Run the PF Record Summary engine before you process any fact table modifications. |
Access the Record Summary page.
SQL Object Prefix ID |
A system-generated number that identifies the location of the SQL that is built by this component in the SQL Repository. |
Record Abbreviation |
A naming convention that populates all other fields on the page. Enter up to four characters. By defining the record abbreviation, the system makes a best guess and automatically populates all the fields on the rest of the page. |
Apply Fact Error Correction |
Select to apply fact error correction to this table. |
Error Table and TSE Table |
Are display-only and based on the error and TSE table definitions that were made in the record metadata for the selected table. |
The following fields are automatically populated based on the record abbreviation. These objects are used for PF Modification:
TSE Table's View 1 |
The work record that is used in the PF Summary process. |
TSE Table's View 2 |
The work record that is used by PF Edi. |
Flash Total Field 1, 2, 3 |
Select the totals that you want to summarize. These totals are defined in the job totals metadata. These are monetary amounts that you want to track. |
You can now run the PF Record Summary engine.
Note. Record Summary metadata must be defined before you run the PF Record Summary engine. You must run the PF Record Summary engine to run PF Modification.
See Also
Reviewing and Defining Job Totals Metadata
Access the PF Record Summary page.
Description |
The description is important because it is used by the Metadata Search engine to locate your metadata. |
As Of Dated Jobstream |
Select to replace the fiscal year and period with the as of date field for the engine run. |
Business Unit |
Select the appropriate business unit. |
Scenario ID |
Select the appropriate scenario. |
Fiscal Year |
Select the appropriate fiscal year. This field does not display for an as of dated jobstream. |
Period |
Select the appropriate period. This field does not display for an as of dated jobstream. |
As Of Date |
If you selected the As of Dated Jobstream check box, enter the date. |
Job ID |
Select the job ID of Summ. Job IDs are set up in job metadata. |
Record |
Select the record. |
PF_SUMM summarizes error statistics by field and value for the PF Modification process. The record summary metadata uses record metadata to determine which records can be summarized.
This section provides overviews of trees and tree flattening, and discusses how to:
Define trees.
Flatten tree metadata.
When you add trees in PeopleTools Tree Manager, you should also create a record on the Tree Metadata page to identify it to the PeopleSoft EPM system. The Tree Manager does not assign a level number to a node unless you specify that the tree either loosely or strictly enforces levels. Levels are assigned by creating a code for each level. In PeopleSoft EPM, for trees that use rules and are processed through the tree level, you must set up your trees using levels. PeopleSoft recommends specifying strictly enforced levels for all your trees.
PeopleSoft trees add a visual layer to show how detail items such as departments, accounts, products, channels, geography, and security fit into your organizational structure.
Trees depict hierarchical structures that represent a group of summarization rules for a particular database field. For example, a tree can specify how your manufacturing locations should be summarized, or rolled up, for reporting purposes. A tree can also represent the reporting relationships within an organization by specifying how the individual department should be summarized into territories, territories into regions, and regions into countries. Similarly, a tree can categorize items in a catalog.
The summarization rules depicted in a tree apply to the detail values of a particular field: vendors, departments, customers, or other values that you define. These detail values are summarized into nodes on a tree. The nodes may also be organized into levels to logically group nodes that represent the same type of information or level of summarization.
By building trees, you give the system a single place to look for summarization rules. Trees enable you to define rules once and then use them throughout the system. Different reports, ledgers, and security profiles might refer to parts of your company's organizational chart; therefore, all of these objects can be referenced in the same predefined tree.
For example, the values of the DEPTID field identify individual departments in your organization. You use Tree Manager to define the organizational hierarchy that specifies how each department relates to the others—departments 10700 and 10800 report to the same manager, department 20200 is part of a different division, and so on.
You create trees using the PeopleSoft Tree Manager. You can use the PeopleSoft Tree Mover to move trees between different versions of PeopleSoft databases, move tree node data, or move tree level data.
See Also
Enterprise PeopleTools PeopleBook: PeopleSoft Tree Manager
Trees are used in PeopleSoft EPM to represent hierarchies. The trees are stored in a proprietary format that is optimized for the PeopleSoft Tree Manager module. However, this format is not optimized for EPM batch processing. To convert tree structures to a format that is optimized for batch processing, PeopleSoft delivers a tree flattener process.
The Tree Flattener (PF_TREEFLT) application engine processes the hierarchy tree and converts it to a flattened format, which simplifies the structure to retrieve information such as all descendents and immediate descendents from a node with a simple join to the flattened table. The Application engine can then use very simple SQL to access the tree data that it needs. The flattened output from the Tree Flattener (PF_TREEFLT) process is loaded to a temporary table, and you can choose to load the flattened data to a permanent OWE table if you wish. The tree flattener process can flatten detail, node-oriented, and dynamic detail trees.
To flatten dynamic detail trees, the tree flattener process retrieves the dynamic detail value table name from the Tree Structure ID and inserts these values into the flattened table. During this process a static view of the tree is created as of the date and time the flattening occurs. This means that the flattener process gets the current values of the tree detail from the dynamic detail value table and uses these in the flattener table. Because of this fact, you should not use the Persist Permanently check box with dynamic detail trees unless you are sure that the dynamic table will not change.
Page Name |
Object Name |
Navigation |
Usage |
Tree Metadata |
PF_METATREE_TBL1 |
EPM Foundation, Business Metadata, Tree Metadata, Tree Metadata |
Define tree metadata. |
Run Tree Flattener |
PF_RUN_TREEFLAT |
EPM Foundation, Business Metadata, Tree Metadata, Tree Flattener |
Run the tree flattener process. |
Jobstream Email Notification |
PF_EMAIL_MSG |
EPM Foundation, Business Metadata, Tree Metadata, Tree Flattener, Specify Email Parameters |
Specify email parameters for the tree flattener notification. |
Access the Tree Metadata page.
Tree Metadata Information
Tree Name |
Select the tree for which you are creating tree metadata. |
Display Tree |
Click to access the Tree Viewer page and view your tree. |
Tree Tables
Flattened Table |
Enter the name of the table where the preprocessor (flattened) output is placed. This table name was created in the Record Metadata page where a temporary table was assigned to it. You should not assign the same tree flattener table to more than one tree metadata definition. |
Denormalized Table Name |
Enter the name of the table where the denormalized tree output is placed after the Tree Flattener process flattens the tree. Denormalized tree output is used for reporting purposes. |
Persist Permanently |
Select this check box to move the preprocessor (flattened) output to a permanent OWE table from the temporary table. |
Tree Type |
Specify whether the tree is a summer or winter tree. If you override a summer tree by selecting Winter, the tree nodes are used, and the summer tree leaf values are ignored. You cannot override a winter tree. |
Access the Run Tree Flattener page.
SetID |
Select the setID that is associated with the tree being flattened. |
Job ID |
Displays the job ID that is associated with the run control ID. Because the tree flattener process is not delivered as a standalone process but as a routine that is called within application engine, you must associate the run control ID with a job ID. |
Specify Email Parameters |
Click to access the Jobstream Email Notification page and specify email details for the email notification. |
Tree ID |
Enter the tree ID for the tree that you want flattened You can add rows to have multiple trees flattened. |
Report metadata is delivered for Crystal reports, data mart reporting, and delivered PeopleSoft analytical application reports. Each delivered report or cube has a record within the Report Metadata page. You can change the metadata as reports change, or as additional parameters are needed. If you create your own reports, you can use report metadata to document them.
This section discusses how to define report details.
Page Name |
Object Name |
Navigation |
Usage |
Report Details |
PF_META_RPT_TBL1 |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Report Metadata, Report Details |
Define report details, including the report name and type. Report metadata is not required. Rather, it is provided for your reference. |
Tables Used |
PF_META_RPT_TBL2 |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Report Metadata, Tables Used |
Display the tables that are used to generate the report. |
Access the Report Details page.
Report Type |
The type of report. |
Product |
The associated product for the report. |
URL |
Use to specify either a URL or the directory where the generated reports are stored. |
Cube Instance ID |
The ID assigned by PeopleTools Cube Manager that is used for reporting. This field is active when you select Cognos Cube as the report type. |
Notes |
Enter any notes to further describe the report. |
Click the Tables Used tab to review the tables that are used to create the selected report.
You can create metric metadata to process specific groups of your transactional data, based on columns in your record tables. These metrics can help you track essential measures for your organization, such as total sales and revenue. PeopleSoft does not deliver metrics. You can, however, create your own metrics using the Define Metric page. The page also enables you to define security for your metric.
See Securing Dimensions and Metrics.
Page Name |
Object Name |
Navigation |
Usage |
Define Metric |
PF_SY_METR_DEFN |
EPM Foundation, EPM Security, Metrics and Dimensions, Create/Edit Secured Metrics, Define Metric |
Define and secure metrics. |
Access the Define Metric page.
Record Name |
Enter the name of the record that is associated with the metric that you are defining. |
Column Name |
Select a column that is associated with the metric that you are defining. |
Is Secured |
Select this check box to indicate that the metric is secured. |
This section provides an overview of dimensions and discusses how to:
Define dimensions.
Define dimension details for an Operational Warehouse - Enriched (OWE) warehouse table.
Define dimension details for a Multidimensional Warehouse (MDW) table.
Dimension metadata is associated with specific record metadata and its fields. For example, you can define the dimension DEPARTMENT that references the record DEPARTMENT_TBL and the DEPT_ID column in that record. Dimension metadata captures additional column, key, and security information that is not included with a standard datamap, such as alternate key fields and dimension security. A single dimension can be defined for both an OWE and MDW tables, enabling you to use the same dimension name for both table types.
Dimension metadata is used by the functional warehouses, applications, security, and KPI manager.
Dimension Security
Because EPM is delivered with no security restrictions, dimensions are also delivered unsecured. Before you can grant a user access to a dimension you must first indicate to the system that a particular dimension requires securing. Dimensions that are not secured are classified as public, or unsecured. All EPM users can view these objects.
You specify dimensions that require securing using the Dimension page. After you specify a dimension to secure you must associate that dimension with a security join table to complete dimension security. Security join tables are EPM database tables that store the security profiles for users along with the corresponding dimension values for which they have access. During security processing, a security join table acts as a lookup. For example, when a user is trying to access a row of data, the SQL that processes this request uses the security join table to identify the user and her access to the particular row of data.
A security join table must be created for every dimension that you plan to secure. Each security join table should match the key structure of the dimension table for which it is defined. Each row in a security join table identifies a user or security role and his access to a specific dimension value. A user who is granted access to multiple values in a single dimension table has several rows in the security join table. In the event that a user has access to an entire dimension, you can insert a single row designated all and prevent the table from ballooning in size with several rows of data. A user that is granted access to multiple dimensions appears in several security join tables. The following is an example of a security join table.
Account security join table
The security join table model is better than a single security output table for two main reasons: Processing smaller tables is more efficient when you are inserting or deleting data, or querying the table to determine access privileges, and modeling individual security tables enable you to be in sync with the anticipated migration to data objects in future releases.
See Securing Dimensions and Metrics.
Page Name |
Object Name |
Navigation |
Usage |
Dimension |
PF_DIM_DEFN |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Define Dimension |
Define dimension metadata. |
OWE Detail for Dimension |
PF_DIM_OWE_DETL |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Define Dimension, Define |
Define dimension metadata details for an OWE dimension and apply security parameters. |
MDW Detail for Dimension |
PF_DIM_MDW_DETL |
EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Define Dimension, Define |
Define dimension metadata details for an MDW dimension and apply security parameters. |
Access the Dimension page.
Warehouse |
Indicates the warehouse layer that is associated with each dimension type. |
Define |
Click to access the OWE Detail for Dimension or MDW Detail for Dimension page, depending on which dimension type you are defining. |
Record Name |
Displays the name of the record that is associated with a particular dimension. |
Owner ID |
Select an owner ID for this dimension. The owner represents an EPM functional area (such as ABM or Budgeting) that is associated with the dimension. Assign an owner ID to help organize and group the metadata—making locating and auditing easier. |
Access the OWE Detail for Dimension page.
Record Information
Record Name |
Select the record that you want to associate with this dimension. |
Leading Key |
Select a leading key for set processing. SetID indicates that the leading key is a setID. Business Unit indicates that the leading key is a business unit. None indicates that there is no leading key. |
Business Key |
Select a business key to serve as the primary key for the selected dimension. |
Business Key Label |
Select a label for your business key. |
Description |
Select a description for the business key field. On Same Record indicates that the description is available in the record. If you select this option, specify a value for the Description Fieldname field. Translate Value indicates that the description is available through the translate table. |
Description Fieldname |
Select the field which contains the description of the business key field. |
Create Record Metadata |
Select to access the Record Metadata page and define new record metadata to associate with this dimension. If you select an existing record from the prompt, this field displays the name of the record metadata. |
Security
Is Secured |
Select this check box to indicate whether this dimension is secured. If you secure this dimension, you must specify its related security join table in the Security Join Table field. |
Security Join Table |
Select a security join table to associate with this dimension. The security join table is an EPM table that stores the security profiles for users along with the corresponding dimension values to which they have access. Only Dimensions that are associated with security join tables are presented to a security administrator when security is defined. |
Alternate Fields
Field Name |
Select an alternate name for your dimension fields. Alternate field names share the same IDs as the primary field and can be used by different fact tables to join to the same ID. Specifying alternate field names is optional. |
Access the MDW Detail for Dimension page.
The MDW Detail for Dimension page contains the same fields as the OWE Detail for Dimension page with the addition of the following three fields.
Surrogate Key |
Select a surrogate key for this dimension. MDW dimensions are keyed by surrogate key, and not business unit. |
Source ID |
Select the source transaction system that is associated with the data in the record. |
Prompt View |
Enter a key to map the surrogate key to the business key. This field enables you to select data using meaningful keys instead of surrogate keys, which are generated numbers. |
Cloning metadata is an efficient and quick way to create a duplicate copy of existing or already defined metadata.
This section discusses how to define metadata clone criteria.
Page Name |
Object Name |
Navigation |
Usage |
Metadata Clone |
PF_MD_CLONE |
EPM Foundation, Foundation Metadata, Other Metadata Operations, Metadata Clone |
Define metadata clone criteria for copying metadata from one setID to another. |
Access the Metadata Clone page.
SetID |
The setID from which you want to clone the metadata. |
Clone SetID |
The setID for which you want to create the clone. |
Clone Metadata Type |
Select the type of metadata that you want to clone. Values are: AL Rule (allocation manager rule), DM Rule (data manager rule), Constraint, DataSet, or Filter. Select on of the following operations: =, <>, Like, or Not Like. Enter a metadata value to search on. |
|
Click the Get Selected Metadata button. Your results will appear in the Metadata Selection List field. |
Clone |
Select this check box to return all metadata objects that are based on your selection criteria. Clear the check box for any metadata that you do not want to clone. |
Note. Remember that you must compile all cloned metadata. You can do so using the Mass Compile utility.
See Also
This section provides an overview of the Metadata Mover utility and discusses how to:
Specify metadata export criteria.
Export metadata from a source to a target database.
Import metadata into the target database.
Build imported metadata in the target database.
The Metadata Mover utility enables you to migrate PeopleSoft Performance Management Warehouse metadata from one database to another. Specifically, the Metadata Mover utility moves record metadata, tablemaps, datamaps, expressions, filters, constraints, data sets, engine metadata, jobs, jobstreams, report metadata, metric metadata, tree metadata, value objects rule metadata, Data Manager rules, Data Manager rule sets, Allocation Manager rules, Allocation Manager rule sets, Data Mapper rule groups, and Data Mapper rule sets.
The Metadata Mover utility allows metadata to be moved between PeopleSoft 8.8x environments. For example, you cannot move metadata from a PeopleSoft 8.8x environment to a PeopleSoft 8.0x environment. Additionally, to successfully move metadata between PeopleSoft 8.8x EPM environments, the source and target databases must be the same type. For example, if you have a Microsoft database, you must migrate to another Microsoft database. You can't migrate from a Microsoft database to an Oracle database.
The Metadata Mover utility maintains SQL counters for the target database. The source SQL counter will be lost when the move is completed so that uniqueness in ensured within the target database.
Follow these steps to move PeopleSoft Performance Management Warehouse metadata from your source database to your target database:
Sign in to your source database.
Select the metadata that you plan to export from your source database using the Metadata Export page.
Use the Data Mover script to export the objects from the source database.
Use the Data Mover script to import the objects into the target database.
Use the Mass Compile and Audit processes in the target database to build and validate the migrated metadata objects.
Page Name |
Object Name |
Navigation |
Usage |
Metadata Export - Export Definition |
PF_MD_EXPORT |
EPM Foundation, Foundation Metadata, Other Metadata Operations, Metadata Export, Export Definition |
Specify metadata export criteria. |
Metadata Export - Export List |
PF_MD_EXPLST |
EPM Foundation, Foundation Metadata, Other Metadata Operations, Metadata Export, Export List Click the Generate Export List button on the Export Definition page. |
View a list of metadata objects that you selected on the Export Definition page. These objects will be exported to your target database. The list includes the following information for each metadata object: metadata type, setID value, metadata object code, and related objects. |
Access the Export Definition page.
Note. You can maintain many export IDs, although you can have only one metadata export list. This means that when you generate the metadata export list, the system replaces the existing list with a new list.
In the Metadata Selection List group box, select:
Metadata Object Type and Metadata Object Code |
Select each metadata object type and code that you want to export to your target database. Remember that you must select metadata dependencies (if the metadata has dependencies). |
Fetch Related Objects |
Select this check box to have the system fetch all objects that are related to or dependent on the object that you have selected and add them to the metadata export list. |
Generate Export List |
Click to have the system generate the list of objects to be exported. You can view them on the Metadata Export - Export List page. For example, if you select a datamap as the object type and select the Fetch Related Objects check box, the system adds all related expressions, filters, constraints, data sets, Data Manger rules, Allocation Manager rules, and value objects to the metadata export list. |
The following table shows which metadata objects are dependent upon each other.
Metadata Type |
Related Objects |
Allocation Manager rules |
Meta Values. |
Allocation Manager rule set |
Allocation Manager Rules and Meta Values. |
Data Manager rules |
Meta Values. |
Data set |
Option is not available. |
Data Manager rule set |
Data Manager rules and meta values. |
Data Mapper rule set |
Set of data mapper rules. |
Data Mapper rule group |
Group of Data Mapper rules. |
Constraint |
Data sets, Allocation Manager rules, Allocation Manager meta values, Data Manager rules, and Data Mart meta values. |
Filter |
Constraints and related objects. |
Datamap |
Expression, filter, and related objects. |
Tablemap |
Datamap and related objects. |
Record |
Tablemap and related objects. |
Jobstream |
Option is not available. |
Job |
Jobstream. |
Engine |
Job and jobstream. |
Tree, metric, report, and rule metadata, expressions, Data Loader and data mart data |
Option is not available. |
Review the metadata export list and if you are sure that this is the data that you want to export, proceed to the next step.
The Data Mapper is documented in the PeopleSoft Enterprise Global Consolidations 8.9 PeopleBook.
See PeopleSoft Enterprise Global Consolidations 8.9 PeopleBook.
After selecting the metadata that you want to export from your source database to your target database (using the Export Definition page), perform the export. To perform the export, follow these steps:
Sign in to Data Mover in the metadata source database.
Open PFMMEXP.dms in the Data Mover tool.
Edit the path name for the output files (you can select which path you want).
Run the script.
The script exports the metadata tables into a DAT file for the export list that is generated through the Export Definition page.
Sign out of the source database.
You now need to import the metadata that you just exported.
Now that you have exported the metadata from the source database, you must import the metadata into the target database in the following way:
Sign in to the target database.
Open PFMMIMP.dms in the Data Mover tool.
Edit the path of both the input files to point to the output directory of the exported files.
Run the script.
The script exports the metadata tables from the .DAT files.
After you've finished importing the metadata into your target database, you must build the new metadata. To build the new imported metadata, you must first recompile it. If you imported a small number of new metadata objects, you can go to each metadata object's page and click the Compile button.
Note. Now there is no SQL object counter on the pages. Clicking the Compile button generates the counter.
If you have imported a large quantity of metadata objects, run the Mass Compile process followed by the Audit process.
Correct any errors that were identified during the Audit process.
Rerun the audit until it is clean.
See Also
Auditing PeopleSoft EPM Objects
The Metadata Delete process enables you to delete records, tablemaps, datamaps, and rule metadata.
Page Name |
Object Name |
Navigation |
Usage |
Metadata Delete |
PF_METADATA_DEL |
EPM Foundation, Foundation Metadata, Other Metadata Operations, Delete Metadata, Metadata Delete |
Delete metadata. |
Access the Metadata Delete page.
MetaData Type |
Select the type of metadata that you want to delete. |
Metadata Object Code |
Based on the metadata type that you have selected, select the metadata object from the valid values that are available. For example, if you select datamap as the metadata type, you will be able to select from the available datamap objects. |
|
Click the Fetch Related Objects button to populate the grid with the related metadata objects. |
|
Click the Delete All Objects button to delete the object code and all the related objects. |
This section provides an overview of Performance Management Warehouse data archive and discusses how to define a warehouse archive project.
You can use the PeopleSoft Performance Management Warehouse data archive functionality to archive your OWE or MDW data. The PeopleSoft Performance Management Warehouse Build Archive Project links to the PeopleTools archive tool when you have defined your selection criteria.
The system archives any OWE or MDW data that is over two years old. The system archives any warehouse records that are over five years old.
The archived data is deleted from the database and saved to a flat file.
Page Name |
Object Name |
Navigation |
Usage |
Warehouse Archive Project |
PF_ARCH |
EPM Foundation, Foundation Metadata, Other Metadata Operations, Build Archive Project, Warehouse Archive Project |
Archive your PeopleSoft Performance Management Warehouse data. |
Access the Warehouse Archive Project page.
Note. Make sure the Archive to Flat File check box is selected on the PeopleTools Record Criteria page (ARCH_PROJ).