1. User Guide
  2. Dimension Population
  3. Dimension Table Population
  4. Tables Used by the SCD Component
  5. SYS_STG_JOIN_MASTER

4.3.3.3 SYS_STG_JOIN_MASTER

The ALMBI Application Installer populates this table for the Seeded Dimensions.

Table 4-8 Seeded Dimensions

Column Name Data Type Column Description
MAP_REF_NUM NUMBER (3) NOT NULL The Mapping Reference Number for this unique mapping of a Source to a Dimension Table
COL_NM VARCHAR2(30) NOT NULL Name of the column in the Dimension Table
COL_TYP VARCHAR2(20) NOT NULL Type of column. The possible values are given below
STG_COL_NM VARCHAR2(30) NOT NULL Name of the column in the Staging Table
SCD_TYP_ID NUMBER (3) NOT NULL SCD type for the column
PRTY_LOOKUP_REQD CHAR(1) NOT NULL Column to determine whether Lookup is required for Priority of Source against the Source Key Column or not
COL_DATATYPE VARCHAR2(15) NULL Column Data Type
COL_FORMAT VARCHAR2(15) NULL Column Format

The possible values for column type (the column COL_TYPE) in SYS_STG_JOIN_MASTER are:

  • PK: Primary Dimension Value (maybe multiple for a given "Mapping Reference Number")
  • SK: Surrogate Key
  • DA: Dimensional Attribute (maybe multiple for a given "Mapping Reference Number")
  • SD: Start Date
  • ED: End Date
  • LRI: Latest Record Indicator (Current Flag)
  • CSK: Current Surrogate Key
  • PSK: Previous Surrogate Key
  • SS: Source Key
  • LUD: Last Updated Date or Time
  • LUB: Last Updated By

Example: The following data is inserted by the application installer for the Product Dimension.

Table 4-9 Product Dimensions

Column Name Data Type
MAP_REF_NUM 6
COL_NM V_PRODUCT_NAME
COL_TYP DA
STG_COL_NM V_PRODUCT_NAME
SCD_TYP_ID 2
PRTY_LOOKUP_REQD_FLG N
COL_DATATYPE VARCHAR
COL_FORMAT

No changes are required to this table if the standard key dimensions are being used within ALMBI. If any new dimensions have been added (for example, ALM_COA_ID), a row must be inserted to this table manually.

  • DIM_<dimensionname>_V: The database view which SCD uses as the source.

    Example:

    Dim_products_V

    These views come as part of the application installation.

    For any new dimension added, a View will have to be created similar to DIM_PRODUCTS_V.

  • DIM_<dimensionname> – Output table to which SCD writes the dimension data. A sequence should be added for every user-defined dimension.

    Example:

    create sequence SEQ_DIM_<DIM> minvalue 1 maxvalue 999999999999999999999999999 increment by 1.