Importing Source Business Units into EPM to Create Warehouse Business Units

This chapter provides an overview of warehouse business units, tableset sharing, setID mapping, and warehouse business unit setup, and discusses how to:

• Define setID and warehouse lineage.

• Establish warehouse business units using the Business Unit Creation Wizard.

• Establish warehouse business units manually.

• Review warehouse and general ledger business unit creation.

• Define collision maps.

Understanding Warehouse Business Units, TableSet Sharing, and SetID Mapping

As a part of EPM setup, you must create warehouse business units (WBU), and establish a mapping between warehouse business units and the business units that exist in the source systems that you are bringing into EPM. The mapping between source business units and warehouse business units has implications for the appropriate setID assignments for warehouse business units on warehouse record groups. Setting up warehouse business units is mandatory regardless of whether you plan to implement only data marts, only Analytical Applications, or some combination of the two.

SetIDs and TableSet Sharing

In PeopleSoft source systems (such as Campus Solutions or CRM), the rows in a control table (such as supplier, customer, and account) are divided into groups based on a key called the set ID. Each group of rows with the same setID constitutes a tableset. For example the figure below shows the tablesets on a hypothetical supplier table with two setIDs represented in red and blue:

Tablesets on a hypothetical supplier table

Every business unit associates with a particular setID on every control table. For example, BU1 might use the red tableset on the supplier table. This assignment serves to limit the suppliers that appear in prompts for transactions that take place in the context of BU1. Several other business units could also use the red setID on the supplier table, in which case these business units share the red tableset (hence the term tableset sharing). Since control tables in a PeopleSoft source system generally become dimensions in EPM, most EPM dimensions are setID-based.

Record Groups

Control tables such as supplier are themselves grouped into record groups based on the commonality of business process. SetID assignments actually take place at the record group level. In other words, a business unit must have the same setID on all control tables in a given record group. Each business unit in a PeopleSoft source system must have a setID assignment on every record group. At the time a business unit is created, the system assigns a default setID to all record groups for that business unit. The user can then manually modify the setID assignments as desired. For example, the following table illustrates the setID assignment for a few hypothetical business units on a few hypothetical record groups:

SetID assignments for hypothetical record groups

Assuming that the supplier table shown earlier is included in record group RG1, then (given the setID assignments indicated in the figure above) BU1, BU2, and BU3 would have access to the first three suppliers, while BU4, BU5, and BU6 would have access to the last three.

Mapping Two SetID Based Systems Together and Warehouse Lineage

EPM is a setID-based database. Consequently, all warehouse business units must have setID assignments on all warehouse record groups. However, because each PeopleSoft source system has its own tableset sharing (or set control space), the mapping between source business units and warehouse business units has implications for how setIDs must relate between the source system and the warehouse. As illustrated below, in order to correspond two set control spaces, it is necessary to provide two distinct mappings: the business unit mapping (which is under user control) and the warehouse lineage. Warehouse lineage defines the correspondence between all PeopleSoft source tables and EPM warehouse tables.

See Warehouse Lineage and Source Blueprints.

Hypothetical source to warehouse set control space mapping

The preceding figure illustrates two set control spaces and the mappings between them, with setID values shown as colored squares. In this example, warehouse business units have been created and mapped to source business units in a one-to-one relationship. Based on the lineage, it is possible to determine which setID values to assign to the corresponding warehouse record groups. Without the lineage information this determination would not be possible.

Potential SetID Conflicts

SetIDs from one or more sources can merge into a single warehouse setID (in other words, "many-to-one" relationships are allowed). However, the system does not allow one-to-many relationships to exist between source setIDs and warehouse setIDs on the same warehouse record group. For example, in the illustration below, BU1 and BU2 shared setID RED in the source. Assuming that RG1 is related by lineage to WG1, then the situation illustrated would force one setID (RED) to map to two different values (SHARE and MODEL) on warehouse record group WG1. This situation is not allowed because it would create a multi-valued lookup that would cause errors in the ETL process. Consequently the system does not allow this configuration to pass its validation step. You must modify the warehouse setID assignments to remove the splitting. In the case illustrated below, simply assigning the same warehouse setID to WBU1 and WBU2 on record group WG1 resolves the problem.

SetID splitting

FSCM and SetID Conflicts

Business unit merging is not the only way that setID conflicts can arise. There are two instances in the FSCM product that has a lineage that can introduce conflicts, as shown below:

SetID conflicts in the FSCM to warehouse mapping

The problem occurs when record groups from a source merge in the warehouses (for example, AP_10 and FS_02 merging into FS_02, as shown in the diagram above). When you use the Business Unit Creation Wizard, it assigns an initial guess for the setIDs of the warehouse business units that it creates for you. A problem can occur in the case where two or more business units have different setIDs on AP_10 and FS_02. For example, if you had two business units in FSCM (BU1 and BU2), and assigned them both setID RED on AP_10, and BLUE on FS_02, it is possible that the wizard could select RED as the setID assignment for FS_02 for WBU1, and BLUE for the setID of WBU2. In this case the wizard would detect splitting because it would see both RED and BLUE from the source being split to RED and BLUE on the target. The solution to this problem is straightforward: you should manually create the warehouse business units, and assign the correct setID. Then you can map the business units and do the validation in the Business Unit Mapping page. If you have an FSCM source with a complex setID configuration and you detect conflicts related to AP_10, FS_02, FS_07, or FS_18, it may be due to this lineage problem.

Warehouse Lineage and Source Blueprints

Warehouse lineage defines the relationship between PeopleSoft transaction tables and EPM target warehouse tables, as determined by the ETL process. Certain ETL jobs move data from PeopleSoft source tables to EPM Operational Warehouse - Staging (OWS), Operational Warehouse - Enriched (OWE), and Multidimensional Warehouse (MDW) target tables, and warehouse lineage simply refers to the path the data takes between the two systems. Lineage information is used during the warehouse business unit creation process to determine the appropriate PeopleSoft source setID for a given warehouse business unit, and map that setID to the appropriate warehouse record group.

Source blueprints actually record the warehouse lineage, and ship with EPM as system data.

Warehouse lineage information is only required for setID-based source systems. As such PeopleSoft delivers blueprints for PeopleSoft source systems only. Typically, third-party source systems do not use a setID column, and thus have no impact on the blueprints. You need not create blueprints for non setID-based third-party source systems.

Modifying a Source Blueprint Due to Customizations

If you customize your ETL jobs or add columns to an existing source or target table , you may need to modify the source blueprints to reflect these changes. It is only necessary to update the blueprints if you add a new setID-based table to your PeopleSoft source system and map the table into new, setID-based dimension in the warehouse. In that case, simply access the Source Blueprint page and add a row for each new dimension.

Remapping Business Units for an Updated Source Blueprint

If you receive an updated Source Blueprint from PeopleSoft, you must remap business units manually or with the business unit wizard after running the dms/dat. This creates the source to target mapping. All entries must be truncated and rerun.

Understanding Warehouse Business Unit Setup

Warehouse business units are created using several processes, including ETL jobs, defining parameters in PeopleSoft pages, and running a setup and validation wizard.

The following steps are required to establish warehouse business units:

1. Run prerequisite setup jobs.

2. Create setIDs in the warehouse.

3. Define source systems.

4. Establish warehouse lineage.

5. Create warehouse business units.

6. Assign warehouse setIDs to warehouse business units on all warehouse record groups.

7. Map source business units to warehouse business units.

8. Validate the business unit mapping.

PeopleSoft delivers the Business Unit Creation Wizard that automates many of these steps. However, you can manually perform these steps if you prefer.

Establishing Default SetIDs - Overview

You must define a default setID for each source that you wish to bring into the warehouse. SetIDs are created in a PeopleTools page, where you can add a new value for each default setID that you need to create. Generally you should use a different default setID for each source to keep tablesets from all sources separate—unless you have a good reason why data from different tablesets should merge. The wizard uses the default setID for all business units that it creates on all warehouse record groups for which the source blueprint supplies no lineage information. For example, if you bring in a CRM source, the CRM blueprint has no lineage information for many warehouse tables that relate to HCM or FSCM, simply because there are no source tables in CRM that relate to these tables. All unspecified warehouse tables receive the default setID.

Establishing Warehouse Sources - Overview

For each PeopleSoft source that you are bringing into the warehouse, use the Define Warehouse Sources page to define the properties of your source. The Business Unit Creation Wizard uses the defaults specified on the page for warehouse business unit creation. You should define a warehouse source even for third-party systems that are not setID-based.

The procedure for defining warehouse sources will not be discussed in this chapter because it is discussed in another chapter of this PeopleBook.

See Defining Warehouse Sources.

Note. To review the sources you currently have defined for your system, run the PS Query DMRP_2_1_SRC_SYSTEM_TBL.

Establishing Warehouse Lineage - Overview

Establishing lineage consists of two parts:

• Reviewing or updating source blueprints.

• Defining warehouse lineage.

Review or Update Source Blueprints

Source blueprints are used in establishing warehouse lineage. PeopleSoft delivers source blueprints for PeopleSoft sources. If you have added new SetID-based tables to your PeopleSoft system and you wish to bring these tables into the EPM database, you must update the source blueprints accordingly.

Define Warehouse Lineage

After you have defined your sources, you must associate a blueprint to each source by using the Warehouse Lineage page. The system ships with blueprints for all supported systems. Note that the FSCM Blueprint includes lineage information for ESA, SCM, and PeopleSoft Financials. Enterprise Learning Management (ELM) is not setID-based, so you can use the NONSETID blueprint for ELM sources (you should also use the NONSETID blueprint for third-party systems that have no setID). When you save the warehouse lineage page the system populates the PF_SRC_LINEAGE table with the combined lineage for all warehouse sources.

Creating Warehouse Business Units with the Business Unit Creation Wizard - Overview

The Business Unit Creation Wizard automates many of the steps required to set up warehouse business units and setIDs. Before you can use the wizard, you should understand the input and output tables used by the wizard. The following diagram illustrates the inputs that the wizard uses, and the output tables that it populates:

Input and output tables used by the Business Unit Creation Wizard

The Business Unit Creation Wizard uses the following input tables:

• SRC_SYSTEM_TBL: This table defines all the pillars that you are bringing into EPM. You populate this table using a PIA page

• PF_SRC_SETCNTRL: This table records the set controls (a combination of business units and setIDs) from all of your sources, and the setID assignments for all set controls on all source record groups. It is populated by ETL.

• PF_SRC_BU_NAMES: This table provides a distinct list of business units from all sources. It is populated by ETL

• PF_SRC_BU_ROLES: This table records all of the roles that associate to each source business unit, such as AP (accounts payable business unit), GL (general ledger business unit), and IN (inventory business unit).

• PF_SRC_LINEAGE: This table is a compilation of all of the Source Blueprints that you specify for all active sources that you create. It is populated by a PIA page when you save the Blueprint assignments (see below).

The Business Unit Creation Wizard populates the following output tables:

• PF_SETID_LOOKUP: The ETL process uses this table to map source setIDs to warehouse equivalents.

• PF_BUS_UNIT_MAP: The ETL process uses this table to map source business units to warehouse equivalents.

• BUS_TABLE_PF: The Wizard makes entries in this table for all warehouse business units that it creates. These tables are used by the analytical applications.

• BUS_TABLE_FS: Same as BUS_UNIT_TABLE_PF

• Warehouse Set Control Tables: The wizard makes the necessary entries in the warehouse set control tables when it assigns setIDs to each warehouse business unit on each warehouse record group.

The Business Unit Creation Wizard consists of three steps:

1. Reviewing Incoming Set Controls, Business Unit Wizard Step 1: Review the set controls from all of your sources and ensure you see the business units and setIDs that you expect from each source.

2. Addressing Collisions Between Set Controls, Business Unit Wizard Step 2: Examine name collisions between and among incoming set controls and set controls that may already exist in the warehouse. You should resolve the name conflicts by renaming the incoming set control unless you have a good reason to merge them.

3. Validating the Mapping, Business Unit Wizard Step 3: After the wizard has analyzed the setID assignments for all incoming set controls, you must review the mapping report produced by the wizard to see if any conflicts exist. If conflicts exist, you must correct the conflicts yourself or let the wizard correct the conflicts.

Note. Prior to running the Business Unit Creation Wizard, you must run the ETL jobs that populate the aforementioned input tables. These jobs are collectively described as dimension mapper ETL setup jobs. The Wizard populates the tables indicated above as output tables. For a new EPM installation, the output tables are empty. In the event that you make a mistake and need to re-run the wizard, you must truncate the output tables manually; no provision is made for the wizard to delete these entries. If you have already loaded data into the warehouse and then decide to re-run the wizard, the data you have already loaded may become invalid.

Creating Warehouse Business Units Manually - Overview

You can manually setup warehouse business units. The steps are outlined here:

Create Warehouse Business Units

Manually define all the warehouse business units that you require, using the Warehouse Business Unit page. You must define a default setID, a default currency rate type, and a calendar for each warehouse business unit.

Assign Warehouse SetIDs to Warehouse Business Units

As you create warehouse business units, you are prompted to provide a default setID. The default setID is automatically assigned to the new business unit on all record groups. In order to modify these assignments, you must use the Tableset Control page. Select the business unit (set control) of interest and modify its setID assignments on the record groups of your choice.

If several warehouse business units have exactly the same setID assignments, it's possible to copy the setIDs of an existing business unit. Define the setID assignments for one warehouse business unit, and then as you create new ones, use the name of the first warehouse business unit as the default setID of subsequent warehouse business units. The system assigns each record group the same setID as the copied business unit.

Map Warehouse Business Units

Once warehouse business units have been created, you must map them to source business units using the Business Unit Mapping page. It's possible to associate several source business units with a single warehouse business unit, thus creating a many-to-one mapping. However this is not good practice since it promotes setID conflicts, and it could cause fact table collisions as several business keys merge into one. PeopleSoft delivered ETL jobs do not support aggregating fact data during loading.

Validate Business Unit Mapping

Once you have created your mapping configuration, validate the mapping. The system analyzes the mapping configuration. If all of the set controls have been made unique as suggested above, the potential for conflicts in your business unit mapping is minimized. If conflicts are detected, you have two choices:

• Let the system correct the conflicts: The simplest choice is to click the "Accept Proposed setIDs and Save" button. This causes the system to automatically reassign the setIDs used by warehouse business units on various warehouse record groups in order to make the configuration valid. Generally this reduces the number of warehouse setIDs to eliminate conflicts.

• Modify business unit Mapping: You can choose to resolve the conflicts manually. This requires that you analyze the report to determine which warehouse setIDs must change in order to avoid setID Splitting.

Regardless of how you fix the problem, if conflicts are detected, you must return to the business unit mapping page and try the validation again, until no conflicts exist. Continue to iterate the validation process until you see no more conflicts. Once the conflicts are resolved, the process of creating business units and assigning setIDs is complete. You only need to inspect and modify the properties of the new warehouse business units.

Working with Invalid or Unused Source Business Units - Overview

The ETL logic that extracts source business unit data and brings it into the EPM database does not filter-out invalid or unused source business units. Hence, if you have invalid or unused business units in your PeopleSoft source, you can:

• Create a dummy Warehouse Business Unit (WBU) and map the undesired source business units to the dummy WBU.

• Reconfigure the Dimension Mapper setup jobs to filter out the source business units that you do not want to bring into the EPM database.

However, it is not recommended that you reconfigure the Dimension Mapper setup jobs or use SQL to delete business units from the internal Dimension Mapper tables (the unwanted business units will reappear the next time you run the Dimension Mapper setup jobs).

Working with PeopleSoft Human Capital Management (HCM) Source Business Units - Overview

As part of your configuration of the PeopleSoft HCM source system, you used the Company component to enter information about a single company or multiple companies in your organization. You assigned a three-character code for each company you defined in the HRMS source system. During the ETL process, those company codes are brought into the PF_SRC_SETCNTRL table, which records the source set controls, and is used as an input for the warehouse business unit mapping process.

The company codes reside in the same table (PF_SRC_SETCNTRL) as your source business units. Hence, when you map source business units to warehouse business units, be sure not to include the company codes in your mapping. If you have a large number of company codes, you may wish to filter-out these codes in the ETL job.

Filtering Company Codes in the ETL Job - Trans_Assign_Values Stage

If you are sure that all SETID and BUSINESS_UNIT values are five-characters in length (as is recommended), and all company codes are three-characters in length, you can access the ETL job J_Stage_PS_S_SET_CNTRL_REC_HCM_HCM91_EPM91 using DataStage Designer and filter all rows with Len(IPC_in.SETCNTRLVALUE) < = 3 in the Trans_Assign_Values Stage.

However, if you perform this configuration and you set up table-set sharing using company as the driving parameter along with business unit, the same table-set sharing information would be prevented from entering EPM and the business unit wizard would not be able to retain table-set sharing based on company codes in the HRMS source system.

Filtering Company Codes in the ETL Job - Source Stage

Alternatively you can create a WHERE filter in the selection-output of the source stage (DRS_SRC_PS_SET_CNTRL_REC) in the ETL job J_Stage_PS_S_SET_CNTRL_REC_HCM_HCM91_EPM91.

The WHERE filter above is just an example, it is not meant to be the exact WHERE filter you should create for the source stage.

Reviewing Warehouse and General Ledger Business Unit Properties - Overview

After creating and mapping warehouse business units, you should review your warehouse business units and general ledger business units to ensure the properties (such as default calendar) meet your requirements.

Review Warehouse Business Unit Properties (Business Unit Creation Wizard Only)

After creating warehouse business units with the Business Unit Creation Wizard, access the Warehouse Business Unit page to review the detailed properties of each business unit. Some of your warehouse business units may have been created with a base currency or rate type that differs from the defaults that you defined for your source. If this is the case, you need to change these settings for the appropriate business units. In addition, the Business Unit Wizard does not associate calendars to business units. You must do this manually for all your warehouse business units.

Review General Ledger Business Unit Properties

PeopleSoft general ledger business units (GLBU) are extracted from your source system and populated in the EPM database using ETL jobs; you do not need to recreate them in the warehouse. You can view general ledger business units by accessing the General Ledger Business Unit page. You may, on some occasions, create general ledger business units manually in the warehouse for certain analytical applications. See your EPM application PeopleBook for more details.

Creating Collision Maps - Overview

If you choose to allow two tablesets to merge in the EPM database, it is possible for collisions to occur between business keys (such as supplier ID). For example two suppliers from two different tablesets could both have the business ID "PEP", but could refer to very different suppliers (for example, Pepsi and Pep Boys). Collision maps provide a framework for resolving collisions between business IDs from two or more tablesets that merge in the warehouse. PeopleSoft provides three resolution methods for colliding business IDs: First In Wins, Error-Out Duplicates, and Use Mapping Table.

First In Wins

In this method, the first instance of a business ID is loaded into a tableset, and subsequent instances of the same ID from different tablesets is ignored. This approach is appropriate for large datasets, when the number of collisions is known to be small and the value of fixing errors is low. You can inspect the business keys that have been ignored by navigating to the Collision Map Error Report page.

Error Out Duplicates

In this method the system automatically loads the first instance of a business ID into a tableset, but subsequent instances of the same ID from different tablesets is sent to an error table where they can be inspected and remapped. This approach is appropriate for relatively small datasets, where the value of fixing errors is high. You must inspect and correct the errors manually by navigating to the Collision Map Error Reports page for the appropriate map.

Use Mapping Table

When using this mapping type, the system checks every incoming business key against a mapping table. If an entry does not exist in the mapping table, then the row errors-out. You can inspect the errors in the appropriate error report. For those entries that error out, you must update the mapping table and re-run the appropriate ETL map. This mapping method is relevant to the case where you have created a mapping table using an offline process or third-party tool.

Prerequisites to Creating Warehouse Business Units

This section provides you with prerequisites to creating warehouse business units and discusses how to:

• Run prerequisite ETL setup jobs.

• Size tablespaces for input tables.

• Create backups of impacted output tables.

• Enable PS Queries

• Verify the state of output tables

• Validate business unit and set control data

• Verify source blueprints

Running Prerequisite ETL Setup Jobs

Before you can begin creating warehouse business units you must run specific ETL jobs that setup certain OWS and OWE tables, and bring your source business unit data into EPM tables. Some of these jobs include OWS hash file, setup OWS, shared lookup, and setup OWE jobs.

The general OWS and OWE ETL setup jobs are not discussed here because the information is covered in another chapter of this PeopleBook. However, the ETL jobs that are specific to business unit data are discussed below.

See Setting Up DataStage for EPM, Running Initial Setup Jobs.

Running Hash File - Business Unit Data Jobs

To run the hash file jobs that pertain to business unit data:

• In DataStage Director, navigate to the hash file jobs for business unit data using the following navigation: Setup_E, Dimension_Mapper, Base, Load_Hash_Files, Server.

• Select the jobs in this category and run.

Running Setup - Business Unit Tables Jobs

To run the setup jobs that load business unit setup tables:

• In DataStage Director, navigate to the hash file jobs for business unit data using the following navigation: Setup_E, Dimension_Mapper, Base, Load_Tables, Sequence.

• Select the jobs in this category and run.

Running Shared Lookup - Business Unit Data Jobs

To run the shared lookup jobs that pertain to business unit data:

• In DataStage Director, navigate to the shared lookup jobs for business unit data using the following navigation: Shared_Lookups, DimensionMapper_Lookups

• Select the jobs in this category and run.

Sizing Tablespaces for the Input Tables

The Business Unit Creation Wizard input tables use the EWLARGE tablespace , while the PSINDEX tablespace is used for indexes. These table spaces should be large enough to store the data in input, output and intermediate tables related to warehouse business unit creation.

A good rule of thumb is to make sure that each of the two tablespaces are at least twice the size of the PF_SRC_SETCNTRL table. Determine the maximum size for a row of data in PF_SRC_SETCNTRL by examining this table in your database. Estimate the number of rows expected in this table, and then multiply these factors together. Double the result to get a minimum tablespace size estimate: Minimum tablespace size = 2 * #Rows * Size of one row of PF_SRC_SETCNTRL.

Creating Backups of Impacted Output Tables (Optional)

Create a DAT file backup of the output tables that are impacted by the warehouse business unit creation process. That way, if you encounter problems during the creation process you can use the DAT file to roll back your system.

Run the prepackaged Data Mover script DMBK_CREATE_DM_BACKUP to produce the backup DAT file of the impacted output tables. The following are the output tables backed up by the DAT file:

• SETID_TBL

• SET_CNTRL_TBL

• SET_CNTRL_GROUP

• SET_CNTRL_REC

• BUS_UNIT_TBL_FS

• BUS_UNIT_TBL_PF

• BUS_UNIT_TBL_GL

• PF_BUS_UNIT_MAP

• PF_SETID_LOOKUP

To roll back your system, run the Data Mover script DMBK_RESTORE_TABLES.

Enabling PS Queries for Data Verification

The following sections provide instructions on how to verify different aspects of your business unit related data. To perform the verifications you must run certain prepackaged PS Queries. To obtain the specific queries you must first enable the queries and the query security by installing the DMRP_QUERIES Application Designer project.

Perform the following steps to install the DMRP_QUERIES project and enable prepackaged PS Queries:

1. Locate the DMRP_QUERIES Application Designer project on My Oracle Support and install it.

2. In PIA navigate to QUERY_TREE_EW using the following path: PeopleTools, Security, Query Security, Query Access Manager.

3. Enter QUERY_TREE_EW for the tree name and search.

4. Click the QUERY_TREE_EW link from the grid to access.

5. Click the Insert Child Group button.

6. Enter DM_RED_PAPER _GROUP for the access group and add.

7. Enter Dimension Mapper Red Paper for the description and click OK.

8. Select the DM_RED_PAPER _GROUP - Dimension Mapper Red Paper link and click the Insert Child Record button.

9. Enter BUS_UNIT_SRC_PF for the record and add.

10. Repeat the steps to insert the following records:

    • PF_BLUEPR_DFN

    • PF_BLUEPR_DTL

    • PF_BUS_UNIT_MAP

    • PF_SCR_TBL

    • PF_SETID_LOOKUP

    • PF_SRC_BU_NAMES

    • PF_SRC_BU_ROLES

    • PF_SRC_LINEAGE

    • PF_SRC_SETCNTRL

    • PF_SSCL_DFN

    • PF_SSCL_DTL

    • SRC_SYSTEM_TBL

11. Click Save.

Once the queries are installed you can access them as follows:

1. In PIA navigate to the queries using the following path: Reporting Tools, Query, Query Viewer.

2. Enter DMRP in the search box.

3. Click on the HTML link next to the query of your choice to view the results in a browser window.

Verifying the State of Output Tables (Optional)

The prepackaged PS Queries enable you to inspect the state of the output tables that Dimension Mapper populates. If you are populating a warehouse for the first time, the output tables are empty of data except for the set control tables MODEL and SHARE, which will contain setID data.

If you are upgrading an existing warehouse, the tables are populated with data from all previously installed products. Existing warehouse business units can have an impact on the setup process if you map incoming business unites to existing warehouse business units. The Business Unit Creation Wizard respects the setID assignments of the existing warehouse business units and attempts to correlate the incoming setIDs and the existing setIDs on the appropriate record groups. If, for some reason, extraneous business units exist in the warehouses, unwanted setID mappings may be created.

For example, if you manually create a business unit named US01 in the OWE, you must assign a setID to US01 on all warehouse record groups. A default setID is required at business unit creation time, although you can manually reassign the setID for US01 on any warehouse record group by accessing the PeopleTools pages (should you wish).

If you bring in another source business unit (assume it is also named US01), and you map the two together, the system attempts to create mappings between the setIDs used by US001 in the source, and the setIDs used by US001 in the OWE. These mapping are then validated to ensure that no setID conflicts exist. If the US001 entry in the warehouse is in fact erroneous (demo data, for example), then you may create unnecessary conflicts that will impact the setup process.

Likewise, if you bring in a setID such as SHARE, and SHARE already exists in the warehouse, the Business Unit Creation Wizard prompts you to merge the two setIDs. If you select yes, then any setID assignments that SHARE itself might have in the source will be mapped to the existing setID assignments that SHARE has in the warehouse. This "second-order" indirection is utilized by some applications to enable advanced prompting features. Consequently you should make sure that you do not have erroneous WBUs or setIDs prior to running Dimension Mapper.

For these reasons, it is important to check the state of the output tables because existing business units and setIDs impact how incoming business units and setIDs from the source are mapped to their warehouse counterpart.

To inspect the state of the relevant input tables, you can use the following PS Queries:

Validating Business Unit and Set Control Data

Before you can begin creating warehouse business units or using the Business Unit Creation Wizard, you must run specific ETL job that setup the OWS, OWE, and common tables, and bring in your source business unit data. Use the PS Queries listed in the following subsections to ensure that the appropriate setup jobs have been executed, the necessary data resides in your tables, and there is no corrupt data.

Source Business Unit Data

Run the queries listed below to ensure your source business unit data is present in EPM. All business units from each source should be present. If any are missing, check the ETL jobs and run again. If extraneous business units are present (perhaps you imported the wrong data) then you will have to truncate the PF_SRC_BU_NAMES table manually.

Source Set Control Data

Run the query DMRP_3_4_SRC_SETCNTRL to ensure your source set control data is present in EPM. Set control information from all source systems should be present in PF_SRC_SETCNTRL and the query provides a count of the number of rows of set control information for each source that you have extracted into the system.

The number of rows returned for this query vary depending on the number of setID based and set control tables in your source. You can determine how many rows should be present in the query results by multiplying the number of setID-based tables in each source by the number of set controls that you defined in that source. The number of set controls for a given source is equal to the number of business units in that source plus the number of setIDs in that source.

For example, if you have an FSCM source with 30 business units and one setID, you should expect approximately 129,363 rows [(30 BUs + 1 setID) * (4,173) = 129,363]. If you have more than one pillar then repeat this calculation for each pillar and add up the totals. Your estimate should come very close to the results of the query, with a 10-15 percent deviation at most. If the results are significantly different, check the ETL process for errors.

The following table is provided for you to estimate the number of rows in PF_SRC_SETCNTRL.

Corrupt Source Data

Occasionally corrupt data can make its way into your source system (for example, someone enters data in the back-end rather than the using PIA pages, which control data quality). Corrupt data can affect your business units and set controls, and should not be allowed to enter EPM. As such, the following queries are provided to capture some business unit and set control error conditions:

Verifying Source Blueprints

Source blueprints are vital in determining warehouse lineage and are delivered as system (SYS) data in EPM. Use the DMRP_2_2_BLUEPR_DFN query to confirm that the blueprints are present in the EPM system and populated with the correct data.

The table, PF_BLUEPR_DFN, should contain rows for all supported source systems. You should have the following number of rows for each source system:

• CRM90: 50

• FSCM90: 174

• HCM90: 69

• NONSETID: 1

Note. These numbers may change slightly due to updated bundle fixes.

Establishing Default Set IDs, TableSets, and Warehouse Lineage

Before you can create your warehouse business units, you must define setIDs and warehouse lineage. This process identifies the source and warehouse tablesets, and the relationships (lineage) that exist between the two.

Pages Used to Define Default Set IDs, Warehouse Sources, and Lineage

Creating Default SetIDs

Access the TableSet Control page (PeopleTools, Utilities, Administration, TableSetIDs, TableSet ID).

Use this page to define a default setID for each source that you wish to bring into the warehouse. You should use a different default setID for each source to keep tablesets from all sources separate—unless you have a good reason why data from different tablesets should merge.

This page is discussed in detail in the PeopleTools PeopleBooks.

See PeopleSoft PeopleTools PeopleBook: PeopleSoft Application Designer Developer's Guide

Review or Update Source Blueprints

Access the Source Blueprint page (EPM Foundation, EPM Setup, Warehouse Sources and Bus. Units, View Source Blueprint, Source Blueprint).

Note. Before you access this page, run the ETL job that populates the PS_PF_SRC_SETCNTRL table. Otherwise, the page cannot display all the source blueprint details correctly.

Source Table	Displays the source table associated with the selected PeopleSoft source. If you have customized your ETL jobs or added rows or columns to your tables, you may need to modify this field.
Staging Table	Displays the OWS staging table associated with the preceding source table. If you have customized your ETL jobs or added rows or columns to your tables, you may need to modify this field.
Warehouse Table	Displays the warehouse table (OWE or MDW) associated with the preceding OWS staging table. If you have customized your ETL jobs or added rows or columns to your tables, you may need to modify this field.

Defining Warehouse Lineage

Access the Warehouse Lineage page (EPM Foundation, EPM Setup, Warehouse Sources and Bus. Units, Warehouse Lineage).

Use the Source Blueprint field to associate a blueprint with each PeopleSoft source you are using with EPM. This process define the lineage between source and warehouse tables for each PeopleSoft source.

Note. The blueprints you select for the Source Blueprint field are compatible with all supported source releases, regardless of the release number associated with the blueprint.

Establishing Warehouse Business Units Using the Business Unit Creation Wizard

The Business Unit Creation Wizard automates the creation of warehouse business units. Prior to running the Business Unit Creation Wizard, you must run the ETL jobs that populate the input tables used by the wizard. These jobs are collectively described as dimension mapper ETL setup jobs.

Pages Used to Run the Business Unit Creation Wizard

Accessing the Business Unit Creation Wizard

Access the Business Unit Creation Wizard - Start page (EPM Foundation, EPM Setup, Warehouse Sources and Bus. Units, Business Unit Wizard, Business Unit Creation Wizard).

Use this page to access and start the Business Unit Creation Wizard.

Reviewing Incoming Set Controls, Business Unit Wizard Step 1

Access the Business Unit Creation Wizard - Review Set Controls from Sources page (Click Start on the Business Unit Creation Wizard - Start page.).

This page enables you to review the set controls from all of your sources. Set control includes both source setIDs and source business units. Review this list to make sure that you see the business units and setIDs that you expect from each source. If business units are missing, you may not have run the setup ETL jobs properly. Check the ETL error logs and the Business Unit Wizard input tables mentioned above for potential problems. Assuming that you see the business units and setIDs you expect, click Next to proceed to the Business Unit Creation Wizard - Address Set Control Collisions page.

Addressing Collisions Between Set Controls, Business Unit Wizard Step 2

Access the Business Unit Creation Wizard - Address Set Control Collisions page (Click Next on the Business Unit Creation Wizard - Review Set Controls from Source page.).

This page enables you to review name collisions between and among incoming and existing warehouse set controls. You should resolve the name conflicts by renaming the incoming set control unless you have a good reason to merge them. If you are adding a new database to an existing warehouse installation, this page gives you the opportunity to inspect name collisions between new, incoming set controls and those already in the warehouse.

You can allow colliding set controls to merge with existing set controls. However, note that an existing set control has setID assignments on warehouse record groups at the time of its creation, and these assignments may not be consistent with those of the incoming set control. This increases the chances for setID conflicts. The Business Unit Creation Wizard can fix these problems by reassigning warehouse setIDs, but if data already exists in the warehouse, then some dimensions may have to be reloaded. It is preferable to rename conflicting set controls to avoid these problems. Once you have renamed conflicting set controls, click Next to proceed to the Business Unit Creation Wizard - Validate Mapping page. It may take some time to proceed to the next page as the system processes all the inputs and validates the mapping configuration.

Validating the Mapping, Business Unit Wizard Step 3

Access the Business Unit Creation Wizard - Validate Mapping-Conflicts page (Click Next on the Business Unit Creation Wizard - Address Set Control Collisions page.).

This page displays a report based on your source to warehouse set control mappings and the analysis of setID assignments for all incoming set controls. More specifically, the report displays any instance of setID splitting (one-to-many SetID mappings) and total numbers of setIDs created and merged. If all of the set controls have been made unique as suggested, the potential for conflicts should be minimized.

If conflicts exist, you have two choices:

• Let the system correct the conflicts: Click the Accept Proposed SetIDs and Save button to automatically reassign the setIDs used by warehouse business units on various warehouse record groups in order to make the configuration valid. The system will attempt to eliminate conflicts by reducing the number of warehouse setIDs in order to eliminate one-to-many mappings.

• Modify business unit mapping or setID assignments manually: You can analyze the report to where setID splitting is occurring, and remove the conflicts by either remapping business units or choosing different SetID assignments for warehouse business units.

Regardless of how you fix the problem, if conflicts are detected, you must return to the business unit mapping page and validate the configuration again, to make sure that all conflicts have been removed. Continue to iterate the validation process until you see no more conflicts.

See Defining Collision Mappings (Optional).

Once any conflicts are resolved, the process of creating warehouse business units and assigning setIDs is complete. You only need to inspect the properties of the new warehouse and general ledger business units.

See Reviewing Warehouse and General Ledger Business Unit Creation.

Establishing Warehouse Business Units Manually

You can manually create warehouse business units by defining warehouse business units, assigning warehouse setIDs to warehouse business units, mapping source business units to warehouse business units, and then validating those mappings.

Pages Used to Create Warehouse Business Units Manually

Defining Warehouse Business Units

Access the Warehouse Business Unit page (EPM Foundation, Business Metadata, Business Framework, Warehouse Business Units, Warehouse Business Unit).

Base Currency	Select the base, or primary, currency for the selected warehouse business unit. A warehouse business unit can have only one base currency. This is usually the local currency for the organization, but accounting rules or other circumstances might require a different base currency.
Rate Type	Select a rate type for the selected warehouse business unit.
Create GLBU with this ID	Select this check box to create a general ledger business unit with the same ID as the selected warehouse business unit.
Last Batch Number	enter the last batch number that was assigned. This number automatically increments as you run batches of transactions and you should not normally need to edit it. For example, you might want to enter a batch number when you install the system for the first time; however, you only need to reset it to reuse or skip batch numbers.

Default Properties

Business Unit Type

Mapping Source to Warehouse Set Controls

Access the Business Unit Mapping page (EPM Foundation, EPM Setup, Warehouse Sources and Bus. Units, Business Unit Mapping).

Validating Your Business Unit Mappings

Access the Validate Mapping - Conflicts page (Click Validate on the Business Unit Mapping page).

This page displays a report based on your source to warehouse set control mappings and the analysis of setID assignments for all incoming set controls. More specifically, the report displays any instance of setID splitting (one-to-many SetID mappings) and total numbers of setIDs created and merged. If all of the set controls have been made unique as suggested, the potential for conflicts should be minimized.

If conflicts exist, you have two choices:

• Let the system correct the conflicts: Click the Accept Proposed SetIDs button to automatically reassign the setIDs used by warehouse business units on various warehouse record groups in order to make the configuration valid. The system will attempt to eliminate conflicts by reducing the number of warehouse setIDs in order to eliminate one-to-many mappings.

• Modify business unit mapping or setID assignments manually: You can analyze the report to where setID splitting is occurring, and remove the conflicts by either remapping business units or choosing different SetID assignments for warehouse business units. Click the Return to Business
  Unit Mapper button to return to the Business Unit Mapping page.

Regardless of how you fix the problem, if conflicts are detected, you must return to the Business Unit Mapping page and validate the configuration again, to make sure that all conflicts have been removed. Continue to iterate the validation process until you see no more conflicts.

See Defining Collision Mappings (Optional).

Once any conflicts are resolved, the process of creating warehouse business units and assigning setIDs is complete. You only need to inspect the properties of the new warehouse and general ledger business units.

See Reviewing Warehouse and General Ledger Business Unit Creation.

Reviewing Warehouse and General Ledger Business Unit Creation

After you complete your warehouse business unit creation, you should review your warehouse business units and general ledger business units to ensure certain properties (such as base currency) meet your requirements.

If you created your warehouse business units automatically using the Business Unit Creation Wizard, some of your warehouse business units may have been created with a base currency or rate type that differs from the defaults that you defined for your source. If this is the case, you need to change these settings for the appropriate business units. In addition, the wizard does not associate calendars to business units. You must do this manually for all your warehouse business units.

Regardless of whether you used the Business Unit Creation Wizard to create your warehouse business units or did so manually, you should verify the properties of your general ledger business units (GLBU). PeopleSoft general ledger business units are extracted from your source system and populated in the EPM database using ETL jobs.

Pages Used to Review Your Warehouse and General Ledger Business Units

Reviewing Your Warehouse Business Units (Business Unit Creation Wizard Only)

Access the Warehouse Business Unit page (EPM Foundation, Business Metadata, Business Framework, Warehouse Business Units, Warehouse Business Unit).

If you created your warehouse business units automatically using the Business Unit Creation Wizard, review the Base Currency and Rate Type properties to ensure that they match those defined in your source system. Also, define the calendar properties for the warehouse business unit.

Reviewing Your General Ledger Business Units

Access the General Ledger page (EPM Foundation, Business Metadata, Business Framework, General Ledger Business Units, General Ledger).

Review the Base Currency and PF Business Unit properties to ensure that they match the properties defined in your source system.

Defining Collision Mappings (Optional)

When you validate your source to warehouse set control mappings, you can choose to allow two tablesets merge in the warehouse. However, if two tablesets merge it is possible for collisions to occur between business keys (such as supplier ID). If such collisions do occur, you must create a collision map to resolve the collision.

Pages Used to Define Collision Maps

Defining Collision Maps

Access the Collision Map Definition page (EPM Foundation, EPM Setup, ETL Map Parameters, Collision Map Definition).

Map Type	Select a collision map type to resolve any business ID conflict resulting from merging tablesets. You can select: First In Wins: The first instance of a business ID is loaded into a tableset and subsequent instances of the same ID from different tablesets are ignored. Error Out Duplicates: The first instance of a business ID is loaded into a tableset, but subsequent instances of the same ID from different tablesets are sent to an error table where they can be inspected and remapped. You can examine these errors by accessing the Collision Map Error Reports page. If you select this option, the Warehouse Record and Dimension ID fields appear for editing. Use Mapping Table: Each incoming business ID is checked against a mapping table. If an entry does not exist in the mapping table, the row errors out.
Dimension Name	Select the target dimension that contains the merging tablesets. The list is drawn from the set of dimensions defined in the Define Dimension page.
Mapping Table	Select a mapping table that stores resolved conflicts to verify future incoming business IDs. You can use the default mapping table (PF_DIMN_MAP) or create your own mapping table in Application Designer.
Warehouse Record	Select the OWE or MDW table that associates with the dimension selected for the Dimension Name field. This table is used for prompting in the Error-Out Duplicates map type.
Dimension ID Field	Select the ID column for the table you specified in the Warehouse Record field.
Description Fieldname	Select the column that stores the description for the table you specified in the Warehouse Record field.

Reviewing the Error Out Duplicates Collision Map Error Report

Access the Collision Map Error Reports page (EPM Foundation, EPM Setup, ETL Map Parameters, Collision Map Error Reports, Collision Map Error Report).

This page displays the results of the error out duplicates collision map, where the first instance of a business ID is loaded into the relevant tableset and the subsequent instances of the same ID from different tablesets are sent to this page for review. For each row of data you can chose to:

• Accept: The mapping between the colliding business IDs is preserved as displayed.

• Create: You can create a new business ID that can be loaded into the applicable tableset.

• Defer: You can defer action to a later time.

• Select: You can select an existing member of the tableset as a mapping for this incoming business ID.

If you select Create or Select, the Target Business ID field becomes available for editing and you can create or select another business ID to load into the applicable tableset.