Oracle^® Business Intelligence Applications Upgrade Guide > Upgrading Oracle BI Applications >

Upgrading the Data Warehouse Schema

NOTE:  This procedure applies to all upgrade paths except for upgrades from version 7.5.x. If you are upgrading from version 7.5.x, go directly to one of the following procedures: To migrate data from version 7.5.x to 7.9 (Horizontal), or To migrate data from version 7.5.x to 7.9 (Vertical). These procedures provide instructions for upgrading the data warehouse schema.

This procedure adds new tables, columns, and indexes to the existing data warehouse schema. It does not alter or disrupt your existing data. During this procedure, you will run four different scripts. Below is a description of what each script does.

Data Warehouse Schema Upgrade Scripts (for All Versions Except 7.5.x)

• UPGRADE.ctl. This script upgrades older schemas to the Siebel Industry Applications 7.8.4 version. It adds new tables and columns and modifies existing columns. It also adds lookup tables. This script is necessary for all previous versions of Siebel Business Applications and Siebel Industry Applications.
• 790_UPGRADE_PRE_CTL_SCRIPT.sql. For all database types except for DB2, this script renames columns to support Teradata databases. It also adds default values in columns in case a column changes from nullable to not null, and truncates all fact and dimension staging tables.

  For DB2 databases, this script does not rename columns. Instead, the new columns are updated with the old values in the 790_UPGRADE_PRE_DIMENSION_SCRIPT.sql.

• DW.ctl. This script upgrades the schema to the version Oracle BI Applications 7.9.
• 790_UPGRADE_PRE_DIMENSION_SCRIPT.sql. This script creates backup tables for the dimension and fact tables that are being updated during the 7.8.4 to 7.9 upgrade. The backup tables are called <table_name>_784.

  This script also sets the SRC_EFF_FROM_DT and EFFECTIVE_FROM_DT to 1/1/1899 in the tables where these columns were defaulted to SYSDATE in DW.ct.

  For DB2 databases, this script contains update commands to load new column names from the old column names.

To upgrade the data warehouse schema

1. Run the UPGRADE.ctl script.

   This script upgrades older schemas to the version of Siebel Industry Applications 7.8.4, from which in a later step you will upgrade to version 7.9.

   1. Navigate to the folder OracleBI\dwrep\Upgrade\CTLFiles.
   2. Use the DDLimp utility to run the UPGRADE.ctl script. Use the following command:

   ..\OracleBI\dwrep\bin\DDLIMP /U <USER> /P <PASSWORD> /C <ODBC connect string>
   /G SSE_ROLE /R Y /F <..\OracleBI\dwrep\UPGRADE.CTL>
   /L <..\oracleBI\dwrep\UPGRADE.log>

   For example:

   DDLIMP /U SADMIN /P SADMIN /C SIEBEL_OLTP /G SSE_ROLE
   /R Y /F C:\OracleBI\dwrep\UPGRADE.CTL /L C:\OracleBI\dwrep\UPGRADE.log

   Notes:

   • /P <PASSWORD> - The password for Oracle's CRM OLTP.
   • /C <ODBC connect string> - The name of the ODBC connect string.
   • For Oracle databases, use the Siebel Merant ODBC Drivers.
   • In addition, you can use the following commands:
   • /W Y - If the OLTP database is Oracle and Unicode.
   • /Z Y - If the OLTP database is DB2 or SQL Server and Unicode.
   • /B <TABLE_SPACE_NAME> - If you want to create these tables in a separate table space. For DB2, This must be specified as 32K tablespace.
   • /X <INDEX_TABLE_SPACE_NAME> - If you want to create the indexes in a separate tablespace. For DB2, This must be specified as 32K tablespace.
   • /Y - Storage File for DB2/390.
   • /R - Regrant tables.
2. Run the 790_UPGRADE_PRE_CTL_SCRIPT.sql script.
   1. Open the SQL client for your database type, for example, SQLPLUS for Oracle, Query Analyzer for SQL Server, or a command window for DB2.
   2. Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\<database type>.
   3. Open the 790_UPGRADE_PRE_CTL_SCRIPT.sql file, and copy the contents into the SQL client.
   4. Execute the script.
3. Navigate to the folder OracleBI\dwrep\Upgrade\CTLFiles, and use the DDLimp utility to run the DW.ctl script.

   Use the command provided in Step 1, but substitute the correct script name.

4. Run the 790_UPGRADE_PRE_DIMENSION_SCRIPT.sql script.
   1. Open the SQL client for your database type.
   2. Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\<database type>.
   3. Open the 790_UPGRADE_PRE_DIMENSION_SCRIPT.sql file, and copy the contents into the SQL client.
   4. Execute the script.