B Generating DDL and Assigning Tablespaces to Tables and Indexes

This appendix describes how to generate DDL to deploy Business Analytics Warehouse tables and how to assign tablespaces to tables and indexes.

This appendix contains the following topics:

• Section B.1, "Overview"

• Section B.2, "Generating the Business Analytics Warehouse DDL"

• Section B.3, "Patching Oracle BI Applications"

• Section B.4, "Assigning Tablespaces"

B.1 Overview

The 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 does not 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 Business Analytics Warehouse data model, you use ODI to generate a new DDL script.

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

• You may want to modify the Business Analytics Warehouse, either by modifying existing tables or creating new ones.

• You may want to have separate development environments. In this case you will need to use ODI to generate the DDL to be executed in these environments to deploy the Business Analytics Warehouse tables rather than run the RCU for each environment.

• The default tablespace assigned to the 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 will need to be altered or created.

You can make changes directly in the database but these changes need to be synchronized with the ODI Repository. This is done by running the Oracle BI Applications reverse knowledge module (RKM) to bring in the changes into ODI. If this RKM is not run, ODI will not be aware of these changes, which can lead to problems when populating the affected warehouse table.

B.2 Generating the Business Analytics Warehouse DDL

Follow this procedure to generate Business Analytics Warehouse DDL.

To generate Business Analytics Warehouse DDL:

1. Launch ODI Studio, and display the Designer navigator.

2. In the Projects editor, expand the following folders: Components, DW, Oracle, Generate DW DDL.

3. Execute the GENERATE_DW_DDL scenario:

   Specify the following information:

   Option	Description
   CREATE_SCRIPT_FILE	If set to True, this will create 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 will create a file in the oracledi directory named biappsddl.sql.
   REFRESH_MODE	FULL or INCREMENTAL FULL will generate the DDL for the entire data warehouse using CREATE statements. The Table Mask option is ignored. INCREMENTAL will compare 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 will be included in the DDL.
   RUN_DDL	If set to True, the DDL will run against your warehouse.
   SCRIPT_LOCATION	If you are creating a script, this field indicates the path where the script will be created.
   TABLE_MASK	Default % will compare 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 would set this value to W_%_D.

   
   

Keep the following 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 will get an error when deploying the DDL to the database.

  • Modify column:

    - Column renaming in ODI will be treated as a drop of the existing column and a new column will be 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 will not be found in the database so it is added.

    - Changes to datatype, scale, precision, 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 should not 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 should not be changed to less accommodating datatype (VARCHAR to NUMBER). ODI does not prevent users from making this kind of change but when deploying the DDL, users will likely encounter issues.

  • Delete column: You should not delete preconfigured columns. You should only delete custom columns. If a column is deleted from the ODI Repository, it will be dropped from the table using an ALTER statement. This is done by identifying those columns that exist in the database but do not 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 will 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 are not 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 previously executed and created the index, the index will continue to exist with the old name in the database. Because ODI is not 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 do not want a preconfigured index, you should inactivate it.

• Add or drop a table: You can add a new table or delete an existing table in OD.

  • If a table is added in ODI and does not exist in the database, it will be added using a CREATE TABLE statement.

  • If a table is deleted from ODI and still exists in the database, a DROP TABLE statement will be generated.

  • You should not 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 does not exist in the database, it will be added using a CREATE SEQUENCE statement.

  • Sequences deleted from ODI are not dropped from the database.

B.3 Patching Oracle BI Applications

Patching of Oracle BI Applications may require changes to database objects, such as modifying existing tables or introducing new tables. Oracle will provide a patch file that you will use to merge changes with the existing ODI Repository definition. These changes will then need to 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.

B.4 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 are not created by this utility but are created during the ETL process and will reflect the tablespace assigned in ODI.

To assign tablespaces to data warehouse tables:

1. Launch ODI Studio, and display the Designer navigator.

2. In the Models editor, expand Oracle BI Applications model.

3. 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 will use the tablespaces defined here.

4. (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.

5. (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.