Use this procedure to generate 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 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 doesn't 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.