5.2.1 Simple Dimension Stage Table

You can create stage tables for the required simple dimensions by using the following template:

Table 5-2 STG_<DIM>_MASTER

COLUMN_NAME	DATA TYPE	PRIMARY KEY	NULLABLE
v_< DIM>_display_code	Varchar2(10)	Y	N
d_Mis_date	Date	Y	N
v_Language	Varchar2(10)	Y	N
v_< DIM>_NAME	Varchar2(40)		N
v_Description	Varchar2(255)		N
v_Created_by	Varchar2(30)		Y
v_Modified_by	Varchar2(30)		Y

Here is a sample structure:

Table 5-3 STG_ACCOUNT_OFFICER_MASTER

COLUMN_NAME	DATA TYPE	PRIMARY KEY	NULLABLE
v_acct_officer_display_code	Varchar2(10)	Y	N
d_Mis_date	Date	Y	N
v_Language	Varchar2(10)	Y	N
v_Name	Varchar2(40)		N
v_Description	Varchar2(255)		N
v_Created_by	Varchar2(30)		Y
v_Modified_by	Varchar2(30)		Y

Here are some examples:

• Example For FSI CD/MLS tables:

  CREATE TABLE <XXXXX>_FSI_<DIM>_CD -- ACME_FSI_ACCT_STATUS_CD (<DIM>_CD NUMBER(5) -- ACCT_STATUS_CD ,LEAF_ONLY_FLAG VARCHAR2(1) ,ENABLED_FLAG VARCHAR2(1) ,DEFINITION_LANGUAGE VARCHAR2(10) ,CREATED_BY VARCHAR2(30) ,CREATION_DATE DATE ,LAST_MODIFIED_BY VARCHAR2(30) ,LAST_MODIFIED_DATE DATE <dim>_display_CD VARCHAR2(10) );

• Example for FSI_<DIM>_MLS table:

  CREATE TABLE <XXXXX>_FSI_<DIM>_MLS -- ACME_FSI_ACCT_STATUS_CD (<DIM>_CD NUMBER(5) -- ACCT_STATUS_CD ,LANGUAGE VARCHAR2(10) ,<DIM> VARCHAR2(40) -- ACCT_STATUS ,DESCRIPTION VARCHAR2(255) ,CREATED_BY VARCHAR2(30) ,CREATION_DATE DATE ,LAST_MODIFIED_BY VARCHAR2(30) ,LAST_MODIFIED_DATE DATE );

Note:

FSI_<DIM>_CD and FSI_<DIM>_MLS should follow the same standards as mentioned above, else Loader will not work as expected.