DAC: Customizing the Data Warehouse

You can add tables, columns, and indexes to the data warehouse, and you can modify these existing objects. Customizing the data warehouse in this way requires using DAC and Informatica client tools. For more information about using Informatica client tools to customize the Oracle Business Analytics Warehouse, see the Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users.
Figure shows the major steps required for adding a new object to the data warehouse or modifying existing objects. As shown in Figure, you can begin the customization process by adding or modifying the new data warehouse object in DAC and then using the DAC's Data Warehouse Configurator to create or update the object in the data warehouse. Alternatively, you can add or modify the object directly in the data warehouse database and then use the DAC's Import from Database command to add the new object in DAC.

Figure 8-1 Process Flow to Add New Object to Data Warehouse

Adding a New Table and Columns to the Data Warehouse

As shown in Figure, there are two alternative process flows for adding a new object to the data warehouse. You can enter the table and column definitions in DAC and then use the DAC's Data Warehouse Configurator to create the table and columns in the data warehouse database.
Alternatively, you can add the new table and column definitions directly in the data warehouse database and then use the DAC's Import from Database command to add the new table and columns in DAC;
To add a new table and columns to the data warehouse using the DAC's Data Warehouse Configurator

1. In the DAC toolbar, select the appropriate source system container from the drop-down list in the toolbar.
   
2. From the Menu bar, select Views, then select Design, then select Tables.
   
3. Create the new table.
   
   1. In the Tables tab, click New.
      
   2. In the Edit child tab, enter the appropriate information about the table, and click Save.
      For a description of the fields in this tab
      
4. Add the columns for the new table.
   
   1. In the Columns child tab, click New.
      
   2. Enter the appropriate column information for each column you want to add to the table, and click Save.
      
   3. Enter the appropriate foreign key table and column information.
      
      Note:

      For performance purposes, it is recommended that you do not enter more than 254 columns to a dimension of fact table.
      
5. Create the new tables and columns in the data warehouse database.
   
   1. Select Tools, then select ETL Management, then select Configure.
      
   2. Select the appropriate Source and Target database platforms, and then click OK.
      
   3. In the Data Warehouse Configuration Wizard, select Create Data Warehouse Tables, and then click Next.
      
   4. Enter the required information, and then click Start.
      An informational message reports whether the process was successful. For information about the process, you can review the createwtables.log file in the OracleBI\DAC\log\config folder.
      

To add a new table and columns using the DAC's Import command

1. Add the new table and column definitions into the data warehouse database.
   
2. In the DAC toolbar, select the appropriate source system container from the drop-down list in the toolbar.
   
3. From the Menu bar, select Views, then select Design, then select Tables.
   
4. Import the new table definition.
   
   1. Right-click and select Import from Database, then select Import Database Tables.
      
   2. In the Import Tables dialog, select DataWarehouse.
      
   3. Optionally, enter filter criteria to identity the table name you entered in Step 1.
       for available filter commands and operators.
      
   4. Click Read Tables.
      
   5. In the list of tables displayed, select the Import check box for the tables you want to import.
      
   6. Click Import Tables.
      An informational message indicates whether the process was successful.
      
5. Import the new column definitions.
   
   1. In the Tables tab, query for the table you imported in Step 4.
      
   2. With the table highlighted, right-click and select Import from Database, then select Import Database Columns.
      
   3. In the Importing Columns... dialog, select Selected Record Only, and then click OK.
      
   4. In the Import Table Columns dialog, click Read Columns.
      The Changes column displays a description of column changes, which are explained below:
      

      Change	Explanation
      The object was added to the database.	The column is in the database but not the DAC repository. Importing it will add the column to the DAC repository.
      The object was added to the repository.	The column is in the DAC repository but not in the database. Importing it will delete it from the DAC repository.
      The object was modified.	The column definition in the database does not match the definition in the DAC repository.

   5. In the list of columns displayed, select the Import check box for the columns you want to import.
      
   6. Click Import Columns.
      An informational message indicates whether the process was successful.
      

Adding an Index to the Data Warehouse

Follow this procedure to add a new index to the data warehouse.
To add a new index to the data warehouse

1. Add the new index definition into the data warehouse database.
   
2. In the DAC toolbar, select the appropriate source system container from the drop-down list in the toolbar.
   
3. From the Menu bar, select Views, then select Design, then select Tables.
   
4. Query for the table for which you want to import index definitions.
   
5. Right-click and select Import from Database, then select Import Indices.
   
6. Choose to import indexes for a selected table or for all the records retrieved in your query, and click OK.
   
7. In the Import Indices dialog, select DataWarehouse from the Data Sources drop-down list.
   
8. Click Read Indices.
   
   1. In the list of indexes displayed, select the Import check box for the indexes you want to import.
      
   2. Click Import Indices.
      An informational message indicates whether the process was successful.