Bookshelf v10.1.3.2: Adding Dimensions to Existing Facts

This section covers adding a dimension (preexisting or custom) to an existing fact. It assumes you have already built the required process to populate this dimension.

This process involves extending both the fact staging table and the fact data warehouse table to include the new column. In Informatica, remember to define the tables using the Oracle database type. The staging table should be defined as a varchar2(80) field and named with in _ID suffix. The data warehouse table column should be defined as an integer and named with a _WID suffix.

The SDE fact mapping must be modified to pass through the unique identifier of the dimension key. This assumes that there is some relationship between the base table and this unique identifier. It may already be stored in the base table or stored by joining to a related table. Depending on the source system, this identifier may be based on a single column or derived from multiple columns. Table 35 depicts various formats used to derive the INTEGRATION_ID, which is used to identify a dimension key. The INTEGRATION_ID value should be passed to the fact staging table.

Table 35. Formats to Derive INTEGRATION_ID
Dimension	Foreign Key	When Source is Oracle Application	When Source is Siebel Application
W_AP_TERMS_D		TO_CHAR(TERM_ID)	Not applicable
W_BUSN_LOCATION_D	ASSET_LOC_WID	ASSET_LOC~' \|\| LOCATION_ID	Not applicable
W_BUSN_LOCATION_D	EMP_LOC_WID	EMP_LOC~' \|\| LOCATION_ID	Not applicable
W_BUSN_LOCATION_D	INVENTORY_LOC_WID	STORAGE_LOC' \|\| '~' \|\| ORGANIZATION_ID \|\| '~' \|\| SUBINVENTORY_CODE \|\| '~' \|\| INVENTORY_LOCATION_ID	Not applicable
W_BUSN_LOCATION_D	PLANT_LOC_WID	'PLANT' \|\| '~' \|\| TO_CHAR(ORGANIZATION_ID)	Not applicable
W_BUSN_LOCATION_D	RECEIVING_LOC_WID	'RECIPIENT_LOC' \|\| '~' \|\| TO_CHAR(LOCATION_ID)	Not applicable
W_BUSN_LOCATION_D	STORAGE_LOC_WID	'STORAGE_LOC' \|\| '~' \|\| ORGANIZATION_ID \|\| '~' \|\| SECONDARY_INVENTORY_NAME \|\| '~'	Not applicable
W_CUSTOMER_FIN_ PROFL_D	CUSTOMER_FIN_PROFL_WID	P'\|\|'~'\|\|TO_CHAR(CUSTOMER_ID) \|\|'~' \|\| TO_CHAR(SITE_USE_ID) \|\|'~' \|\| CURRENCY_CODE - CUSTOMER_ID is CUST_ACCOUNT_ID from HZ_CUST_ACCOUNTS and CURRENCY_CODE is from HZ_CUST_PROF_CLASS_AMTS	Not applicable
W_CUSTOMER_LOC_D		To get the customer location key, look up W_CUSTOMER_LOC_USE_D as below	Not applicable
W_CUSTOMER_LOC_ USE_D		TO_CHAR(SITE_USE_ID) - Get Site Use Id from HZ_CUST_ACCOUNT_ROLES	Not applicable
W_FREIGHT_TERMS_D		LOOKUP_CODE	Not applicable
W_GL_ACCOUNT_D		to_char(ccid)	Not applicable
W_INT_ORG_D	COMPANY_ORG_KEY	COMPANY'\|\|'~'\|\|TO_CHAR(SET_OF_BOOKS_ID)	S_ORG_EXT.ROW_ID
W_INT_ORG_D	*_ORG_KEY	Remove any prefixes and use TO_CHAR()	S_ORG_EXT.ROW_ID
W_ORG_D	CUSTOMER_WID	TO_CHAR(CUSTOMER_ID) - CUSTOMER_ID is CUST_ACCOUNT_ID from HZ_CUST_ACCOUNTS	UNION OF S_ORG_EXT AND S_CONTACT. When source is S_ORG_EXT, ROW_ID is passed. When source is S_CONTACT, use ''C-'\|\|ROW_ID - the ROW_ID is the ROW_ID from the S_PERSON (not from S_ORG_EXT). This is the new value passed to lookup the Contact Customers in W_ORG_D for resolving the ACCOUNT_WID (read as CUSTOMER_WID)
W_PAYMENT_METHOD_D		LOOKUP_CODE	Not applicable
W_PAYMENT_METHOD_D		TO_CHAR(TERM_ID)	Not applicable
W_PERSON_D	CUST_CONTCT_WID	TO_CHAR(PARTY_ID) - PARTY_ID from HZ_PARTY_RELATIONS	S_CONTACT.ROW_ID
W_PRODUCT_D	PRODUCT_WID	TO_CHAR(INVENTORY_ITEM_ID)	S_PROD_INT.ROW_ID
W_SALES_PRODUCT_D		TO_CHAR(INVENTORY_ITEM_ID) \|\|'~'\|\|TO_CHAR(ORGANIZATION_ID)	Not applicable

If you are adding an existing dimension, the SIL mapping should be extended to include the preexisting reusable Lookup transformation to that dimension. Pass the dimension's INTEGRATION_ID through the mapping along the path identified by the X_CUSTOM column and connect it to the Lookup after the Filter transformation. Also, connect the DATASOURCE_NUM_ID to the Lookup. If the dimension is a slowly changing dimension, the fact table's standard or 'canonical' date should be passed to the lookup as well, even if the dimension has not been enabled to capture Type II changes.

Remember to connect the ROW_WID of the Lookup to the X_CUSTOM transformation and include logic to default this value to 0 if no record is returned from the Lookup. Pass this column on to the Update strategy, and then on to the target.

Update the DAC to include the foreign key to this dimension in the fact table's definition. You should reassemble the subject Area and rebuild the execution plan to ensure that the DAC populates this dimension table before this fact table starts to load.