5.1.8 Executing the Dimension Load Procedure using Master Table approach

FSI_DIM_LOADER_SETUP_DETAILS table should have a record for each dimension that has to be loaded. The table contains entries for key dimensions that are seeded with the application.
  1. The following columns must be populated for user-defined Dimensions.
    v_stg_member_column
    v_stg_member_name_col
    v_stg_member_desc_col
    V_STG_INTF_MEMBER_COLUMN column is available in the FSI_DIM_LOADER_SETUP_DETAILS table to avoid manual configuration for Legal Entity and Customer dimensions issues.
    fn_drmDataLoader refers to this column (V_STG_INTF_MEMBER_COLUMN) and fn_STGDimDataLoader refers to V_STG_MEMBER_COLUMN.
    Following values must be seeded in these columns for Legal entity and Customer dimensions.
    For other seeded dimensions the values in both columns will be the same.
  2. Additionally, the FSI_DIM_ATTRIBUTE_MAP table should be configured with column attribute mapping data. This table maps the columns from a given master table to attributes.
    N_DIMENSION_ID: This stores the Dimension ID
    V_STG_TABLE_NAME: This holds the source Stage Master table
    V_STG_COLUMN_NAME: This holds the column from the master table
    V_ATTRIBUTE_NAME: This holds the name of the attribute the column maps to
    V_UPDATE_B_CODE_FLAG: This column indicates if the attribute value can be used to update the code column in the DIM_<Dimension>_B table.

    Note:

    fn_STGDimDataLoader does not use FSI_DIM_ATTRIBUTE_MAP.V_UPDATE_B_CODE_FLAG
  3. You can execute this procedure either from SQL*Plus or from within a PL/SQL block or from the Batch Maintenance window within OFSAAI framework. To run the procedure from SQL*Plus, login to SQL*Plus as the Schema Owner.
    The function requires 5 parameters:
    Batch Run Identifier , As of Date, Dimension Identifier , MIS-Date Required Flag, Synchronize flag (Optional).
    The syntax for calling the procedure is:
    function fn_STGDimDataLoader(batch_run_id varchar2, as_of_date varchar2, pDimensionId varchar2, pMisDateReqFlag char default 'Y', pSynchFlag char default 'N')
    where
    BATCH_RUN_ID is any string to identify the executed batch.
    AS_OF_DATE in the format YYYYMMDD.
    pDIMENSIONID dimension id.
    pMisDateReqFlag is used to identify if AS-OF_DATE should be used in the where clause to filter the data.
    pSynchFlag is used to identify if a complete synchronization of data between staging and fusion table is required. The default value is 'Y'.
    For Example
    Declare num number;Begin num := fn_STGDimDataLoader ('INFODOM_20100405','20100405' ,1,'Y','Y' );End;
  4. To execute the procedure from OFSAAI Batch Maintenance, create a new Batch with the Task as TRANSFORM DATA.
  5. Specify the following parameters for the task:
    Datastore Type: Select appropriate datastore from list
    Datastore Name: Select appropriate name from the list
    IP address: Select the IP address from the list
    Rule Name: fn_STGDimDataLoader
    Parameter List: Dimension ID, Mis Date Required Flag , Synchronize Flag
    Customer may face a problem while loading customer dimension into AMHM using the Master table approach.
    Configuring the setup table for CUSTOMER dimension is pretty confusing while dealing with attributes like FIRST_NAME , MIDDLE_NAME and LAST_NAME.
    Most customers would like to see FIRST_NAME , MIDDLE_NAME and LAST_NAME forming the name of the member within the customer dimension.
    Currently the STG_DIMENSION_LOADER disallows concatenation of columns. Moreover the concatenation might not ensure unique values. As a solution to this problem we can work on the following options:
    • Approach 1
      1. Create a view on STG_CUSTOMER_MASTER table with FIRST_NAME, MIDDLE_NAME and LAST_NAME concatenated and identify this column as NAME.
      2. Configure the name column from the view in FSI_DIM_LOADER_SETUP_DETAILS
      3. Increase the size of DIM_CUSTOMER_TL.NAME column.
      4. Disable the unique index on DIM_CUSTOMER_TL.
      5. NAME or append Customer_code to the NAME column.
      6. The NAME column will be populated into the DIM_CUSTOMER_TL.NAME column.
    • Approach 2
      1. Populate customer_code into the DIM_CUSTOMER_TL.NAME column.