1. User Guide
  2. Dimension Loading Process
  3. Tables Used by the SCD Component
  4. SYS_STG_JOIN_MASTER

5.5.2 SYS_STG_JOIN_MASTER

The solution installer will populate this table for the seeded dimensions. Table for Seeded Dimension:

Table 5-6 Rows populated for the seeded dimensions in the SYS_STG_JOIN_MASTER table

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(30) NOT NULL Type of column. The possible values are given in the following section.
STG_COL_NM VARCHAR2(60) NULL Name of the column in the Staging Table.
SCD_TYP_ID NUMBER(3) NULL SCD type for the column.
PRTY_LOOK? UP_REQD_FLG CHAR(1) NULL

Column to determine whether Lookup is required for Priority of Source against the Source Key

Column or not.
COL_DATATYPE VARCHAR2(15) NULL The list of possible values are VARCHAR, DATE, NUMBER based on the underlying column datatype.
COL_FORMAT VARCHAR2(15) NULL

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

  • PK – Primary Dimension Value (may be multiple for a given ?Mapping Reference Number")
  • SK – Surrogate Key
  • DA – Dimensional Attribute (may be 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 / Time
  • LUB – Last Updated By
  • NN – Not Null

Sample Data: This is the row put in by the solution installer for the Line of Business dimension. Sample Data:

Table 5-7 Sample data in the rows populated for the Line of Business dimension in the SYS_STG_JOIN_MASTER table

Parameter Value
MAP_REF_NUM 6
COL_NM V_LOB_CODE
COL_TYP PK
STG_COL_NM V_LOB_CODE
SCD_TYP_ID
PRTY_LOOKUP_REQD_FLG N
COL_DATATYPE VARCHAR
COL_FORMAT 61

Note:

For any new dimension added, the column details will have to be inserted to this table manually.

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

These views come as part of install for the dimensions seeded with the application.

Note:

For any new dimension added, a view will have to be created similar to DIM_BANDS_V.

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

Note:

Example:

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