Loading Data into the Multidimensional Warehouse

This chapter provides an overview of moving data into the Multidimensional Warehouse (MDW) and data exceptions and discusses the steps to move data into the MDW.

See Also

Loading Data Into EPM Foundation

EPM 8.9 Installation Guide: Configuring Ascential DataStage for PeopleSoft Enterprise Performance Management Applications or Data Marts

Understanding Moving Data into the MDW

This section provides an overview of moving data into the MDW.

Moving Data into the MDW

You use extract, transform, and load (ETL) to move data into the MDW. You can bring data into the MDW by:

• Loading data from the Operational Warehouse Staging (OWS) into the MDW. This is the most common path.

• Bringing enriched data from the Operational Warehouse Enriched (OWE) into the MDW.

  This is the path for enriched data that is required for dimensional analysis. It is warehouse business unit-based. Reporting for Profitability and Global Consolidations in the Financials Warehouse and external survey data in the HCM Warehouse use this path.

• Bringing data directly from a source system to the MDW.

  The data never resides in the Operational Warehouse. This path applies to large amounts of data, specifically CRM Warehouse Online Marketing.

Understanding Data Exceptions

This section provides an overview of data exceptions.

Data Exceptions

Normally, fact rows coming from the source system contain a valid reference to an existing row in the dimension table, in the form of a foreign key using a business key field. However, data exceptions can exist, for example:

Dimension value not specified; that is, a fact row does not contain the dimension key. To accommodate this situation, in the MDW each dimension has a row for “Value Not Specified” with a predefined key value of zero for a missing numeric value and a hyphen (-) for a missing character value. The description for that dimension contains “Value Not Specified.”

Performing Steps to Move Data into the MDW

This section discusses the steps to move data into the MDW.

Steps to Move Data into the MDW

Regardless of the location of the data that you are moving into the MDW, you use Ascential ETL jobs to move source data into the MDW.

See MDW Layer.

1. Complete the setup steps specific to the functional warehouse you are installing. Only the Financials Warehouse and CRM Warehouse have this requirement.

   See Defining ETL Parameters in the Financials Warehouse, Defining ETL Parameters in the Financials Warehouse.

2. Identify the tables that contain the jobs for the mart that you are installing. These tables are listed on Customer Connection.

3. Identify these tables in the .dsx files on the installation CD. As the .dsx file may contain additional jobs that you do not wish to use, you can:

   • Import all of the files in the .dsx file and delete the ones you do not plan to run.

   • Import all of the files in the .dsx file and set to the jobs that you do not plan to run to Inactive.

   • Using Ascential Job Sequencer, run only the jobs that you need to run.

    

   Be aware that Financials Warehouse profitability and Global Consolidations data and HCM Warehouse external survey data are loaded into the MDW from the OWE.

4. Create the project.

5. Import the .dsx file.

6. Set up parameters in DSPARAMS file in DataStage Administrator.

7. Run the appropriate jobs. You must run the jobs in this order:

   • Complete the setup requirements for populating the OWS.

     See ETL Setups.

   • Populate the OWS from source data.

   • Complete the setup requirements for populating the MDW.

   • (Optional) Complete setup for language swap, if you are running the Language Swap utility.

   • (Optional) Run the Language Swap utility.

     See Setting Up Multilanguage Processing.

   • (Optional) Complete setup for populating the OWE, if you are populating the OWE.

   • (Optional) Populate the OWE, if required.

   • Populate the MDW in this order:

     Load common dimensions.

     Load global (intra-warehouse) shared dimensions. (Before loading these, you must determine which shared dimensions your product requires).

     Load local (inter-warehouse) shared dimensions. (Before loading, you must determine which shared dimensions are required for your product).

     Load function-specific (non-shared) dimensions.

     (Optional) Complete setup for tree and recursive hierarchy processing, if you are running the Tree and Recursive Hierarchy process. You run this step only if your data includes trees or recursive hierarchies.

     See Processing Trees and Recursive Hierarchies.

     (Optional) Run the Tree and Recursive Hierarchy process.

     (Optional) Run the DMS script if you have a fact row that does not have a dimension key. This DMS script inserts a row in dimension tables that says “Dimension value not specified.”

     Load facts.

     (Optional) Complete setup for currency conversion processing, if you are running the Currency Conversion process. You run this step only if you need to convert currencies for transactions.

     (Optional) Run MDW Currency Conversion utility.

     See Setting Up and Running Currency Conversion for the Performance Management Warehouse.