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

About Creating New Dimension or Fact Tables

If you are creating a new dimension or fact table, use the required system columns that are part of each of the data warehouse tables to maintain consistency and the ability to reference existing table structures. When you create a new table, you need to register the tables and indices in the DAC. You will also have to register in the DAC the new tasks for new Informatica workflows and then reassemble the appropriate subject area and rebuild the appropriate execution plan. For information about assembling subject areas and building execution plans, see the Oracle Business Intelligence Data Warehouse Administration Console Guide.

When you create a new extension table, use the same naming convention as is used in the Oracle Business Analytics Warehouse by adding the prefix WC to the table name, for example, WC_TABLENAME_DS. This will simplify future upgrades to the Oracle Business Analytics Warehouse.

NOTE:  If you are creating tables on a DB2-UDB database, make sure when you register the tables in the DAC the Not Logged Initially option is enabled.

Required Columns

For main and extension staging tables the following columns are required:

• INTEGRATION_ID. Stores the primary key or the unique identifier of a record as in the source table.
• DATASRC_NUM_ID. Stores the data source from which the data is extracted. For example, Siebel OLTP is 1. For data from any external data source assign a number greater than 1.

For dimension and fact tables, the required columns are the INTEGRATION_ID and DATASRC_NUM_ID columns as well as the following:

• ROW_WID. A sequence number generated during the ETL process, which is used as a unique identifier for the data warehouse.
• ETL_PROC_WID. Stores the ID of the ETL process information. The details of the ETL process are stored in the W_ETL_RUN_S table on the data warehouse side. This is also the Process ID on Current Run/Run History screen in the DAC.

About the Oracle Business Analytics Warehouse DATASRC_NUM_ID Column

All the tables in the Oracle Business Analytics Warehouse schema have DATASRC_NUM_ID as part of their unique user key. While the transactional application normally ensures that a primary key is unique, it is possible that a primary key is duplicated between transactional systems. To avoid problems when loading this data into the data warehouse, uniqueness is ensured by including the DATASOURCE_NUM_ID as part of the user key. This means that the rows can be loaded in the same data warehouse tables from different sources if this column is given a different value for each data source.

NOTE:  The DATASRC_NUM_ID is maintained by the DAC. Make sure that each source system has a unique value assigned. It is possible to have multiple instances of the same source system (for example, a U.S.-based and a European-based Siebel transactional database both loading into the same data warehouse). The two different transactional database systems should be assigned different DATASOURCE_NUM_ID values in the DAC. The DAC is predefined with one entry for Siebel and the DATASOURCE_NUM_ID is assigned the value of 1. If you are going to extract from additional Siebel transactional database systems and load the data into the same data warehouse, a different DATASOURCE_NUM_ID must be assigned to each Siebel transactional database system.