Oracle^® Business Intelligence Data Warehouse Administration Console Guide > Customizing, Designing, Executing and Monitoring ETL Processes >

Adding a New Table and Columns to the Data Warehouse

As shown in Figure 9, there are two alternative process flows for adding a new object to the data warehouse. You can enter the table and column definitions in the DAC and then use the DAC's Data Warehouse Configurator to create the table and columns in the data warehouse database; for this method, follow the procedure, To add a new table and columns to the data warehouse using the DAC's Data Warehouse Configurator.

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 the DAC; for this method, follow the procedure, To add a new table and columns using the DAC's Import command.

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. In the Menu bar, click Views > Design > 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, see About the DAC Tables 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 or fact table.

5. Create the new tables and columns in the data warehouse database.
   1. Choose Tools > ETL Management > 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. In the Menu bar, click Views > Design > Tables.
4. Import the new table definition.
   1. Right-click and select Import from Database > Import Database Tables.
   2. In the Import Tables dialog box, select DataWarehouse.
   3. Optionally, enter filter criteria to identity the table name you entered in Step 1.

      See DAC Query Commands and Operators 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 > Import Database Columns.
   3. In the Importing Columns... dialog box, select Selected Record Only, and then click OK.
   4. In the Import Table Columns dialog box, 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 doesn't

   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.