Prerequisites to Creating Warehouse Business Units

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 topic of this documentation. However, the ETL jobs that are specific to business unit data are discussed below.

See Understanding IBM WebSphere DataStage Setup and Configuration and Verifying ETL Components Have Imported Properly.

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.

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.

The following topics 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.

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:

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 sections to ensure that the appropriate setup jobs have been executed, the necessary data resides in your tables, and there is no corrupt data.

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