1. User Guide
  2. Data Loaders
  3. Dimension Loaders
  4. Dimension Load Procedure

5.1.4 Dimension Load Procedure

This procedure performs the following functions:

  • Gets the list of source and target dimension tables. The dimension tables for a given dimension are stored in REV_DIMENSIONS_B table. The stage tables for a given dimension are stored in FSI_DIM_LOADER_SETUP_DETAILS.
  • The parameter Synchronize Flag can be used to completely synchronize data between the stage and the dimension tables. If the flag = 'Y' members from the dimension table which are not present in the staging table will be deleted. If the flag is 'N' the program merges the data between the staging and dimension table.
  • The Loader program validates the members/attributes before loading them.

The program validates the number of records in the base members table - STG_<DIMENSION>_B_INTF and translation members table - STG_<DIMENSION>_TL_INTF. The program exits if the number of records does not match

In case values for mandatory attributes are not provided in the staging tables, the loader program populates the default value (as specified in the attribute maintenance screens within AMHM of OFSAAI) in the dimension table.

The program validates for data types of attribute value. For example an attribute that is configured as 'NUMERIC' cannot have non-numeric values.

Dimension Loader validates the attribute against their corresponding dimension table. If any of the attributes is not present, then an error message will be logged in FSI_MESSAGE_LOG table.

Dimension Loader will check the number of records in Dim_<Dim_Name>_B and Dim_<Dim_Name>_TL for the language. In case any mismatch is found, then an error will be logged and loading will be aborted.

  • If all the member level validations are successful the loader program inserts the data from the staging tables to the dimension tables

Note:

In release 6.0 (7.3) The stage dimension loader program is modified to move alphanumeric code values from STG_< DIMENSION >_B_INTF.V_< DIM >_CODE to DIM_< DIM >_B.< DIM >_CODE column. Previously, DIM_< DIM>_B.< DIM >_CODE column was populated using the fn_updateDimensionCode procedure from the code attributes. With this enhancement users can directly load alphanumeric values.

The fn_updateDimensionCode procedure is still available for users who do not want make any changes to their ETL procedures for populating the dimension staging tables (for example, STG_< DIMENSION >_B_INTF, STG_< DIMENSION>_ATTR_INTF).

  • After this, the loader program loads hierarchy data from staging into hierarchy tables.
  • In case of hierarchy data the loader program validates if the members used in the hierarchy are present in the STG_<DIMENSION>_B_INTF table.
  • The program validates if the hierarchy contains multiple root nodes and logs error messages accordingly, as multiple root nodes are not supported.
  • Dimension Loader will check special characters in Hierarchy. Hierarchy name with special characters will not be loaded.
  • Following are the list of special characters which are not allowed in Hierarchy Name:

    ^&\'

After execution of the dimension loader, the user must execute the reverse population procedure to populate OFSA legacy dimension and hierarchy tables.