Setting Up and Working with Metadata for the Operational Warehouse - Enriched

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.

• Set up metric metadata.

• Set up record summary metadata.

• Set up report metadata.

• Clone metadata.

• Use the Metadata Mover utility.

• Delete metadata.

• Create user-defined functions.

Understanding Metadata

The PeopleSoft Analytical Applications are 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 Analytical Applications product suite.

When you run PeopleSoft EPM processes, you require two types of input: your business data and the appropriate EPM metadata. Metadata provides an abstraction layer that enables technical users to establish dynamic relationships between tables, business users to easily identify the data that interests them without having to know the database structure, and administrators to manage 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 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 EPM and enables users to work with EPM Analytical 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 EPM. 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 metadata is nested to define a SQL statement.

EPM 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 (EPM Foundation, Foundation Metadata, Other Metadata Operations, Metadata Search).

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 . Your PeopleSoft database contains several types of tables:

• PeopleTools tables.

• PeopleSoft EPM business rules tables.

• PeopleSoft EPM data tables.

Record metadata defines and identifies the PeopleSoft EPM data tables only.

PeopleSoft delivers permanent data tables 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 EPM 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 (EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Record Metadata, Record Metadata).

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 (EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Record Metadata, Field Properties).

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 (EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Record Metadata, Related Metadata).

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 (EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Rule Metadata).

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

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 (EPM Foundation, Foundation Metadata, Metadata Creation and Editing, TableMap, TableMap).

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 (EPM Foundation, Foundation Metadata, Metadata Creation and Editing, TableMap, Reference).

Reference Tables

Child Reference Tables

Viewing SQL IDs

Access the TableMap - SQL IDs page (EPM Foundation, Foundation Metadata, Metadata Creation and Editing, TableMap, SQL IDs ).

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.

• View datamap fields.

• View or add constraints for a datamap.

• Use the DataMap Wizard to create datamaps.

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

Understanding Datamap Setup

PeopleSoft provides you with the option of setting up datamaps using the DataMap Wizard, which is intended to simplify the datamap creation process. You can still use the Record Metadata, TableMap, and DataMap components to manually set up datamaps.

See Setting Up Record Metadata, Setting Up Tablemaps.

Pages Used to Set Up Datamaps

Defining Datamaps

Access the DataMap - General Properties page (EPM Foundation, Foundation Metadata, Metadata Creation and Editing, DataMap, General Properties).

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 (Click DataMap Fields link on the General Properties 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 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 (EPM Foundation, Foundation Metadata, Metadata Creation and Editing, DataMap, Constraints).

You can use this page to view constraints associated with a datamap. You can also add new constraints to the datamap.

Setting Up Datamaps using the Datamap Wizard

Access the Datamap Wizard (EPM Foundation, Foundation Metadata, Metadata Wizards, Datamap Wizard)

DataMap Wizard - Record Metadata (Step 1 of 6)

The first view of the DataMap Wizard enables you to define Record metadata for your DataMap.

Next	Click to advance to the next step of the DataMap Wizard and confirm your record metadata selection.
Record Metadata	Select the record metadata (primary table) to associate with the tablemap (which is used as the basis for your datamap) Generally, a primary table will be a fact table. Only tables that have been defined in record metadata appear as valid values.
Create Record	Click to access the Record Metadata page and create a new primary table to associate with the tablemap.

Note. Once you select record metadata for the wizard, a new link appears that can access the Record Metadata page for that specific record. The link name is determined by the description provided on the Record Metadata page.

In the example above the link is named Demo Source because that is the description given for the PF_AL_SRC record on the Record Metadata page.

DataMap Wizard - TableMap Metadata (Step 2 of 6)

The second view of the DataMap Wizard enables you to define TableMap metadata for your DataMap.

Previous	Click to return to the previous step of the DataMap Wizard.
Next	Click to advance to the next step of the DataMap Wizard and confirm your tablemap selection.
TableMap	Select a tablemap to associate with the datamap.
Create TableMap	Click to access the TableMap page and create a new tablemap to associate with the datamap.

Once you select a tablemap for the wizard, a new link appears that can access the TableMap page for that specific tablemap. The link name is determined by the description provided on the TableMap page.

Note that as you progress through the DataMap Wizard steps, the previous section becomes unavailable for input. You can click Previous at any time to return to the previous step.

DataMap Wizard - DataMap Metadata (Step 3 of 6)

The third view of the DataMap Wizard enables you to define your DataMap.

Previous	Click to return to the previous step of the DataMap Wizard.
Next	Click to advance to the next step of the DataMap Wizard and confirm your datamap selection.
DataMap	Select a datamap.
Create DataMap	Click to access the DataMap page and create a new datamap.

Once you select a datamap for the wizard, a new link appears that can access the DataMap page for that specific datamap. The link name is determined by the description provided on the DataMap page.

Note that as you progress through the DataMap Wizard steps, the previous section becomes unavailable for input. You can click Previous at any time to return to the previous step.

DataMap Wizard - Filter Metadata (Step 4 of 6)

The fourth view of the DataMap Wizard enables you to define filter metadata for your DataMap.

Previous	Click to return to the previous step of the DataMap Wizard.
Next	Click to advance to the next step of the DataMap Wizard and confirm your filter selection.
Filter	Select a filter to associate with the datamap.
Create Filter	Click to access the Filter page and create a new filter.

Once you select a filter for the wizard, a new link appears that can access the Filter page for that specific filter. The link name is determined by the description provided on the Filter page.

Note that as you progress through the DataMap Wizard steps, the previous section becomes unavailable for input. You can click Previous at any time to return to the previous step.

DataMap Wizard - Constraint Metadata (Step 5 of 6)

The fifth view of the DataMap Wizard enables you to define constraint metadata for your DataMap.

Previous	Click to return to the previous step of the DataMap Wizard.
Next	Click to advance to the next step of the DataMap Wizard and confirm your constraint selection.
Constraint	Select a constraint to associate with the datamap.
Create Constraint	Click to access the Constraint page and create a new constraint.

Once you select a constraint for the wizard, a new link appears that can access the Constraint page for that specific constraint. The link name is determined by the description provided on the Constraint page.

Note that as you progress through the DataMap Wizard steps, the previous section becomes unavailable for input. You can click Previous at any time to return to the previous step.

DataMap Wizard - DataMap Summary (Step 6 of 6)

The sixth and final view of the DataMap Wizard provides you with a summary of all the metadata defined for your DataMap.

You can click on the links to the right of each metadata object to return to their main criteria pages and make adjustments to the rules.

You can also click Previous to return to the previous DataMap Wizard steps.

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 (EPM Foundation, Business Metadata, Constraint and Expressions, Expression).

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 analytical 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 (EPM Foundation, Foundation Metadata, Metadata Creation and Editing, DataSet).

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 analytical 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 (EPM Foundation, Business Metadata, Constraint and Expressions, Filter, Filter).

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 (EPM Foundation, Business Metadata, Constraint and Expressions, Filter, Selection Criteria).

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 (Click the Filter Tree button on the Selection Criteria 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 (EPM Foundation, Business Metadata, Constraint and Expressions, Constraint, Constraint).

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 (EPM Foundation, Business Metadata, Constraint and Expressions, Constraint, Criteria).

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 (EPM Foundation, Business Metadata, Constraint and Expressions, Constraint, Details).

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

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 (EPM Foundation, EPM Security, Metrics and Dimensions, Create/Edit Secured Metrics, Define Metric).

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.

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 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 (EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Record Summary).

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 (EPM Foundation, Data Enrichment Tools, Profit Manager, Record Summary, Summarize Error Statistics, PF Record Summary).

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 Report Metadata

Report metadata is delivered for Crystal reports, 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 (EPM Foundation, Foundation Metadata, Metadata Creation and Editing, Report Metadata, Report Details).

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	Select the ID assigned by PeopleTools Cube Builder 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.

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 (EPM Foundation, Foundation Metadata, Other Metadata Operations, Metadata Clone).

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. Deselect 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 EPM 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 EPM 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 (EPM Foundation, Foundation Metadata, Other Metadata Operations, Metadata Export, Export Definition).

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.

Note. Data Mapper is documented in the PeopleSoft Global Consolidations PeopleBook.

See Mapping to a Common Chart of Accounts (Data Mapper).

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 (EPM Foundation, Foundation Metadata, Other Metadata Operations, Delete Metadata, Metadata Delete).

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.

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 Analytical 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

OWE Dimension Correction Pages

Organizational change is both necessary and inevitable. Your organization will restructure departments, hire new department managers, develop new products, and obtain new customers through a variety of sales channels. In each case, you must capture these business rules in EPM.

Although the prepackaged ETL jobs are designed to automatically capture these changes to your organization, you might encounter times when you are required to add or update dimensions directly in the PeopleSoft pages. You can use the OWE Dimension Maintenance menu items for this purpose (EPM Foundation, Business Metadata, OWE Dimension Maintenance).

This menu includes a separate menu item for each functional area that corresponds to one or more dimensions from your PeopleSoft source data and contains the following categories:

• Common.

• CRM.

• Supply Chain Management.

• Human Resource Management System.

For example, under the Common menu there is a specific menu item to add or update common dimensions. The following is an example of one of the maintain dimension pages:

For each dimension, enter the relevant information for your dimension and save.

Warning! It is recommended that dimensions be accessible only to the EPM Administrator.

Note. Critical errors, such as missing setIDs or business units, must be fixed on their respective setup pages. Typically, the types of errors you correct in the dimension pages are the non-key fields of an error record.