Setting Up Record Metadata

This topic provides an overview of record metadata and discusses how to:

Define record metadata.
Review record column properties.
Create and review related metadata objects.

Pages Used to Set Up Record Metadata

Page Name	Definition Name	Navigation	Usage
Record Metadata	PF_META_REC_TBL1	select EPM Foundation, then select Foundation Metadata, then select Metadata Creation and Editing, then select Record Metadata, then select Record Metadata	Define record metadata for any new tables that you add to EPM.
Record Metadata - Field Properties	PF_META_REC_SEQ	select EPM Foundation, then select Foundation Metadata, then select Metadata Creation and Editing, then select Record Metadata, then select Field Properties	Verify that your permanent and temporary tables are in sync.
Record Metadata - Table Description	PF_META_REC_NOTE	select EPM Foundation, then select Foundation Metadata, then select Metadata Creation and Editing, then select Record Metadata, then select Table Description	Enter any notes that you want to associate with the record metadata.
Record Metadata - Related Metadata	PF_META_RELMD	select EPM Foundation, then select Foundation Metadata, then select Metadata Creation and Editing, then select Record Metadata, then select Related Metadata	Create and review tablemaps, datamaps, and constraints that are related to specific record metadata.

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.

Record Metadata Page

Use the Record Metadata page (PF_META_REC_TBL1) to define record metadata for any new tables that you add to EPM.

Navigation

select EPM Foundation, then select Foundation Metadata, then select Metadata Creation and Editing, then select Record Metadata, then select Record Metadata

Image: Record Metadata page

This example illustrates the fields and controls on the Record Metadata page. You can find definitions for the fields and controls later on this page.

Record Details

Temporary Table

Select a temporary table to use in parallel processing.

When the system uses temporary tables for parallel processing, it uses the temporary table in conjunction with the record suite table.

Note: Temporary tables need to be selected only for Operational Warehouse - Enriched (OWE) tables.

Table Type

Select the type of table for the selected record.

You can select Dimension Reference Table, Dimension Table, Fact Reference Table, Fact Table, Security Join Table, or Transaction-Dated Fact Table.

Merge Allowed

Select this check box if the table name needs to be merged from a temporary table to a final table by the Merge (PF_MERGE) application engine.

Generally, this check box is used for engine output tables only.

Warning! The following tables must never be marked for Merge: PF_LEDGER_F00, PF_JRNL_F00, PF_ADB_JRNL_F00, PF_LED_ADB_F00, LEDGER, or LEDGER_ADB.

Selective Merge

Select this check box to use in the selective merge delete clause.

Temporary tables that are created during the last step of a jobstream run are merged into permanent tables (F00 tables). To avoid duplicates in the permanent tables, a delete must occur before the merge. Typically, the run control parameters are used to delete the necessary rows from the permanent tables and then the temporary tables are merged in. For some isolated cases, a more restrictive delete is necessary. The Selective Merge process performs a delete with even more criteria than just the run control parameters. Selective Merge bases its delete on the run control parameters plus the SQL Object ID that is entered in the record metadata settings for each specific table and settings within the Application Engine (AE).

Note: You generally do not use Selective Merge unless your application uses KPI Manager.

SQL Object ID

Select the ID of the SQL object for the selective merge.

Related Warehouse Tables

Error Table

Select the OWE error table related to the selected record.

The error table contains the error data that fails as part of the edit and modification process.

TSE Table Name

Select the TSE table related to the selected record.

The TSE (transaction editor set) table contains error message detail information.

Owner Details

Component

Select the component to which the record metadata belongs.

Select either Multi-Dimensional Warehouse or Operational Warehouse - Enrich.

Sub Component

Select the sub component, or type of data, associated with the selected record.

Select either Enriched Data orInput Data.

When you have completed your record metadata setup, click the Compile button to generate the SQL objects. You must also build your SQL anytime you make changes to the record metadata.

Record Metadata - Field Properties Page

Use the Record Metadata - Field Properties page (PF_META_REC_SEQ) to verify that your permanent and temporary tables are in sync.

Navigation

select EPM Foundation, then select Foundation Metadata, then select Metadata Creation and Editing, then select Record Metadata, then select Field Properties

Image: Field Properties page

This example illustrates the fields and controls on the Field Properties page. You can find definitions for the fields and controls later on this 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.

Record Metadata - Related Metadata Page

Use the Record Metadata - Related Metadata page (PF_META_RELMD) to create and review tablemaps, datamaps, and constraints that are related to specific record metadata.

Navigation

select EPM Foundation, then select Foundation Metadata, then select Metadata Creation and Editing, then select Record Metadata, then select Related Metadata

Image: Related Metadata page

This example illustrates the fields and controls on the 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).