Setting Up Tablemaps

Pages Used to Set Up TableMaps

Page Name	Definition Name	Navigation	Usage
TableMap	PF_TBLMAP_DEFN	select EPM Foundation, then select Foundation Metadata, then select Metadata Creation and Editing, then select TableMap, then select TableMap	Define tablemap and tablemap child tables.
Reference	PF_TBLMAP_REF	select EPM Foundation, then select Foundation Metadata, then select Metadata Creation and Editing, then select TableMap, then select Reference	Define reference and reference child tables.
SQL IDs	PF_TBLMAP_REF	select EPM Foundation, then select Foundation Metadata, then select Metadata Creation and Editing, then select TableMap, then select SQL IDs	View the generated SQL object ID prefixes for the tables that are defined in this tablemap.

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:

Primary table

Often a fact table, the primary table, from a logical point of view, to which the other tables are related.

Child table

A hierarchical child of the primary table (all the same keys, plus one). Child tables enable you to have a one-to-many relationship between a primary table and its child tables and to define the relationship between these tables through the tablemaps. You specify a child table only if you need fields from that table in your tablemap.

Reference table

A table that defines the properties for one or more keys in the primary table. Reference tables contain additional attributes, or properties, of the transactional fact information. Some examples of reference tables are Customers, Products, Channels, Geography, and Time. A reference table always has one or more key fields that it shares with the primary table. These fields are defined on this page and matched with the corresponding fact field to enable engines to access data through the join. Each reference table may be the parent table to one or more reference child tables.

Reference child table

A hierarchical child of the reference table (all the same keys, plus one). Reference child tables are similar to child tables in that they allow for multiple types or rows of data per related dimension table. An example of a reference child and its related dimension is a customer demographic table's relationship to a customer table. That is, you may want to track various demographic values for each customer, such as salary range and geographic codes

The relationship between these tables is often referred to as a star schema. A star schema is a group of tables that are related to a central fact table. A single fact table can have numerous fact references and dimensions and each dimension can have numerous dimension references.

Image: Example of relationship between tables (star schema)

This diagram illustrates an example of table relationships.

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 to Set Up TableMaps

Join Mapping

Depending on the key field type, you may or may not be able to edit this field. The system automatically resolves certain fields. Fields that you can edit are in white. If you change the method to Map One to One =, then you can specify the primary field. Map One to One = is the default if the reference key field has the same name as on the primary table.

Primary Field

If you change the join mapping to Map One to One =, then you can specify the primary field using the valid values that are provided by the system.

Additional Field Lookup Table

If join mapping is set to Additional Key, you can specify that key value in this field using the valid values that are provided by the system. Additional Key is the default when the key cannot be resolved and if it does not have the same name on the primary table. In a valid tablemap, every child has only one additional key.

TableMap Page

Use the TableMap page (PF_TBLMAP_DEFN) to define tablemap and tablemap child tables.

Navigation

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

Image: TableMap page

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

Select the table that you want to use as the primary table for this tablemap.

Only tables that have been defined in record metadata appear as valid values.

A primary table is the center of the tablemap. Generally, a primary table will be a fact table. Fact tables contain the data that is relevant to a single business transaction, and they also have a unique key structure that can identify other related fact reference or child tables. A tablemap can contain only one primary table, but it may be related to many child and reference tables.

View Primary Table Fields

Click to go to the Field Properties page and review the fields that are associated with the underlying record metadata.

List Only Fact Records

Select this check box if you want to view only the tables that are defined in record metadata as fact table type.

Child Fact Tables

Child Table

Select the appropriate child table to join with the primary table for this tablemap.

Only tables that have been defined in record metadata appear as valid values.

List Only Fact Reference Recs (list only fact reference records)

Select this check box if you want to view only the tables that are defined in record metadata as fact table type.

Child Key Field

Displays the key field of the child table used to join to the fact table key field.

This key field relates the child table to the primary table.

Join Operator

Select a method to join the child key field and the fact table key field.

If you select Additional Key to use in the join, you must specify the record in which the additional key resides.

Fact Table Key Field

Select the key field of the primary fact table used to join to the child key field.

This field relates the fact table to the child table. In some cases, this field is display only.

Additional Field Lookup Table

Select the record in which the additional key for the join resides.

This field is available only when you select Additional Key in the Join Operatorfield.

Reference Page

Use the Reference page (PF_TBLMAP_REF) to define reference and reference child tables.

Navigation

select EPM Foundation, then select Foundation Metadata, then select Metadata Creation and Editing, then select TableMap, then select Reference

Image: Reference page

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

Reference Tables

Reference Table

Select the appropriate reference table that you want to join to the primary table.

Only tables that have been defined in record metadata appear as valid values.

View Only Dimension Records

Select this check box to view only the tables that are defined in record metadata as dimension table type.

View Reference Table Fields

Click to access the Field Properties page and view the record metadata fields related to the selected reference table.

Reference Key Field

Displays the key field of the reference table used to join to the primary table key field.

Join Mapping

Select a method to join the reference key field and the primary table key field.

Primary Field

Select the key field of the primary table used to join to the reference key field.

Child Reference Tables

Child Table

Select the appropriate reference child table that you want to join to the primary table.

Only tables that have been defined in record metadata appear as valid values.

View Only Dimension Ref Recs (view only dimension reference records)

Select this check box to view only the tables that are defined in record metadata as dimension table type.

Reference Child Key Field

Displays the key field of the child reference table used to join to the reference table key field.

This key field relates the child reference table to the primary reference table.

Join Mapping

Select a method to join the reference child key field and the reference table key field.

If you select Additional Key to use in the join, you must specify the record in which the additional key resides.

Reference Key Field

Select the key field of the reference table used to join to the reference child key field.

Additional Field Lookup Table

Select the record in which the additional key for the join resides.

This field is available only when you select Additional Key in the Join Mappingfield.

SQL IDs Page

Use the SQL IDs page (PF_TBLMAP_REF) to view the generated SQL object ID prefixes for the tables that are defined in this tablemap.

Navigation

select EPM Foundation, then select Foundation Metadata, then select Metadata Creation and Editing, then select TableMap, then select SQL IDs

Image: SQL IDs page

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