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:
Run prerequisite setup jobs.
Create SetIDs in the warehouse.
Define source systems.
Establish warehouse lineage.
Create warehouse business units.
Assign warehouse SetIDs to warehouse business units on all warehouse record groups.
Map source business units to warehouse business units.
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 is discussed in the Specifying Your EPM Sources topic.
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.
Image: Input and output tables used by the Business Unit Creation Wizard
This diagram illustrates the inputs that the wizard uses, and the output tables that it populates.
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:
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.
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.
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.
Image: Modifying the Stage Constraint, 1 of 2
This example illustrates the Modifying the Stage Constraint, 1 of 2.
Image: Modifying the Stage Constraint, 2 of 2
This example illustrates the Modifying the Stage Constraint, 2 of 2.
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.
Image: Creating a WHERE filter in the selection-output of the source stage
This example illustrates the Creating a WHERE filter in the selection-output of the source stage.
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 documentation 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.