Adding a New Table and Columns to the Data Warehouse

Adding a New Table and Columns to the Data Warehouse

1)   As shown in Figure 8–1, there are two alternative process flows for adding a new object to the data warehouse.

2)   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; 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 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. From the Menu bar, select Views, then select Design, then select Tables.

3. Create the new table.

a. In the Tables tab, click New.

b. In the Edit child tab, enter the appropriate information about the table, and

click Save.

Adding a New Table and Columns to the Data Warehouse

8-4 Oracle Business Intelligence Data Warehouse Administration Console User’s Guide

For a description of the fields in this tab, see "Tables Tab".

4. Add the columns for the new table.

a. In the Columns child tab, click New.

b. Enter the appropriate column information for each column you want to add to

the table, and click Save.

c. Enter the appropriate foreign key table and column information.

5. Create the new tables and columns in the data warehouse database.

a. Select Tools, then select ETL Management, then select Configure.

b. Select the appropriate Source and Target database platforms, and then click

OK.

c. In the Data Warehouse Configuration Wizard, select Create Data Warehouse

Tables, and then click Next.

d. 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.

a. Right-click and select Import from Database, then select Import Database

Tables.

b. In the Import Tables dialog, select DataWarehouse.

c. 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.

d. Click Read Tables.

e. In the list of tables displayed, select the Import check box for the tables you

want to import.

f. Click Import Tables.

An informational message indicates whether the process was successful.

5. Import the new column definitions.

a. In the Tables tab, query for the table you imported in Step 4.

b. With the table highlighted, right-click and select Import from Database, then

select Import Database Columns.

Note: For performance purposes, it is recommended that you do not

enter more than 254 columns to a dimension of fact table.

Importing New Data Warehouse Objects into the Informatica Repository

Customizing DAC Objects and Designing Subject Areas 8-5

c. In the Importing Columns... dialog, select Selected Record Only, and then click

OK.

d. 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.

e. In the list of columns displayed, select the Import check box for the columns

you want to import.

f. Click Import Columns.

An informational message indicates whether the process was successful.