Oracle® Business Intelligence Applications Installation and Configuration Guide > Customizing the Oracle Business Analytics Warehouse > Other Types of Customizations Requiring Special Handling >
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
|
|
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.
|