Skip Headers
Oracle® Business Intelligence Applications Upgrade Guide for Informatica PowerCenter Users
Version 7.9.6

Part Number E14218-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

3 Upgrading Siebel Analytics 7.5.x

This section contains instructions for upgrading Oracle's Siebel Analytics release 7.5.x.

This section includes the following topics:

3.1 Upgrading Oracle BI Infrastructure

Upgrade the Oracle BI Infrastructure to the version that is supported for this release of Oracle BI Applications. See the System Requirements and Supported Platforms for Oracle Business Intelligence Applications for the current version that is supported. For information on installing the supported version of Oracle BI Infrastructure, see the Oracle Business Intelligence Infrastructure Upgrade Guide.

3.2 Upgrading Oracle BI Applications

Run the Oracle BI Applications installer to upgrade your Oracle BI Applications environment to the current version. For instructions on running the installer, see Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users.

Note:

Perform only the step that addresses the running of the installer. Do not perform the configuration instructions that follow the running of the installer.

Note:

If you have a previous release of Oracle BI Applications installed, you must uninstall it before you run the installer for the current release. If you do not uninstall the old release, some folders from the current release will not be correctly installed.

3.3 Upgrading to Informatica PowerCenter Version 8.6 and Hotfix 4

You must install Informatica PowerCenter 8.6 and Hotfix 4 to run the current version of Oracle BI Applications.

The components and architecture for Informatica PowerCenter 8.6 differ significantly from PowerCenter 7.x versions. Oracle recommends that you carefully review the Informatica PowerCenter 8.6 documentation, which is included on the Informatica DVD provided with Oracle BI Applications.

For a summary of installation instructions for installing Informatica PowerCenter 8.6 on a single machine in an Oracle BI Applications deployment, see the Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users.

For detailed information about deploying Informatica PowerCenter 8.6, refer to the Informatica PowerCenter Installation Guide, Informatica PowerCenter Configuration Guide, Informatica PowerCenter Administrator Guide, and related documentation.

For information about applying Hotfix 4, see PowerCenter Version 8.6.0 Hotfix 4 Release Notes. This PDF is included with the Informatica documentation on the Informatica PowerCenter DVD and is also included with the Hotfix 4 installation

To upgrade to Informatica PowerCenter 8.6 and Hotfix 4

  1. Perform the pre-upgrade steps documented in the Informatica PowerCenter Installation Guide.

  2. Install Informatica PowerCenter version 8.6 and Hotfix 4, by following the instructions in the Informatica PowerCenter Installation Guide and PowerCenter Version 8.6.0 HotFix 4 Release Notes.

  3. Perform the post-upgrade steps documented in the Informatica PowerCenter Installation Guide.

Note:

The Informatica PowerCenter 8.6 upgrade process includes upgrading your current Informatica Repository to the version 8.6 format. This process is necessary so that you will be able to access your current repository using version 8.6 client tools so that you can perform the procedure in Section 3.4, "Upgrading the Informatica Repository."

In Section 3.4, "Upgrading the Informatica Repository," you back up and rename your current repository and then restore the Informatica Repository (Oracle_BI_DW_Base.rep) that is installed during the Oracle BI Applications installation. You then copy your custom folder from the backed up repository into the newly restored Oracle_BI_DW_Base repository.

3.4 Upgrading the Informatica Repository

Follow this procedure to upgrade the Informatica Repository.

For detailed instructions on backing up and restoring the Informatica Repository, see the topic titled, "Backing Up and Restoring the Repository," in "Chapter 8: Managing the Repository," in the Informatica PowerCenter Administrator Guide, which is included on the Informatica DVD provided with Oracle BI Applications.

To upgrade the Informatica Repository

  1. Make sure you have backed up and renamed your current Informatica Repository.

    Note:

    This repository must be upgraded to the version 8.6 format during the procedure in Section 3.3, "Upgrading to Informatica PowerCenter Version 8.6 and Hotfix 4." You must upgrade this repository to the version 8.6 format in order to move your custom folder from this repository into the new Oracle_ BI_DW_Base repository that you restore in the steps below.
  2. Copy the Oracle_BI_DW_Base.rep file from the folder OracleBI\dwrep\Informatica\Repository into the folder \Informatica\PowerCenter 8.6\server\infa_shared\Backup.

    Note:

    The Oracel_BI_DW_Base.rep file is installed in the OracleBI root directory when you run the Oracle BI Applications installer, as described in Section 3.2, "Upgrading Oracle BI Applications."
  3. Restore the Oracle_BI_DW_Base.rep repository.

  4. Copy the custom folder from your previous Informatica Repository to the newly created Informatica Repository:

    1. Launch the Informatica PowerCenter Repository Manager, and connect to both your previous and newly created Informatica repositories.

    2. Copy the Custom folder in your previous repository into the newly created Informatica Repository.

    3. Make sure there is an individual workflow for each of the mappings in the Custom folder.

3.5 Configuring Informatica PowerCenter Version 8.6 to Work with DAC

Informatica PowerCenter version 8.6 requires additional configuration steps to work with Oracle BI Applications and DAC. For instructions on performing these steps, see the following sections in the Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users:

3.6 Installing and Configuring DAC

Siebel Analytics release 7.5.x does not include the Data Warehouse Administration Console (DAC). In Oracle BI Applications release 7.9.6, you will use the DAC Client and Server and the DAC Repository. To install and configure DAC, follow the instructions in the Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users. Make sure you perform the tasks in the following sections of the Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users:

3.7 Overview of Upgrading the Data Warehouse Schema and Migrating Data

The process for upgrading the data warehouse schema and migrating data for Siebel Business Analytics releases 7.5.x involves multiple phases, as described below.

Note:

The Oracle BI Applications upgrade logic is coded such that you must complete each of the phases listed below. To upgrade to the current release of Oracle BI Applications, you cannot skip any of the phases.

3.8 Upgrading the Data Warehouse Schema to Version 7.7 and Migrating Data

This procedure adds new tables, columns, and indexes to the existing data warehouse schema. It also modifies the existing data warehouse schema objects.

To upgrade the data warehouse schema

  1. Run the schema upgrade script.

    1. Navigate to the folder OracleBI\dwrep\Upgrade\CTLFiles, and locate the ddlsme_HOR_77.ctl file (Horizontal) or ddlsme_SIA_77.ctl file (Vertical).

    2. If you changed any of the preconfigured column definitions in the CTL file for your implementation, you need to edit the CTL file to reflect the change. For example, if you renamed or resized an existing column in W_DAY_D, you need to make the same change in the CTL file. If you do not make the change in the CTL file, when the CTL file is run, the column definition will revert to the preconfigured definition. (As a customization, if a column size was changed from VARCHAR(50) to VARCHAR(100), and the same change was not made in the CTL file before running, the column will revert to the preconfigured value of VARCHAR(50), which could cause data to be truncated in some databases.)

    3. Use the DDLimp utility to run one of the following scripts:

      For Siebel Applications (Horizontal), run ddlsme_HOR_77.ctl.

      For Siebel Industry Applications (Vertical), run ddlsme_SIA_77.ctl.

      Use the following command, substituting the correct script name where appropriate.

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

      For example:

      DDLIMP /U SADMIN /P SADMIN /C SIEBEL_OLTP /G SSE_ROLE
      /I N /R Y /F C:\OracleBI\dwrep\ddlsme_HOR_77.ctl /L C:\OracleBI\dwrep\ddlsme_HOR_77.log
      

      Notes:

      • /P <PASSWORD> - The password for the data warehouse.

      • /C <ODBC connect string> - The name of the ODBC connect string.

      • /I N - Tells DDLimp to ignore the indexes DDL defined in the CTL file if any exist. It does not change existing indexes. (The default is /I Y, which tells DDLimp to create and merge indexes from the CTL file with the indexes in the database.

      • For Oracle databases, use the Data Direct drivers.

      • In addition, you can use the following commands:

      • /W Y - If the OLAP database is Oracle and Unicode.

      • /Z Y - If the OLAP 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. Copy the file Upgrade.rep file from the folder OracleBI\dwrep\Upgrade\Informatica\Repository into the folder Informatica PowerCenter 8.6\server\infa_shared\Backup.

  3. Launch the Informatica PowerCenter Administration Console and restore Upgrade.rep (located in Informatica PowerCenter 8.6\server\infa_shared\Backup) into a database other than the database in which you restored Oracle_BI_DW_Base.rep.

  4. Navigate to the folder OracleBI\dwrep\Upgrade\Informatica\ParameterFiles and copy the file 753_TENERIFE_UPG_PARAMS.txt into the SrcFiles folder on the Informatica Server machine, for example, C:\Program Files\Informatica PowerCenter 8.6\server\infa_shared\SrcFiles.

  5. Rename 753_TENERIFE_UPG_PARAMS.txt to TENERIFE_UPG_PARAMS.txt.

  6. In the Informatica Workflow Manager, open the Relational Connection Browser (in the menu bar, select Connections, and then select Relational), and edit the connect string, user and password for the relational connections as follows:

    1. Edit the PARAM_OLTP_SIEBEL connection to point to the Siebel transactional database.

    2. Edit the DataWarehouse connection to point to the newly upgraded data warehouse database.

    3. Create or edit the PARAM_DAC_OLD connection to point to the previous DAC Repository database (the version from which you are upgrading).

    4. Create or edit the PARAM_DAC_NEW connection to point to the DAC Repository database.

      Note:

      If you are connected to an Oracle database, use the Oracle Native driver instead of ODBC.

      If you are connected to a SQL Server database, use the ODBC driver rather than the native SQL Server driver.

  7. For Siebel Applications (Horizontal), in Informatica Workflow Manager, navigate to the folder UPGRADE_753_to_770_HOR, and execute the following workflows in the order indicated:

    1. Upgrade_R_Image

    2. Upgrade_Unspecifieds

    3. Upgrade_Agree

    4. Upgrade_Asset

    5. Upgrade_Opty

    6. Upgrade_Order

    7. Upgrade_Quote

    8. Upgrade_Response

    9. Upgrade_ServiceRequest

    10. Upgrade_Others

    11. Upgrade_Visibility

    12. UpgradeSlowlyChangingDimensionStartDates

    13. DAC_Metadata_Upgrade_Workflow

  8. For Siebel Industry Applications (Vertical), in Informatica Workflow Manager, navigate to the folder UPGRADE_753_to_770_SIA, and execute the following workflows in the order indicated

    1. Upgrade_R_Image

    2. Upgrade_Unspecifieds

    3. Upgrade_Agree

    4. Upgrade_Asset

    5. Upgrade_Opty

    6. Upgrade_Order

    7. Upgrade_Quote

    8. Upgrade_Response

    9. Upgrade_ServiceRequest

    10. Upgrade_Others

    11. Upgrade_Visibility

    12. UpgradeSlowlyChangingDimensionStartDates

    13. Upgrade_Industry_R_Image

    14. Upgrade_Industry_Unspecified

    15. Upgrade_LS_ActivityProduct

    16. Upgrade_LS_Others

    17. Upgrade_Industry_Household

    18. Upgrade_FINS_Visibility

    19. UpgradeSlowlyChangingDimensionStartDates_Industry

    20. DAC_Metadata_Upgrade_Workflow

3.9 Upgrading the Data Warehouse Schema to Version 7.9.0

This procedure adds new tables, columns, and indexes to the existing data warehouse schema. It also modifies the existing data warehouse schema objects.

To upgrade the data warehouse schema

  1. Run the UPGRADE.ctl script.

    1. Navigate to the folder OracleBI\dwrep\Upgrade\CTLFiles, and locate the UPGRADE.ctl file.

    2. If you changed any of the preconfigured column definitions in the CTL file for your implementation, you need to edit the CTL file to reflect the change. For example, if you renamed or resized an existing column in W_DAY_D, you need to make the same change in the CTL file. If you do not make the change in the CTL file, when the CTL file is run, the column definition will revert to the preconfigured definition. (As a customization, if a column size was changed from VARCHAR(50) to VARCHAR(100), and the same change was not made in the CTL file before running, the column will revert to the preconfigured value of VARCHAR(50), which could cause data to be truncated in some databases.)

    3. 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 /I N /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
      /I N /R Y /F C:\OracleBI\dwrep\UPGRADE.CTL /L C:\OracleBI\dwrep\UPGRADE.log
      

      Notes:

      • /P <PASSWORD> - The password for the data warehouse.

      • /C <ODBC connect string> - The name of the ODBC connect string.

      • /I N - Tells DDLimp to ignore the indexes DDL defined in the CTL file if any exist. It does not change existing indexes. (The default is /I Y, which tells DDLimp to create and merge indexes from the CTL file with the indexes in the database.

      • For Oracle databases, use the Data Direct drivers.

        In addition, you can use the following commands:

      • /W Y - If the OLAP database is Oracle and Unicode.

      • /Z Y - If the OLAP 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. Run the DW.ctl script.

    1. Navigate to the folder OracleBI\dwrep\Upgrade\CTLFiles, and locate the DW.ctl file.

    2. If you changed any of the preconfigured column definitions in the CTL file for your implementation, you need to edit the CTL file to reflect the change. For example, if you renamed or resized an existing column in W_DAY_D, you need to make the same change in the CTL file. If you do not make the change in the CTL file, when the CTL file is run, the column definition will revert to the preconfigured definition. (As a customization, if a column size was changed from VARCHAR(50) to VARCHAR(100), and the same change was not made in the CTL file before running, the column will revert to the preconfigured value of VARCHAR(50), which could cause data to be truncated in some databases.)

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

3.10 Importing New Schema Definitions into the Siebel Transactional Database

This procedure upgrades transactional database objects that relate to Oracle Business Intelligence Applications. It does not upgrade transactional database objects for Siebel CRM applications.

To import new schema definitions into the Siebel transactional database

  1. Import schema definitions for non-image tables:

    1. Access the DDLimp utility.

    2. Run the DDL_OLTP.ctl script that is located in the OracleBI\dwrep\Upgrade\CTLFiles folder.

  2. Import schema definitions for image tables:

    1. In DAC, go to the Design view, and select your custom container from the drop-down list to the right of the Execute button.

    2. Click the Tables tab.

    3. Query for all tables for which the image suffix is not null.

    4. Right-click in the list of tables returned by the query, and select Change Capture Scripts, and then Generate Image and Trigger Scripts.

    5. In the Triggers and Image Tables dialog box, do the following:

      - Select the option All Tables in the List.

      - Select the option Generate Image Table Scripts.

      - Select the appropriate database type for the source system.

      - Click OK.

    6. Open the SQL client for the source system database type, for example, SQL Plus for Oracle, Query Analyzer for SQL Server, or a command window for DB2.

      The script may contain many lines; therefore, you can save the script file as a SQL file and execute it in a SQL client.

    7. Copy the scripts generated by DAC into the SQL client and execute them.

3.10.1 Verifying the Siebel Transactional Database Upgrade

Follow this procedure to verify the following tables were created in the Siebel transactional database.

To verify the transactional database upgrade

  • For all upgrade paths, verify the following tables were created in the Siebel transactional database:

    • S_ETL_R_IMG_xxx

    • S_ETL_I_IMG_xxx

    • S_ETL_D_IMG_xxx

    • S_ETL_PARAM

    • S_ETL_PRD_ATTR

    • S_ETL_PRD_REL

3.11 Migrating Data into the Data Warehouse Upgraded to Version 7.9.0

Follow this procedure to migrate data into the data warehouse upgraded to version 7.9.0.

To migrate data into the upgraded data warehouse

  1. Navigate to the folder OracleBI\dwrep\Upgrade\Informatica\SrcFiles and copy the *.csv files into the SrcFiles folder on the Informatica Server machine, for example, C:\Program Files\Informatica PowerCenter 8.6\server\infa_shared\SrcFiles.

  2. Navigate to the folder OracleBI\dwrep\Upgrade\Informatica\ParameterFiles and copy the file 770_TENERIFE_UPG_PARAMS.txt into the SrcFiles folder on the Informatica Server machine, for example, C:\Program Files\Informatica PowerCenter 8.6\server\infa_shared\SrcFiles.

  3. Rename 770_TENERIFE_UPG_PARAMS.txt to TENERIFE_UPG_PARAMS.txt.

  4. In the file TENERIFE_UPG_PARAMS.txt:

    1. Search for the parameter $$SourceConnection.

    2. Set the value to one of the following options, based on your Siebel applications (OLTP) version:

      • SEBL_63

      • SEBL_753

      • SEBL_771

      • SEBL_78

      • SEBL_80 (use this value for OLTP versions 8.0 and 8.1.1)

      • SEBL_VERT_753

      • SEBL_VERT_771

      • SEBL_VERT_78

      • SEBL_VERT_80 (use this value for OLTP versions 8.0 and 8.1.1)

      For example, if your Siebel applications (OLTP) version is Siebel Industry Applications 7.5.x, the parameter should appear as:

      $$SourceConnection=SEBL_VERT_753
      
  5. In the file TENERIFE_UPG_PARAMS.txt:

    1. Search for the parameter $$Source_Container.

    2. Set the value to one of the following options, based on your Siebel applications (OLTP) version:

      • Siebel 6.3

      • Siebel 7.5.3

      • Siebel 7.5.3 Vertical

      • Siebel 7.7.1

      • Siebel 7.7.1 Vertical

      • Siebel 7.8

      • Siebel 7.8 Vertical

      • Siebel 8.0 (use this value for OLTP versions 8.0 and 8.1.1)

      • Siebel 8.0 Vertical (use this value for OLTP versions 8.0 and 8.1.1)

      For example, if your Siebel applications (OLTP) version is Siebel Industry Applications 7.5.x, the parameter should appear as:

      $$Source_Container=Siebel 7.5.3 Vertical
      
  6. In the file TENERIFE_UPG_PARAMS.txt, edit the ETL_PROC_WID parameter as follows:

    MPLT_GET_ETL_PROC_WID.$$ETL_PROC_WID=<latest ETL_PROC_WID value from your database>
    

    You can get this value from W_PARAM_G.ETL_PROC_WID.

  7. If you are running Siebel Industry Applications (Vertical), in the file TENERIFE_UPG_PARAMS.txt, set the VERTICAL_UPGRADE parameter to 1. For example:

    $$VERTICAL_UPGRADE=1
    
  8. For Siebel Industry Applications (Vertical), define the alignment rule to be used for ETL loads.

    1. Navigate to the folder OracleBI\dwrep\Upgrade\Informatica\SrcFiles and copy the file AlignmentType_LS_782.csv into the SrcFiles folder on the Informatica Server machine, for example, C:\Program Files\Informatica PowerCenter 8.6\server\infa_shared\SrcFiles.

    2. Rename the file AlignmentType_LS_782.csv to AlignmentType.csv.

    3. Open the file AlignmentType.csv, and enter one of the following alignment item types under the ALIGN_TYPE field:

      • Zipcode

      • Account/Contact

      • Brick

    4. Save the file.

    The Oracle Business Analytics Warehouse allows only one alignment type to be used for ETL loads during upgrade.

  9. In Informatica Workflow Manager, navigate to the folder UPGRADE_770_to_79, and execute the following workflows in the order indicated:

    1. MARKETING_LOAD

    2. Upgrade_Dimensions_Industry

    3. Update_Dimensions

    4. Update_Dimension_Unspecified

    5. Update_Facts

    6. (For Siebel Industry Applications only) Upgrade_LS_Dimensions

    7. (For Siebel Industry Applications only) Upgrade_LS_Facts

    8. SIL_PositionDimensionHierarchy_Full

    9. Load_INT_ORG_DH

    10. DIMENSION_LOAD

      Note:

      If you are using the SCD version of the dimension, replace the corresponding TENN_UPG_W_XXX_D_784_To_W_XXX_D session with TENN_UPG_W_XXX_D_784_SCD_To_W_XXX_D. This will upgrade the data from the W_XXX_SCD version of the dimension to the new SCD-enabled W_XXX_D dimension.
    11. DIMENSION_UNSPECIFIED_UPDATE

    12. FACT_UPDATE

    13. DAC_Metadata_Upgrade_Workflow

      Note:

      You need to run this workflow for upgrading to the data warehouse schema version 7.9.0 even though you ran a workflow with the same name during the upgrade to version 7.7.
  10. If you upgraded your transactional database to Siebel Applications 8.0 or 8.1.1, navigate to the folder UPGRADE_790_to_791_SBL80UPG and run the following workflows in the order indicated:

    1. UPGRADE_DIMENSIONS

    2. UPGRADE_FACTS

  11. Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the main Informatica Repository (Oracle_BI_DW_Base.rep).

    1. Navigate to OracleBI\dwrep\Upgrade\DbScripts\<database type>.

    2. Open the reset_infa_seq_gen.bat file.

      The file opens a command prompt, which will prompt you for the parameters listed below.

    3. Enter the appropriate parameter values to reflect your environment (the parameters are database specific). For information about the settings to use, see Appendix D, "Database-Specific Parameter Settings for the reset_infa_seq_gen.bat File."

  12. To verify the data migrated successfully:

    1. Check whether any of the Informatica mapping log files stored in the directory \Informatica PowerCenter 8.6\Server\SessLogs indicates errors or failures.

    2. Check whether the SQL scripts (790_UPGRADE_PRE_CTL_SCRIPT.sql and 790_UPGRADE_PRE_DIMENSION_SCRIPT.sql) that you ran in the SQL client of the database failed or errored out while executing.

    3. Check the log files for the CTL files (Upgrade.ctl and DW.ctl) that you ran using the DDLimp command to determine whether any of the runs failed.

      If you did not detect any errors or failures in the steps above, then the data migration was successful.

      Note:

      Ignore any error messages that indicate tables or indexes could not be dropped from the database. These error messages are displayed when the specified objects do not exist in the database. This can occur because each source system container holds a subset of all data warehouse tables.
  13. If the data migration was successful, drop the tables that were created during the upgrade process, such as, W_xxxx_x_784 tables, LKP_xxxx_x, and W_ASSET_D_TMP.

    This step frees the space occupied by these backup tables.

3.12 Upgrading the Data Warehouse Schema to Version 7.9.4 and Migrating Data

Follow this procedure to upgrade the data warehouse schema to version 7.9.4 and migrate data.

This procedure adds new tables, columns, and indexes to the existing data warehouse schema. It also modifies the existing data warehouse schema objects.

To upgrade the data warehouse schema to version 7.9.4 and migrate data

  1. Run the 792_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 792_UPGRADE_PRE_CTL_SCRIPT.sql file, and copy the contents into the SQL client.

    4. Execute the script.

  2. Run the ddl_794.ctl script.Use the DDLimp utility to run the ddl_794.ctl script, which is located in the OracleBI\dwrep\Upgrade\CTLFiles folder. Use the following command:

    1. Navigate to the folder OracleBI\dwrep\Upgrade\CTLFiles, and locate the ddl_794.ctl script.

    2. If you changed any of the preconfigured column definitions in the CTL file for your implementation, you need to edit the CTL file to reflect the change. For example, if you renamed or resized an existing column in W_DAY_D, you need to make the same change in the CTL file. If you do not make the change in the CTL file, when the CTL file is run, the column definition will revert to the preconfigured definition. (As a customization, if a column size was changed from VARCHAR(50) to VARCHAR(100), and the same change was not made in the CTL file before running, the column will revert to the preconfigured value of VARCHAR(50), which could cause data to be truncated in some databases.)

    3. Use the DDLimp utility to run ddl_794.ctl.

      Use the following command:

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

      For example:

      DDLIMP /U SADMIN /P SADMIN /C OBIA /G SSE_ROLE
      /I N /R Y /F C:\OracleBI\dwrep\ddl_794.ctl /L C:\OracleBI\dwrep\ddl_794.log
      
  3. Run the 792_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 792_UPGRADE_PRE_DIMENSION_SCRIPT.sql file, and copy the contents into the SQL client.

    4. Execute the script.

  4. If you upgraded your transactional database to Siebel Applications 8.0 or 8.1.1, run the workflows to migrate your data into the upgraded data warehouse.

    1. In Informatica Workflow Manager, navigate to the folder UPGRADE_790_TO_791_SBL80UPG.

    2. Run the following workflows in the order indicated:

      UPGRADE_DIMENSIONS

      UPGRADE_FACTS

  5. Verify the data migrated successfully by checking whether any of the Informatica mapping log files stored in the \Informatica PowerCenter 8.6\server\infa_shared\SessLogs directory indicate errors or failures.

  6. If the data migration was successful, drop the tables that were created during the upgrade process, such as, W_xxxx_x_79x tables, LKP_xxxx_x and 79x_XXXX_TMP.

    This step frees the space occupied by these backup tables.

  7. Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the main Informatica Repository (Oracle_BI_DW_Base.rep).

    1. Navigate to OracleBI\dwrep\Upgrade\DbScripts\<database type>.

    2. Open the reset_infa_seq_gen.bat file.

      The file opens a command prompt, which will prompt you for the parameters listed below.

    3. Enter the appropriate parameter values to reflect your environment (the parameters are database specific). For information about the settings to use, see Appendix D, "Database-Specific Parameter Settings for the reset_infa_seq_gen.bat File."

3.13 Upgrading the Data Warehouse Schema to Version 7.9.5 and Migrating Data

Follow this procedure to upgrade the data warehouse schema to version 7.9.5 and migrate data.

This procedure adds new tables, columns, and indexes to the existing data warehouse schema. It also modifies the existing data warehouse schema objects.

To upgrade the data warehouse schema to version 7.9.5 and migrate data

  1. Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade repository.

    1. Navigate to OracleBI\dwrep\Upgrade\DbScripts\<database type>.

    2. Open the reset_infa_seq_gen.bat file.

      The file opens a command prompt, which will prompt you for the parameters listed below.

    3. Enter the appropriate parameter values to reflect your environment (the parameters are database specific). For information about the settings to use, see Appendix D, "Database-Specific Parameter Settings for the reset_infa_seq_gen.bat File."

  2. Run the 795_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 795_UPGRADE_PRE_CTL_SCRIPT.sql file, and copy the contents into the SQL client.

    4. Execute the script.

  3. Run the UPGRADE_795.ctl script.

    1. Navigate to the folder OracleBI\dwrep\Upgrade\CTLFiles, and locate the UPGRADE_795.ctl file.

    2. If you changed any of the preconfigured column definitions in the CTL file for your implementation, you need to edit the CTL file to reflect the change. For example, if you renamed or resized an existing column in W_DAY_D, you need to make the same change in the CTL file. If you do not make the change in the CTL file, when the CTL file is run, the column definition will revert to the preconfigured definition. (As a customization, if a column size was changed from VARCHAR(50) to VARCHAR(100), and the same change was not made in the CTL file before running, the column will revert to the preconfigured value of VARCHAR(50), which could cause data to be truncated in some databases.)

    3. Use the DDLimp utility to run the UPGRADE_795.ctl script. Use the following command:

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

      For example:

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

      Notes:

      • /P <PASSWORD> - The password for the data warehouse.

      • /C <ODBC connect string> - The name of the ODBC connect string.

      • /I N - Tells DDLimp to ignore the indexes DDL defined in the CTL file if any exist. It does not change existing indexes. (The default is /I Y, which tells DDLimp to create and merge indexes from the CTL file with the indexes in the database.For Oracle databases, use the Data Direct drivers.

        In addition, you can use the following commands:

      • /W Y - If the OLAP database is Oracle and Unicode.

      • /Z Y - If the OLAP 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.

  4. Use the DDLimp utility to run the ddl_795.ctl script, which is located in the OracleBI\dwrep\Upgrade\CTLFiles folder.

    If you changed any of the preconfigured column definitions in the CTL file for your implementation, you need to edit the CTL file to reflect the change. For example, if you renamed or resized an existing column in W_DAY_D, you need to make the same change in the CTL file. If you do not make the change in the CTL file, when the CTL file is run, the column definition will revert to the preconfigured definition. (As a customization, if a column size was changed from VARCHAR(50) to VARCHAR(100), and the same change was not made in the CTL file before running, the column will revert to the preconfigured value of VARCHAR(50), which could cause data to be truncated in some databases.)

    Use the following command:

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

    For example:

    DDLIMP /U SADMIN /P SADMIN /C OBIA /G SSE_ROLE
    /I N /R Y /F C:\OracleBI\dwrep\ddl_795.ctl /L C:\OracleBI\dwrep\ddl_795.log
    
  5. Run the 795_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 795_UPGRADE_PRE_DIMENSION_SCRIPT.sql file, and copy the contents into the SQL client.

    4. Execute the script.

  6. Copy all of the domain value files in the folder \OracleBI\dwrep\Informatica\LkpFiles into the folder \Informatica PowerCenter 8.6\server\infa_shared\LkpFiles.

  7. Migrate data into the upgraded data warehouse.

    1. Copy the file Upgrade.rep file from the folder OracleBI\dwrep\Upgrade\Informatica\Repository into the folder Informatica PowerCenter 8.6\server\infa_shared\Backup.

    2. Launch the Informatica PowerCenter Administration Console and restore Upgrade.rep (located in Informatica PowerCenter 8.6\server\infa_shared\Backup).

    3. Navigate to the folder OracleBI\dwrep\Upgrade\Informatica\ParameterFiles and copy the file 795_UPG_PARAMS.txt into the SrcFiles folder on the Informatica Server machine, for example, C:\Program Files\Informatica PowerCenter 8.6\server\infa_shared\SrcFiles.

    4. Set the parameter $$ETL_PROC_WID to the latest ETL_PROC_WID value from the database. You can get this value from W_PARAM_G.ETL_PROC_WID.

    5. Set the parameter $$DATASOURCE_NUM_ID to the relevant value from the source system setup.

    6. In Informatica Workflow Manager, open the Relational Connection Browser (in the menu bar, select Connections, and then select Relational), and edit the connect string, user and password for the relational connections as follows: create one relational connection based on the appropriate database platform for your OLTP database. Create the connection with the name PARAM_OLTP.

      Edit the PARAM_OLTP connection to match your OLTP environment.

      Edit the PARAM_OLAP connection to match your OLAP environment.

      Note:

      If you are connected to an Oracle database, use the Oracle Native driver instead of ODBC.

      If you are connected to a SQL Server database, use the ODBC driver rather than the native SQL Server driver.

    7. In Informatica Workflow Manager, navigate to the folder UPGRADE_794_to_795_SBL and execute the UPGRADE_DIMENSIONS workflow.

  8. Verify the data migrated successfully.

    1. Check whether any of the Informatica mapping log files stored in the \Informatica PowerCenter 8.6\serva\infa_shared\Sesslogs directory indicates errors or failures.

    2. Check whether the script 795_UPGRADE_PRE_DIMENSION_SCRIPT.sql that you ran in the SQL client of the database failed or errored out while executing.

    3. Check the log file for the script Upgrade_795.ctl that you ran using the DDLimp command to determine whether any of the runs failed.

      If you did not detect any errors or failures in the steps above, then the data migration was successful.

      Note:

      Ignore any error messages that indicate tables or indexes could not be dropped from the database. These error messages are displayed when the specified objects do not exist in the database. This can occur because each source system container holds a subset of all data warehouse tables
  9. If the data migration was successful, drop the tables that were created during the upgrade process, such as W_xxxx_x_79x, LKP_xxxx_x and 79x_xxxx_TMP.

    This step frees the space occupied by these backup tables.

  10. Once the data migration steps above are complete, you can delete the Upgrade repository to avoid any accidental use or confusion.

    1. In the Informatica PowerCenter Administration Console, select the Upgrade repository service.

    2. In the General Properties area of the Properties tab, click Edit .

    3. Make sure the operating mode of the repository service is set to Exclusive.

    4. Click OK.

    5. Choose Actions, and then click Delete Contents.

    6. In the Delete contents for <repository name> dialog, enter the repository username and password (for example, Administrator/Administrator), then click OK.

  11. Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the main Informatica Repository (Oracle_BI_DW_Base.rep).

    1. Navigate to OracleBI\dwrep\Upgrade\DbScripts\<database type>.

    2. Open the reset_infa_seq_gen.bat file.

      The file opens a command prompt, which will prompt you for the parameters listed below.

    3. Enter the appropriate parameter values to reflect your environment (the parameters are database specific). For information about the settings to use, see Appendix D, "Database-Specific Parameter Settings for the reset_infa_seq_gen.bat File."

3.14 Upgrading the Data Warehouse Schema to Version 7.9.5.1

Follow this procedure to upgrade the data warehouse schema to version 7.9.5.1.

To upgrade the data warehouse schema

3.15 Upgrading the Data Warehouse Schema to Version 7.9.6 and Migrating Data

Follow the procedures in this section to upgrade the data warehouse schema to version 7.9.6 and migrate data.

This section contains the following topics:

3.15.1 Upgrading the Data Warehouse Schema to Version 7.9.6

Follow this procedure to upgrade the data warehouse schema to version 7.9.6.

To upgrade the data warehouse schema to version 7.9.6

  1. Run the 796_UPGRADE_DROP_INDEXES.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 796_UPGRADE_DROP_INDEXES. sql file, and copy the contents into the SQL client.

    4. Execute the script.

  2. Run the UPGRADE_796.ctl script.

    This script adds temp tables for the upgrade process.

    1. Navigate to the folder OracleBI\dwrep\Upgrade\CTLFiles, and locate the UPGRADE_796.ctl file.

    2. If you changed any of the preconfigured column definitions in the CTL file for your implementation, you need to edit the CTL file to reflect the change. For example, if you renamed or resized an existing column in W_DAY_D, you need to make the same change in the CTL file. If you do not make the change in the CTL file, when the CTL file is run, the column definition will revert to the preconfigured definition. (As a customization, if a column size was changed from VARCHAR(50) to VARCHAR(100), and the same change was not made in the CTL file before running, the column will revert to the preconfigured value of VARCHAR(50), which could cause data to be truncated in some databases.)

    3. Use the DDLimp utility to run the UPGRADE_796.ctl script. Use the following command:

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

      For example:

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

      Notes:

      • /P <PASSWORD> - The password for the data warehouse.

      • /C <ODBC connect string> - The name of the ODBC connect string.

      • /I N - Tells DDLimp to ignore the indexes DDL defined in the CTL file if any exist. It does not change existing indexes. (The default is /I Y, which tells DDLimp to create and merge indexes from the CTL file with the indexes in the database.For Oracle databases, use the Data Direct drivers.

        In addition, you can use the following commands:

      • /W Y - If the OLAP database is Oracle and Unicode.

      • /Z Y - If the OLAP 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.

  3. Run the 796_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 796_UPGRADE_PRE_CTL_SCRIPT.sql file, and copy the contents into the SQL client.

    4. Find and replace the Datasource_Num_id = -1 with the correct Datasource_Num_id from your existing implementation. (The value -1 is a dummy value.)

    5. Execute the script.

  4. Use the DDLimp utility to run the ddl_796.ctl script, which is located in the OracleBI\dwrep\Upgrade\CTLFiles folder.

    If you changed any of the preconfigured column definitions in the CTL file for your implementation, you need to edit the CTL file to reflect the change. For example, if you renamed or resized an existing column in W_DAY_D, you need to make the same change in the CTL file. If you do not make the change in the CTL file, when the CTL file is run, the column definition will revert to the preconfigured definition. (As a customization, if a column size was changed from VARCHAR(50) to VARCHAR(100), and the same change was not made in the CTL file before running, the column will revert to the preconfigured value of VARCHAR(50), which could cause data to be truncated in some databases.)

    Use the following command:

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

    For example:

    DDLIMP /U SADMIN /P SADMIN /C OBIA /G SSE_ROLE
    /I N /R Y /F C:\OracleBI\dwrep\ddl_796.ctl /L C:\OracleBI\dwrep\ddl_796.log
    
  5. Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade repository.

    1. Navigate to OracleBI\dwrep\Upgrade\DbScripts\<database type>.

    2. Open the reset_infa_seq_gen.bat file.

      The file opens a command prompt, which will prompt you for the parameters listed below.

    3. Enter the appropriate parameter values to reflect your environment (the parameters are database specific). For information about the settings to use, see Appendix D, "Database-Specific Parameter Settings for the reset_infa_seq_gen.bat File."

  6. Run the 796_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 796_UPGRADE_PRE_DIMENSION_SCRIPT.sql file, and copy the contents into the SQL client.

    4. Execute the script.

  7. Copy all of the domain value files in the folder \OracleBI\dwrep\Informatica\LkpFiles into the folder \Informatica PowerCenter 8.6\server\infa_shared\LkpFiles.

3.15.2 Migrating Data into the Upgraded Data Warehouse

Follow this procedure to migrate data into the upgraded data warehouse.

To migrate data into the upgraded data warehouse

  1. Copy the file Upgrade.rep file from the folder OracleBI\dwrep\Upgrade\Informatica\Repository into the folder Informatica PowerCenter 8.6\server\infa_shared\Backup.

  2. Launch the Informatica PowerCenter Administration Console and restore Upgrade.rep (located in Informatica PowerCenter 8.6\server\infa_shared\Backup).

  3. Navigate to the folder OracleBI\dwrep\Upgrade\Informatica\ParameterFiles and copy the file 796_UPG_PARAMS.txt into the SrcFiles folder on the Informatica Server machine, for example, C:\Program Files\Informatica PowerCenter 8.6\server\infa_shared\SrcFiles.

  4. In the 796_UPG_PARAMS.txt file, set the following parameters:

    1. $$ETL_PROC_WID. Set this parameter to the relevant value from the source system setup. You can get this value from W_PARAM_G.ETL_PROC_WID

    2. $$DATASOURCE_NUM_ID. Set this parameter to the relevant value from the source system setup.

    3. $$INITIAL_EXTRACT_DATE. Set this parameter to the initial extraction data of the data warehouse.

    4. $$WH_DATASOURCE_NUM_ID. Set this parameter to the data source number ID you have set up for the data warehouse.

    5. $$MASTER_ORG. Get this value from the Source System Parameters tab in DAC.

    6. $$INV_PROD_CAT_SET_ID1. Get this value from the Source System Parameters tab in DAC.

    7. $$PROD_CAT_SET_ID1. Get this value from the Source System Parameters tab in DAC.

    8. Set the parameter $$IS_SOURCE_PRE_80 to Y if your source OLTP application was on a version prior to Siebel 8.0 before you began the upgrade process. Otherwise, set this parameter to N.

  5. Configure common parameters specific to Siebel source systems. For instructions, see Section B.2, "Configuring Common Parameters for Siebel Source Systems."

  6. In Informatica Workflow Manager, open the Relational Connection Browser (in the menu bar, select Connections, and then select Relational), and edit the connect string, user and password for the relational connections as follows:

    1. Edit the connection PARAM_OLTP_SIEBEL to match your OLTP environment.

    2. Edit the connection PARAM_OLAP to match your OLAP environment.

    3. Edit the connection PARAM_DAC to match your DAC database.

    Note:

    If you are connected to an Oracle database, use the Oracle Native driver instead of ODBC.

    If you are connected to a SQL Server database, use the ODBC driver rather than the native SQL Server driver.

  7. In Informatica Workflow Manager, navigate to the folder UPGRADE_7951_to_796_SBL and execute the UPGRADE_DIMENSIONS and UPGRADE_FACTS workflows.

  8. Run the 796_UPGRADE_POST_SCRIPT.sql.

    1. Open the SQL client for your database type.

    2. Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\<database type>.

    3. Open the 796_UPGRADE_POST_SCRIPT.sql file, and copy the contents into the SQL client.

    4. Execute the script.

3.15.3 Verifying the Data Migrated Successfully

Follow this procedure to verify that the data was migrated successfully into the upgraded data warehouse.

To verify the data migrated successfully

  1. Check whether any of the Informatica mapping log files stored in the \Informatica PowerCenter 8.6\serva\infa_shared\Sesslogs directory indicates errors or failures.

  2. Check whether the following scripts that you ran in the SQL client of the database failed or errored out while executing:

    796_UPGRADE_DROP_INDEXES.sql

    796_UPGRADE_PRE_CTL_SCRIPT.sql

    796_UPGRADE_PRE_DIMENSION.sql

    796_UPGRADE_POST_SCRIPT.sql

  3. Check the log files for the ddl_796.ctl and Upgrade_796.ctl scripts that you ran using the DDLimp command to determine whether any of the runs failed.

    If you did not detect any errors or failures in the steps above, then the data migration was successful.

    Note:

    Ignore any error messages that indicate tables or indexes could not be dropped from the database. These error messages are displayed when the specified objects do not exist in the database. This can occur because each source system container holds a subset of all data warehouse tables
  4. If the data migration was successful, drop the tables that were created during the upgrade process, such as W_xxxx_x_79x, LKP_xxxx_x, 79x_xxxx_TMP, and TMP_xxxx_79x.

    This step frees the space occupied by these backup tables.

3.15.4 Resetting Refresh Dates

After verifying the data was migrated successfully into the upgraded data warehouse, follow this procedure to reset refresh dates.

  1. In Informatica Workflow Manager, navigate to the folder UPGRADE_7951_TO_796_SBL and execute the RESET_DAC_REFRESH_DATES workflow.

  2. Once the data migration steps above are complete, you can delete the Upgrade repository to avoid any accidental use or confusion.

    1. In the Informatica PowerCenter Administration Console, select the Upgrade repository service.

    2. In the General Properties area of the Properties tab, click Edit .

    3. Make sure the operating mode of the repository service is set to Exclusive.

    4. Click OK.

    5. Choose Actions, and then click Delete Contents.

    6. In the Delete contents for <repository name> dialog, enter the repository username and password (for example, Administrator/Administrator), then click OK.

3.16 Upgrading the Siebel Analytics Repository

This process merges your customizations of a prior release of the Siebel Analytics repository with the new version of the Oracle BI repository. Before you begin this process, make sure you have backed up and renamed your existing repository.

To upgrade the repository, perform the following tasks:

The tasks in this section refer to multiple versions of the Siebel Analytics and Oracle BI repositories. Table 3-1 provides the names and descriptions of the repositories used in the examples in this section.

Table 3-1 Names of Analytics Repositories used in Examples

Name of Repository Description

SiebelAnalytics_7x.rpd

The standard Siebel Analytics repository for the version you are upgrading from. This repository is referred to as the "original" repository in the examples in this section.

Note: Standard repositories from previous releases are available in the folder \OracleBI\Upgrade.

OracleBIAnalyticsApps.rpd

The standard Oracle BI repository for the version you are upgrading to.

Customer_SiebelAnalytics.rpd

The Siebel Analytics repository that contains your customizations for the version you are upgrading from.

Merged_Repository_OracleBI.rpd

The Oracle BI repository that contains your customizations for the version you are upgrading to.


3.16.1 Preparing for the Oracle BI Repository Upgrade

Follow this procedure to prepare for the repository upgrade.

To prepare for the Analytics repository upgrade

  1. Set up a directory for the merge process, such as \OracleBIUpgrade, and create the following subfolders:

    • Original

    • AfterEqualize

    • AfterMerge

    • AfterManualWork

    • AfterRegressions

  2. Copy the original repository (for example, SiebelAnalytics_7x.rpd), the production repository (for example, Customer_SiebelAnalytics.rpd, and the repository from the latest installation (for example, OracleBIAnalyticsApps.rpd) into the folder \OracleBIUpgrade\Original.

    If, in your current environment, you are running Siebel Analytics for one or more modules using a Siebel Analytics repository in which you extracted the corresponding projects for the modules from the standard Siebel Analytics repository file you received from the previous release, you need to extract the same projects from the SiebelAnalytics_7x.rpd file and use this as your original repository. (If you have the original repository that you extracted during the last upgrade, you can use it as the original repository file.) This will prevent you from losing any new metadata you would like to add in this upgrade.

    Also, if you customized the Siebel Analytics repository by trimming a large number of objects and you would like to get those objects back during the current upgrade, you need to trim the SiebelAnalytics_7x.rpd file in the same way and use the modified version as the original repository file. This will prevent you from losing any new metadata you would like to add in this upgrade.

3.16.2 Equalizing the Oracle BI Repositories

The Merge feature in the Administration Tool relies on a change detection algorithm to determine the changes that need to be made to upgrade repositories correctly. For the algorithm to work correctly, it has to determine which objects in the three repositories (for example, SiebelAnalytics_7x.rpd, OracleBIAnalyticsApps.rpd, and Customer_SiebelAnalytics.rpd) are equivalent.

The point of this step is to determine for every object in the OracleBIAnalyticsApps.rpd and the Customer_SiebelAnalytics.rpd whether it is coming from the SiebelAnalytics_7x.rpd.

Equivalence between objects is established using the Administration Tool's Equalize feature. The file that you specify in the Output option (-O) is the only file that is modified during the equalization process.

The Equalize feature has several mechanisms for determining whether an object in two different repositories is semantically the same:

  • Fully Qualified Name. If an object in one repository has the same fully qualified name as another object of the same class in another repository, then the two objects are declared equal.

  • Simple String Substitution. Equivalence can be declared between two objects of the same class in two repositories whose only difference is that some key characters in their names differ. The equalizerpds executable file ignores those characters while checking fully qualified names. For example, "Core"."W_DAY_D" might be considered equivalent to "Core"."W DAY D" if the characters "_" and " " have been declared as equivalent.

  • Rename File. When none of the preceding rules are applicable, equivalence can be manually declared using a script as input to the equalizerpds executable file. Oracle ships the rename files (MAP) for the major releases. The files are located in the OracleBI\Upgrade folder. You can also create your own rename files for customizations not covered in the files that Oracle ships. You can open and edit the rename files in Microsoft Excel.

The syntax of the equalizerpds command is as follows:

equalizerpds.exe  -A userid1 [-B [password1]] -C base_repository_name -D userid2 [-E [password2]] -F repository2_name [-J udml_utf8_file_name_equalization] [-O ouput_repository_name] [-X] [-Y equalStringSet]
-X          Treat 'Factxxxx' as 'Fact' in Business Model.
-Y          Treat the characters as equals.
/?          Display this usage information and exit.

To equalize a repository

  1. Copy the appropriate MAP file from the OracleBI\Upgrade folder into the folder where you will execute equalizerpds.exe, for example, \OracleBIUpgrade\Original.

  2. Run equalizerpds.exe to equalize the repository from the latest installation (for example, OracleBIAnalyticsApps.rpd) with the original repository (for example, SiebelAnalytics_7x.rpd). An example of the equalizerpds command is as follows:

    equalizerpds -A Administrator -B SADMIN
    -C \\OracleBIUpgrade\Original\SiebelAnalytics_7x.rpd
    -D Administrator -E SADMIN
    -F \\OracleBIUpgrade\Original\OracleBIAnalyticsApps.rpd
    -O \\OracleBIAnalyticsUpgrade\AfterEqualize\OracleBIAnalyticsApps.rpd
    -X -J rename7x-79.map
    

    The MAP files are located in the \OracleBI\Upgrade folder.

    If the equalizerpds.exe executable file runs correctly, no errors are returned.

  3. Run equalizerpds.exe to equalize your customized repository (for example, Customer_SiebelAnalytics.rpd) with the original repository (for example, SiebelAnalytics_7x.rpd). An example of the equalizerpds command is as follows:

    equalizerpds -A Administrator -B SADMIN
    -C \\OracleBIUpgrade\Original\SiebelAnalytics_7x.rpd
    -D Administrator -E SADMIN
    -F \\OracleBIUpgrade\Original\Customer_SiebelAnalytics.rpd
    -O \\OracleBIUpgrade\AfterEqualize\Customer_OracleBIAnalyticsApps.rpd
    

    The execution of equalizerpds that equalizes the customer repository with the original repository does not use the rename file.

    Make sure that the original repository is copied unchanged into its new location so that after running the script, all three repositories are contained within the \OracleBIUpgrade\AfterEqualize directory.

  4. To verify the process completed successfully, compare the size of the repositories. The output repository (-O) should be close to the same size as the repository you equalized (-F).

3.16.3 Comparing the Siebel Analytics and Oracle BI Repositories

Follow this procedure to compare your existing repository with the new version to which you are upgrading.

To compare the repositories

  • Use the Administration Tool's Compare Repositories feature to analyze the differences between your existing repository and the new version of the repository to which you are upgrading. Note where elements have been created, removed, or changed in the new version. Consider whether you can use the new metadata and retire customizations you made in the existing repository.

    For instructions on how to use the Administration Tool's Compare Repositories feature, see Oracle Business Intelligence Server Administration Guide.

3.16.4 Merging the Siebel Analytics and Oracle BI Repositories

In this procedure, you execute the main algorithm to upgrade the repository. For more information on merging the repositories, see Oracle Business Intelligence Server Administration Guide.

To merge versions of the repositories

  1. Copy the three repositories (for example, SiebelAnalytics_7x.rpd, OracleBIAnalyticsApps.rpd, and Customer_SiebelAnalytics.rpd) to the AfterMerge folder.

  2. Open the repository from the latest installation (for example, OracleBIAnalyticsApps.rpd) in the \OracleBIUpgrade\AfterMerge folder.

  3. Save the repository with a new name, for example, Merged_Repository_OracleBIAnalyticsApps.rpd.

    This new repository will contain the final results of the upgrade.

  4. From the Administration Tool menu bar, select File, then select Merge.

  5. In the Select Original Repository dialog box, select the original repository (for example, SiebelAnalytics_7x.rpd).

  6. Enter the password, and click OK.

  7. Click Select for the Modified Repository field.

  8. In the Select Modified Repository dialog box, select the repository that contains the customizations you made to the previous version of the Analytics repository.

  9. Click Open, type the password, and then click OK.

  10. In the Decision drop-down list, select the action you want to take regarding the repository change, or accept the default action.

  11. To locate subsequent rows with empty Decision fields, click the Decision header cell.

    When all rows have a value in the Decision field, the Merge button is enabled.

  12. Click Merge.

    This process can take up to 40 minutes, depending on the size of the repositories you are working with. A message will alert you when the merge is complete.

  13. Click Yes when asked if you want to run a consistency check.

    The number of errors returned by the consistency check is an indication of how successful the merge process was. If you receive many errors, for example, over 300, you should analyze the reason for the errors. If the merge process failed to recognize that two objects are the same, you may need to edit the rename file if the object is in the Current repository, or add your own rename file if you have renamed many of the objects and the upgrade engine failed to relate them to the original objects.

    You also may need to change the actions you selected in the Decision drop-down list before rerunning the merge. This could save you time by reducing the number of errors that you will need to fix manually.

    Once you are satisfied with the results of the merge, you should fix the remaining errors manually. It is important that you fix all errors before moving on to the next step. This repository serves as the input for the next stage.

    You should also check that all of your customized objects are present and that no duplicate physical tables were introduced. To check for duplicate tables, search for physical tables using a query such as:

    where name like '*#1'
    
  14. Copy the repository to the folder \OracleBIUpgrade\AfterManualWork.

3.16.5 Regression Testing the Oracle BI Repository Merge

In performing a regression test for the repository merge, the objective is to collect a set of logical SQL statements that are used for reports and to verify that they continue to work with the new metadata. For this purpose, it is recommended that you perform the following procedure.

To perform regression testing

  1. Run the reports that are necessary to include in the regression suite. These reports might be a subset of the reports in the Presentation Catalog.

  2. Collect the logical SQL generated in the previous step. You can do this using Usage Tracking or by parsing the query log file.

    For information about Usage Tracking, see Oracle Business Intelligence Server Administration Guide.

  3. Execute the logical SQL against the old repository using the command line utility nQCmd.exe located in \OracleBI\server\bin, and save the results to a file.

    For information about the nQCmd.exe utility, see Oracle Business Intelligence Server Administration Guide.

  4. Edit the logical SQL test scripts to account for the name changes or modifications resulting from the upgrade.

  5. Execute the edited logical SQL against the merged repository, and save the results.

  6. Compare the results from the steps above and try to explain the differences. If it is determined that these differences are due to the upgrade process, then you have to correct them manually.

    This repository now contains the merged content from the new OracleBIAnalyticsApps.rpd and the production repository.

3.17 Upgrading the Oracle BI Presentation Catalog

You will need to upgrade your current Oracle BI Presentation Catalog if your organization:

If you made no changes to the previous Presentation Catalog distributed with previous versions of prebuilt applications, you do not need to upgrade the catalog. You can begin using the newer version of the catalog.

This process includes the following tasks:

Caution:

In releases of Oracle BI Applications previous to 7.9, the Presentation Catalog (formerly known as the Siebel Analytics Web Catalog) was stored in a single file rather than in a directory structure of individual files. If you have a previous version of the Presentation Catalog, you will need to convert it to the new format. For more information about how to convert the Presentation Catalog to the new format, see the Oracle Business Intelligence Infrastructure Upgrade Guide

3.17.1 Trimming the Input Presentation Catalog

Before you upgrade and merge your current Presentation Catalog with the new Presentation Catalog, determine which of the existing content you want to keep and which new content you want to incorporate. Review your existing Presentation Catalog and determine the usage patterns of reports and dashboards. Note that some of the preconfigured content in the existing catalog may appear in the new version in a redesigned format. In addition, the new version includes completely new content. After you have decided the content that is to make up your enterprise Presentation Catalog, trim the input catalogs using the Catalog Manager. For information on trimming catalogs, see Oracle Business Intelligence Presentation Services Administration Guide.

3.17.2 Upgrading the Oracle BI Presentation Catalog to a Newer Version

The Presentation Catalog upgrade process makes use of three catalogs:

  • The original Presentation Catalog. This is the unmodified Presentation Catalog that you received with the Oracle BI Applications release that you are upgrading from.

  • The new Presentation Catalog. This is the Presentation Catalog that is installed in the OracleBIData\web\catalog folder with the installation of Oracle BI Applications.

  • The current Presentation Catalog. This is the Presentation Catalog currently in use at your organization.

You use Catalog Manager for this upgrade process. Catalog Manager compares the content in both the Current Presentation Catalog and the Modified Presentation Catalog with the content in the Original Presentation Catalog, merges any changes into the Current Presentation Catalog, and produces a list of upgrade differences, which you must resolve by indicating how you want the differences handled. If the catalogs have conflicting content, you can choose which catalog the content should be taken from. The end result is a merged Presentation Catalog that contains the site-specific changes, as well as new metadata.

To upgrade your Presentation Catalog to a newer version

  1. Make a backup copy of the current Presentation Catalog, rename the folder <catalogname>_old, and move it to a temporary location.

  2. Copy the original Presentation Catalog into the folder that holds your current Presentation Catalog and rename it <catalogname>_Original.

  3. Start Catalog Manager and open the new Presentation Catalog in offline mode.

  4. Select Tools, then select Upgrade Catalog.

  5. In the original Presentation Catalog field, browse to locate the original Presentation Catalog.

  6. In the Current Presentation Catalog field, browse to locate your current Web Catalog, <catalogname>_old.

  7. Click OK.

  8. Resolve any upgrade differences as follows:

    1. Review each unresolved difference in the Unresolved differences list.

    2. For each unresolved difference, select the version that you want to keep.

    3. Click OK.

    The log file SiebelAnalyticsMigrationLog.txt holds information about the merge process. This log file is written to \OracleBI\web\catalogmanager. If you get an error logged in the file, this means that the path in question had a problem that did not allow the merge mechanism to resolve the merge. No action was taken. To merge that particular item, go into your original Presentation Catalog and merge it manually.

  9. Review the upgraded Presentation Catalog, and, if necessary, set permissions for objects.

  10. Save the new Presentation Catalog.

3.17.3 Testing the Results of the Presentation Catalog Upgrade

Note:

Before you perform this step, you must first migrate the data into the upgraded data warehouse.

The Presentation Catalog upgrade functionality does not automatically carry over object permissions; therefore, you should review the Presentation Catalog object permissions before you perform this step.

This step ensures that the upgraded reports and the new preconfigured reports are functional and render correct results within the new, merged Presentation Catalog. This step is typically performed by visually inspecting the final results of the complete end-to-end upgrade process.

For upgraded reports, the preferred approach for comparison purposes is to have side-by-side environments, and have users review specific dashboard content between the two environments. Examine not only the look and feel of the application but also the data contained in the reports to make sure the content remains the same. It is recommended that you request users to use various elements of the user interface to validate results, such as global prompts, column selectors, report filters, drills, and navigations, as they normally do on a day-to-day basis.

Also review the overall visibility and administrative settings in the new Presentation Catalog to ensure they are correct. Pay careful attention to the visibility rules that are established for any content that was migrated during the upgrade. You might have to manually adjust these settings.