| Oracle® Business Intelligence Applications Release Notes Release 7.9 B31981-09 |
|
 Previous |
| Oracle® Business Intelligence Applications Release Notes Release 7.9 B31981-09 |
|
Previous |
These release notes describe known issues and workarounds for versions 7.9, 7.9.1, 7.9.2, and 7.9.3, and contains the following sections:
Section 1.3, "Oracle Business Intelligence Applications: General"
Section 1.4, "Oracle Business Intelligence Pharma Analytics"
These release notes are updated periodically as new information becomes available. To ensure that you are reading the latest version of the release notes, check the Oracle Business Intelligence Applications Documentation Web site:
This section describes general issues and workarounds for Oracle Business Intelligence Applications products. It contains the following topics:
Section 1.2.4, "Support for Oracle Financial Services Applications (OFSA)"
Section 1.2.5, "Support for Oracle's PeopleSoft HR and PeopleSoft Financials"
Section 1.2.6, "Security Support In Oracle's Siebel CRM Applications"
Section 1.2.7, "Enhancements to the Oracle BI Applications Data Warehouse Administration Console"
For certification information, refer to the System Requirements and Supported Platforms for Oracle Business Intelligence Applications document. This document is part of the Oracle Business Intelligence Applications documentation set. It is also available from the Certify tab on Oracle Metalink at the following location:
This section provides release notes on installing and upgrading Oracle Business Intelligence Applications. It contains the following topics:
Section 1.2.2.1, "Maintain Side-By-Side Environments When Upgrading"
Section 1.2.2.3, "Issues Upgrading Repository for Siebel CRM Analytics Suite"
Section 1.2.2.4, "Upgrading From Version Oracle BI Applications 7.9 to Oracle BI Applications 7.9.3"
Section 1.2.2.5, "Installation Of The Informatica EBF 108240 Patch"
Section 1.2.2.7, "ACCT_DOC_ID Column Is Truncated In W_GL_REVN_F For PeopleSoft"
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
If you are upgrading from a previous release, then include in your upgrade preparations, plans for side-by-side instances of the entire Oracle BI Applications environment, not just for the Oracle Business Intelligence Presentation Catalog.
Enabling side-by-side instances of the entire Oracle BI Applications environment is a critical success factor for upgrade.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
There is no distinction between Data Warehouse Administration Console LITE and Data Warehouse Administration Console in this release. When you install Data Warehouse Administration Console 7.9.0, you can access all the features of Data Warehouse Administration Console. Customers upgrading from Data Warehouse Administration Console LITE also can access all the Data Warehouse Administration Console 7.9.0 features.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
If you are an existing customer of Oracle's Siebel CRM Analytics Suite, then you might encounter issues upgrading the repository if one of the following is true:
You were running Analytics for one module only (such as Siebel Sales Analytics) by extracting the corresponding project for that module from the SiebelAnalytics.rpd that you received in the previous release.
During the last upgrade, you removed a large number of objects (such as catalogs or logical tables) that you want to retrieve during this upgrade.
To work around this issue, you must perform one additional step during the repository upgrade that is described in the Oracle Business Intelligence Applications Upgrade Guide, Chapter 3, "Process of Upgrading the Siebel Analytics Repository."
For the original repository that is described in this chapter (that is, the first one in Table 4), you must extract the same project that is mentioned in Item 1 above instead of the full SiebelAnalytics_7x.rpd that is in the \OracleBI\Upgrade directory. If you removed a large number of objects during the last upgrade), then you must do the same thing for the SiebelAnalytics_7x.rpd and take the modified version as the original repository. This will minimize the number of "good" objects that you will lose during the merge.
Due to changes in the data model introduced in Oracle Business Intelligence Applications 7.9.3, customers who plan to move from Oracle Business Intelligence Applications versions 7.9, 7.9.1, or 7.9.2 to version 7.9.3 will need to upgrade their Oracle BI Application components. For details of the upgrade steps, refer to Technical Note 676 on SupportWeb at http://supportweb.siebel.com.
Customers upgrading from versions prior to Oracle BI Applications 7.9 should follow instructions in the Oracle Business Intelligence Applications Upgrade Guide version 7.9 and then perform the incremental upgrade steps by referring to Technical Note 676 on SupportWeb at http://supportweb.siebel.com.
This issue applies to Version 7.9.3.
For Oracle Business Intelligence Applications version 7.9.3, you must install Informatica Emergency Bug Fix (EBF)108240 for ETL to work. If you are installing or upgrading to Oracle Business Intelligence Applications version 7.9.3, you must install this EBF for Informatica Powercenter version 7.1.4. For instructions on installing the EBF, refer to the file EBF108240.html. This file and the EBF product files are located in the \Third_Party_Software\Informatica\Patch2 directory on the Oracle BI Applications 7.9.3 Installation CD.
The informatica file EBF108240.html 'Special Notes' section provides one registry path but your machine might have a slight variation of the given path, and you might have one of the following two paths below. An additional regedit path needs to be included for the INFA 7.1.4 Patch2. EBF108240.html for Infa 7.1.4. Patch2 specifies the regedit path as: \hkey_local_machine\system\currentcontrolset\services\powermart\configuration
for setting the Value overrideMpltVarWithMapVar to Yes.
The following path might exist in the INFA7.1.4 installation:
\hkey_local_machine\system\currentcontrolset\services\powermart\parameters\configuration
Without the Informatica EBF patch installed, the Informatica debugger fails to initialize when debugging a mapping with mapplets in PowerCenter 7.1.4, and you might see error messages similar to the examples below.
Example error messages with tracing turned off:
Establishing a connection to the server <server name>... Server <server name> acknowledged its ability to debug a mapping. Initializing debugger... Sending request to server <server name> to debug mapping m_ODS_Properties... Waiting for server to initialize...
Example error messages with tracing turned on:
16:12:46 4-17-2006 MAIN: starting debugger... 16:12:46 4-17-2006 MAIN: creating listening socket, auto select port is off 16:12:46 4-17-2006 MAIN: created listening socket, port is 5111 16:12:46 4-17-2006 MAIN: started debugger"
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
To install Oracle Business Intelligence Applications, you must have an Oracle Business Intelligence Infrastructure that uses Java SDK Version 1.5.0.
This issue applies to Version 7.9.3.
This issue mainly affects PeopleSoft Financials implementations. The column ACCT_DOC_ID in table W_GL_REVN_F and W_GL_REVN_FS might have been created as VARCHAR2(30) instead of the correct VARCHAR2(80). As a result, there will be many rows in the W_GL_REVN_F fact table with document type = 'MANUAL'. MANUAL doc type is used to stamp Journals that are created manually in General Ledger for Revenue accounts bypassing the AR or Billings module. Before you upgrade, you must correct the column width for ACCT_DOC_ID in the W_GL_REVN_F and W_GL_REVN_FS tables as described in the steps below.
To return the revenue amount and the number of rows that are stamped as MANUAL, run the following SQL statement:
SELECT B.W_XACT_TYPE_CODE, SUM(A.REVENUE_LOC_AMT), COUNT(*) FROM W_GL_REVN_F A, W_XACT_TYPE_D B WHERE A.ACCT_DOC_TYPE_WID = B.ROW_WID AND B.W_XACT_TYPE_CODE = 'MANUAL' GROUP BY B.W_XACT_TYPE_CODE;
To work around this issue, before you upgrade, do the following:
Log into Informatica Designer and display the Source Analyzer tool.
In the SILOS folder, use the Edit Tables dialog to edit the table W_GL_REVN_FS and make sure that the port length for ACCT_DOC_ID column is VARCHAR2(80).
In the SILOS folder, use the Edit Tables dialog to edit the table W_GL_REVN_F and make sure that the port length for ACCT_DOC_ID column is VARCHAR2(80).
If you do not have manual journals in General Ledger for Revenue accounts, you can safely delete the MANUAL rows in the fact table. If you do have manual journals in General Ledger for Revenue accounts, perform a full load of the Revenue Fact and the Revenue Aggregates (W_GL_REVN_GRPACCT_*_A).
Oracle Business Intelligence Applications Version 7.9.1 provides support for Oracle's Siebel CRM 8.0 as a Source System.
Oracle Business Intelligence Applications Version 7.9.2 provides support for Oracle Financial Services Applications (OFSA) Version 4.5, with the introduction of a new Business Intelligence application called Financial Services Profitability Analytics.
Oracle Business Intelligence Applications Version 7.9.3 provides support for Oracle's PeopleSoft HR 8.8 and PeopleSoft Financial 8.4 and 8.8 as Source Systems. PeopleSoft application-specific Object and Data Security have been built into Oracle BI Applications 7.9.3. For details on how to configure and customize Oracle BI Applications to use PeopleSoft 8.4 or 8.8 as a data source, refer to Technical Note 676 on SupportWeb at http://supportweb.siebel.com.
Oracle Business Intelligence Applications Version 7.9.3 provides support for Primary Position and Team Based Security for Oracle BI Applications that are sourced from Oracle's Siebel CRM applications. New security groups have been added in the Oracle BI Applications 7.9.3 release that enhance flexibility for dealing with the needs of super users and administrators. For details on how positional security is configured and how to implement alternative team based visibility, refer to Technical Note 676 on SupportWeb at http://supportweb.siebel.com.
Oracle Business Intelligence Applications Version 7.9.3 offers the following enhancements to the Oracle BI Data Warehouse Administration Console (DAC):
Creating indexes in parallel
Enhancements to Parameterization Framework
Use of database specific text
Nesting of parameters within parameters
Using SQL within timestamp parameters
Programmatic addition of Tables in 'Refresh Dates' Screen
Query Mode for 'All Dependencies' and 'Task Details' Screens
Define Multiple tablespaces for indexes by table type
For more information about DAC enhancements, refer to Technical Note 676 on SupportWeb at http://supportweb.siebel.com.
This section provides corrections for various pieces of the documentation set for Oracle Business Intelligence Applications. It contains the following topics:
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
Note the following corrections to the Oracle Business Intelligence Applications Installation and Configuration Guide:
The Oracle Business Intelligence Applications Installation and Configuration Guide does not provide instructions on the correct upgrade methodology for moving localized string content from the S_MSG table in the Siebel OLTP database to the W_LOCALIZED_STRING_G table in the Business Analytics Warehouse database. Use the following steps:
Export the customized localization data in the S_MSG table using the DATAEXP command from a command prompt, as shown in the following sample text:
DATAEXP /U <Username for OLTP read access> /P <Password> /C <Siebel_OLTP odbc> /D <Table Owner> /F CUSTSTRINGS.DATA /W Y /L <EXPCUST.LOG>
Respond to the prompts as follows:
When you are prompted for the TABLE_NAME to export, enter S_MSG and press ENTER.
When you are prompted for the WHERE CLAUSE for filtering records from the table, enter WHERE LAST_UPD > '1/1/1980' and press ENTER twice.
A message is displayed that indicates the number of rows that were exported. Press ENTER to enter the Database Table Name and to end the process.
Import the data into the W_LOCALIZED_STRING_G table using the data file that you generated in the previous step.
See the "Importing Locale Seed Data into the Translation Table (W_LOCALIZED_STRING_G)" section in the Oracle Business Intelligence Applications Installation and Configuration Guide to understand the process of importing localization data into the W_LOCALIZED_STRING_G table.
Use the /R 1000000 argument in the DATAIMP command to insert the customized strings beyond Row Id 1000000.
Import the localization data into the W_LOCALIZED_STRING_G table.
In Chapter 4, "Preinstallation and Predeployment Considerations for Oracle BI Applications", the "General Guidelines for Setting Up Oracle Business Analytics Warehouse" section requires the following additional bullet point:
To maximize ETL performance, create three indices on the Siebel OLTP database, using the following SQL commands:
CREATE INDEX S_AUDIT_ITEM_M100 ON S_AUDIT_ITEM (FIELD_NAME ASC, BUSCOMP_NAME DESC) PCTFREE 10 ALLOW REVERSE SCANS COLLECT DETAILED STATISTICS;
CREATE INDEX S_AUDIT_ITEM_M101 ON S_AUDIT_ITEM (RECORD_ID ASC, FIELD_NAME DESC) PCTFREE 10 ALLOW REVERSE SCANS COLLECT DETAILED STATISTICS;
CREATE INDEX S_OPTY_M102 ON S_OPTY (ROW_ID ASC, PR_POSTN_ID DESC) PCTFREE 10 ALLOW REVERSE SCANS COLLECT DETAILED STATISTICS;
In Chapter 5, "Installing and Configuring Oracle BI Applications on Windows", the "Process of Creating Data Warehouse Tables" section requires that the following text be added after Step 6 to indicate the two log files that are generated by the schema creation process:
\OracleBI\DAC\config\generate_ctl.log - A log of the schema definition process, including details of conflicts between containers, if any.
\OracleBI\DAC\config\createtables.log - A log of the ddlimp process.
In Chapter 5, "Installing and Configuring Oracle BI Applications on Windows", the text that is included in the "Process of Creating Data Warehouse Tables on a Teradata Database" section should be replaced with the following process:
To create data warehouse tables on a Teradata database
From the Data Warehouse Administration Console menu bar, click Tools, then ETL Management, then Configure.
In the Sources dialog box, select "Teradata" in the "Target data warehouse database platform" field, and select the source database platform in the "Source transactional database platform" field.
Click OK to display the Data Warehouse Configuration Wizard.
Click the "Generate create statements for Data Warehouse Tables" check box, then click Next to display the Data Warehouse SQL tab.
Use the Container field to specify individual containers, or leave the field blank to deploy all containers.
Click Start.
The Run Status tab displays information about the data warehouse creation process. If a "Success" message is displayed, then the data warehouse creation script has been created successfully. If a "Failure" message is displayed, then the data warehouse creation script has not been created (refer to the \DAC\log\config\createwtables.log file to diagnose the failure).
Click Finish.
Copy the SQL file that was created in Step 6 from the \conf\sqlgen\sql\Teradata directory into SQL Assistant and execute the script.
From the Data Warehouse Administration Console menu bar, click Tools, then ETL Management, then Configure.
Chapter 5, "Installing and Configuring Oracle BI Applications on Windows" contains sections called "Installing the Java SDK" and "Running the Oracle Business Intelligence Applications Installation Wizard". The beginning of both of those sections should include the following text:
If you are using Oracle client software for connection to an Oracle database, then you must ensure that the Oracle client software has been installed before the latest verified version of Java SDK is installed when you install Oracle Business Intelligence.
Chapter 5, "Installing and Configuring Oracle BI Applications on Windows", contains the "Installing JDBC Drivers for DAC Database Connectivity" section. In that section, the Microsoft SQL Server bullet point should read as follows:
Data Warehouse Administration Console is configured for Microsoft SQL Server 2005 JDBC drivers. If you are using a Microsoft SQL Server database, then download the Microsoft SQL Server 2005 JDBC Driver files and copy them to the <DRIVE>:\OracleBI\DAC\lib\ folder. You can use the Microsoft SQL Server 2000 JDBC Driver files if you edit the \conf\connection_templates.xml file and un-comment the section that starts "<!-- THIS SECTION IS FOR SQL SERVER 2000. Comment this section while using SQL Server 2005.-->
In Chapter 5, "Installing and Configuring Oracle BI Applications on Windows", in the "Process of Setting DAC Source System Parameters" section, the Description for the Default Language parameter in Commonly Available Source System Parameters table should be:
Siebel Specific. The language into which columns' display strings are translated.
NOTE: Language-independent strings are stored in the _l columns.
If you do not know the default language setting, do one of the following:
Issue the following query against the transactional database:
SELECT S_LST_OF_VAL.LANG_ID,S_LST_OF_VAL.TRANSLATE_FLG,S_LST_OF_VAL.TYPE,S_LST_OF_VAL.NAME FROM S_LST_OF_VAL WHERE S_LST_OF_VAL.ACTIVE_FLG = 'Y' AND S_LST_OF_VAL.TRANSLATE_FLG = 'Y' AND TYPE LIKE 'ETL%';
Refer to the Language code of the System Requirements and Supported Platforms document under the Globalization requirements section.
In Chapter 6, "Installing and Configuring Oracle BI Applications on UNIX", the following procedure should be inserted after the "Installing and Configuring the Informatica Repository Server on UNIX" section:
Process of Defining The Informatica Repository Server as an Environment Variable
To define the Informatica Repository Server as an environment variable:
Open the dac_env.sh or dac_env.csh file in a text editor, from the \OracleBI\DAC directory.
Specify the path of the Informatica Repository by editing the value of the following environment variables:
setenv PM_HOME=<Informatica repository path>
setenv PM_REPHOME=<Informatica repository path>
Save and close the dac_env.sh or dac_env.csh file.
Execute the "source" command to populate the environment variables using the values specified in the dac_env.sh or dac_env.csh file.
For example, at a command prompt, type "source dac_env.sh".
Restart the Informatica Repository server.
In Chapter 10, "Configuring Oracle Business Analytics Warehouse for Oracle 11i", disregard the section "Mapping Source Customer Hierarchies to the Customers Dimension Table" on page 248, and its sub sections "To map Oracle-defined customer hierarchies to the Customers dimension table" and "To configure the category lookup".
Process of Defining The Informatica Repository Server as an Environment Variable
In Chapter 12, "Configuring Oracle BI Applications Consumer", the section "Configuring Product Categories" requires the following changes:
The task "To configure product category extract from Oracle Applications" should be replaced with the following steps:
1. Identify the categories that need to be mapped to the extension hierarchy columns.
These categories are extracted from the source and placed in the hierarchy column specified.
2. In PowerCenter Workflow Manager, open the Configuration for Oracle Applications v11i.
3. Open the SDE_ORA_PRODUCT_CATEGORY_DERIVE session with the Task Developer.
4. In the Mappings tab, click MPLT_BC_ORA_PRODUCT_CATEGORY.SQ_MTL_CATEGORIES.
5. In the right pane, scroll down and click SQL Query to edit session SQL override.
6. Click the arrow to edit the WHERE clause.
The following statement is an example of how to structure the WHERE clause:
WHERE...MTL_CATEGORY_SETS_B.CATEGORY_SET_ID IN (27,2)
In this example, the WHERE clause extracts categories where the Category SetID is 27 or 2.
7. Click OK, and then click OK to close the Edit Tasks box.
8. Repeat steps 3 to 7 for the SDE_ORA_PRODUCT_CATEGORY_DERIVE_FULL session.
|
Note: The default product hierarchy level is two. If you only have one product hierarchy level, you need to make the following modifications:1. In the EXP_PRODUCTS expression transformation in the mplt_SA_ORA_ProductDimension mapplet, modify the EXT_PROD_HIER1_CODE port to INP_SEGMENT1. 2. In the SDE_ORA_ProductDimension mapping, open the EXP_W_PRODUCT_DS transformation expression and modify the PROD_HIER1_CODE port expression to IN_PROD_HIER1_CODE. |
The task "To modify the default Category Set" should be replaced with the following task:
1. Open the ParameterOLTP.TXT file in the OracleBI\DAC\Informatica\parameters\input\ folder.
2. Replace the default Category Set ID (27) with your new value.
3. Save and close the file.
The task "To reconfigure the product hierarchy loads" should be replaced with the following task:
1. In PowerCenter Designer, open the Configuration for Oracle Applications v11i folder, and expand the Mapplets folder.
2. Open the mplt_SA_ORA_ProductDimension mapplet for Oracle 11i.
3. Double-click the EXP_PRODUCTS expression transformation to open the Edit Transformation box.
4. In the Ports tab, scroll down to find the hierarchy code port.
Hierarchy levels are named with the following convention EXT_PROD_HIERX_CODE, where X denotes the level within the hierarchy. For example, if you want to edit the first level of your hierarchy, you must edit the definition for EXT_PROD_HIER1_CODE port. The first two levels are preconfigured as follows:
EXT_PROD_HIER1_CODE = IIF(ISNULL(INP_SEGMENT1) OR ISNULL(INP_SEGMENT2), NULL,
INP_SEGMENT1||'~'||INP_SEGMENT2)
EXT_PROD_HIER2_CODE = INP_SEGMENT1
5. Modify the expression that defines your hierarchy code.
6. Validate and save your changes to the repository.
The following section should be appended:
Configure the Master Inventory Organization in Product dimension Extract for Oracle 11i adapter
In Oracle 11i applications, the products are defined in a Master Organization and then copied into the other Inventory Organizations for transactions. The Product dimension Extract mapping SDE_ORA_ProductDimension_Derive has been enabled configuration of this Master Organization based on the configuration in the OLTP. This is enabled via a session parameter "MASTER_ORG" in the parameter file (OracleBI\DAC\Informatica\parameters\input\ParameterOLTP.TXT) for the ETL. After installation, this parameter is set to "$$MASTER_ORG=204". This organization Id 204 needs to be changed based on the individual implementation of OLTP at the customer site.
Note: This ETL implementation supports the best practice prescribed by Oracle for the creation of Single Master Organization for defining the Product master. This ETL implementation does not support the multiple master Organizations if the same product is defined in multiple master organizations.
The following section should be appended:
Configure the Product Category in Product dimension Extract for Oracle 11i adapter
In Oracle 11i applications, the products have a unique Purchase Category set defined. The Product dimension Extract mapping SDE_ORA_ProductDimension has been enabled configuration of this Category Set which is in turn used in populating the Product hierarchy columns. This is enabled via a session parameter "CATEGORY_SET_ID" in the parameter file (OracleBI\DAC\Informatica\parameters\input\ParameterOLTP.TXT) for the ETL. After installation, this parameter is set to "$$CATEGORY_SET_ID =27". This Category Set Id - 27 needs to be changed based on the individual implementation of OLTP at the customer site.
This documentation addendum applies to Appendix B, "Localizing Oracle Business Intelligence Deployments".
In prior releases of the Oracle Business Intelligence Applications (prior to Version 7.9), localization data was stored within a Siebel CRM table called S_MSG. Starting with Version 7.9, a new table was introduced in the Business Analytics Warehouse called W_LOCALIZED_STRING_G to store localization data for the Oracle BI metadata.
This table, W_LOCALIZED_STRING_G, is created by the Data Warehouse Administration Console (DAC) when it is setting up the Business Analytics Warehouse. However, it is created without indexes, which might affect performance.
On page 422, before following the steps in the section entitled "To import Locale seed data into the Translation Table (W_LOCALIZED_STRING_G)", do the following:
To verify creation of Translation Table (W_LOCALIZED_STRING_G) and corresponding indexes:
Verify that in the setting up of the Business Analytics Warehouse, tables included the creation of the W_LOCALIZED_STRING_G table (see page 105, Process of Creating Data Warehouse Tables).
Lookup the definitions of the indexes in the DAC and create them manually in the Business Analytics Warehouse. The names of the indexes are as follows:
W_LOCAL_STRING_G_U1
W_LOCAL_STRING_G_P1
W_LOCAL_STRING_G_M1
W_LOCAL_STRING_G_M2
Note: It is better to add these indexes to W_LOCALIZED_STRING_G prior to importing the locale seed data in the next section, in order to safeguard against inadvertently duplicating the data in the table.
There is an error in Appendix D, "Integrated Security for Oracle BI Applications", in the "Implementing Security With Oracle EBS" section and the "Authorization Initialization Block" sub-section.
The first line of the second SQL statement in this section should be changed from "select DISTINCT RESPONSIBILITY_NAME" to "select DISTINCT 'GROUP', RESPONSIBILITY_NAME".
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
Note the following corrections to the Oracle Business Intelligence Applications Upgrade Guide:
The Oracle Business Intelligence Applications Upgrade Guide contains specific steps for migrating data during the upgrade process. During the data migration stage, you might encounter steps that may violate your company's security policy. To safeguard for security, delete the Reset_infa_seq_gen.bat file after the upgrade process is complete.
Chapter 3, "Upgrading Oracle BI Applications", contains the "Migrating Data into the Upgraded Data Warehouse" section. In the procedures, "To migrate data from Version 7.5.x to 7.9 (Horizontal)" and "To migrate data from Version 7.5.x to 7.9 (Vertical)", some steps are in the incorrect order. In both procedures, you should perform steps 11, 12, 13, and 14 immediately after step 5. This means that you will set parameters as directed in steps 11, 12, 13, and 14 for the 753_TENERIFE_UPG_PARAMS.txt file. You do not need to repeat steps 11, 12, 13, 14 to set parameters in the 770_TENERIFE_UPG_PARAMS.txt.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
Note the following corrections to the Oracle Business Intelligence Data Warehouse Administration Console Guide:
Chapter 6, "Common Tasks Performed in the DAC", contains the "Running Two DAC Servers on the Same Machine". The steps in this task should be replaced with the following:
Copy the \DAC\ directory to a different location on the same machine.
For example, you might copy the C:\OracleBI\DAC folder to C:\DAC_SERVER2\DAC.
Make sure that the config.bat file is configured correctly.
For example, if you copy the C:\OracleBI\DAC folder to C:\DAC_SERVER2\DAC, make sure that the C:\DAC_SERVER2\DAC\config.bat files is configured correctly.
The documentation supplied with Oracle Business Intelligence Applications Version 7.9 accurately describes Version 7.9.1, 7.9.2, and 7.9.3 as well. As the Version 7.9 documentation has not been updated for Version 7.9.1, 7.9.2, or 7.9.3, the title pages continue to show 'Version 7.9'.
This section provides release notes for Oracle Business Intelligence Applications in general. It contains the following topics:
Section 1.3.1, "Issue Including Subject Areas from Two Different Sources into One Execution Plan"
Section 1.3.2, "Incorrect Order for Two Tasks in ETL Execution Plan"
Section 1.3.3, "Incremental Aggregation Tasks Can Run Slowly in Multisource ETL Situation"
Section 1.3.4, "Mapping Fails in Contact Center Telephony Analytics"
Section 1.3.7, "Lack of Time Zone Setting Prevents Delivery of iBots to Applications Users"
Section 1.3.8, "Large Dimensional Tables Causing Slow Fact Lookups"
Section 1.3.10, "Load Cost Lists Task Hangs When Running Full or Incremental Load"
Section 1.3.11, "Issues Extracting CRM Data on IBM DB2 Database Version 8.2"
Section 1.3.12, "Issue with Exchange Rates and Transaction Currencies"
Section 1.3.13, "Incorrect Default Physical SQL for Saved Result Sets on Teradata"
Section 1.3.14, "Issue with Mapping in Oracle Business Intelligence Applications 7.7.1.x Versions"
Section 1.3.15, "Issues Inserting Tables on Infa Server in a Non-English Language Deployment"
Section 1.3.16, "Restoring the Informatica Repository in a Non-English Language Deployment"
Section 1.3.18, "ACCT_DOC_ID Column is Defined Inconsistently"
Section 1.3.19, "Running an ETL with a Non-English OLTP Data Source"
Section 1.3.20, "Issue with Reports in Oracle Business Intelligence Presentation Catalog"
Section 1.3.21, "Running Informatica on HP-UX Itanium 64 bit"
Section 1.3.22, "Using the Installed Database Template Files"
Section 1.3.25, "Data Type Problem On Booking Serial Num and Column Order"
Section 1.3.26, "Running the DDLIMP Tool From The Command Line"
Section 1.3.29, "Slowly Changing Dimensions Update Mappings Causing ORA-01652"
Section 1.3.30, "Data Granularity Changes In Oracle Financial Analytics Application Version 7.9.3"
Section 1.3.31, "Data Type Mismatch In SDE_ORA_SalesInvoiceLinesFact"
Section 1.3.32, "Invalid Expressions In MPLT_SA_ORA_SALESINVOICELINESFACT"
Section 1.3.33, "Invalid Expression For Shippable Flag in MPLT_SA_ORA_SalesOrderLinesFact"
Section 1.3.35, "Forecasting Interoperability With Siebel OLTP 8.0"
Section 1.3.36, "ETL Mappings With Source Employee Data From Oracle E-Business Suite"
Section 1.3.37, "Data Security In Oracle E-Business Suite 11i"
Section 1.3.39, "Recommendations For Importing The DAC Metadata"
Section 1.3.40, "Incorrect Mapping In Purchase Invoice Number In Table Fact_W_AP_AGING_INVOICE_A"
Section 1.3.42, "HR Analytics Issue With Oracle e-Business Suite"
Section 1.3.43, "Expression Change For Operational Backlog Flag"
Section 1.3.44, "SQL File Cannot Accept Tables With Columns Named As Teradata Reserved Words"
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
Data Warehouse Administration Console allows customers to build execution plans that are comprised of subject areas from different source system containers. However, such behavior is not supported in this release. For example, you cannot have a subject area from Oracle Applications 11.5.8 and Siebel Applications 7.8 in the same execution plan.
To work around this issue, create separate execution plans for subject areas in different containers. For example, suppose an execution plan has subject areas such as Sales, Inventory, and Receivables that come from an Oracle Applications 11.5.8 container. If the Sales subject area in Siebel Applications 7.8 must be populated, then it requires another execution plan.
This issue applies to Version 7.9. This issue is fixed in Versions 7.9.1, 7.9.2, and 7.9.3.
In ETL execution plans in Data Warehouse Administration Console, the following tasks are in the incorrect order:
SIL_CustomerLocationDimension_SCDUpdate
SIL_CustomerFinancialProfileDimension_SCDUpdate
To work around this issue, you must change the order of the tasks in Design View and in Execute Plan view before running the first ETL.
To change the order in Design View:
Log into the Oracle Business Intelligence Application version 7.9 Data Warehouse Administration Console Repository.
From the File menu, select New Source System Container.
Specify the container ID and name.
Select Create as a copy of existing application.
Select the existing application that you want to use.
In the new container, select the Tasks tab and perform the following steps for both the SIL_CustomerLocationDimension_SCDUpdate and SIL_CustomerFinancialProfileDimension_SCDUpdate tasks:
Query on the Command for Incremental Load column.
Change the Task Phase to "Update Slowly Changing Dimension."
To change the order in Execution Plan View:
Create a new execution plan if you have been using an out-of-the-box execution plan.
Assign the appropriate subject areas that you want to run.
Generate the Parameters in the Parameters tab.
Select Build to build the execution plan and its dependencies
Note: If you have already built a custom execution plan that is based on existing subject areas, then you must rebuild that custom execution plan by selecting Build in the Execute Plan View.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
Data Warehouse Administration Console runs full load or incremental load commands based on the dates when data was last extracted from a source table and when data was loaded into a target table.
Dependence on last extract or last load dates can become an issue when loading data from two discrete source systems for the first time. ETL from the first source runs as a full extract and full load. ETL from the second source runs as a full extract and as an incremental load. This can cause the second load to be slower than the first, because full load mappings are simple while incremental mappings (especially for aggregate tables) must handle delta aggregation, manipulation, and other complicated situations. Thus incremental loads are costlier.
Slow performance when running incremental ETL is not noticeable in daily ETL runs because of lower data volumes. But, in situations where data is extracted and loaded for the first time from two source systems one after the other, there will be a noticeable slowing down of the second load due to the high data volumes loaded in an incremental mode.
For example, suppose that three years of data is extracted and loaded from Oracle Applications 11.5.8 followed by a three-year load of data from Oracle Applications 11.5.10. The load from Oracle Applications 11.5.10 source will be noticeably slower.
This issue has no workaround.
This issue applies to Version 7.9. This issue is fixed in Versions 7.9.1, 7.9.2, and 7.9.3.
A specific mapping within the Universal Adapter for Oracle BI Applications will fail if data is supplied within the specified Universal Adapter CSV file. This affects the Contact Center Telephony Analytics product.
The SDE_Universal_CodeDimension_Uom task will fail to run if there is data to be extracted from the specific Universal Adapter csv file. The error is caused by a missing $$CATEGORY parameter in this ETL mapping.
To work around this issue:
Log on to the DAC repository in Design mode and navigate to the Universal container.
In the upper pane, display the Tasks tab.
Select the 'SDE_Universal_CodeDmension_Uom' task.
In the lower pane, select the Parameters tab.
Create a new parameter with the following attributes:
Name: $$CATEGORY
Data Type: Text
Value: UOM
Save your changes and exit.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
You cannot use the Data Warehouse Administration Console repository in batch mode when building execution plans on an Oracle 9i Release 2 Database. If batch mode is enabled in this situation, then you might see an error message such as the following one:
MESSAGE:::Error while persisting dependency. EXCEPTION CLASS::: com.siebel.analytics.etl.execution.ExecutionPlanInitializationException
This error occurs because the Oracle 9i JDBC drivers do not handle array inserts or bulk inserts. The execution plan build works well without the bulk inserts.
To work around this issue, perform these steps:
Deselect the "Enable Batch Mode" box when building execution plans. The default is to enable the batch mode.
Use the JDBC driver that is available in Oracle Database 10g and connect to the DAC repository with a thin connection.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
The Oracle Business Intelligence Delivers iBots use a predefined query against the SA System subject area to retrieve a list of applications users who are associated with the iBot's Recipient Group. When an iBot is run, this predefined query will fail with an error message that is similar to the following one:
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27005] Unresolved column: "Time Zone".
The query error prevents the Oracle BI Delivers iBot from generating and delivering any content for the applications users that are associated with the iBot's Recipient Group. This impacts all iBot destination types including Interactive Dashboards and Emails and Disconnected Application cache generation for Oracle Business Intelligence Disconnected Analytics. The issue occurs because the "Time Zone" presentation column name is incorrectly spelled as "Timezone"in the SA System subject area.
To work around this issue use the Oracle Business Intelligence Administration Tool to change the name of the existing "Timezone" presentation column in the SA System presentation catalog to "Time Zone".
For information on the SA System subject area, see the Oracle Business Intelligence Server Administration Guide.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
The Oracle BI Delivers iBots use a predefined query against the SA System subject area to retrieve a list of applications users who are associated with the iBot's Recipient Group. When an iBot is run, users who do not have a time zone specified in user preferences are considered invalid users and iBots are not delivered to them.
This issue occurs because the join type for the S_TIMEZONE join in the S_USER Logical Table Source in the SA System Business Model is defined as INNER when it should be defined as RIGHT OUTER.
To work around this issue, perform these steps:
Display the Oracle Business Intelligence Administration Tool.
In the Business Model and Mapping layer, expand the SA System Business Model and the USER Logical Table.
Double-click the S_USER Logical Table Source under Sources in the USER Logical Table.
In the Logical Table Source - S_USER Dialog Box, change the type to RIGHT OUTER from INNER for the S_TIMEZONE join in the Joins section of the General tab.
For information on the SA System subject area, see the Oracle Business Intelligence Server Administration Guide.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
Several dimensional tables that are larger than 255 columns are causing the fact lookups on these dimensions to perform slowly and the lookup database server to run out of memory. The following tables have been identified as experiencing this issue:
W_CUSTOMER_FIN_PROFL_D
W_CUSTOMER_LOC_D
W_CUSTOMER_LOC_USE_D
W_ORG_D
W_PRODUCT_D
W_SALES_PRODUCT_D
W_SUPPLIER_PRODUCT_D
To work around this issue and improve performance, complete the following steps:
Edit the slow running session.
For all transformations that look up the large dimensions that are listed in this section, change the following
Index cache size = 10 MB
Data cache size = 20 MB
This is a session-side change. No modifications are required in mapping.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
Data Warehouse Administration Console automatically drops and recreates the registered indexes on the primary target table that is defined for a task for better ETL performance if the primary target table is truncated in full or incremental load. Data Warehouse Administration Console also gathers statistics on the ETL indexes automatically after recreating these indexes on this table. Statistics help the database optimizer to select the best access path to query the data.
The underlying workflow for this task's full and incremental load command is SDE_ListOfValues, which consists of the SIL_ListOfValuesGeneral and SIL_CopyListOfValuesToOLTP sessions. The first session populates the W_LST_OF_VAL_G table, and the second session populates the S_ETL_LOV table from the W_LST_OF_VAL_G table. Because the S_ETL_LOV table is defined as the primary table for this task, Data Warehouse Administration Console does not analyze the W_LST_OF_VAL_G table.
Even if this task is modified to include the W_LST_OF_VAL_G table as another primary table, Data Warehouse Administration Console will not analyze this table until after executing the entire workflow that comprises these two sessions. This causes the task to hang while it extracts rows from the unanalyzed W_LST_OF_VAL_G table to populate the S_ETL_LOV table.
To work around this issue:
If necessary, make a copy of the deployed container where you want to apply this workaround. You cannot make changes to the out-of-the-box container.
Create two new workflows in the Informatica Workflow Manager: one for the SIL_ListOfValuesGeneral session and the other for the SIL_CopyListOfValuesToOLTP session. The workflow name should match the session name that is used inside the workflow.
Make two copies of the Fix List Of Values and Copy to OLTP task in Data Warehouse Administration Console. Rename the first copy as Fix List Of Values and the second copy as Copy List Of Values to OLTP task. Verify that the properties for both these tasks are set as follows:
Task 1: Fix List of Values
Command for Incremental Load: SIL_ListOfValuesGeneral
Command for Full Load: SIL_ListOfValuesGeneral
Folder Name: Extract
Primary Source: DBConnection_OLAP
Primary Target: DBConnection_OLTP
Task Phase: General
Execution Type: Informatica
Priority: 5
Source Tables: W_LST_OF_VAL_G (Primary), W_PARAM_G (Lookup)
Target: W_LST_OF_VAL_G (Primary).
Ensure that Truncate Always and Truncate For Full Load options are not selected.
Task 2: Copy List of Values to OLTP
Command for Incremental Load: SIL_CopyListOfValuesToOLTP
Command for Full Load: SIL_CopyListOfValuesToOLTP
Folder Name: Extract
Primary Source: DBConnection_OLAP
Primary Target: DBConnection_OLTP
Task Phase: General
Execution Type: Informatica
Priority: 5
Source Tables: W_LST_OF_VAL_G (Primary)
Target: S_LST_OF_VAL (Primary)
Ensure that Truncate Always and Truncate For Full Load options are selected.
Edit the TASK_GROUP_Extract_lst_of_val task group by removing the Fix List of Values and Copy to List task and adding the newly created tasks Fix List Of Values and Copy List Of Values to OLTP to this task group. Set the execution order for these tasks as follows:
Fix List of Values: 1
Copy Lists of Values to OLTP: 2
Reassemble the appropriate subject area to ensure that these new tasks are included in the subject area.
Rebuild the execution plan in which these tasks must be executed.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
Data Warehouse Administration Console automatically drops and recreates the registered indexes on the primary target table that is defined for a task for better ETL performance if the primary target table is truncated in full or incremental load. Data Warehouse Administration Console also gathers statistics on the ETL indexes automatically after recreating these indexes on this table. Statistics help the database optimizer to select the best access path to query the data.
This underlying workflow for this task's full and incremental load command is SDE_CostList_LoadCosts, which consists of three sessions: SIL_CostList, SDE_CostList_MissingCosts, and SIL_CopyCostListsToOLTP. The first two sessions populate the W_COSLST_G table from two different sources. The third session populates the S_ETL_COSTLST table from the W_COSTLST_G table.
Even though both the W_COSTLST_G and S_ETL_COSTLST tables are listed as primary target tables, Data Warehouse Administration Console does not analyze these tables until after executing the entire workflow that comprises these three sessions. This causes the process to hang when it extracts rows from the unanalyzed W_COSTLST_G table to populate the S_ETL_COSTLST table.
To work around this issue:
If necessary, make a copy of the deployed container where you want to apply this workaround. You cannot make changes to the out-of-the-box container.
Create a new workflow called SIL_CopyCostListsToOLTP. Remove the SIL_CopyCostListsToOLTP session from the SDE_CostList_LoadCosts workflow and place it in the newly created workflow. The workflow name should match the session name that is used inside the workflow.
Make a copy of the Load Cost Lists task in DAC. Rename the copy as Copy Cost Lists. Verify that the properties for the new task are set as follows:
Command for Incremental Load: SIL_CopyCostListsToOLTP
Command for Full Load: SIL_CopyCostListsToOLTP
Folder Name: Extract
Primary Source: DBConnection_OLAP
Primary Target: DBConnection_OLTP
Task Phase: General
Execution Type: Informatica
Priority: 5
Source Tables: W_COSTLST_G (Primary) from DBConnection_OLAP data source
Target: S_ETL_COSTLST (Primary) from DBConnectioN_OLTP data source
Remove the S_ETL_COSTLST table from the target table list in the Load Cost Lists task.
Create a new task group TASK_GROUP_Extract_CostList in DAC. Add Load Cost Lists and Copy Cost Lists tasks to this task group. Set the execution order for these tasks as follows:
Load Cost Lists: 1
Copy Cost Lists: 2
Reassemble the appropriate subject area to ensure that these new tasks are included in the subject area.
Rebuild the execution plan in which these tasks must be executed.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
You might encounter issues extracting Oracle CRM Analytics Suite data that is hosted on the IBM DB2 Database version 8.2, FixPak 9. You might see an error message such as the following one:
[IBM][CLI Driver][DB2/SUN64] SQL0117N The number of values assigned is not the same as the number of specified or implied columns. SQLSTATE=42802
To work around this issue, mark the Extract for Internal Organization Dimension task or the Extract for Asset Dimension task as "Completed", and the ETL run will proceed normally.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
Current design and support of multiple currencies within the Oracle BI Applications and the data warehouse assumes that the transactional system (or OLTP system) provides exchange rates and table structures that store exchange rates.
In the case when the OLTP system does not provide exchange rates from "transaction currency" to the chosen "one or more data warehouse currencies", this will cause the Fact table to have a null exchange rate value for "transaction" currency to "Global1" currency, and hence, analysis based on Global currencies will not be possible for these transactions. It also impacts the correctness of the data for data that resides in various aggregate tables. This issue is also seen in the other two supported currencies (Global2 and Global3).
To work around this issue, ensure that the OLTP system has all currency exchange rates from all possible transaction currencies added to all the three chosen data warehouse currencies, up front. If this is not taken care of beforehand and you encounter a missing exchange rate issue, then you can rerun transactions in "full" mode after you have fixed the missing exchange rate issue in the OLTP system.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
When you set up segment Saved Result Sets in the Marketing Metadata on a Teradata data source, you find that the default SQL for inserting the saved result set header has incorrect syntax. The user will receive the following error message when trying to save a saved result set for a segment:
Odbc driver returned an error (SQLExecDirect). State: 37000. Code: 10034. [NQODBC] [SQL_STATE: 37000] [nQSError: 10034] The SQL statement has a syntax error. [nQSError: 43093] An error occurred while processing the EXECUTE PHYSICAL statement. [nQSError: 16001] ODBC error state: 37000 code: -3706 message: [NCR][ODBC Teradata Driver][Teradata RDBMS]Syntax error: expected something between the 'COUNT' keyword and ','.. [nQSError: 16014]]
This issue is caused by the fact that in the default SQL, the COUNT keyword requires double quotes on Teradata databases. To work around this issue, perform the following steps:
In the Oracle BI Administration Tool, select Marketing from the Manage menu.
Double-click the Target Level and select the Saved Result Sets tab.
Modify the physical SQL that inserts the saved result set header by adding double quotes around the COUNT keyword.
The following example shows SQL code in which double quotes were included around the COUNT keyword.
INSERT INTO M_SR_HEADER (GUID, SEGMENT_PATH, DATE_TIME, TARGET_LEVEL, "COUNT", CREATED_BY) VALUES ('@{guid}', '@{segmentPath}', '@{createdTime}', '@{targetLevel}', @{count}, '@{createdBy}')
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
The "SIL_ListofValuesDimension_MissingActivityCodes" mapping, which was used to move unbounded LOV types from the S_LST_OF_VAL OLTP table to the data warehouse table, is not available. This issue does not affect customers who have bounded LOV type. However, if customers use unbounded LOV types for sra_resolution_cd, this mapping is still required in the 7.7.1.x versions.
To work around this issue, re-import the old mapping in the new environment and execute it as a one-time job, if your data source is Oracle's Siebel CRM Applications Version 7.7 or higher. If your data source is Oracle's Siebel CRM Applications Version 7.5.3 or lower, then run this job as part of the ETL process.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
If you use the Informatica Infa server on a non-English language machine and your warehouse is on an Oracle database, then you might encounter an Oracle database problem when the Informatica server is trying to enter records with a decimal data type column into the warehouse. You might see an Oracle error message such as the following one:
Error: ORA-01722: invalid number
To work around this issue, disable the "Enable high precision" option in the session property. This option indicates whether to keep the precision of a decimal port up to 28 digits. If this option is disabled, then Informatica keeps a precision of up to only 15 digits for decimal ports. Because Oracle Business Intelligence Applications uses decimal ports for ID columns, a precision of 15 is sufficient.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
Due to a limitation in Informatica PowerCenter, you must use the 'PMREPAGENT restore' command to restore the Informatica Repository in a non-English language deployment of Oracle BI Applications (for example, Japanese or Simplified Chinese). If you try to use the Informatica PowerCenter user interface to restore the Informatica Repository in a non-English language deployment of Oracle BI Applications, the process will fail.
To restore the Informatica Repository in a non-English language deployment of Oracle BI Applications, use the 'PMREPAGENT restore' command, as specified in the following sample command:
\OracleBI\dwrep\Informatica\PMREP\PMREPAGENT restore -r <INFA_REP_NAME> -t <INFA_REP_CONNECT_TYPE> -u <INFA_REP_DBOWNER> -p <INFA_REP_PASSWORD> -c <INFA_REP_CONNECT_STR> -h <INFA_REPSERVNAME> -o <INFA_REPSERVPORT> -i <INFA_REP_FILE> -n
For more information about the PMREPAGENT command, see Informatica PowerCenter documentation on installation and configuration.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
The Contact Geography dimension attributes on the Campaign Contacts_Segmentation catalog, which map to the Person Geography dimension table, are not joined appropriately to the Campaign History Fact table. Therefore, Oracle Business Intelligence Server cannot find an appropriate navigation path when this dimension is used.
To work around this issue, remove the Geography dimension from the Campaign Contacts_Segmentation catalog. If users want to use this dimension, then they can switch to another subject area that has this dimension and reference it there, for example, Customer Profile_Segmentation.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
The ACCT_DOC_ID column is defined as Varchar2(80) in the base fact and in staging tables such as W_AP_XACT_F/W_AP_XACT_FS. However, the ACCT_DOC_ID column is defined as Varchar2(30) in the W_GL_REVN_FS and W_GL_REVN_F tables. This definition is inconsistent and can affect the W_GL_REVN_FS and W_GL_REVN_F tables if the ACCT_DOC_ID column in these tables is required to be greater than 30 characters wide.
To work around this issue, change the ACCT_DOC_ID column in the W_GL_REVN_FS and W_GL_REVN_F tables to be defined as Varchar2(80), if you need these tables to be greater than 30 characters wide.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
To run an ETL with a non-English OLTP Data Source, you must make a copy of an appropriate source system container, and configure the language, country, and continent parameters.
To run an ETL with a non-English OLTP Data Source:
In Data Warehouse Administration Console, choose File, then New Source System to display the New Source System Container dialog.
Select the Create as a Copy of Existing Container radio button.
From the Existing Containers drop down list, select the container that you want to copy, then click OK.
Display the Design view.
Ensure that you have selected the correct container from the containers drop down list.
Select the Source System Parameters tab.
Use the Edit tab below the list of Source System Parameters to change the value of the following parameters in the list:
$$DFLT_LANG (Default Language) – For example, for a Japanese data source, change this value to JPN.
(optional) $$DFLT_COUNTRY (Default Country)
(optional) $$DFLT_CONTINENT (Default Continent)
Tip: To find out the value to specify for the $$DFLT_LANG parameter, issue the query 'select VAL from S_SYS_PREF where SYS_PREF_CD=<ETL value>' against the OLTP database. For example, to find out the default ETL language, issue the following command:
select VAL from S_SYS_PREF where SYS_PREF_CD='ETL Default Language';
Create a new ETL plan for the new source system container, and edit its parameters, as follows:
Display the Execute tab.
Click the Execution Plans sub-tab.
Click New to create a new blank execution tab and use the sub-tabs below (for example, Subject Areas, Parameters, Ordered Tasks) to specify the execution plan details.
Click Save.
Click Run Now to run the new ETL plan (or use the Schedule tab to specify when you want to run the new execution plan).
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
The following reports within the shipped Oracle BI Presentation Catalog have poor query performance:
/shared/Interactive Selling/Pricing/Promotions/Recommended Promotions
/shared/Interactive Selling/Pricing/Promotions/Market Basket Analysis - Promotion Recommendations
/shared/Marketing/Customer Insight (B2C)/Products/Next Product Purchased
/shared/Interactive Selling/Products/Marketing/Next Product Purchased
Do not use these reports in the current release. This issue has no workaround.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
If you want to perform ETL runs in Informatica on HP-UX Itanium 2 64-bit, you need to install one of the following patches:
For HP-UX 11.11, apply patch PHSS_33033.
For HP-UX 11.23, apply patch PHSS_33349.
If you do not install one of the patches listed above, the ELT run might fail with one of the following errors:
FATAL ERROR: Caught a fatal signal [11]. Will abort this server process shortly. INFO: CMN_1131: (3644|1) System shared memory [360454] for [Load Manager Shared Memory] removed.
FATAL ERROR: Aborting this server process due to a fatal signal [11].
This issue applies to Version 7.9.3.
To configure Business Analytics Data warehouses on Oracle databases more easily, refer to the following init.ora parameter template files, located in <DRIVE>:\<BI Apps install directory>\dwrep\Documentation\. For example, C:\OracleBI\dwrep\Documentation\.
init9iR2.ora - init.ora template for Oracle RDBMS 9iR2
init10gR2.ora - init.ora template for Oracle RDBMS 10gR2
Copy the template file corresponding to your database version into your $ORACLE_HOME/dbs folder, review the recommendations in the template file, and make the changes based on your specific database sizes, data shape, server size (CPU and memory), and type of storage. The database administrator should make changes to the settings based on performance monitoring and tuning considerations.
This issue applies to Version 7.9.2. You need Version 7.9.3 to correct this issue, using the steps below.
The two security groups related to Oracle EBS security, "Inventory Org-based Security"and "Operating Unit Org-based Security", are missing in the following RPD projects' definition:
Inventory Analytics
Order Management Analytics
Order Fulfillment Analytics Option
Procurement and Spend Analytics
Supplier Performance Analytics
To correct this issue, do the following:
Set up a Multi-User Development (MUD) environment for the Version 7.9.3 RPD file EnterpriseBusinessAnalytics.rpd, which contains the required project definitions.
Extract the same combination of project sets as the customized 7.9.x RPD that the you are using from this MUD.
There will be two extracted RPD files, for example, ABC.rpd and originalABC.rpd.
Refer to the definitions of security groups "Inventory Org-based Security" and "Operating Unit Org-based Security" in the old RPD (for example, originalABC.rpd), and manually replicate them in the existing RPD (for example, ABC.rpd).
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
Hibernate libraries required to run the DAC Client or DAC server must be installed on each machine that runs the DAC client or DAC server. Hibernate libraries are not installed as part of Oracle BI Applications 7.9.x., but have to be downloaded from the Hibernate Web site. Oracle recommends that you download Hibernate Core Package Version 3.2.2 GA.
Newer versions of Hibernate Core Package 3.2 are now generally available. DAC is supported on the libraries of these versions also. You can download and use any version (for example, 3.2.x versions such as 3.2.3 or 3.2.4) of Hibernate Core Package that is Generally Available from http://www.hibernate.org. The Oracle BI Applications Installation and Configuration Guide describes how to download and copy these libraries.
This issue applies to Versions 7.9, 7.9.1, and 7.9.2. It is fixed in Version 7.9.3.
To correct this issue, do the following:
Replace W_SALES_ORDER_LINE_FS with W_SALES_SCHEDULE_LINE_FS in the SQL override of the following transformations:
LKP_W_SALES_BOOKING_LINE_F_Credit in SIL_SalesBookingLinesFact_Load_ScheduleLine_Credit
LKP_W_SALES_BOOKING_LINE_F in SIL_SalesBookingLinesFact_Load_ScheduleLine_Debt
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
In Chapter 5 of the Oracle Business Intelligence Installation and Configuration Guide, the section "Updating Siebel Transactional Database Schema Definitions" explains how to apply schema changes using the DDLIMP utility. In addition, refer to the following notes:
To run DDLIMP from command line, run the following command:
..\OracleBI\dwrep\bin\DDLIMP /U <USER> /P <PASSWORD> /C <ODBC_CSN> /GSSE_ROLE /F <..\OracleBI\dwrep\DDL_OLTP.CTL> /L <..\oracleBI\dwrep\DDL_OLTP.log>
NOTE: For Oracle, please use the Siebel Merant ODBC Drivers. For MSSQL you need to use both flags /w and /Z. The syntax for MSSQL Server is:
..\OracleBI\dwrep\bin\DDLIMP /U <USER> /P <PASSWORD> /C <ODBC_CSN> /GSSE_ROLE /W Y /Z Y /F <..\OracleBI\dwrep\DDL_OLTP.CTL> /L<..\oracleBI\dwrep\DDL_OLTP.log>
Additionally you can use the following commands:
/W Y (if the OLTP database is Unicode).
/Z Y (if the OTTP database is DB2 and Unicode).
/B <TABLE_SPACE_NAME> if you want to create these table in a separate table space.
/X <INDEX_TABLE_SPACE_NAME> if you want to create the indexes in a separate table space.
/Y Storage File for DB2/390.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
In Oracle Order Management and Fulfillment Analytics, W_CUSTOMER_STATUS_HIST_F is a fact table that tracks the status of customers based on the frequency of orders they place with the organization. Possible statuses are NEW, RECENT, DORMANT and LOST. The time duration for each status bucket is configurable, out of the box being a calendar year. The grain of this table is at a Customer, Customer Status and the Status Start Date level. This section explains the possible configurations available for this table, what they mean and how to implement them.
This section talks about the following configurations that are available for the Customer Status History Fact table:
Configure the Data Warehouse Identifier
Configure the Period for each status bucket
Configuring the Data Warehouse Identifier
This table uses some of the Oracle Business Intelligence Applications defined statuses, like NEW, RECENT, DORMANT and LOST. These status data gets loaded into the Data Warehouse directly through an out of box pre-packaged CSV file. The data in the file is independent of any specific OLTP source systems where your Customer or Sales data resides. In order to differentiate between source-based statuses from the pre-packaged out of box Data Warehouse statuses, a definite identifier is required. The Informatica mapping parameter $$WH_DATASOURCE_NUM_ID serves that purpose.
A pre-packaged value equal to 999 is set out of the box. Ideally you would not need to configure this value unless you have chosen to use this number (999) for a specific data source of yours, like Oracle EBS 11.5.10, etc.
Configuring the Period for each status bucket
When a customer orders some products/services from your organization for the first time, Oracle Business Intelligence Applications sets the status for the customer as NEW. The customer maintains the same status if he/she shows a constant order pattern, as long as the duration between any two of his/her orders is less than a configurable/business defined period. The value (out of box being 365 days) of this Informatica parameter $$PERIOD is configurable. An use case for that would be a Fast Moving / Retail Goods company many define 30 days as their choice of period, whereas a Slow Moving company may be even happy with 730 days as period.
In case the customer is seen to have not ordered anything for more than one period, he/she is moved to the next status, RECENT. Similarly, no orders for one more period since he/she became RECENT would make him/her DORMANT. And lastly, he/she is set to LOST if no orders were seen for more than one more period after he/she became DORMANT.
However, if a customer orders while in DORMANT status, for instance, Oracle Business Intelligence Applications would upgrade his/her status back to RECENT. If the customer were in LOST status, and he/she orders, then he/she will be upgraded back to RECENT.
All these examples above illustrate how important it is for the business to set the right value for the period. Organizations would tend to launch different campaigns targetting to different customers based on their current status, or order-patterns, putting it in a different way.
This section explains the steps involved in configuring the Customer Status History Fact table:
Configure $$WH_DATASOURCE_NUM_ID
Configure $$PERIOD
Configure $$WH_DATASOURCE_NUM_ID
The out of box value for this parameter is equal to 999. If you have chosen this value to represent one of your actual OLTP source systems, then you need to modify this parameter. Otherwise, you do not have to. If you are modifying the value of this parameter, make sure you select a value that is not used-up by any of your OLTP source systems.
To modify the value of $$WH_DATASOURCE_NUM_ID:
Log on to the DAC repository in Design mode and navigate to the appropriate container that signifies your OLTP source system.
Click on the Source System Parameters tab and locate the parameter $$WH_DATASOURCE_NUM_ID.
In the bottom pane, set the new value of your choice. Make sure you choose a number (not text) that is not yet used-up.
Save and exit the DAC
To configure $$PERIOD:
The out of box value for this parameter is equal to 365. If your business demands something else, make sure you provide the value in days.
To modify the value of $$WH_DATASOURCE_NUM_ID:
Log on to the DAC repository. Be in Design mode and navigate to the appropriate container that signifies your OLTP source system.
Click on the ¡°Tasks¡± tab and query for the following two tasks:
PLP_CustomerStatusHistoryFact_New_Customers_Load
PLP_CustomerStatusHistoryFact_Status_Revalidate
For each of the above tasks, set the new value of your choice in the bottom pane. Make sure you the set same values for both tasks.
Save and exit the DAC.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
The DAC Deployment procedure causes sequence generators transformations in the ETL to be reset to start from the number 1 again. This can cause some issues when running Source Independent Load (SIL) mappings for the different applications and adapters together. These SIL mappings will be unable to run together.
To illustrate this limitation, two examples of ETLs being unable to run together are shown below:
When the Siebel Vertical adapter and Oracle EBS adapter is used, the Siebel adapter leverages the SIL mappings in the SIL_Vert folder, while the Oracle EBS adapter leverages the SIL mappings in the SILOS folder. The sequence generators in the SIL_Vert folder gets updated to new values, while the same ones in the SILOS folder does not. This results in all SILOS mappings for common dimensions (like Employee, Exchange Rate, etc.) to fail.
The same dimensional tables are loaded from mappings within the SILOS folder and the PLP folders. This results in the same issue as above, and when the mappings are run, they fail.
The workaround is to set the Sequence Generator value to run between 1 and a sufficiently large finite value (for example, 1,000,000,000) for one of the folders and set the SILOS folder Sequence Generator value to run between 1,000,000,001 and its maximum limit of 2,000,000,000.
This issue applies to Versions 7.9, 7.9.1, 7.9.2. It is fixed in Version 7.9.3.
The following mappings fail with ORA-01652: unable to extend temp segment by 64 in tablespace TEMP:
SIL_GLAccountDimension_SCDUpdate
SIL_ProfitCenterDimension_SCDUpdate
SIL_SupplierProductDimension_SCDUpdate
Each of them reference dimensions:
W_GL_ACCOUNT_D
W_PROFIT_CENTER_D
W_SUPPLIER_PRODUCT_D
This information is applicable to customers of Oracle Business Intelligence Applications 7.9 who have Oracle E-Business Suite (Oracle EBS) as a source system. In Oracle Business Intelligence Applications version 7.9.3, the granularities of the following three dimensions have changed:
GL Account Dimension (W_GL_ACCOUNT_D)
Profit Center Dimension (W_PROFIT_CENTER_D)
Cost Center Dimension (W_COST_CENTER_D)
This granularity change impacts the dimensional tables as well as the fact tables. For more information on these granularity changes, refer to Technical Note 676 on SupportWeb at http://supportweb.siebel.com.
This issue applies to Versions 7.9, 7.9.1, and 7.9.2. It is fixed in Version 7.9.3.
In the source qualifier of SDE_ORA_SalesInvoiceLinesFact, there is a data type mismatch on two sides of the following join.
...
FROM RA_CUSTOMER_TRX_LINES_ALL, OE_ORDER_LINES_ALL
WHERE (CASE WHEN RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_CONTEXT ='ORDER ENTRY' THEN RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6
ELSE NULL END) = OE_ORDER_LINES_ALL.LINE_ID (+);
...
This issue results in the following error:
ERROR at line 1: ORA-01722: invalid number
Two columns with different data types are being compared: RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6 is varchar2. While OE_ORDER_LINES_ALL.LINE_ID is number.
For customers with Versions 7.9, 7.9.1, and 7.9.2, the following steps will fix this issue.
Change the join in source qualifier of SDE_ORA_SalesInvoiceLinesFact to the following:
...
FROM RA_CUSTOMER_TRX_LINES_ALL, OE_ORDER_LINES_ALL
WHERE (CASE WHEN RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_CONTEXT='ORDERENTRY' THEN RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6
ELSE NULL END) = TO_CHAR (OE_ORDER_LINES_ALL.LINE_ID (+));
...
Save the changes.
This issue applies to Versions 7.9, 7.9.1, and 7.9.2. It is fixed in Version 7.9.3.
The expressions for EXT_SALES_ORDER_NUM, EXT_SALES_ORDER_ITEM, and EXT_ITEM_DETAIL_NUM in MPLT_SA_ORA_SalesInvoiceLinesFact are inconsistent with SALES_ORDLN_ID.
For customers with Versions 7.9, 7.9.1, and 7.9.2, the following steps will fix this issue.
Change the expressions of SALES_ORDER_NUM, SALES_ORDER_LINE and ITEM_DETAIL_NUM in MPLT_SA_ORA_SalesInvoiceLinesFact as follows:
IIF(INP_INTERFACE_LINE_CONTEXT='ORDER ENTRY', INP_SALES_ORDER, NULL)
IIF(INP_INTERFACE_LINE_CONTEXT='ORDER ENTRY', INP_SALES_ORDER_LINE, NULL)
IIF(INP_INTERFACE_LINE_CONTEXT='ORDER ENTRY', INP_ITEM_DETAIL_NUMBER, NULL)
This issue applies to Versions 7.9, 7.9.1, and 7.9.2. It is fixed in Version 7.9.3.
The expression for Shippable Flag in MPLT_SA_ORA_SalesOrderLinesFact is incorrect.
For customers with Versions 7.9, 7.9.1, and 7.9.2, the following steps will fix this issue.
Change the expression for Shippable Flag in MPLT_SA_ORA_SalesOrderLinesFact to:
IIF(INP_CANCELLED_FLAG = 'N' AND INP_SHIPPABLE_FLG = 'Y', 'Y', 'N')
This issue applies to Versions 7.9, 7.9.1, and 7.9.2. It is fixed in Version 7.9.3.
The 'effective from date' and 'effective to date' in the W_INT_ORG_D dimension table are set incorrectly. The installed product maps these dates to DATE_FROM and DATE_TO in the HR_ALL_ORGANIZATION_UNITS table. However, these dates are not apart of the unique key of the table and might not be reliable if application logic does not use them appropriately and hence they should not be used for SCD effectivity.
For customers with Versions 7.9, 7.9.1, and 7.9.2, the following steps will fix this issue.
ETL Fix:
In Informatica Mapping Designer, find the expression transformation EXP_W_INT_ORG_D_W_INT_ORG_DS_ORGANIZATION_UNITS in the SDE_ORA_InternalOrganizationDimension mapping.
Change the expression of the port EFFECTIVE_FROM_DT to TO_DATE('01/01/1899 00:00:00','MM/DD/YYYY HH24:MI:SS').
Change the expression of the port EFFECTIVE_TO_DT to NULL.
Save the mapping.
Data Upgrade:
If you have already loaded data in the W_INT_ORG_D table, it is difficult to fix the data because you have to fix the dimension and all the foreign keys in every fact table.
To detect errors, run the following query:
select integration_id, EFFECTIVE_fROM_DT, EFFECTIVE_TO_DT from W_INT_ORG_D where integration_id in (Select integration_id from w_int_org_D group by integration_id having max(effective_to_dt) <> '1-JAN-3714' or min(effective_from_Dt) <> '1-JAN-1899') order by 1
If you have fact transactions linked to Organizations at times outside the period that you see above, those transactions will not have the Organization foreign key populated in your warehouse. The Oracle application should not have allowed you to select the organization in the first place, so the chances of such data existing is remote. To ensure that your data is valid and prevent this inconsistency occurring in the future, run the following SQL command to set the boundaries of the W_INT_ORG_D:
update W_INT_ORG_D_backup set EFFECTIVE_FROM_DT = '1-JAN-1899' where (integration_id, datasource_num_id, effective_From_dt) in (select integration_id, datasource_num_id, min(effective_from_Dt) from w_int_org_D group by integration_id, datasource_num_id); Commit;
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
If you are using Siebel OLTP 8.0, you must add a filter in the RPD fiscal layer, as follows:
Display the Oracle BI Administration Tool.
In the physical layer under Forecasting Siebel OLTP connection pool, open the table S_FCST_ITEM_DTL.
Change the Type to Select.
Paste the following SQL into the window.
S_FCST_ITEM_DTL.AGGR_DIM_NAME_1 IS NULL AND
S_FCST_ITEM_DTL.AGGR_DIM_NAME_2 IS NULL AND
S_FCST_ITEM_DTL.AGGR_DIM_NAME_3 IS NULL AND
S_FCST_ITEM_DTL.AGGR_DIM_NAME_4 IS NULL AND
S_FCST_ITEM_DTL.AGGR_DIM_NAME_5 IS NULL AND
S_FCST_ITEM_DTL.AGGR_DIM_NAME_6 IS NULL
Save the changes.
This issue applies to Versions 7.9, 7.9.1, and 7.9.2. It is fixed in Version 7.9.3.
If you are using Oracle BI Applications source employee data from Oracle E-Business Suite (EBS), the ETL mappings populating W_EMPLOYEE_D might fail if the same employee is represented as a Sales Rep in two different Operating Units.
This issue applies to Versions 7.9, 7.9.1, and 7.9.2. It is fixed in Version 7.9.3.
Oracle Business Intelligence Applications version 7.9.3 offers enhancements to the Data Security mechanisms applied to Oracle E-Business Suite 11i. For details on implementation steps to ensure that when different users look at the same report, the data is different depending on a user's access rights and roles in the organization, refer to Technical Note 676 on SupportWeb (http://supportweb.siebel.com).
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
The email personalization formats that are installed out-of-the-box in Siebel 8.0 are not constraining the generated lists by treatment ID. As a result, when a campaign launch issues the SOAP call to generate list files for a given treatment, everyone that qualifies for the campaign is being returned in the list. For example, if the campaign has two email treatments that have been allocated to two different sets of campaign members, this issue causes all campaign members to receive both treatments.
This section explains how to work around this issue.
This section explains how to update the repository in Oracle BI Administration Tool.
Start the Oracle BI Administration Tool.
In the Physical Layer, add a physical column DCP_ID to the Campaign Promotion physical table in Marketing OLTP database and specify the following values in the Physical Column dialog:
Name: DCP_ID
Type: VARCHAR
Length: 15
Nullable: yes
In the Business Model and Mapping Layer, add a logical column Treatment Id to the OLTP Campaign Promotion logical table in Marketing Contact List business model and specify the following values in the Logical Column dialog:
Name: Treatment Id
Logical Table Source: S_CAMP_CON
Mapped as: "Marketing OLTP".dbo."Campaign Promotion".DCP_ID
In the Business Model and Mapping Layer, add a logical column Treatment Id to the OLTP Campaign Promotion logical table in Marketing Account List business model and specify the following values in the Logical Column dialog:
Name: Treatment Id
Logical Table Source: S_CAMP_CON
Mapped as: "Marketing OLTP".dbo."Campaign Promotion".DCP_ID
In the Presentation Layer, add the Treatment Id presentation column to the Campaign History (Transaction Database) presentation table in the Marketing Contact List presentation catalog and specify the following values in the Presentation Column dialog:
Name: Treatment Id
Logical Column: "Marketing Contact List"."OLTP Campaign Promotion"."Treatment Id".
In the Presentation Layer, add the Treatment Id presentation column to the Campaign History (Transaction Database) presentation table in the Marketing Account List presentation catalog and specify the following values in the Presentation Column dialog:
Name: Treatment Id
Logical Column: "Marketing Account List"."OLTP Campaign Promotion"."Treatment Id".
This section explains how to update the Campaign Load Format and Email Server Format in Siebel Marketing.
Log in to Siebel Marketing.
Add Treatment Id to the Campaign Contact integration component and specify the following values in the Edit Column Formula dialog:
Table heading: Campaign Contact
Column Heading: Treatment Id
Column Formula: '@{treatmentID}{0}'
Add a filter to constrain the output based on the Treatment Id column and specify the following values in the Create/Edit Filter dialog:
Operator: is equal to / is in
Expression: '@{treatmentID}{0}'
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
When you import the DAC metadata and deploy the warehouse schema, Oracle recommends that you do the following:
You should always import the 'Universal' container.
When deploying the warehouse schema, you should always leave the 'Container' field blank in the Configurator, so that it will do a union of all containers.
This issue applies to Versions 7.9, 7.9.1, and 7.9.2. It is fixed in Version 7.9.3.
The logical column "Purchase Invoice Number" in "Core"."Dim - PurchaseInvoice Details" has been wrongly mapped to the physical column "REF_DOC_NUM" in table Fact_W_AP_AGING_INVOICE_A. It should be mapped to "PURCH_INVOICE_NUM" in table Fact_W_AP_AGING_INVOICE_A.
The solution to this issue is:
Start Oracle Business Intelligence Administration Tool.
Go to the Business Model and Mapping layer.
Locate "Dim - Purchase Invoice Details" in "Core".
Expand the "Sources" folder.
Double click on "Fact_W_AP_AGING_INVOICE_A".
Go to Column Mapping Tab and identify "Purchase Invoice Number".
Click on expression builder and change the expression to "Oracle Data Warehouse"."Catalog"."dbo"."Fact_W_AP_AGING_INVOICE_A"."PURCH_INVOICE_NUM".
Click "OK" and save your changes.
For Oracle EBS source systems, this issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3. For PeopleSoft source systems, this issue applies to Version 7.9.3.
The username that appears in the Greetings message in the Oracle Business Intelligence Interactive Dashboards does not display correctly if the source system is either Oracle E-Business Suite (EBS) or Oracle's PeopleSoft. The variable DISPLAY_NAME used in the Greetings message in the dashboard header gets populated through an Initialization Block called LOGIN Properties. Out of the box, the connection pool and SQL statements used in this init block point to the Siebel OLTP. If you are running Oracle Business Intelligence Applications and your source system is either Oracle EBS or PeopleSoft, you need to change the Connection pool and data source SQL for the Initialization Block: LOGIN Properties, as described below.
The solution to this issue is:
Open the EnterpriseBusinessAnalytics.rpd file using Oracle BI Administration Tool.
Navigate to Manage > Variables to open the Variables Manager.
Under Session > Initialization Block, select the "LOGIN Properties" initialization block.
Double-click to open the properties dialog box.
Click on the Edit Data Source button.
Click on the Browse button.
In the Select Connection Pool window, select either Oracle EBS OLTP Connection Pool or PeopleSoft OLTP Connection Pool, depending on your OLTP system.
In the Default Initialization String box on the "Session Variable Initialization Block Data Source - LOGIN Properties" window, enter the SQL as appropriate for your source system application and the database platform it is running on.
Table 1-1 Required SQL Strings For Each Source System Application And Database Combination
| Source System Application (and Database Platform) | SQL required for Default Initialization String |
|---|---|
|
Oracle EBS (Oracle RDBMS) |
Select PER.FULL_NAME, 0 from PER_ALL_PEOPLE_F PER, FND_USER USR WHERE USR.USER_NAME= ':USER' AND USR.EMPLOYEE_ID=PER.PERSON_ID AND (SYSDATE <= USR.END_DATE OR USR.END_DATE IS NULL) AND PER.EFFECTIVE_START_DATE <= USR.START_DATE AND (USR.START_DATE < PER.EFFECTIVE_END_DATE OR PER.EFFECTIVE_END_DATE IS NULL) |
|
PeopleSoft (Oracle RDBMS) |
SELECT CASE WHEN EMPLOYEE_NAME_TODAY.EMPLID IS NULL THEN USR.OPRDEFNDESC ELSE EMPLOYEE_NAME_TODAY.NAME END DISPLAY_NAME, 0 FROM PSOPRDEFN USR LEFT OUTER JOIN (SELECT B.EMPLID, B.NAME FROM PS_NAMES B, (SELECT EMPLID, MAX(EFFDT) EFFDT FROM PS_NAMES WHERE NAME_TYPE = 'PRI' AND EFFDT <= SYSDATE GROUP BY EMPLID) C WHERE B.EMPLID = C.EMPLID AND B.EFFDT = C.EFFDT AND B.NAME_TYPE = 'PRI') EMPLOYEE_NAME_TODAY ON USR.EMPLID = EMPLOYEE_NAME_TODAY.EMPLID WHERE USR.OPRID=':USER' |
|
PeopleSoft (MSSQL RDBMS) |
SELECT CASE WHEN EMPLOYEE_NAME_TODAY.EMPLID IS NULL THEN USR.OPRDEFNDESC ELSE EMPLOYEE_NAME_TODAY.NAME END DISPLAY_NAME, 0 FROM PSOPRDEFN USR LEFT OUTER JOIN (SELECT B.EMPLID, B.NAME FROM PS_NAMES B, (SELECT EMPLID, MAX(EFFDT) EFFDT FROM PS_NAMES WHERE NAME_TYPE = 'PRI' AND EFFDT <= GETDATE() GROUP BY EMPLID) C WHERE B.EMPLID = C.EMPLID AND B.EFFDT = C.EFFDT AND B.NAME_TYPE = 'PRI' ) EMPLOYEE_NAME_TODAY ON USR.EMPLID = EMPLOYEE_NAME_TODAY.EMPLID WHERE USR.OPRID=':USER' |
|
PeopleSoft (DB2 RDBMS) |
SELECT CASE WHEN EMPLOYEE_NAME_TODAY.EMPLID IS NULL THEN USR.OPRDEFNDESC ELSE EMPLOYEE_NAME_TODAY.NAME END DISPLAY_NAME, 0 FROM PSOPRDEFN USR LEFT OUTER JOIN (SELECT B.EMPLID, B.NAME FROM PS_NAMES B, (SELECT EMPLID, MAX(EFFDT) EFFDT FROM PS_NAMES WHERE NAME_TYPE = 'PRI' AND EFFDT <= CURRENT TIMESTAMP GROUP BY EMPLID) C WHERE B.EMPLID = C.EMPLID AND B.EFFDT = C.EFFDT AND B.NAME_TYPE = 'PRI' ) EMPLOYEE_NAME_TODAY ON USR.EMPLID = EMPLOYEE_NAME_TODAY.EMPLID WHERE USR.OPRID=':USER' |
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
When running the ETL for HR Analytics against an Oracle EBS instance (11.5.8, 11.5.9, 11.5.10), the columns EVENT_TYPE_NAME, EVENT_SUB_CAT_NAME, EVENT_REASON_NAME, and EVENT_CAT_NAME are not populated in the W_EVENT_TYPE_D warehouse table.
The solution to this issue is:
In the Informatica repository, edit the Exp_W_EVENT_TYPE_D_Transform transform in the mplt_SIL_EventTypeDimension mapplet in the SILOS folder.
In the expression for Port number 6, EVENT_TYPE_NAME_OUT, replace:
$$MASTER_CODE_NOT_FOUND
with:
IIF(ISNULL(EVENT_TYPE_NAME),$$MASTER_CODE_NOT_FOUND,EVENT_TYPE_NAME)
In the expression for Port number 13, EVENT_CAT_NAME_OUT, replace:
$$MASTER_CODE_NOT_FOUND
with:
IIF(ISNULL(EVENT_CAT_NAME),$$MASTER_CODE_NOT_FOUND,EVENT_CAT_NAME)
In the expression for Port number 19, EVENT_SUB_CAT_NAME_OUT, replace:
$$MASTER_CODE_NOT_FOUND
with:
IIF(ISNULL(EVENT_SUB_CAT_NAME),$$MASTER_CODE_NOT_FOUND,EVENT_SUB_CAT_NAME)
In the expression for Port number 25, EVENT_REASON_NAME_OUT, replace:
$$MASTER_CODE_NOT_FOUND
with:
IIF(ISNULL(EVENT_REASON_NAME),$$MASTER_CODE_NOT_FOUND,EVENT_REASON_NAME)
This issue applies to Version 7.9.3.
The 7.9.3 release was modified to account for an additional use case related to backlog and shipment. In Versions 7.9, 7.9.1, and 7.9.2, the Shipping Interfaced Flag was used to evaluate whether an order item is eligible for operational backlog instead of the Shippable Flag. An order item should be considered for operational backlog as long as it is shippable. There could be cases when an order item is not interfaced for shipping like drop shipments but is still shippable and hence applicable for operational backlog.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
If the table definitions in the DAC contain Teradata reserve words, then those reserve words will appear in the DDL script generated and stored by the DAC under the folder \DAC\conf\sqlgen\sql\teradata\. When creating those tables using the DDL script, the database will return errors, (for example, "expecting something like a 'CHECK' keyword" ).
Workaround: Identify the reserve words and wrap double quotes (" ") around the reserve words before executing the SQL.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
The following facts in Sales Forecasting show incorrect values leading to errors in reporting:
Prior Forecast Summary Best Case
Prior Forecast Summary Worst Case
Prior Forecast Summary Revenue
Prior Forecast Summary Expected Revenue
Prior Forecast Summary Cost
Prior Forecast Summary Margin
This issue is caused by absence of necessary filters that need to be applied to the above metrics.
|
Note: This issue does not affect any out-of-the-box reports or dashboards. |
The workaround to solve this issue is to add the column "S_FCST_ITEM_DTL". "AGGR_DIM_NAME_1" in the physical layer and make necessary changes to the metric expressions in the logical layer. Following the detailed steps enumerated below will result in filtering out of the detail rows that are causing the issue.
To add the column "S_FCST_ITEM_DTL". "AGGR_DIM_NAME_1" in the physical layer and make necessary changes:
Open the repository (.rpd file) using the Oracle BI Administration Tool.
In the Physical layer of the repository, locate the table S_FCST_ITEM_DTL (path: Forecasting Siebel OLTP.Catalog.dbo).
Add the column AGGR_DIM_NAME_1 (Data type: varchar; Length: 50) to the table.
In the Business Model and Mapping layer of the repository, look for the metrics listed above under the folder Forecasting.FACTS.
For each metric, do the following:
Right click a metric and choose Properties.
Highlight the OLTP Prior Forecast Summary under Data Type and choose View….
Wrap the following around the metric expression:
CASE WHEN "Forecasting Siebel OLTP"."Catalog"."dbo"."S_FCST_ITEM_DTL"."AGGR_DIM_NAME_1" IS NULL THEN IFNULL ( ) END
The table below shows the amended expression for each metric.
Save the repository.
Table 1-2 Metric Expressions And Their Values After Being Updated
| Metric | Amended Expression |
|---|---|
|
Prior Forecast Summary Best Case |
|
|
Prior Forecast Summary Worst Case |
|
|
Prior Forecast Summary Revenue |
|
|
Prior Forecast Summary Expected Revenue |
|
|
Prior Forecast Summary Cost |
|
|
Prior Forecast Summary Margin |
|
This section provides release notes for Oracle Business Intelligence Pharma Analytics. It contains the following topics:
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
The Oracle BI Disconnected Application Manager displays an error and terminates when Oracle BI Disconnected is started after synchronization has completed successfully. The error occurs because the Oracle BI Disconnected RPD file name exceeds the current name length limit for the Disconnected RPD file in Oracle Business Intelligence Enterprise Edition 10.1.3.2.
To work around this issue, perform the following steps:
In the \OracleBIData\disconnected\pharma\pharma.xml configuration file, change the value of <repository name="PharmaDisconnect.rpd"/> to <repository name="Pharma.rpd"/>.
In the \OracleBIData\disconnected\pharma\Application folder, rename the RPD file from PharmaDisconnect.rpd to Pharma.rpd.
Synchronize Oracle BI Disconnected.
This issue applies to Versions 7.9, 7.9.1, 7.9.2, and 7.9.3.
When the Syndicated Data flat files are prepared for loading, the string case of the account/contact city, country, and zip code values in the data files must match the string case of the address information that is loaded from Oracle's Siebel Application's OLTP and stored in the W_GEO_D dimension table. During the Syndicated Data load process, the string values in the ACCNT_CITY, ACCNT_COUNTRY, ACCNT_ZIPCODE, CON_CITY, CON_COUNTRY, CON_ZIPCODE flat file fields are compared to the string values in W_GEO_D, and the corresponding Dimension WID value is populated into the syndicated data target fact tables as ACCNT_GEO_WID and CON_GEO_WID. The default configuration in the Informatica Repository uses an UPPER function call to convert these string values to uppercase for the string comparisons.
Inconsistent behavior of the UPPER function call has been observed on IBM DB2 UDB, which impacts the Address string value comparison in the Syndicated Data load. To work around this issue on IBM DB2 UDB databases, remove the UPPER function call from the LS_MPLT_FLATFILE_GEO_WID mapplet and LS_EXP_FLATFILE_CITY_COUNTRY_ZIP_CHKNULL transformation. In addition, ensure that the case of the City, Country, and Zipcode address string values in the Syndicated Data flat files exactly match the string values in W_GEO_D in order for the comparison to succeed and for ACCNT_GEO_WID and CON_GEO_WID to be correctly populated in the syndicated target fact tables.
To remove the UPPER function call from the mapplet and transformation, perform the following steps:
Open Informatica Designer and log into the Oracle_BI_DW_Base repository.
Open the SIL_Vert folder.
Open the Mapplet Designer in the right-hand window.
Go to Mapplets under the SIL_Vert folder. Then drag and drop LS_MPLT_FLATFILE_GEO_WID to the Mapplet Designer.
Double-click the LKP_W_GEO_D Lookup Procedure in the right-hand window.
Navigate to the Properties tab.
Click to open the Lookup SQL Override. The SQL Editor Window is displayed.
Change the default SQL statement by removing the UPPER function call as shown here:
SELECT
W_GEO_D.ROW_WID as ROW_WID,
W_GEO_D.COUNTRY as COUNTRY,
W_GEO_D.ZIPCODE as ZIPCODE,
W_GEO_D.CITY as CITY
FROM
W_GEO_D
ORDER BY
W_GEO_D.COUNTRY,
W_GEO_D.ZIPCODE,
W_GEO_D.CITY,
ROW_WID -
Click OK, Apply, and OK again.
Save the repository.
Open the Transformation Developer.
Go to Transformations under the SIL_Vert folder.
Drag and drop the LS_EXP_FLATFILE_CITY_COUNTRY_ZIP_CHKNULL to the Transformation Developer.
Double-click the LS_EXP_FLATFILE_CITY_COUNTRY_ZIP_CHKNULL expression that is displayed in the Transformation Developer.
Navigate to the Ports tab.
Go to the OUT_CITY port name and open the Expression.
Remove the UPPER function call from the default expression as shown here: lv_OUT_CITY
Click OK.
Go to the OUT_COUNTRY port name and open the Expression.
Remove the UPPER function call from the default expression as shown here: lv_OUT_COUNTRY
Click OK, Apply, and OK.
Save the repository.
