Oracle® Business Intelligence Applications Upgrade Guide > Upgrading Oracle BI Applications >
Migrating Data into the Upgraded Data Warehouse
To migrate data into the upgraded data warehouse, follow the appropriate procedure listed in Table 3 based on the version of Analytics from which you are upgrading.
Table 3. Procedures for Migrating Data
|
|
Siebel Analytics 7.5.x (Horizontal) |
|
Siebel Business Analytics 7.7, 7.7.1.x, or 7.8 (Horizontal) |
|
Siebel Industry Applications 7.5.x (Vertical) |
|
Siebel Industry Applications 7.7, 7.7.1.x, or 7.8 (Vertical) |
|
Life Sciences 7.8.0, 7.8.1, or 7.8.2 (Vertical) |
|
To migrate data from version 7.5.x to 7.9 (Horizontal)
NOTE: Upgrading from version 7.5.x requires two stages. In the first stage, you upgrade the data warehouse schema and migrate the data from version 7.5.x to 7.7. In the second stage, you upgrade the data warehouse schema and migrate the data from version 7.7 to 7.9.
- Upgrade the data warehouse schema from version 7.5.x to 7.7 by using the DDLimp utility to run the script ddlsme_HOR_77.ctl script.
- Navigate to the folder OracleBI\dwrep\Upgrade\CTLFiles.
- Use the DDLimp utility to run the ddlsme_HOR_77.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\ddlsme_HOR_77.ctl> /L <..\oracleBI\dwrep\ddlsme_HOR_77.log>
For example:
DDLIMP /U SADMIN /P SADMIN /C SIEBEL_OLTP /G SSE_ROLE /R Y /F C:\OracleBI\dwrep\ddlsme_HOR_77.ctl /L C:\OracleBI\dwrep\ddlsme_HOR_77.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.
- Copy the file Upgrade.rep file from the folder OracleBI\dwrep\Upgrade\Informatica\Repository into the folder Informatica PowerCenter 7.1.4\RepositoryServer\bin\Backup.
- Launch the Informatica Repository Console and restore Upgrade.rep (located in Informatica PowerCenter 7.1.4\RepositoryServer\bin\Backup).
- 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 7.1.4\Server\SrcFiles.
- Rename 753_TENERIFE_UPG_PARAMS.txt to TENERIFE_UPG_PARAMS.txt.
- In Informatica Workflow Manager, navigate to the folder UPGRADE_753_to_770_HOR, and execute the following workflows in the order indicated:
- Upgrade_R_Image
- Upgrade_Unspecifieds
- Upgrade_Agree
- Upgrade_Asset
- Upgrade_Opty
- Upgrade_Order
- Upgrade_Quote
- Upgrade_Response
- Upgrade_ServiceRequest
- Upgrade_Others
- Upgrade_Visibility
- UpgradeSlowlyChangingDimensionStartDates
- DAC_Metadata_Upgrade_Workflow
- Upgrade the data warehouse schema from version 7.7 to 7.9 by following the steps in the procedure To upgrade the data warehouse schema.
- 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 7.1.4\Server\SrcFiles.
- 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 7.1.4\Server\SrcFiles.
- Rename 770_TENERIFE_UPG_PARAMS.txt to TENERIFE_UPG_PARAMS.txt.
- In the file TENERIFE_UPG_PARAMS.txt:
- Search for the parameter $$SourceConnection.
- Set the value to one of the following options, based on your Siebel applications (OLTP) version:
$$SourceConnection=SEBL_VERT_753
- In the file TENERIFE_UPG_PARAMS.txt:
- Search for the parameter $$Source_Container.
- Set the value to one of the following options, based on your Siebel applications (OLTP) version:
$$Source_Container=Siebel 7.5.3 Vertical
- 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.
- In the Informatica Workflow Manager, open the Relational Connection Browser (Connections > Relational), and do the following:
- Edit the preconfigured ODBC connection PARAM_OLTP_SIEBEL to point to the Siebel transactional database.
- Edit the preconfigured ODBC connection DataWarehouse to point to the newly upgraded data warehouse database.
- Edit the preconfigured ODBC connection PARAM_DAC_OLD to point to the previous DAC repository database (the version from which you are upgrading).
- Edit the preconfigured ODBC connection PARAM_DAC_NEW to point to the new DAC repository database (the version to which you are upgrading).
- In Informatica Workflow Manager, navigate to the folder UPGRADE_770_to_79, and execute the following workflows in the order indicated:
- MARKETING_LOAD
- Update_Dimensions
- Update_Dimension_Unspecified
- Update_Facts
- SIL_PositionDimensionHierarchy_Full
- Load_INT_ORG_DH
- DIMENSION_LOAD
- DIMENSION_UNSPECIFIED_UPDATE
- FACT_UPDATE
- Run the Reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs:
- Navigate to the directory \OracleBI\dwrep\Upgrade\DbScripts\<database type>.
- Open the Reset_infa_seq_gen.bat script, and review the parameters at the top of the file.
- Replace the existing values for the parameters to reflect your environment (the parameters are database-specific).
For Oracle databases:
|
|
TARGET_USERID_PWD
|
<olap schema user-name>/<olap schema password>@<olap database TNS name>
For example: TARGET_USERID_PWD=siebel/siebel@siebel_olap
|
INFA_USERID_PWD
|
<user-name of Informatica Repository database that contains the adapter mappings repository metadata>/<password of the Informatica Repository database>@<TNS name of the database for the Informatica Repository>
For example: INFA_USERID_PWD=INFAREP/INFAREP@infa.corp.siebel.com
|
INFA_REP
|
Name of the Informatica Repository where the adapter mappings are available. |
INFA_U
|
User name of the administrator of the Informatica Repository. |
INFA_P
|
Password for the administrator of the Informatica Repository. |
INFA_SRV
|
Name of the Informatica Server. |
For Microsoft SQL Server databases:
|
|
OLAP_SERVER
|
Name of the SQL Server for the OLAP database. |
OLAP_DB
|
Name of the database on which OLAP data is available. |
OLAP_U
|
User name for logging into the database on which OLAP data is available. |
OLAP_P
|
Password for logging into the database on which OLAP data is available. |
INFA_SERVER
|
Name of the SQL Server for the Informatica Repository database. |
INFA_DB
|
Name of the database on which Informatica Repository metadata is available. |
INFA_U
|
User name for logging into the database on which Informatica Repository metadata is available. |
INFA_P
|
Password for logging into the database on which Informatica Repository metadata is available. |
INFA_REP
|
Name of the Informatica Repository where adapter mappings are available. |
INFA_REP_U
|
User name of the administrator of the Informatica Repository. |
INFA_REP_P
|
Password of the administrator of the Informatica Repository. |
For IBM DB2 databases:
|
|
TARGET_USERID_PWD
|
<olap schema user-name>/<olap schema password>@<olap database name>
For example: TARGET_USERID_PWD=siebel/db2@OLAP_DB2DB
|
INFA_USER
|
User name for logging into the database on which Informatica metadata is available. |
INFA_PWD
|
Password for logging into the database on which Informatica Repository metadata is available. |
INFADB_ALIAS
|
Name of the database in which Informatica Repository metadata is available. |
INFA_REP
|
Name of the Informatica Repository where the adapter mappings are available. |
INFA_U
|
User name of the administrator of the Informatica Repository. |
INFA_P
|
Password of the administrator of the Informatica Repository. |
INFA_SRV
|
Name of the Informatica Server. |
- Run the script.
NOTE: Because the Reset_infa_seq_gen.bat file contains user names and passwords, it is recommended that you delete the file after the upgrade process is complete.
- To verify the data migrated successfully:
- Check whether any of the Informatica mapping log files stored in the directory \Informatica PowerCenter 7.1.4\Server\SessLogs indicates errors or failures.
- 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.
- 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.
- (Optional) 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.
To migrate data from versions 7.7, 7.7.1.x, or 7.8 to 7.9 (Horizontal)
- Copy the file Upgrade.rep file from the folder OracleBI\dwrep\Upgrade\Informatica\Repository into the folder Informatica PowerCenter 7.1.4\RepositoryServer\bin\Backup.
- Launch the Informatica Repository Console and restore Upgrade.rep (located in Informatica PowerCenter 7.1.4\RepositoryServer\bin\Backup).
- 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 7.1.4\Server\SrcFiles.
- Navigate to the folder OracleBI\dwrep\Upgrade\Informatica\ParameterFiles and copy the file <previous version of Siebel Analytics>_TENERIFE_UPG_PARAMS.txt into the SrcFiles folder on the Informatica Server machine, for example, C:\Program Files\Informatica PowerCenter 7.1.4\Server\SrcFiles.
- Rename the file <previous version of Siebel Analytics>_TENERIFE_UPG_PARAMS.txt to TENERIFE_UPG_PARAMS.txt.
- In the file TENERIFE_UPG_PARAMS.txt:
- Search for the parameter $$SourceConnection.
- Set the value to one of the following options, based on your Siebel applications (OLTP) version:
$$SourceConnection=SEBL_VERT_753
- In the file TENERIFE_UPG_PARAMS.txt:
- Search for the parameter $$Source_Container.
- Set the value to one of the following options, based on your Siebel applications (OLTP) version:
$$Source_Container=Siebel 7.5.3 Vertical
- 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.
- In the Informatica Workflow Manager, open the Relational Connection Browser (Connections > Relational), and do the following:
- Edit the preconfigured ODBC connection PARAM_OLTP_SIEBEL to point to the Siebel transactional database.
- Edit the preconfigured ODBC connection DataWarehouse to point to the newly upgraded data warehouse database.
- Edit the preconfigured ODBC connection PARAM_DAC_OLD to point to the previous DAC repository database (the version from which you are upgrading).
- Edit the preconfigured ODBC connection PARAM_DAC_NEW to point to the new DAC repository database (the version to which you are upgrading).
- In the Informatica Workflow Manager, navigate to the appropriate folder and execute the workflows in the order specified:
|
|
|
7.7 to 7.9 (Horizontal) |
UPGRADE_770_to_79 |
- MARKETING_LOAD
- Update_Dimensions
- Update_Dimension_Unspecified
- Update_Facts
- SIL_PositionDimensionHierarchy_Full
- Load_INT_ORG_DH
- DIMENSION_LOAD
- DIMENSION_UNSPECIFIED_UPDATE
- FACT_UPDATE
- DAC_Metadata_Upgrade_Workflow
|
7.7.1.x to 7.9 (Horizontal) |
UPGRADE_771_to_79 |
- Update_Dimensions
- Update_Dimension_Unspecified
- Update_Facts
- SIL_PositionDimensionHierarchy_Full
- Load_INT_ORG_DH
- DIMENSION_LOAD
- DIMENSION_UNSPECIFIED_UPDATE
- FACT_UPDATE
- DAC_Metadata_Upgrade_Workflow
|
7.8 to 7.9 (Horizontal) |
UPGRADE_78_to_79 |
- SIL_PositionDimensionHierarchy_Full
- Load_INT_ORG_DH
- DIMENSION_LOAD
- DIMENSION_UNSPECIFIED_UPDATE
- FACT_UPDATE
- DAC_Metadata_Upgrade_Workflow
|
- Run the Reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs:
- Navigate to the directory \OracleBI\dwrep\Upgrade\DbScripts\<database type>.
- Open the Reset_infa_seq_gen.bat script, and review the parameters at the top of the file.
- Replace the existing values for the parameters to reflect your environment (the parameters are database-specific).
For Oracle databases:
|
|
TARGET_USERID_PWD
|
<olap schema user-name>/<olap schema password>@<olap database TNS name>
For example: TARGET_USERID_PWD=siebel/siebel@siebel_olap
|
INFA_USERID_PWD
|
<user-name of Informatica Repository database that contains the adapter mappings repository metadata>/<password of the Informatica Repository database>@<TNS name of the database for the Informatica Repository>
For example: INFA_USERID_PWD=INFAREP/INFAREP@infa.corp.siebel.com
|
INFA_REP
|
Name of the Informatica Repository where the adapter mappings are available. |
INFA_U
|
User name of the administrator of the Informatica Repository. |
INFA_P
|
Password for the administrator of the Informatica Repository. |
INFA_SRV
|
Name of the Informatica Server. |
For Microsoft SQL Server databases:
|
|
OLAP_SERVER
|
Name of the SQL Server for the OLAP database. |
OLAP_DB
|
Name of the database on which OLAP data is available. |
OLAP_U
|
User name for logging into the database on which OLAP data is available. |
OLAP_P
|
Password for logging into the database on which OLAP data is available. |
INFA_SERVER
|
Name of the SQL Server for the Informatica Repository database. |
INFA_DB
|
Name of the database on which Informatica Repository metadata is available. |
INFA_U
|
User name for logging into the database on which Informatica Repository metadata is available. |
INFA_P
|
Password for logging into the database on which Informatica Repository metadata is available. |
INFA_REP
|
Name of the Informatica Repository where adapter mappings are available. |
INFA_REP_U
|
User name of the administrator of the Informatica Repository. |
INFA_REP_P
|
Password of the administrator of the Informatica Repository. |
For IBM DB2 databases:
|
|
TARGET_USERID_PWD
|
<olap schema user-name>/<olap schema password>@<olap database name>
For example: TARGET_USERID_PWD=siebel/db2@OLAP_DB2DB
|
INFA_USER
|
User name for logging into the database on which Informatica metadata is available. |
INFA_PWD
|
Password for logging into the database on which Informatica Repository metadata is available. |
INFADB_ALIAS
|
Name of the database in which Informatica Repository metadata is available. |
INFA_REP
|
Name of the Informatica Repository where the adapter mappings are available. |
INFA_U
|
User name of the administrator of the Informatica Repository. |
INFA_P
|
Password of the administrator of the Informatica Repository. |
INFA_SRV
|
Name of the Informatica Server. |
- Run the script.
NOTE: Because the Reset_infa_seq_gen.bat file contains user names and passwords, it is recommended that you delete the file after the upgrade process is complete.
- To verify the data migrated successfully:
- Check whether any of the Informatica mapping log files stored in the directory \Informatica PowerCenter 7.1.4\Server\SessLogs indicates errors or failures.
- 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.
- 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.
- (Optional) 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.
To migrate data from version 7.5.x to 7.9 (Vertical)
NOTE: Upgrading from version 7.5.x requires two stages. In the first stage, you upgrade the data warehouse schema and migrate the data from version 7.5.x to 7.7. In the second stage, you upgrade the data warehouse schema and migrate the data from version 7.7 to 7.9.
- Upgrade the data warehouse schema from version 7.5.x to 7.7 by using the DDLimp utility to run the script ddlsme_SIA_77.ctl script.
- Navigate to the folder OracleBI\dwrep\Upgrade\CTLFiles.
- Use the DDLimp utility to run the ddlsme_SIA_77.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\ddlsme_SIA_77.ctl> /L <..\oracleBI\dwrep\ddlsme_SIA_77.log>
For example:
DDLIMP /U SADMIN /P SADMIN /C SIEBEL_OLTP /G SSE_ROLE /R Y /F C:\OracleBI\dwrep\ddlsme_SIA_77.ctl /L C:\OracleBI\dwrep\ddlsme_SIA_77.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.
- Copy the file Upgrade.rep file from the folder OracleBI\dwrep\Upgrade\Informatica\Repository into the folder Informatica PowerCenter 7.1.4\RepositoryServer\bin\Backup.
- Launch the Informatica Repository Console and restore Upgrade.rep (located in Informatica PowerCenter 7.1.4\RepositoryServer\bin\Backup).
- Navigate to the folder OracleBI\dwrep\Upgrade\Informatica\ParameterFiles and copy the file 753_TENERIFE_UPG_PARAMS.txt into the SrcFiles directory on the Informatica Server machine, for example, C:\Program Files\Informatica PowerCenter 7.1.4\Server\SrcFiles.
- Rename the file 753_TENERIFE_UPG_PARAMS.txt to TENERIFE_UPG_PARAMS.txt.
- In Informatica Workflow Manager, navigate to the folder UPGRADE_753_to_770_SIA, and execute the following workflows in the order indicated:
- Upgrade_R_Image
- Upgrade_Unspecifieds
- Upgrade_Agree
- Upgrade_Asset
- Upgrade_Opty
- Upgrade_Order
- Upgrade_Quote
- Upgrade_Response
- Upgrade_ServiceRequest
- Upgrade_Others
- Upgrade_Visibility
- UpgradeSlowlyChangingDimensionStartDates
- Upgrade_Industry_R_Image
- Upgrade_Industry_Unspecified
- Upgrade_LS_ActivityProduct
- Upgrade_LS_Others
- Upgrade_Industry_Household
- Upgrade_FINS_Visibility
- UpgradeSlowlyChangingDimensionStartDates_Industry
- DAC_Metadata_Upgrade_Workflow
- Upgrade the data warehouse schema from version 7.7 to 7.9 by following the steps in the procedure To upgrade the data warehouse schema.
- 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 7.1.4\Server\SrcFiles.
- 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 7.1.4\Server\SrcFiles.
- Rename 770_TENERIFE_UPG_PARAMS.txt to TENERIFE_UPG_PARAMS.txt.
- In the file TENERIFE_UPG_PARAMS.txt:
- Search for the parameter $$SourceConnection.
- Set the value to one of the following options, based on your Siebel applications (OLTP) version:
$$SourceConnection=SEBL_VERT_753
- In the file TENERIFE_UPG_PARAMS.txt:
- Search for the parameter $$Source_Container.
- Set the value to one of the following options, based on your Siebel applications (OLTP) version:
$$Source_Container=Siebel 7.5.3 Vertical
- 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.
- In the Informatica Workflow Manager, open the Relational Connection Browser (Connections > Relational), and do the following:
- Edit the preconfigured ODBC connection PARAM_OLTP_SIEBEL to point to the Siebel transactional database.
- Edit the preconfigured ODBC connection DataWarehouse to point to the newly upgraded data warehouse database.
- Edit the preconfigured ODBC connection PARAM_DAC_OLD to point to the previous DAC repository database (the version from which you are upgrading).
- Edit the preconfigured ODBC connection PARAM_DAC_NEW to point to the new DAC repository database (the version to which you are upgrading).
- Define the alignment rule to be used for ETL loads.
- 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 7.1.4\Server\SrcFiles.
- Rename the file AlignmentType_LS_782.csv to AlignmentType.csv.
- Open the file AlignmentType.csv, and enter one of the following alignment item types under the ALIGN_TYPE field:
- Zipcode
- Account/Contact
- Brick
- Save the file.
NOTE: The Oracle Business Analytics Warehouse allows only one alignment type to be used for ETL loads during upgrade.
- In Informatica Workflow Manager, navigate to the folder UPGRADE_770_to_79, and execute the following workflows in the order indicated:
- MARKETING_LOAD
- Upgrade_Dimensions_Industry
- Update_Dimensions
- Update_Dimension_Unspecified
- Update_Facts
- Upgrade_LS_Dimensions
- Upgrade_LS_Facts
- SIL_PositionDimensionHierarchy_Full
- Load_INT_ORG_DH
- DIMENSION_LOAD
- DIMENSION_UNSPECIFIED_UPDATE
- FACT_UPDATE
- Run the Reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs:
- Navigate to the directory \OracleBI\dwrep\Upgrade\DbScripts\<database type>.
- Open the Reset_infa_seq_gen.bat script, and review the parameters at the top of the file.
- Replace the existing values for the parameters to reflect your environment (the parameters are database-specific).
For Oracle databases:
|
|
TARGET_USERID_PWD
|
<olap schema user-name>/<olap schema password>@<olap database TNS name>
For example: TARGET_USERID_PWD=siebel/siebel@siebel_olap
|
INFA_USERID_PWD
|
<user-name of Informatica Repository database that contains the adapter mappings repository metadata>/<password of the Informatica Repository database>@<TNS name of the database for the Informatica Repository>
For example: INFA_USERID_PWD=INFAREP/INFAREP@infa.corp.siebel.com
|
INFA_REP
|
Name of the Informatica Repository where the adapter mappings are available. |
INFA_U
|
User name of the administrator of the Informatica Repository. |
INFA_P
|
Password for the administrator of the Informatica Repository. |
INFA_SRV
|
Name of the Informatica Server. |
For Microsoft SQL Server databases:
|
|
OLAP_SERVER
|
Name of the SQL Server for the OLAP database. |
OLAP_DB
|
Name of the database on which OLAP data is available. |
OLAP_U
|
User name for logging into the database on which OLAP data is available. |
OLAP_P
|
Password for logging into the database on which OLAP data is available. |
INFA_SERVER
|
Name of the SQL Server for the Informatica Repository database. |
INFA_DB
|
Name of the database on which Informatica Repository metadata is available. |
INFA_U
|
User name for logging into the database on which Informatica Repository metadata is available. |
INFA_P
|
Password for logging into the database on which Informatica Repository metadata is available. |
INFA_REP
|
Name of the Informatica Repository where adapter mappings are available. |
INFA_REP_U
|
User name of the administrator of the Informatica Repository. |
INFA_REP_P
|
Password of the administrator of the Informatica Repository. |
For IBM DB2 databases:
|
|
TARGET_USERID_PWD
|
<olap schema user-name>/<olap schema password>@<olap database name>
For example: TARGET_USERID_PWD=siebel/db2@OLAP_DB2DB
|
INFA_USER
|
User name for logging into the database on which Informatica metadata is available. |
INFA_PWD
|
Password for logging into the database on which Informatica Repository metadata is available. |
INFADB_ALIAS
|
Name of the database in which Informatica Repository metadata is available. |
INFA_REP
|
Name of the Informatica Repository where the adapter mappings are available. |
INFA_U
|
User name of the administrator of the Informatica Repository. |
INFA_P
|
Password of the administrator of the Informatica Repository. |
INFA_SRV
|
Name of the Informatica Server. |
- Run the script.
NOTE: Because the Reset_infa_seq_gen.bat file contains user names and passwords, it is recommended that you delete the file after the upgrade process is complete.
- To verify the data migrated successfully:
- Check whether any of the Informatica mapping log files stored in the directory \Informatica PowerCenter 7.1.4\Server\SessLogs indicates errors or failures.
- 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.
- 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.
- (Optional) 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.
To migrate data from versions 7.7, 7.7.1.x, or 7.8 to 7.9 (Vertical)
- Copy the file Upgrade.rep file from the folder OracleBI\dwrep\Upgrade\Informatica\Repository into the folder Informatica PowerCenter 7.1.4\RepositoryServer\bin\Backup.
- Launch the Informatica Repository Console and restore Upgrade.rep (located in Informatica PowerCenter 7.1.4\RepositoryServer\bin\Backup).
- 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 7.1.4\Server\SrcFiles.
- Navigate to the folder OracleBI\dwrep\Upgrade\Informatica\ParameterFiles and copy the file <previous version of Siebel Analytics>_TENERIFE_UPG_PARAMS.txt into the SrcFiles folder on the Informatica Server machine, for example, C:\Program Files\Informatica PowerCenter 7.1.4\Server\SrcFiles.
- Rename the file <previous version of Siebel Analytics>_TENERIFE_UPG_PARAMS.txt to TENERIFE_UPG_PARAMS.txt.
- In the file TENERIFE_UPG_PARAMS.txt:
- Search for the parameter $$SourceConnection.
- Set the value to one of the following options, based on your Siebel applications (OLTP) version:
$$SourceConnection=SEBL_VERT_753
- In the file TENERIFE_UPG_PARAMS.txt:
- Search for the parameter $$Source_Container.
- Set the value to one of the following options, based on your Siebel applications (OLTP) version:
$$Source_Container=Siebel 7.5.3 Vertical
- 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.
- In the Informatica Workflow Manager, open the Relational Connection Browser (Connections > Relational), and do the following:
- Edit the preconfigured ODBC connection PARAM_OLTP_SIEBEL to point to the Siebel transactional database.
- Edit the preconfigured ODBC connection DataWarehouse to point to the newly upgraded data warehouse database.
- Edit the preconfigured ODBC connection PARAM_DAC_OLD to point to the previous DAC repository database (the version from which you are upgrading).
- Edit the preconfigured ODBC connection PARAM_DAC_NEW to point to the new DAC repository database (the version to which you are upgrading).
- Define the alignment rule to be used for ETL loads.
- 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 7.1.4\Server\SrcFiles.
- Rename the file AlignmentType_LS_782.csv to AlignmentType.csv.
- Open the file AlignmentType.csv, and enter one of the following alignment item types under the ALIGN_TYPE field:
- Zipcode
- Account/Contact
- Brick
- Save the file.
NOTE: The Oracle Business Analytics Warehouse allows only one alignment type to be used for ETL loads during upgrade.
- In Informatica Workflow Manager, navigate to the appropriate folder and execute the workflows in the order specified:
|
|
|
7.7 to 7.9 (Vertical) |
UPGRADE_770_to_79 |
- MARKETING_LOAD
- Upgrade_Dimensions_Industry
- Update_Dimensions
- Update_Dimension_Unspecified
- Update_Facts
- Upgrade_LS_Dimensions
- Upgrade_LS_Facts
- SIL_PositionDimensionHierarchy_Full
- Load_INT_ORG_DH
- DIMENSION_LOAD
- DIMENSION_UNSPECIFIED_UPDATE
- FACT_UPDATE
- DAC_Metadata_Upgrade_Workflow
|
7.7.1.x to 7.9 (Vertical) |
UPGRADE_771_to_79 |
- Update_Dimensions
- Update_Dimension_Unspecified
- Update_Facts
- Upgrade_LS_Dimensions
- Upgrade_LS_Facts
- SIL_PositionDimensionHierarchy_Full
- Load_INT_ORG_DH
- DIMENSION_LOAD
- DIMENSION_UNSPECIFIED_UPDATE
- FACT_UPDATE
- DAC_Metadata_Upgrade_Workflow
|
7.8 to 7.9 (Vertical) |
UPGRADE_78_to_79 |
- SIL_PositionDimensionHierarchy_Full
- Load_INT_ORG_DH
- DIMENSION_LOAD
- DIMENSION_UNSPECIFIED_UPDATE
- FACT_UPDATE
- DAC_Metadata_Upgrade_Workflow
|
- Run the Reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs:
- Navigate to the directory \OracleBI\dwrep\Upgrade\DbScripts\<database type>.
- Open the Reset_infa_seq_gen.bat script, and review the parameters at the top of the file.
- Replace the existing values for the parameters to reflect your environment (the parameters are database-specific).
For Oracle databases:
|
|
TARGET_USERID_PWD
|
<olap schema user-name>/<olap schema password>@<olap database TNS name>
For example: TARGET_USERID_PWD=siebel/siebel@siebel_olap
|
INFA_USERID_PWD
|
<user-name of Informatica Repository database that contains the adapter mappings repository metadata>/<password of the Informatica Repository database>@<TNS name of the database for the Informatica Repository>
For example: INFA_USERID_PWD=INFAREP/INFAREP@infa.corp.siebel.com
|
INFA_REP
|
Name of the Informatica Repository where the adapter mappings are available. |
INFA_U
|
User name of the administrator of the Informatica Repository. |
INFA_P
|
Password for the administrator of the Informatica Repository. |
INFA_SRV
|
Name of the Informatica Server. |
For Microsoft SQL Server databases:
|
|
OLAP_SERVER
|
Name of the SQL Server for the OLAP database. |
OLAP_DB
|
Name of the database on which OLAP data is available. |
OLAP_U
|
User name for logging into the database on which OLAP data is available. |
OLAP_P
|
Password for logging into the database on which OLAP data is available. |
INFA_SERVER
|
Name of the SQL Server for the Informatica Repository database. |
INFA_DB
|
Name of the database on which Informatica Repository metadata is available. |
INFA_U
|
User name for logging into the database on which Informatica Repository metadata is available. |
INFA_P
|
Password for logging into the database on which Informatica Repository metadata is available. |
INFA_REP
|
Name of the Informatica Repository where adapter mappings are available. |
INFA_REP_U
|
User name of the administrator of the Informatica Repository. |
INFA_REP_P
|
Password of the administrator of the Informatica Repository. |
For IBM DB2 databases:
|
|
TARGET_USERID_PWD
|
<olap schema user-name>/<olap schema password>@<olap database name>
For example: TARGET_USERID_PWD=siebel/db2@OLAP_DB2DB
|
INFA_USER
|
User name for logging into the database on which Informatica metadata is available. |
INFA_PWD
|
Password for logging into the database on which Informatica Repository metadata is available. |
INFADB_ALIAS
|
Name of the database in which Informatica Repository metadata is available. |
INFA_REP
|
Name of the Informatica Repository where the adapter mappings are available. |
INFA_U
|
User name of the administrator of the Informatica Repository. |
INFA_P
|
Password of the administrator of the Informatica Repository. |
INFA_SRV
|
Name of the Informatica Server. |
- Run the script.
NOTE: Because the Reset_infa_seq_gen.bat file contains user names and passwords, it is recommended that you delete the file after the upgrade process is complete.
- To verify the data migrated successfully:
- Check whether any of the Informatica mapping log files stored in the directory \Informatica PowerCenter 7.1.4\Server\SessLogs indicates errors or failures.
- 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.
- 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.
- (Optional) 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.
To migrate data from Life Sciences 7.8.0, 7.8.1 or 7.8.2 to 7.9
- Copy the file Upgrade.rep file from the folder OracleBI\dwrep\Upgrade\Informatica\Repository into the folder Informatica PowerCenter 7.1.4\RepositoryServer\bin\Backup.
- Launch the Informatica Repository Console and restore Upgrade.rep (located in Informatica PowerCenter 7.1.4\RepositoryServer\bin\Backup).
- 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 7.1.4\Server\SrcFiles.
- Navigate to the folder OracleBI\dwrep\Upgrade\Informatica\ParameterFiles and copy the file 782_TENERIFE_UPG_PARAMS_LS.txt into the SrcFiles folder on the Informatica Server machine, for example, C:\Program Files\Informatica PowerCenter 7.1.4\Server\SrcFiles.
- Rename the file 782_TENERIFE_UPG_PARAMS_LS.txt to TENERIFE_UPG_PARAMS.txt.
- In the file TENERIFE_UPG_PARAMS.txt:
- Search for the parameter $$SourceConnection.
- Set the value to one of the following options, based on your Siebel applications (OLTP) version:
$$SourceConnection=SEBL_VERT_753
- In the file TENERIFE_UPG_PARAMS.txt:
- Search for the parameter $$Source_Container.
- Set the value to one of the following options, based on your Siebel applications (OLTP) version:
$$Source_Container=Siebel 7.5.3 Vertical
- 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.
- In the Informatica Workflow Manager, open the Relational Connection Browser (Connections > Relational), and do the following:
- Edit the preconfigured ODBC connection PARAM_OLTP_SIEBEL to point to the Siebel transactional database.
- Edit the preconfigured ODBC connection DataWarehouse to point to the newly upgraded data warehouse database.
- Edit the preconfigured ODBC connection PARAM_DAC_OLD to point to the previous DAC repository database (the version from which you are upgrading).
- Edit the preconfigured ODBC connection PARAM_DAC_NEW to point to the new DAC repository database (the version to which you are upgrading).
- Define the alignment rule to be used for ETL loads.
- 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 7.1.4\Server\SrcFiles.
- Rename the file AlignmentType_LS_782.csv to AlignmentType.csv.
- Open the file AlignmentType.csv, and enter one of the following alignment item types under the ALIGN_TYPE field:
- Zipcode
- Account/Contact
- Brick
- Save the file.
NOTE: The Oracle Business Analytics Warehouse allows only one alignment type to be used for ETL loads during upgrade.
- In Informatica Workflow Manager, navigate to the folder UPGRADE_LS_782_to_79, and execute the following workflows in the order indicated:
- Upgrade_LS_Dimensions
- Upgrade_LS_Facts
- SIL_PositionDimensionHierarchy_Full
- Load_INT_ORG_DH
- DIMENSION_LOAD
- DIMENSION_UNSPECIFIED_UPDATE
- FACT_UPDATE
- DAC_Metadata_Upgrade_Workflow
- Run the Reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs:
- Navigate to the directory \OracleBI\dwrep\Upgrade\DbScripts\<database type>.
- Open the Reset_infa_seq_gen.bat script, and review the parameters at the top of the file.
- Replace the existing values for the parameters to reflect your environment (the parameters are database-specific).
For Oracle databases:
|
|
TARGET_USERID_PWD
|
<olap schema user-name>/<olap schema password>@<olap database TNS name>
For example: TARGET_USERID_PWD=siebel/siebel@siebel_olap
|
INFA_USERID_PWD
|
<user-name of Informatica Repository database that contains the adapter mappings repository metadata>/<password of the Informatica Repository database>@<TNS name of the database for the Informatica Repository>
For example: INFA_USERID_PWD=INFAREP/INFAREP@infa.corp.siebel.com
|
INFA_REP
|
Name of the Informatica Repository where the adapter mappings are available. |
INFA_U
|
User name of the administrator of the Informatica Repository. |
INFA_P
|
Password for the administrator of the Informatica Repository. |
INFA_SRV
|
Name of the Informatica Server. |
For Microsoft SQL Server databases:
|
|
OLAP_SERVER
|
Name of the SQL Server for the OLAP database. |
OLAP_DB
|
Name of the database on which OLAP data is available. |
OLAP_U
|
User name for logging into the database on which OLAP data is available. |
OLAP_P
|
Password for logging into the database on which OLAP data is available. |
INFA_SERVER
|
Name of the SQL Server for the Informatica Repository database. |
INFA_DB
|
Name of the database on which Informatica Repository metadata is available. |
INFA_U
|
User name for logging into the database on which Informatica Repository metadata is available. |
INFA_P
|
Password for logging into the database on which Informatica Repository metadata is available. |
INFA_REP
|
Name of the Informatica Repository where adapter mappings are available. |
INFA_REP_U
|
User name of the administrator of the Informatica Repository. |
INFA_REP_P
|
Password of the administrator of the Informatica Repository. |
For IBM DB2 databases:
|
|
TARGET_USERID_PWD
|
<olap schema user-name>/<olap schema password>@<olap database name>
For example: TARGET_USERID_PWD=siebel/db2@OLAP_DB2DB
|
INFA_USER
|
User name for logging into the database on which Informatica metadata is available. |
INFA_PWD
|
Password for logging into the database on which Informatica Repository metadata is available. |
INFADB_ALIAS
|
Name of the database in which Informatica Repository metadata is available. |
INFA_REP
|
Name of the Informatica Repository where the adapter mappings are available. |
INFA_U
|
User name of the administrator of the Informatica Repository. |
INFA_P
|
Password of the administrator of the Informatica Repository. |
INFA_SRV
|
Name of the Informatica Server. |
- Run the script.
NOTE: Because the Reset_infa_seq_gen.bat file contains user names and passwords, it is recommended that you delete the file after the upgrade process is complete.
- To verify the data migrated successfully:
- Check whether any of the Informatica mapping log files stored in the directory \Informatica PowerCenter 7.1.4\Server\SessLogs indicates errors or failures.
- 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.
- 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.
- (Optional) 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.
|