Setting Up and Working with EPM Foundation Metadata

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.

Understanding Metadata

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.

Metadata Terms and Objects

When we discuss metadata, we discuss:

• Technical metadata.

  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.

  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:

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:

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

Working with Engine Metadata

Setting Up Job Metadata

Using Balancing and Reconciliation Features

SQL Object ID

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:

Metadata Utilities

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

Finding Metadata Objects

Running Mass Validate

Performing Impact Analysis

Common Elements Used in This Chapter

Finding Metadata Objects

This section describes how to search for metadata objects.

Page Used to Find Metadata Objects

Searching for 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.

Applying the Hidden Flag to Metadata Objects

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.

Applying the Hidden Flag to 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.

Granting Access to Hidden Metadata Objects

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.

Setting Up Record Metadata

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.

Understanding Record Metadata

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.

The Record Stub

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.

Pages Used to Set Up Record Metadata

Defining Record Metadata

Access the Record Metadata page.

Record Details

Related Warehouse Tables

Owner Details

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.

Reviewing Record Column Properties

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.

Creating and Reviewing Related Metadata Objects

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

Setting Up Rule Metadata

This section provides an overview of rule metadata and discusses how to define rule metadata.

Understanding 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 Used to Set Up Rule Metadata

Defining Rule Metadata

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.

Setting Up Tablemaps

This section provides and overview of tablemaps, lists common elements, and discusses how to:

• Define tablemaps.

• Define reference tables.

• View SQL IDs.

Understanding Tablemaps

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:

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.

Common Elements Used in This Section

Pages Used to Set Up TableMaps

Defining TableMaps

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

Child Fact Tables

Defining Reference Tables

Access the TableMap - Reference page.

Reference Tables

Child Reference Tables

Viewing SQL IDs

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.

Setting Up Datamaps

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.

Understanding Datamaps

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.

Pages Used to Set Up Datamaps

Defining Datamaps

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.

Viewing DataMap Fields

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. See Setting Up Expressions.
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.

Viewing DataMap Constraints

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.

Setting Up Expressions

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 Used to Set Up Expressions

Defining Expressions

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.

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.

Note. All expression functions are operating system independent and database dependent.

Using Data Sets

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 Used to Use Data Sets

Recompiling or Rebuilding DataSets

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

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.

Setting Up Filters

This section provides an overview of filters and discusses how to:

• Define filters.

• Specify filter selection criteria.

• Use the tree viewer.

Understanding Filters

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.

Pages Used to Set Up Filters

Defining Filters

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

Specifying Filter Selection Criteria

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.

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.

Using the Tree Viewer

Access the Tree Viewer page.

You can expand all levels of the tree to view detailed information. Double-click the folder button 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.

Setting Up Constraints

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.

Pages Used to Set Up Constraints

Defining Constraints

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.

Specifying Constraint Criteria

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.

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.

Specifying Constraint Details

Access the Constraint - Details page.

Here you can view the data elements that are targeted based on the filter selection criteria.

Creating User-Defined Functions

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

Working with Record Summary Metadata

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 Used to Work With Record Summary Metadata

Creating or Rebuilding Record Summary Metadata

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:

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

Running the PF Record Summary Engine

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.

Setting Up and Flattening Tree Metadata

This section provides overviews of trees and tree flattening, and discusses how to:

• Define trees.

• Flatten tree metadata.

Understanding Trees

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

Understanding Tree Flattening in the OWE

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.

Note. The OWE uses a different process for flattening trees than the MDW. Do not confuse the two processes.

See Processing Trees and Recursive Hierarchies in the Multidimensional Warehouse.

Pages Used to Set Up and Flatten Tree Metadata

Defining Tree Metadata

Access the Tree Metadata page.

Tree Metadata Information

Tree Tables

Flattening Tree Metadata in the OWE

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.

Setting Up Report Metadata

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.

Pages Used to Set Up Report Metadata

Defining Report Details

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.

Setting Up Metric Metadata

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 Defining Dimension and Metric Security.

Page Used to Set Up Metric Metadata

Defining and Securing Your 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.

Defining and Setting Up Dimensions

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.

Understanding Dimensions

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 Defining Dimension and Metric Security.

Pages Used to Set Up Dimensions

Defining Dimensions

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.

Defining Dimension Details for an OWE Warehouse Table

Access the OWE Detail for Dimension page.

Record Information

Security

Alternate Fields

Defining Dimension Details for an MDW Table

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.

Cloning Metadata

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 Used to Clone Metadata

Defining Metadata Clone Criteria

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

Running Mass Compile

Using the Metadata Mover Utility

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.

Understanding the Metadata Mover Utility

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 enables 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:

1. Sign in to your source database.

2. Select the metadata that you plan to export from your source database using the Metadata Export page.

3. Use the Data Mover script to export the objects from the source database.

4. Use the Data Mover script to import the objects into the target database.

5. Use the Mass Compile and Audit processes in the target database to build and validate the migrated metadata objects.

Pages Used to Migrate Metadata

Specifying Metadata Export Criteria

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:

The following table shows which metadata objects are dependent upon each other.

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 9.0 PeopleBook.

See PeopleSoft Enterprise Global Consolidations 9.0 PeopleBook.

Exporting Metadata from a Source Database to a Target Database

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:

1. Sign in to Data Mover in the metadata source database.

2. Open PFMMEXP.dms in the Data Mover tool.

3. Edit the path name for the output files (you can select which path you want).

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

5. Sign out of the source database.

You now need to import the metadata that you just exported.

Importing Metadata into the Target Database

Now that you have exported the metadata from the source database, you must import the metadata into the target database in the following way:

1. Sign in to the target database.

2. Open PFMMIMP.dms in the Data Mover tool.

3. Edit the path of both the input files to point to the output directory of the exported files.

4. Run the script.

   The script exports the metadata tables from the .DAT files.

Building Imported Metadata in the Target Database

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

Running Mass Compile

Deleting Metadata

The Metadata Delete process enables you to delete records, tablemaps, datamaps, and rule metadata.

Page Used to Delete Metadata

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

Archiving EPM Data

This section provides an overview of EPM data archive and discusses how to define a warehouse archive project.

Understanding EPM Data Archiving

You can use the warehouse archive project functionality to archive your OWE or MDW data. The Warehouse Archive Project links to the PeopleTools archive tool when you have defined your selection criteria. The archived data is deleted from the database and saved to a flat file.

Page Used to Archive EPM Data

Defining a Warehouse Archive Project

Access the Warehouse Archive Project page.

Warehouse Component	Select the component to archive: Operational Warehouse - Enrich or Multi-Dimensional Warehouse.
Archive ID	Enter an archive ID for your project.
Archive records on or before	Select the date. Only records with a date equal to or before the date that is specified will be archived.
	Click to build the archive project. The system accesses the PeopleTools, Archive Data component. You will need to manage the rest of the archival process from the PeopleTools pages. For information about this component, see your PeopleSoft PeopleTools documentation.

Note. Make sure the Archive to Flat File check box is selected on the PeopleTools Record Criteria page (ARCH_PROJ).