Oracle^® Business Intelligence Applications Installation and Configuration Guide > Customizing the Oracle Business Analytics Warehouse > Type II Customizations: Adding Additional Tables >

Adding a New Dimension in the Oracle Business Analytics Warehouse

Follow this procedure to add a new dimension in the Oracle Business Analytics Warehouse.

To add a new dimension and use it with an existing fact table

1. Create a DDL for the new dimension based on the standard structure (with appropriate system columns). Create a staging table for this dimension.
2. Register the new source table and its staging table (if it does not already exist) in the DAC repository and associate it with the appropriate database connection.
3. Create a new custom map SDE_XYZ to populate the dimension stage. Instead of the actual source table (for example S_ABC), use the view that will be generated by the change capture process (for example V_ABC) in the SQL so that it extracts only the incremental data. Use existing reference maps as examples of how to populate the system columns. Make sure you truncate the stage table in corresponding tasks.
4. Create a new custom map SIL_XYZ to populate the new dimension from the stage table. Use the above referenced map as example for how to populate the system columns.
5. Make the following changes to the fact-loading mapping to link the new dimension:

   Do one of the following:

   • Use the extension table's predefined fact table foreign keys that join to this new dimension. (In the above example, the extension table for W_REVN_F must be used to store the foreign key of this new dimension.)
   • Create a new extension table for the fact table with predefined structure to hold a new foreign key, if the foreign keys in existing the extension table have been exhausted.
   • Create new mappings (SDE and SIL) to populate the extension table columns of the fact extension table.
6. Register the new dimension table in the DAC and associate it with the appropriate database connection.

   If you are planning to build a new dimension incrementally, assign an image suffix to the source table.

7. Register the workflows as tasks in the DAC.
8. For SDE mapping of the dimension make sure you set the Build Image flag to True, and the Truncate Always option to True. And in the list of source tables, mark the primary/auxiliary source(s) of this dimension.
9. For SIL workflows of the dimension make sure you set only Truncate for Full Load option to True.
10. Make sure the target table of the SDE_XYZ is defined as source table for SIL_XYZ.
11. Make sure the target tables of the SIL_XYZ and SIL_RevenueFact (load base fact task) are defined as source tables for CustomSIL_RevenueFact.