Generating DDL and Assigning Tablespaces to Tables and Indexes

Overview of Oracle Business Analytics Warehouse Tables

The Oracle Business Analytics Warehouse tables are automatically deployed during the installation process when the Business Analytics Applications Suite Repository Creation Utility (RCU) executes a shipped DDL script.

The RCU doesn't prompt for which tablespace to assign to the individual tables and related indexes nor does it provide a mechanism for you to alter the shipped DDL. To introduce changes to the Oracle Business Analytics Warehouse data model, you use Oracle Data Integrator (ODI) to generate a new DDL script.

You may want to regenerate the Oracle Business Analytics Warehouse DDL for these reasons:

You may want to modify the Oracle Business Analytics Warehouse, either by modifying existing tables or creating new ones.
You may want to have separate development environments. In this case you must use ODI to generate the DDL to be executed in these environments to deploy the Oracle Business Analytics Warehouse tables rather than run the RCU for each environment.
The default tablespace assigned to the Oracle Business Analytics Warehouse database user is used for all tables and indexes when those objects are created by the RCU. You may want to assign different tablespaces to the DW tables other than the default tablespace associated with the database user.
If a patch or upgrade is applied that alters an ODI data store or introduces a new ODI data store, the corresponding database table must be altered or created.

You can make changes directly in the database but these changes must be synchronized with the ODI Repository. Run the Reverse Knowledge Module (RKM) of Oracle BI Applications to bring in the changes into ODI. If this RKM doesn't run, ODI won't be aware of these changes, which can lead to problems when populating the affected warehouse table.

Generating the Oracle Business Analytics Warehouse DDL

Use this procedure to generate the Oracle Business Analytics Warehouse DDL.

Keep these items in mind when introducing changes in ODI:

Full mode generates a script that creates all tables and sequences, regardless of what may already be deployed in the database. Incremental mode compares the ODI and database definition of the tables and columns. Neither mode creates, modifies, or drops indexes.
Add or modify columns:
- Add column: ODI allows duplicate column names but you get an error when deploying the DDL to the database.
- Modify column:
  
  - Column renaming in ODI is treated as a drop of the existing column and a new column is added in the database. This means any existing data in that column is lost. The procedure works on column name. When generating the DDL, the procedure compares the database and ODI definitions of the columns. If a column has been renamed in ODI, that column won't be found in the database, so it's added.
  
  - Changes to datatype, scale, precision, and nullability are supported as ALTER statements
  
  - You should avoid altering preconfigured columns. They should introduce their own custom column if they need a change in functionality.
  
  - Existing columns can be changed to a larger size but shouldn't be reduced to a smaller size. Similarly, column data types can be changed to one that already accommodates existing data (CHAR to VARCHAR, DATE to DATETIME) but shouldn't be changed to less accommodating datatype (VARCHAR to NUMBER). ODI doesn't prevent users from making this kind of change, but when deploying the DDL, users will likely encounter issues.
- Delete column: You shouldn't delete preconfigured columns. You should only delete custom columns. If a column is deleted from the ODI Repository, it is dropped from the table using an ALTER statement. Identify the columns that exist in the database but don't exist in ODI. (Thus, renaming the column in ODI results in a drop and add statements being generated).
Add or modify indexes:
- Add index: ODI allows duplicate index names and duplicate index definitions, but you get an error when deploying the DDL on the database.
- Modify index:
  
  - Indexes are only dropped and created during the execution of a load plan. Indexes aren't synchronized by this procedure.
  
  - Changes to preconfigured indexes is fully supported. Changes to uniqueness and active/inactive are supported. The primary use case is to make a preconfigured index active or inactive
  
  - Changes to preconfigured index names should be avoided. If a load plan was previously executed and created the index, the index continues to exist with the old name in the database. Because ODI isn't aware of this index, problems may occur during the execution of the load plan.
- Delete index:
  
  - You should only delete custom indexes and not preconfigured indexes.
  
  - If you don't want a preconfigured index, inactivate it.
Add or drop a table: You can add a new table or delete an existing table in ODI.
- If a table is added in ODI and doesn't exist in the database, it is added using a CREATE TABLE statement.
- If a table is deleted from ODI and still exists in the database, a DROP TABLE statement is generated.
- You shouldn't delete preconfigured tables.
Add or drop a sequence:

You should add a sequence in ODI for new tables that includes the ROW_WID column and that follows the format <TABLE_NAME>_SEQ. You should add a sequence in ODI for new tables that includes the SCD1_WID column and that follows the <TABLE_NAME>_S1W naming convention.
- If a sequence is added in ODI and doesn't exist in the database, it is added using a CREATE SEQUENCE statement.
- Sequences deleted from ODI aren't dropped from the database.

Launch ODI Studio, and display the Designer navigator.
In the Projects editor, expand these folders: Components, DW, Oracle, and Generate DW DDL.

Execute the GENERATE_DW_DDL scenario:

Specify this information:

Option	Description
CREATE_SCRIPT_FILE	If set to True, this creates a .sql file with the DDL script. This is useful to see what the changes are. If no value is provided for the SCRIPT_LOCATION option, the procedure creates a file in the `oracledi` directory named `biappsddl.sq`l.
REFRESH_MODE	FULL or INCREMENTAL FULL generates the DDL for the entire data warehouse using CREATE statements. The Table Mask option is ignored. INCREMENTAL compares the ODI data stores with the tables in the database. This option should be used with the Table Mask option because this process can take a very long time comparing the entire data warehouse.
CHARCLAUSE	Provided for Unicode support. If set to True, the CHAR clause is included in the DDL.
RUN_DDL	If set to True, the DDL runs against your warehouse.
SCRIPT_LOCATION	If you're creating a script, this field indicates the path where the script is created.
TABLE_MASK	Default % compares all tables. If you want to create DDL for a subset of tables only, or just a single table, you can change this value. For instance, to create a diff script for dimension tables, you'd set this value to W_%_D.

Oracle BI Applications Patches

Patching Oracle BI Applications may require changes to database objects, such as modifying existing tables or introducing new tables. Oracle provides a patch file that you use to merge changes with the existing ODI Repository definition.

These changes must then be deployed to the database. New tables, columns and indexes that you introduced are retained. Any changes you made to preconfigured tables, columns, and indexes must go through a conflict resolution process.

Assigning Tablespaces

Using the Designer navigator in Oracle Studio, you can assign default tablespaces for the data warehouse tables, such as the W_%_D and W_%_F tables, their associated indexes, and the staging tables, such as the W_%_DS and W_%_FS tables. Indexes aren't created by this utility but are created during the ETL process and reflects the tablespace assigned in ODI.

To assign tablespaces to data warehouse tables:

Launch ODI Studio, and display the Designer navigator.
In the Models editor, expand the Oracle BI Applications model.
Edit the Oracle BI Applications model and assign the OBI Tablespace flexfields with the tablespace values to be used for these objects.

All data warehouse tables and indexes use the tablespaces defined here.
(Optional) Individual tables can be overridden to use a different tablespace from those assigned at the model level.

Edit the particular data store and assign the OBI Tablespace flexfield with the tablespace to be used for this table.
(Optional) To assign the tablespace for individual indexes, edit the constraint and assign the OBI Tablespace flexfield value to match the tablespace name to be used.