1 Release Notes

1.2.1 Source Systems and Database Platforms Not Supported

This issue applies to Version 7.9.6 only.

The product documentation may contain references to the following source systems and database platforms:

Source Systems:

• Siebel 7.7, 7.8

• PeopleSoft 8.8

Database platforms:

• Teradata v2R6.x, v12

• IBM DB2 for z/OS 8.2, 9.1

• Microsoft SQL Server 2000, 2005

These source systems and database platforms are not supported in this version of Oracle Business Intelligence Applications. Please ignore any references to them in the product documentation. For up-to-date information about supported databases and source systems in this version of Oracle Business Intelligence Applications, make sure you read System Requirements and Supported Platforms for Oracle Business Intelligence Applications. The most up-to-date version of this document is located on the Oracle Technology Network at: http://www.oracle.com/technology/documentation/bi_apps.html.

1.2.2 Certification Information

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:

http://metalink.oracle.com

1.2.3 Installation and Upgrade

This section provides release notes on installing and upgrading Oracle Business Intelligence Applications. It contains the following topics:

• Section 1.2.3.1, "Installation Error Caused by Invalid Characters in Installation Directory Names"

• Section 1.2.3.2, "Setting the Accept Source Flag when using the DAC Upgrade/Merge Wizard"

• Section 1.2.3.3, "Issues with Extracting Informatica PowerCenter Zipped Media Files"

• Section 1.2.3.4, "796_UPGRADE_POST_SCRIPT.sql Fails to Drop Indexes During Upgrade"

• Section 1.2.3.5, "Installer Generates Invalid RPD File for Oracle Pharma Sales Analytics (Disconnected)"

• Section 1.2.3.6, "Missing Repository RPD Files in \OracleBI\Upgrade Folder"

• Section 1.2.3.7, "Task SDE_PSFT_PROJECTDIMENSION_UPG796 Fails During Upgrade"

• Section 1.2.3.8, "Correction in Siebel Repository Upgrade From V7.9.5.1 to V7.9.6"

• Section 1.2.3.9, "JDE Upgrade - JDE Parameters Missing From 7961_UPG_PARAMS.TXT"

• Section 1.2.3.10, "UPGRADE 7951->7961: W_DTL_FCST_F.OWNER_POSTN_DH_WID DIFFERENCES"

• Section 1.2.3.11, "GLOBAL1_TAX_AMOUNTS GETTING NULL VALUES IN 796 TO 7961 UPGRADE"

UPDATE W_ETL_MERGEDATA SET ACCEPT_FLG='Y' WHERE DIS_CHANGE_TYPE IN ('added-target', 'deleted-source').

1.2.4 Documentation Corrections

This section provides corrections for various pieces of the documentation set for Oracle Business Intelligence Applications. It contains the following topics:

• Section 1.2.4.1, "Corrections to Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users"

• Section 1.2.4.2, "Corrections to Oracle Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users"

• Section 1.2.4.3, "Corrections to Oracle Business Intelligence Applications Upgrade Guide for Informatica PowerCenter Users"

• Section 1.2.4.4, "Corrections to Oracle Business Intelligence Data Warehouse Administration Console Guide"

• Section 1.2.4.5, "Corrections to Oracle Business Analytics Warehouse Data Model Reference"

• Section 1.2.4.6, "Corrections to Oracle Business Intelligence Applications Security Guide"

1.3.1 Lack of Time Zone Setting Prevents Delivery of iBots to Applications Users

This issue applies to Versions 7.9.6 and 7.9.6.1.

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.

Workaround

To work around this issue, perform these steps:

1. In Oracle BI Administration Tool, open the Oracle BI repository file OracleBIAnalyticsApps.rpd.

2. In the Business Model and Mapping layer, expand the SA System Business Model and the USER Logical Table.

3. Double-click the S_USER Logical Table Source under Sources in the USER Logical Table.

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

1.3.2 Issue with Exchange Rates and Transaction Currencies

This issue applies to Versions 7.9.6 and 7.9.6.1.

Current design and support of multiple currencies within Oracle Business Intelligence Applications and the data warehouse assumes that the transactional system (or OLTP system) provides exchange rates and table structures that store exchange rates.

If the OLTP system does not provide exchange rates from the 'transaction currency' to the chosen 'one or more data warehouse currencies', then the Fact table will 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).

Workaround

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.

1.3.3 Contact Geography Attributes in Campaign Contacts_Segmentation Catalog Do Not Join Appropriately

This issue applies to Versions 7.9.6 and 7.9.6.1.

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.

Workaround

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.

1.3.4 Issues with Multi-Source ETL

This issue applies to Versions 7.9.6 and 7.9.6.1.

The DAC Deployment procedure causes sequence generator 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 do 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.

Workaround

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.

1.3.5 Email Personalization for Siebel 8.0

This issue applies to Versions 7.9.6 and 7.9.6.1.

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.

Workaround

This section explains the workaround for this issue.

1.3.6 Incorrect Username Displayed in the Greetings Message in Oracle Business Intelligence Interactive Dashboards

This issue applies to Versions 7.9.6 and 7.9.6.1.

The user name 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.

Workaround

The workaround to this issue is:

1. In Oracle BI Administration Tool, open the Oracle BI repository file OracleBIAnalyticsApps.rpd.

2. Navigate to Manage > Variables to open the Variables Manager.

3. Under Session > Initialization Block, select the 'LOGIN Properties' initialization block.

4. Double-click to open the properties dialog box.

5. Click on the Edit Data Source button.

6. Click on the Browse button.

7. In the Select Connection Pool window, select either Oracle EBS OLTP Connection Pool or PeopleSoft OLTP Connection Pool, depending on your OLTP system.

8. 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'

   
   

1.3.7 Missing Language Folders

This issue applies to Version 7.9.6 only.

The following language folders are missing from the location %:\oraclebidata\disconnected\pharma\messages.

• l_ar - Arabic

• l_el - Greek

• l_hu - Hungarian

• l_iw - Hebrew

• l_no - Norwegian

• l_pl - Polish

• l_ro - Romanian

• l_ru - Russian

• l_sk - Slovakian

• l_th - Thai

• l_tr - Turkish

Workaround

1. Go to %:\oraclebidata\disconnected\pharma\messages\ and add the following folders:

   • l_ar

   • l_el

   • l_hu

   • l_iw

   • l_no

   • l_pl

   • l_ro

   • l_ru

   • l_sk

   • l_th

   • l_tr

2. Copy the corresponding _iBotsCaptions.xml and PharmaCaptions.xml files from %:\oraclebidata\web\res\l_XX\Captions\ to %:\oraclebidata\disconnected\pharma\messages\l_XX\.

1.3.8 Error in Reports Based on 'Opportunity' Under Opportunity Contact Segmentation

This issue applies to Versions 7.9.6 and 7.9.6.1.

This issue occurs when creating a report by selecting all of the attributes from OPPORTUNITY under the subject area, OPPORTUNITY CONTACT SEGMENTATION. If you create a report with this criteria, your system will give the following ODBC error:

Odbc driver returned an error (SQLExecDirectW)
Error Details 
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 14026] Unable to navigate requested expression: Fact - Marketing - Segmentation Opportunity Contact.Implicit Fact Column. Please fix the metadata consistency warnings. (HY000) SQL Issued: SELECT Opportunity."Opportunity Name" saw_0, Opportunity."Opportunity Account Name" saw_1, Opportunity."Opty Status" saw_2, Opportunity."Lead Quality" saw_3, Opportunity."Lead Age Category" saw_4, Opportunity."Deal Size" saw_5, Opportunity."Primary Competitor" saw_6, Opportunity."Sales Stage Name" saw_7, Opportunity."Sales Method" saw_8, Opportunity."Targeted Opportunity Flag" saw_9, Opportunity."Reason Won or Lost" saw_10, Opportunity."Competitor ROW_ID" saw_11, Opportunity.ROW_ID saw_12 FROM "Opportunity Contact_segmentation" ORDER BY saw_0, saw_1, saw_2, saw_3, saw_4, saw_5, saw_6, saw_7, saw_8, saw_9, saw_10, saw_11, saw_12 " 

Workaround

There is no workaround for this issue.

1.3.9 Teradata Connection Configuration

This issue applies to Versions 7.9.6 and 7.9.6.1.

This section is only relevant if you are running your data warehouse in a Teradata database. For detailed information about using the TPump external loader, refer to Informatica documentation and Teradata documentation.

When using a TPump command, if get the following error messages (or similar error messages), make sure that you have set the parameters listed below:

TRANSF_1_1_1> DBG_21216 Finished transformations for Source Qualifier [Sq_W_POSITION_DS]. Total errors [0] 
WRITER_1_*_1> WRT_8047 Error: External loader process [2192] exited with error [12] 
WRITER_1_*_1> CMN_1761 Timestamp Event: [Mon Apr 28 14:39:54 2008] 
WRITER_1_*_1> WRT_8004 Writer initialization failed [Error opening session output file [\\.\pipe\w_XXXX_d1.out] [error=]]. Writer terminating. 
WRITER_1_*_1> CMN_1761 Timestamp Event: [Mon Apr 28 14:39:54 2008] 
WRITER_1_*_1> WRT_8047 Error: External loader process [2192] exited with error [12] 
WRITER_1_*_1> CMN_1761 Timestamp Event: [Mon Apr 28 14:39:54 2008] 
WRITER_1_*_1> WRT_8088 Writer run terminated. [External loader error.]

To run the Tpump loader, you need to specify the following parameters at the command line in the order specified:

• Database Name: Enter the Teradata Database name.

• Error Database Name: Enter the Teradata Database name.

• Log Table Database Name: Enter the Teradata Database name.

• Error Table name: Enter the name of the table to use as the error table.

• Log Table name: Enter the name of table to use as the log table.

Notes

The following attributes can be specified at the connection level:

Database

Error Database

Log Table Database

The following attributes are specified at the workflow level:

Error Table

Log Table

Workaround

Not applicable.

1.3.10 Installation Errors with Oracle Applications Server Advanced Security Option

This issue applies to Versions 7.9.6 and 7.9.6.1.

When installing Oracle Business Intelligence Applications on top of an installation of the Oracle Business Intelligence Enterprise Edition that was installed using the Oracle Applications Server advanced security option, the following error message will be displayed:

"Password length is less than the required minimum, 8"

The Oracle Business Intelligence Applications installation will abort and the OracleBIAnalyticsApps.rpd file will be corrupted. The error happens because the MINIMUM_PASSWORD_LENGTH setting in the NQSConfig.ini file was set to 8 by the OBIEE installation.

Workaround

The workaround is to change the value of MINIMUM_PASSWORD_LENGTH to 0 in NQSConfig.ini, install Oracle Business Intelligence Applications, then change the value back to 8.

1.3.11 ODBC Error When Generating Reports from Some Reports

This issue applies to Versions 7.9.6 and 7.9.6.1.

When generating reports from OBI Presentation Services for HR, you might get the following error:

ODBC ERROR - END OF FILE COMMUNICATION ERROR

This error can occur with Oracle database versions 10.2.0.1, 10.2.0.2, and 10.2.0.3.

Workaround

The workaround for this issue is to upgrade the Oracle database version to 10.2.0.4.

1.3.12 'STAT' Currency Journals or Accounts in Financial Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

This issue is specific to Oracle eBusiness Suite adaptors used in conjunction with Financial Analytics.

Currently Oracle BI Applications doesn't support extracting statistical GL balances or journals. No accounts or transactions with a STAT currency code are brought into the warehouse. The existing out-of-the-box mappings SDE_ORA_GLBalanceFact and SDE_ORA_GLJournals contain a filter to filter out the 'STAT' records.

Workaround

To support 'STAT' currency, perform the task specified below:

1. Log into Informatica Designer.

2. Open the SDE folder.

3. Locate the mapping SDE_ORA_GLBalanceFact.

4. Open the mapplet mplt_BC_ORA_GL_Balance_Fact.

5. Remove or comment out the line 'AND GL_BALANCES.CURRENCY_CODE <> 'STAT' from SQL Qualifier.

6. Save the changes.

Perform similar steps for the mapping SDE_ORA_GLJournals:

1. Log into Informatica Designer.

2. Open the SDE folder.

3. Locate the mapping SDE_ORA_GLJournals.

4. Open the mapplet mplt_BC_ORA_GLXactsJournalsExtract.

5. Remove or comment out the line 'AND GL_JE_HEADERS.CURRENCY_CODE<>'STAT'' from SQL Qualifier.

6. Save the changes.

1.3.13 Requisition Age Band Calculated in RPD Using Current Date

This issue applies to Versions 7.9.6 and 7.9.6.1.

Job requisition age bands are calculated and stored in the recruitment fact tables in the warehouse. However, the age bands are only calculated in the warehouse when a recruitment event has occurred. This means that if an open requisition has no recruitment activities, the corresponding age band would be missing.

To ensure that age bands are available for reporting, the repository (RPD) calculates requisition age using the formula CURRENT_DATE-QSTN_OPENED_DATE and then assigns the requisition age to the appropriate age band based on the job requisition age band configuration.

Workaround

Not applicable.

1.3.14 Performance Issue with Activity Query

If you are implementing Oracle Service Analytics, you should disable the two logical table sources that are part of the logical table Dim - Activity, to improve performance.

This issue applies to Versions 7.9.6 and 7.9.6.1.

Workaround

Do the following:

1. In Oracle BI Administration Tool, open the Oracle BI repository file OracleBIAnalyticsApps.rpd.

2. Navigate to the logical table sources for logical table, Dim - Activity.

3. Double click the logical table source, Fact_W_ACTIVITY_F_Created_Date.

4. Clear the Active check box to make this logical table source inactive.

5. Close the Properties pop-up window.

6. Repeat the previous steps for logical table source, Fact_W_ACTIVITY_F_Due_Date.

Disabling these two logical table sources enables the BI Server to establish a one-to-one (1:1) relationship between fact and dimension logical table sources, and avoids any self join on the W_ACTIVITY_F table.

1.3.15 Usage Accelerator Change from Fiscal to Calendar Based Analysis

This issue applies to Versions 7.9.6 and 7.9.6.1.

Usage Accelerator analysis has been changed back from a fiscal period based analysis to a calendar period based analysis. Consequently, all out-of-the-box reports in Usage Accelerator will be based on calendar periods.

Also, the following repository variables (referenced prior to 7.9.6), will be deleted:

• UA_CURRENT_FWEEK

• UA_CURRENT_FMONTH

• UA_CURRENT_FQUARTER

• UA_CURRENT_JULIAN_MONTH

The following variables (that already exist in the system) will respectively replace the above variables:

• CURRENT_WEEK

• CURRENT_MONTH

• CURRENT_QTR

• CURRENT_JULIAN_MONTH

Workaround

Not applicable.

1.3.16 Joining Campaign History Fact With Industry Dimension with Oracle Marketing Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

This issue affects the Campaign Contacts Segmentation subject area in Oracle Marketing Analytics. If you try to create a report in Oracle BI-EE Answers that joins Campaign History Fact with any of the fields in the Industry dimension, then you will get a metadata inconsistency error. This error is caused by a missing join between the Industry dimension and the W_CAMP_F table at the star schema level.

Workaround

There is no workaround for this issue.

1.3.17 Incorrect Results for Points Accrued value with Oracle Loyalty Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

This issue affects Oracle Loyalty Analytics. If you create a report in Oracle BI-EE Answers that includes the member name and member type columns from the Member Dimension and the number of Accrued Points or Member point balance metric, then the Points Accrued value and the associated 'point balance' calculation will show incorrect results.

Workaround

There is no workaround for this issue.

1.3.18 Incorrect Results for Frequency Score with Oracle Loyalty Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

This issue affects Recency, Frequency, Monetary (RFM) Analysis in Oracle Loyalty Analytics. If you create a report in Oracle BI-EE Answers that is based on the Frequency Score calculation, then the Frequency Score calculation value will be incorrectly based on the number of transactions up to the date one year ago, rather than the number of transactions during the previous 365 days. For example, if you look at the dashboard on June 1st, 2009, you see the number of transactions made up to June 1st, 2008, rather than the number of transactions made between June 1st 2008 and June 1st 2009.

Workaround

In Oracle BI Administration Tool, edit the RPD file in the Business Model and Mapping Layer. Use the Logical Column dialog to edit the Frequency Score column, and replace the YAGO function with the function YTD.

1.3.19 Employee Headcount Returns Null When Combined With Absence Type in Oracle HR Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

This issue affects the Working Days Lost report on the Absence Trend dashboard page in Oracle HR Analytics. The Working Days Lost report calculates the percentage of employees who are absent, using the following calculation:

/* % of employees who were absent */ (COUNT(DISTINCT "Employee Attributes"."Employee Number")/"Headcount Facts"."Employee Headcount")*100

However, when the dashboard is filtered by Absence Category or Type (for example, sickness or maternity), then the employee head count and employee absent rate will return null.

Workaround

If you need to calculate the employee absence rate for a specific absence type, then use Oracle BI Administration Tool to create a derived metric in the RPD. For example, perform the following steps to calculate the percentage of Employees on sick leave. The exact metric name can vary.

1. Add a new measure 'Absence Days (Due to Sickness)' in the RPD presentation layer with the following formula:

   sum(case when Dim_W_ABSENCE_TYPE_RSN_D.ABSENCE_CATEGORY_CODE = 'S' then Fact_W_ABSENCE_EVENT_F_Event.DAYS_DURATION else 0 END )
   

2. In Oracle BI Answers, calculate the percentage of employees on sick leave using the formula “Absence Days (Due to Sickness)”/Employee Headcount.

1.3.20 HR Event Metrics and Secondary Assignments in Oracle HR Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

This issue affects HR Workforce Event fact measures in Oracle HR Analytics. This issue does not affect delivered dashboard content. Workforce Facts are at Assignment level. As a result, metrics that are naturally calculated at the person-level (for example, Performance Rating, or Performance Change Count) repeat for each Assignment.

Workaround

A workaround can be applied at the end user level or the Administrator level.

The end-user workaround is to add a filter to the Oracle BI Answers report. For example, an end user might add the filter: Employment ->Primary Assignment Flag ='Y'.

The Administrator workaround is to use Oracle BI Administration Tool to multiply the measure by Headcount in the RPD for each Logical table source (Year, Quarter, Month, Day). For example, you might change Performance Rating Change Count, as follows:

FROM:

"Oracle Data Warehouse"."Catalog"."dbo"."Fact_W_WRKFC_EVT_MONTH_F_Event"."PFRT_CHANGE_IND"

TO:

"Oracle Data Warehouse"."Catalog"."dbo"."Fact_W_WRKFC_EVT_MONTH_F_Event"."PFRT_CHANGE_IND" * "Oracle Data Warehouse"."Catalog"."dbo"."Fact_W_WRKFC_EVT_MONTH_F_Event"."HEADCOUNT"

1.3.21 SEBL811:W_MKTG_LEAD_F.ACTL_COST_GLOBAL1_AMT Calculation Issue For Incremental Load

This issue applies to Version 7.9.6 only.

An incremental update of ACTL_COST_GLOBAL1_AMT columns in the W_MKTG_LEAD_F table does not complete properly when a subset of leads under the same campaign are changed. This happens because the ACTL_COST_GLOBAL1_AMT is recalculated against the changed leads only instead of all of the leads under the campaign. The affected mapping is PLP_MarketingLeadFact_PreCostUpdate under the PLP folder in the Informatica repository.

Workaround

1. Open Informatica Designer and connect to your repository.

2. Open the PLP folder and drag the mapping PLP_MarketingLeadFact_PreCostUpdate to the working area.

3. Open the SQL Qualifier transformation and replace the existing SQL with the following SQL:

   SELECT 
   SOURCE_WID, COUNT(*), TENANT_ID 
   FROM W_MKTG_LEAD_F 
   WHERE SOURCE_WID IN ( 
   SELECT DISTINCT SOURCE_WID 
   FROM W_MKTG_LEAD_F 
   WHERE W_MKTG_LEAD_F.ETL_PROC_WID = '$$ETL_PROC_WID' AND 
   W_MKTG_LEAD_F.SOURCE_WID > 0) 
   GROUP BY SOURCE_WID, TENANT_ID
   

4. Click OK to close the SQL editor.

5. Click OK to close the SQL Qualifier transformation.

6. Save the mapping.

7. Open Workflow Manager and connect to the repository.

8. Open the PLP folder and expand the session list.

9. Drag both PLP_MarketingLeadFact_PreCostUpdate and PLP_MarketingLeadFact_PreCostUpdate_Full into the working area.

10. From the right-click drop-down menu, choose Refresh Mapping, and then choose Validate.

11. Save both sessions.

1.3.22 Sessions In SDE_COSTLIST Workflow Marked As Impacted

This issue applies to Version 7.9.6 only.

When trying to run the extract mapping for Cost Lists from the Siebel 8.1.1 Adapter, one of the workflows will fail to run and post the following error message:

Session task instance [SIL_CostList_FixEndDates] : [TM_6795 The Repository Service marked the session or session instance as impacted, and the Integration Service is not configured to run impacted sessions.]

This issue only affects BI Applications version 7.9.6 and only the Siebel 8.1.1 ETL Adapter mappings for source dependent extracts. This happens because the sessions in the workflow, SDE_COSTLIST, under the folder SDE_SBL_811_Adaptor, in the Informatica repository were accidentally marked as impacted and not validated. Hence the workflow will not run.

Workaround

Validate all the sessions in the SDE_COSTLIST workflow. To do this, open the workflow in Informatica Workflow Designer. Then validate all the sessions and save the workflow. If the Informatica repository is versioned (the out of the box repository is shipped with versioning turned on), then there are additional steps to check the workflow out before validating and checking the workflow back into the repository.

1.3.23 Reports With Ago Metrics Gives An Error If Year Is Set To Blank

This issue applies to Versions 7.9.6 and 7.9.6.1.

If a dashboard parameter (filter) for 'Year' is set to blank (No value) to view the data for all years, then reports that contain AGO metrics give an error.

Many dashboards use dashboard parameters, and Calendar year is used as one of the dashboard parameters (filters) that allows the user to set it to either a single value (for a single year), multiple values (for multiple years), or no value (to view the data for all years without any filter). When this parameter (filter) is set to blank (or no value is selected), some of the reports using AGO metrics (for example, Year Ago, Quarter Ago, and Month Ago metrics), and having report totals will give errors.

Workaround

Add the following parameter string to the instanceconfig.xml file in OBI EE. This string can be added as a child of the WebConfig element anywhere in instanceconfig.xml.

<ReportAggregateEnabled>true</ReportAggregateEnabled>

For information about modifying the instanceconfig.xml file, see Oracle Business Intelligence Presentation Services Administration Guide Version 10.1.3.2. This book is in the Oracle BI EE library on OTN, on the Documentation tab, in the Administration section (URL: http://download.oracle.com/docs/cd/E10415_01/doc/nav/portal_booklist.htm).

1.3.24 Actual Versus Budget Comparison Not Supported For PeopleSoft GL Standard Budgets

This issue applies to Version 7.9.6 only.

PeopleSoft GL Standard Budgets does not currently include the association with the Actual Ledger's calendar. Therefore, making a comparison between actual and budget ledgers is not possible. This issue is limited to the actual vs budget comparison of PeopleSoft GL Standard Budgets. Scenarios where actual or budget ledgers are viewed independently are not affected, nor does this have any effect on the actual vs budget comparison from PeopleSoft commitment control budgets.

Workaround

There is no workaround for this issue. Oracle recommends upgrading to Oracle Business Intelligence Applications Version 7.9.6.1.

1.3.25 Inconsistency in Installed Repository (RPD) for Oracle Service Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

Logical Table Sources have not been set correctly in relation to the dimensions Dim - Asset Status, Dim - Service Request Status, and Dim - Agreement Status. If you install Oracle Service Analytics and use Oracle BI Administration tool to perform a consistency check on the installed OracleBIAnalyticsApps.rpd file, then the following warnings will be posted:

• [39008] Logical dimension table Dim - Asset Status has a source Dim_W_STATUS_D_Asset_Status that does not join to any fact source.

• [39008] Logical dimension table Dim - Service Request Status has a source Dim_W_STATUS_D_Service_Request that does not join to any fact source.

• [39008] Logical dimension table Dim - Agreement Status has a source Dim_W_STATUS_D_Agreement_Status that does not join to any fact source.

Workaround

1. In Oracle BI Administration Tool, open the Oracle BI repository file OracleBIAnalyticsApps.rpd.

2. Display the Business Model and Mapping pane.

3. In the 'Core' logical model, edit the following logical tables:

   • Fact - CRM - Agree Item

   • Fact - CRM - Asset

   • Fact - CRM - Service Request

   Each of these logical tables has one or more logical table sources defined on the Sources tab.

4. For each logical table source that is defined on the Sources tab, do the following:

   1. Double click on the logical table source to display the Logical Table Source - <Name> dialog.

   2. Display the Content tab.

   3. In the Aggregation content section, locate the following dimensions and for each dimension select 'Detail' from the Logical Level drop down list:

      - Agreement Status

      - Asset Status

      - Service Request Status

5. Do a global consistency check to make sure that the warnings are not posted.

1.3.26 Installation Procedure for Oracle BI DAC Client on Windows Vista

This issue applies to Version 7.9.6 only.

Use the following procedure to install Oracle BI DAC Client on Windows Vista.

1. Before you run the Oracle BI DAC installer, complete the installation of Informatica PowerCenter Client Tools using the procedure described in Oracle Business Intelligence Applications Installation Guide Version 7.9.6, section 4.6.1 Installing Informatica PowerCenter Client Tools (Windows).

   The Oracle BI DAC Client uses the Informatica pmrep and pmcmd command line programs when communicating with Informatica PowerCenter. The installation of Informatica PowerCenter Client Tools provides the pmrep executable file. To obtain the pmcmd executable file, and to allow the Oracle BI DAC installer (which you will run in a subsequent step) to perform post-install configuration tasks, you must run the run the Informatica PowerCenter Services installer on the Windows Vista machine where you will install the Oracle BI DAC Client. This installation of Informatica PowerCenter Services will be a non-functional installation as Informatica does not support PowerCenter Services on Windows Vista.

2. Run the Informatica PowerCenter Services 8.6.0 installer as described in Oracle Business Intelligence Applications Installation Guide Version 7.9.6, section 4.6.2 Installing Informatica PowerCenter Services. Installation of Informatica HotFix 4 is not required.

   Note: The installation of PowerCenter Services on Windows Vista might not complete successfully. Ignore errors similar to the one below:

   "Use the error below and catalina.out and node.log in the server/tomcat/logs directory on the current machine to get more information. EXITCODE: S" Select Retry to continue the installation.
   

3. Copy the pmcmd.exe file located in PowerCenter 8.6.0\server\bin to PowerCenter 8.6.0\client\bin.

4. Run the Oracle BI DAC installer to install the DAC Client using the procedure described in Oracle Business Intelligence Applications Installation Guide Version 7.9.6, section 4.9.1 Installing DAC Using the DAC Installer.

   Note: On Windows Vista, the Oracle Merant Driver is not successfully installed by the Oracle BI DAC installer. Use Microsoft ODBC Administrator to configure an ODBC connection with the native ODBC driver instead. Use this ODBC when creating tables in the Oracle Business Analytics Warehouse via the Oracle BI DAC Client.

Workaround

Not applicable.

1.3.27 Inconsistent Metadata in Repository (RPD) for Oracle Supply Chain and Order Management Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

There are some metrics in Oracle Supply Chain and Order Management Analytics that make use of content from Oracle Financial Analytics. For example, the metrics 'Inventory Turn' and 'Days Inventory Outstanding' in 'Fact - Supply Chain - Compound Inventory' depend on 'Cost of Goods Sold' from Oracle Financial Analytics.

During installation, if you choose only the option 'Supply Chain and Order Management Analytics', then the extracted RPD will have the relevant metrics in Oracle Supply Chain and Order Management Analytics in the RPD metadata file, but there will be Financial Analytics metadata content in the RPD that is not consistent.

This results in an inconsistency error for the RPD in Oracle BI Administration Tool.

Workaround

To work around this issue, you should choose both the 'Supply Chain and Order Management Analytics' and 'Financial Analytics' options at the time of installation so that the RPD file is consistent. Similarly, for ETL, you should include the DAC subject area 'Financials - Cost of Goods Sold' in their ETL execution plan to make sure the fact table 'W_GL_COGS_F' is populated and that the metrics are calculated correctly.

1.3.28 Additional step for Upgrade to Oracle BI Applications 7.9.6 for Oracle EBS Users

This issue applies to Version 7.9.6 only.

This release note applies to you if you are upgrading to Oracle BI Applications Version 7.9.6 and if your source system is Oracle eBusiness Suite. Perform the step described below after you have completed the upgrade procedures described in Oracle Business Intelligence Applications Upgrade Guide Version 7.9.6, and before you run the first incremental load after your upgrade.

Workaround

1. In the Oracle BI DAC Client, navigate to Setup > Physical Data Sources.

2. Select the record for the connection for your Oracle EBS source (for example, ORA_11_5_10).

3. On the Refresh Dates tab, query for the table named FND_LOOKUP_VALUES.

4. In the Refresh Date column, open the Date editor.

5. Click on the Null button to remove the refresh date value.

6. Save the changes.

1.3.29 APPS User Providing Generic Access Violating SOX Compliance With Oracle EBS

This issue applies to Versions 7.9.6 and 7.9.6.1.

This issue only applies to the Oracle EBS adapter. Oracle E-Business Suite applications by default access their database objects via the APPS user. The APPS user provides generic access and can violate SOX compliance. In a production environment, you typically do not want the ETL to be leveraging APPS privileges.

Workaround

Create a new user with select and create synonyms privileges to take care of schema ownership on the selected EBS transactional tables that Oracle BI Applications extract from. A list of the source tables used in the ETL process can be identified by the following steps:

1. Login to DAC.

2. Select the application container, for example, Oracle 11.5.10.

3. Display the Tables tab.

4. Query for tables with the Table Type set to 'Source'.

5. For each table returned by the query, right-click and select 'Output to file' to display the Output table dialog, and note down the table name that is displayed in the Output File box (for example, W_ETL_TABLE.txt).

6. Using the table names that you obtained in step 5, create a database script to grant CONNECT, RESOURCE and CREATE SYNONYM privileges to the new user. Then create synonyms for all the tables generated from the above steps and grant select on the synonyms that are created.

1.3.30 Implementation of Oracle Project Analytics with Oracle eBusiness Suite 11.5.10

This issue applies to Version 7.9.6 only.

Implementation of Oracle Project Analytics with Oracle eBusiness Suite 11.5.10 requires Family Pack M (11i.PJ_PF.M) to be applied to Oracle eBusiness Suite 11.5.10.

For more information, see System Requirements and Supported Platforms for Oracle Business Intelligence Applications.

Workaround

Not applicable.

1.3.31 Index Error Messages Reported During ETL After Upgrade

This issue applies to Versions 7.9.6 and 7.9.6.1.

After upgrading to Oracle BI Applications Version 7.9.6, when you run an incremental ETL process, you might receive an error message stating that one or more of the following indexes could not be created:

• W_SRVREQ_F61

• W_ASSET_F_F70

• W_AGREEITEM_F_F27

• W_MCAL_DAY_D_RB1

• W_MCAL_DAY_D_RB2

• W_MCAL_DAY_D_RB3

Workaround

At the failure of any of these indexes, drop all the above indexes from the data warehouse tables and restart the ETL process from where it failed.

See related issue Section 1.2.3.4, "796_UPGRADE_POST_SCRIPT.sql Fails to Drop Indexes During Upgrade".

1.3.32 SDE_JDEE1_90_ADAPTOR: W_MCAL_PERIOD_D - Fiscal Period Name Incorrect

This issue applies to Version 7.9.6 only.

This issue affects the JD Edwards adaptor SDE_JDEE1_90_Adaptor. The MCAL_PERIOD_NAME parameter incorrectly stores the fiscal year (for example, 2009) instead of the fiscal period (for example, 2009R 1 to 2009R 12), which affects the data in the table W_MCAL_PERIOD_D. This issue affects data searching in Oracle BI-EE Answers or in the dashboard.

Workaround

There is no workaround for this issue.

1.3.33 JDE: W_AP_XACT_F - Additional Record Inserted During Incremental Load

This issue applies to Version 7.9.6 only.

During an incremental ETL run, an additional record is inserted into the W_AP_XACT_F table, which affects the Aging and AP amounts related metrics.

Workaround

Install patch 11882715 available under Automated Release Updates (ARU).

1.3.34 JDE: W_AR_XACT_F - Additional Record Inserted During Incremental Load

This issue applies to Versions 7.9.6 and 7.9.6.1.

During an incremental ETL run, an additional record is inserted into the W_AR_XACT_F table, which affects the Aging and AR amounts related metrics.

Workaround

Install patch 11882715 available under Automated Release Updates (ARU).

1.3.35 Enabling Initialization Blocks Required For Calendars

This issue applies to Version 7.9.6 only.

If you want to deploy multiple calendars (for example, with Oracle Financial Analytics), you must have the following Initialization Blocks enabled:

• EBS Single Sign-on Integration

• EBS Security Context

• Ledgers

• Operating Unit Organizations

These Initialization Blocks are not enabled out-of-the-box. If you try to load data without enabling these Initialization Blocks, dashboards will not be populated with data, and the BI Server log might show Init Block errors (for example, nQSError:43059).

Workaround

To enable the Initialization Blocks listed above:

1. In Oracle BI Administration Tool, open the Oracle BI repository file OracleBIAnalyticsApps.rpd.

2. Choose Manage, then Variables, to display the Variable Manager.

3. In the left-hand pane, select Session, then Initialization Blocks.

4. For each Initialization Block listed above, right-click on the Initialization Block name in the right-hand pane, then select Enable.

5. Restart the BI server.

1.3.36 Application Connections for PeopleSoft PSFT Adapters

This issue applies to Version 7.9.6 only.

For PSFT adapters, you need to define application connections for OLTP data sources.

Workaround

1. In Informatica PowerCenter Workflow Manager, select Connections, then Application, to display the Application Connection Browser dialog.

   You need to create a connection for each PeopleSoft transactional (OLTP) database.

2. For each application connection that you need to create, do the following.

   1. Click New to display the Select Subtype dialog box, select the appropriate database type (for example, PeopleSoft Oracle), then click OK to display the Connection Object Definition dialog.

   2. Use the Connection Object Definition dialog to define an application connection (for example, named PSFT_9_0_HCM).

      The OLTP connection name that you choose must match the name of the OLTP connection created in DAC.

   3. Click OK to save the details.

1.3.37 Learning Error When Selecting Learning Course and Activity in Oracle Human Resource Analytics

This issue applies to Version 7.9.6.1 only.

An error occurs when creating a report that selects only Learning Course and Learning Activity dimensions. The logical SQL is:

SELECT "Learning Course and Activity"."Learning Course" saw_0, "Learning Course and Activity"."Learning Activity" saw_1 FROM "Human Resources - Learning Enrollment and Completion" ORDER BY saw_0, saw_1

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 14070] Cannot find logical table source coverage for logical columns: [Learning Course]. Please check more detailed level keys are mapped correctly. (HY000) SQL Issued: SELECT "Learning Course and Activity"."Learning Course" saw_0, "Learning Course and Activity"."Learning Activity" saw_1 FROM "Human Resources - Learning Enrollment and Completion" ORDER BY saw_0, saw_1. Learning Course and Activity presentation table is a logical grouping of common learning dimensions to be used when viewing learning enrollment facts. Because there is no logical relationship between the course dimension and the activity dimension, there is no way to present data between the two dimensions without the presence of an enrollment fact.

Workaround

To report on learning activities available for a learning course, a metric must be included in the report. For example, including Learning Course, Learning Activity and Enrollment Count in the report.

1.3.38 Fiscal Calendar on Time Dimension

This issue applies to Versions 7.9.6 and 7.9.6.1.

Fiscal calendar rolls-up Gregorian months and quarters to fiscal months and quarters. Oracle HR Analytics Version 7.9.6 does not support Fiscal Calendar on the Time dimension. However, the Enterprise Calendar dimension can be leveraged to roll up the Gregorian calendar into one fiscal calendar. The Enterprise Calendar dimension tables used to configure the fiscal calendar are W_MCAL_DAY_D, W_MCAL_PERIOD_D, W_MCAL_MONTH_D, and W_MCAL_YEAR_D.

Workaround

To enable the fiscal calendar on the Enterprise Calendar dimension, use Oracle BI Administration Tool to edit your repository (RPD) as follows:

1. In the Physical layer, define physical joins between the fiscal time dimension levels and the event fact table.

   The physical tables for the fiscal time dimension levels are W_MCAL_DAY_D, W_MCAL_PERIOD_D, W_MCAL_MONTH_D, and W_MCAL_YEAR_D. For example, define the join on the fiscal calendar date as Dim.MCAL_DAY_DT_WID = Fact.EVENT_DT_WID.

2. In the Business Model and Mapping Layer, define a logical join between the fiscal time dimension and the event fact tables.

   For example, define the logical join as Fact_W_WRKFC_EVT_MONTH_F_Event_Day=Fiscal Day Detail.

3. In the Presentation Layer, drag the Fiscal Time dimension to one of the Human Resources presentation folder (for example, Human Resources - Recruitment, or Human Resources - Workforce Profile).

1.3.39 Division Name Pointing To An Obsolete Column In Oracle Sales Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

In Oracle Sales Analytics deployments, the dimension column 'Organization ->Division Name' refers to the Employee's Division, which is the Business Unit Name in Siebel. Refer to web catalog folder under the Usage Accelerator Subject Areas named 'Usage Accelerator Current' and 'Usage Accelerator Summary'. Division Name points to the physical column, W_INT_ORG_D.BU_NAME, which is obsolete from Oracle Business Intelligence Applications Version 7.9.5 and later.

Workaround

The workaround is to add BU_WID to UA facts that are in use, as follows:

1. In the ETL, do the following:

   1. Extend each fact, including temp tables (e.g. W_UAEMP_TMP) with BU_WID.

      This enables you to make the new physical alias of W_INT_ORG_D for BU conform across all facts in UA (see RPD steps below).

   2. Verify that the BU_ID currently in WS_POSTN is correct.

      If it is incorrect, add new column.

   3. Extend W_UAPOS_TMP with BU_WID and modify the mapping to load it.

   4. Modify related SIL mappings to populate the BU_WID in each fact from W_UAPOS_TMP.

      Note: There are 15 fact tables and three temp tables (WS_POSTN, W_UAEMP_TMP, W_UAPOS_TMP) that need to be extended with the new column BU_WID. You also need to:

      - Modify the mappings that populate the data in the temp tables mentioned above.

      - Change all the mappings that are involved in populating the fact tables, to fetch the data for the new column BU_WID.

2. In the RPD, do the following:

   1. In the Physical Layer, create a new physical alias of W_INT_ORG_D.

   2. In the Business Model and Mapping layer, create a new logical dimension for BU from the alias.

   3. Create a simple hierarchy for the new BU logical dim.

   4. Join (logically and physically) to the facts by BU_WID.

   5. In the Presentation Layer, add the BU name from new BU logical dimension.

   6. Update the reports (if any) following the presentation changes.

Note: If you are upgrading from a version prior to Oracle Business Intelligence Applications Version 7.9.5, you might need to fix the old fact records by a suitable means, such as creating a Workflow.

1.3.40 SQL Error ORA-00923 in DAC With Oracle EBS In Oracle Procurement and Spend Analytics

This issue applies to Version 7.9.6 only.

If you are running Oracle Procurement and Spend Analytics and your EBS source application is on Oracle Database 9i, then you will encounter the SQL Error ORA-00923 in DAC while running the Informatica workflow "SDE_ORA_PurchaseReceiptFact_full". This mapping uses the Oracle database function CONNECT_BY_ROOT, which is not available in Oracle database 9i.

Workaround

Do one of the following:

• Either upgrade EBS source to Oracle Database 10g, if possible.

• Replace the SQL code containing CONNECT_BY_ROOT with REPLACE(SYS_CONNECT_BY_PATH (DECODE (LEVEL,1,TRANSACTION_DATE),'#'),'#') AS RECEIPT_ON_DT.

1.3.41 Tasks Not Auto-Generated When Subject Area is Assembled for JDE With Oracle Financial Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

The following tasks are not auto generated and therefore have to be manually added to the JDE container as follows:

• For Financials - General Ledger Subject area, add the following 2 tasks:

  • SDE_JDE_GL_Other_Fact

  • SDE_JDE_CodeDimension_UDC

• For Financials - Cost of Goods Sold Subject area, add the following 2 tasks:

  • SDE_JDE_GL_COGS_Fact

  • SDE_JDE_CodeDimension_UDC

• For Financials - Revenue Subject area, add the following 2 tasks:

  • SDE_JDE_GL_Revenue

  • SDE_JDE_CodeDimension_UDC

Workaround

1. In DAC, display the Design pane and select the appropriate JDE container from the drop-down list.

2. Display the Subject Areas tab.

3. Select the applicable Subject Area (for example, Financials - General Ledger).

   For a list of applicable Subject Areas, see the list above.

4. In the lower pane, display the Tasks tab, which will have the list of tasks that are part of the Subject Area.

5. Click the Add/Remove button.

6. In the pop-up window highlight the appropriate tasks (for example, SDE_JDE_GL_Other_Fact) from the left pane and then click Add.

   For a list of applicable Tasks, see the list above.

   Once the task has been added it will appear in the right pane.

7. Click OK to close the pop-up window.

8. Select the newly added task and click Save.

9. Click the Save button in the top pane.

10. Click the Assemble button.

1.3.42 Configuring the DAC Parameter when Multicurrency Processing is Disabled in JD Edwards for Oracle Financial Analytics

This issue applies to Version 7.9.6.

LOC_CURR_CODE and ACCT_CURR_CODE in the Data Warehouse are non-nullable fields. Unless they are populated, insertion into the Data Warehouse will fail. If multicurrency processing is disabled in JD Edwards EnterpriseOne or JD Edwards World, then you must set the $$DOC_CURR_CODE parameter in DAC to the domestic or base currency in use. By default, the value of this parameter is set to NULL in DAC.

Workaround

To configure the $$DOC_CURR_CODE parameter:

1. In the DAC client, display the Design pane and select the JDE appropriate container from the drop down list.

2. Display the Source System Parameters tab.

3. Select the row with the $$DOC_CURR_CODE parameter, and then double-click in the Value column.

4. In the Enter Parameter Value screen, replace NULL with the domestic or base currency in use.

1.3.43 PLP_LoyMemberTierMovementQtrAggr fails On Non-Oracle Databases for Oracle Loyalty Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

This is a known issue affecting non-Oracle databases. The ETL for "MEMBER TIER MOVEMENT AGGREGATION" fails with a transformation error if the target warehouse database is non-Oracle.

Workaround

In Informatica PowerCenter Designer, use the Mapping Designer tool to modify the PLP_LoyMemberTierMovementQtrAggr mapping as follows:

For Microsoft SQL Server databases:

1. Open the SQL_LoyMemberTierMove_Agg SQL transformation, and display the SQL Settings tab.

2. Change the Database Type to Microsoft SQL Server.

3. Display SQL Ports tab, and change the Native Type datatype for ALL columns that read 'bit' and change the value to 'varchar'.

For DB2 databases:

1. Open the SQL_LoyMemberTierMove_Agg SQL transformation, and display the SQL Settings tab.

2. Change the Database Type to DB2.

3. Display SQL Ports tab, and change the Native Type datatype for ALL columns that read 'char' and change the value to 'varchar'.

For Teradata databases:

1. Open the SQL_LoyMemberTierMove_Agg SQL transformation, and display the SQL Settings tab.

2. Change the Database Type to TeraData.

3. Display SQL Ports tab, and change the Native Type datatype for ALL columns that read 'char' and change the value to 'varchar'.

1.3.44 Metrics in AR Subject Area Not Displaying Properly in Oracle Financial Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

Metrics such as "Days Payables Outstanding" in AP subject areas and "Days Sales Outstanding" in AR subject areas will not display properly when analyzed by Supplier Account or Customer Account dimensions respectively.

Workaround

To make the following changes to BI repository RPD file:

1. In Oracle BI Administration Tool, open the Oracle BI repository file OracleBIAnalyticsApps.rpd.

2. In the Business Model and Mapping layer, expand the logical table "Fact - Fins - Period Days Count". Display the Levels tab.

3. For each of the metrics "# of Elapsed Days" and "# of Cumulative Elapsed Days", do the following:

   1. Double click on the metric to display the Logical Column - <Name> dialog.

   2. Display the Levels tab.

   3. For the dimensions "Customer Account" and "Supplier Account", set the Logical Level value to "All".

4. Save the changes.

1.3.45 Configuring Bill of Materials Explosion for Oracle Supply Chain and Order Management Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

When you configure a Bill of Materials (BOM) Explosion, Chapter 6 of Oracle Business Intelligence Applications Configuration Guide for Informatica Users does not include steps for using DAC to configure BOM explosion details.

Workaround

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

2. Display the Tasks tab and query for task “SDE_ORA_BOMItemFact”.

3. Display the Parameters subtab, and enter an appropriate value in the Value field.

   Make sure you the set same value for both tasks.

4. Save the changes.

1.3.46 FIND_AUDIT_VALUES Transformation In SDE_OPTYSTGFACT Missing in Teradata Repository

This issue applies to Versions 7.9.6 and 7.9.6.1.

This issue affects all Oracle Sales Analytics customers on above OBIA Releases using Siebel as the source system with Teradata database as target.

FIND_AUDIT_VALUES transformation inside SDE_OptyStgFact is missing in the Teradata ETL repository.

Workaround

1. Logon to Informatica PowerCenter Designer, and open the Mapping Designer tool.

2. Open the Folder SDE_SBL_80_Adaptor_TD.

3. Delete the mapping SDE_OptyStgFact from the folder, which is a shortcut from the folder SDE_SBL_78_Adaptor.

4. Create a new shortcut of the mapping SDE_OptyStgFact from the folder SDE_SBL_80_Adaptor in the SDE_SBL_80_Adaptor_TD, and then re-name it to SDE_OptyStgFact.

5. Save the Changes and refresh the corresponding session in Informatica PowerCenter Workflow Manager for the changes to take effect.

6. To deploy the Stored Procedure on the source system, navigate to the OracleBI\dwrep\Informatica\Stored_Procedure_Scripts folder.

7. Open the folder appropriate to your database platform, and copy the source code from the file FIND_AUDIT_VALUES.sql into source system schema.

8. Compile the stored procedures in the source system schema.

Note: If you need assistance in deploying the stored procedures, see your Database Reference guide, or contact your database administrator.

1.3.47 Revenue Ago Metrics Using GL_Accounting_Period_WID Not Supported by PSFT in Oracle Project Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

PeopleSoft does not support Revenue Ago Metrics Using GL_Accounting_Period_WID.

Workaround

To use the metrics available in table "Ago Measures" from the Subject Area "Project Revenue", you must remove the foreign key joins on Period from these facts in the repository (RPD). The server will then use the Date foreign keys, resolving the problem.

1.3.48 Revenue Value Changing With Multi-Currency Option in Funding Subject Area in Oracle Project Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

In the Subject Area "Project-Funding", table "Amount Metrics”, the metric "Revenue Amount" might show the wrong value when switching between currencies.

Workaround

You must use the "Revenue Amount" metric from the Subject Area "Project - Revenue" and table "Fact - Project Revenue".

1.3.49 ACTIVE_FLG Column Not Populated Correctly in W_XACT_TYPE_D in Oracle Project Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

The are cases when the data load process is not disabling records that are no longer valid in the table W_XACT_TYPE_D. This happens when the update process incorrectly populates column W_XACT_TYPE_D.ACTIVE_FLG with 'Y' when in the OLTP table the value in column END_DATE_ACTIVE is less than SYSDATE. This issue does not affect the data show in the dashboards and customers do not need to take action as this moment.

Workaround

Not applicable.

1.3.50 Writeoff LOC Amounts and Exchange Rates Incorrect in Oracle Project Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

The data sourced from PeopleSoft for the W_PROJ_RETENTION_F table will show incorrect data for Project Retention write off amounts if the transaction currency code is different from the GL currency code.

Workaround

There is no workaround for this issue.

1.3.51 Potential Performance Issue in Absence ETL Mapping SDE_ORA_ABSENCEEVENT_FULL

This issue applies to Versions 7.9.6 and 7.9.6.1.

This issue applies to EBS iRecruitment only. This applies to Absence ETL mapping SDE_ORA_ABSENCEEVENT_FULL. This mapping has an API call to EBS iRecruitment function hri_bpl_utilization.convert_days_to_hours() in the source qualifier that converts absence days to hours. This API has poor performance and can potentially increase ETL load performance for this mapping performance. It is optional to use this API call hri_bpl_utilization.convert_days_to_hours() in the ETL source adaptor.

Workaround

To improve performance, remove the API call and convert absence days to hours by multiplying it with a constant number such as 8 hours a day.

1.3.52 Learning RPD Error When Selecting Learning Course and Learning Activity

This issue applies to Versions 7.9.6 and 7.9.6.1.

The following error occurs when Learning Course is combined with Learning Activity in reports in the Learning Subject Area. Logical SQL: SELECT "Learning Course and Activity"."Learning Course" saw_0, "Learning Course and Activity"."Learning Activity" saw_1 FROM "Human Resources - Learning Enrollment and Completion" ORDER BY saw_0, saw_1 Error: Odbc driver returned an error (SQLExecDirectW). Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 14070] Cannot find logical table source coverage for logical columns: [Learning Course]. Please check more detailed level keys are mapped correctly. (HY000) SQL Issued: SELECT "Learning Course and Activity"."Learning Course" saw_0, "Learning Course and Activity"."Learning Activity" saw_1 FROM "Human Resources - Learning Enrollment and Completion" ORDER BY saw_0, saw_1. Learning Course and Activity presentation table are logical groupings of common learning dimensions and there is no logical relationship between the two logical dimensions. Therefore, these two dimensions can only be viewed together along with the learning enrollment facts.

Workaround

To report learning activities associated with a learning course, one or more metrics on the learning enrollment facts need to be included in the report along with the course and activity dimensions.

1.3.53 Duplicates Inserted in Incremental Loads

This issue applies to Versions 7.9.6 and 7.9.6.1.

SIL_InternalOrganizationHierarchy has an update strategy to do an DD_UPDATE instead of a DD_DELETE in the delete mapping. The main mapping (has DD_INSERT) that inserts rows during an incremental load resulting in duplicates in the table and causes issues in the reports.

Workaround

Replace the DD_UPDATE command with the DD_DELETE command within the incremental ETL delete mapping.

1.3.54 Duplicate Rows in W_PRODCAT_DH Tables

This issue applies to Versions 7.9.6 and 7.9.6.1.

When running some mappings for Product sourcing from Siebel CRM, there may be duplicate data found in W_PRODUCT_DH.

Mapping SIL_ProductCategoryDimension_Hierarchy is vertical specific. The join in sql override to W_PRODUCT_D is missing the condition PRODUCT.CURRENT_FLG='Y'. This allows duplicate data to enter the product table.

Workaround

To avoid this, a join condition needs to be added, as follows:

1. In Informatica PowerCenter Designer, navigate to the folder SIL_Vert\Mappings, and open the mapping "SIL_ProductCategoryDimension_Hierarchy" in the Mapping Designer tool.

2. Edit the Source Qualifier SQ_W_PRODCAT_DS to display the Edit Transformations dialog, and display the Properties tab.

3. Select the Transformation Attribute name Sql Query, and modify the sql override to change the left outer join condition to w_product_d as:

   W_PRODCAT_DS BASE 
   LEFT OUTER JOIN W_PRODUCT_D PROD ON 
   BASE.PROD_ID = PROD.INTEGRATION_ID AND PROD.CURRENT_FLG = 'Y'
   

4. Save the details and check in.

1.3.55 Approved Date is Shown as Junk Date

This issue applies to Versions 7.9.6 and 7.9.6.1.

The Approved date is shown as 1/1/1901 for requisitions in 'In Process' and 'Incomplete' statuses.

Workaround

There is no workaround for this issue.

1.3.56 ORA-00604: Error Reported During ETL

This issue applies to Versions 7.9.6 and 7.9.6.1.

The incremental ETL fails intermittently. When looking at the log it complains about ORA-00604: error occurred at recursive SQL level on the table W_XACT_TYPE_DS.

Workaround

In Informatica Workflow Manager, change the Target Load Type from Bulk to Normal for the following sessions:

• SDE_ORA_TransactionTypeDimension_APDerive

• SDE_ORA_TransactionTypeDimension_ARDerive

• SDE_ORA_TransactionTypeDimension_GLRevenueDerive

To locate the Target load type setting, log into Informatica Workflow Manager, and select Session Properties, then Mapping, then Targets, and select TARGET_TABLE, then select Target load type setting under Properties.

1.3.57 Cost Center Name is Blank

This issue applies to Versions 7.9.6 and 7.9.6.1.

Several of the out-of-the-box reports are not rendering correctly because the JDE E1 Adapter is not mapping the COST_CENTER_NAME column in W_COST_CENTER_D.

Workaround

Map the COST_CENTER_DESC column to the Cost Center Name column in the logical layer to resolve the issue. The COST_CENTER_DESC column in the W_COST_CENTER_D table is populated by the JDE E1 Adapter.

1.3.58 Loyalty AN Metric "# OF MEMBERS" Gives Incorrect Results When Grouped by Quarter

This issue applies to Versions 7.9.6 and 7.9.6.1.

If # of members and quarter are selected to form a report, and if there is no status change for a user in the "W_LOY_MEMBER_STATUS_HIST_F" table in a given quarter, that member does not get counted for that quarter. This is a known issue causing the report to render the wrong count for members (only under this specific criteria).

Workaround

There is no workaround for this issue.

1.3.59 % Received Early Calculated Incorrectly in Oracle Procurement and Spend Analytics – Purchase Cycle Lines Subject Area

This issue applies to Versions 7.9.6 and 7.9.6.1.

The calculated measure '% Received Early' is calculated incorrectly for CWP complex work POs in Oracle Procurement and Spend Analytics – Purchase Cycle Lines Subject Area. The CWP metric is not showing the correct result for received early amount or received on time amount or received late amount and PAYITEMS:Line Types LUMPSUM and MILESTONE are not considered for calculating the % Received Early measure.

Workaround

1. Take the backup for current W_PURCH_CYCLE_LINE_F table if data is available by applying the following query:

   Create table w_purch_cycle_line_f1 as select * from w_purch_cycle_line_f
   

2. In Informatica PowerCenter Designer, open the SIL_PurchaseCycleLinesFact map from the SILOS folder.

3. Select Versioning from the menu and perform a Check out, providing any required comments.

4. Open the SQ_W_PURCH_CYCLNS_ORA (source qualifier), display the Properties tab, and open the query window.

5. Add the three new columns definition given below between ONTIME_QTY and ONTIME_IND:

   SUM (CASE WHEN SCHLNS.DUE_ON_DT_WID > RECPTS.RECEIVED_ON_DT_WID THEN $$Hint_Tera_Pre_Cast RECPTS.RECEIVED_AMT $$Hint_Tera_Post_Cast ELSE 0 END) AS EARLY_AMT, 
   SUM (CASE WHEN SCHLNS.DUE_ON_DT_WID < RECPTS.RECEIVED_ON_DT_WID THEN 
   $$Hint_Tera_Pre_Cast RECPTS.RECEIVED_AMT $$Hint_Tera_Post_Cast ELSE 0 END) AS 
   LATE_AMT, 
   SUM (CASE WHEN SCHLNS.DUE_ON_DT_WID = RECPTS.RECEIVED_ON_DT_WID THEN 
   $$Hint_Tera_Pre_Cast RECPTS.RECEIVED_AMT $$Hint_Tera_Post_Cast ELSE 0 END) AS 
   ONTIME_AMT,
   

6. Similarly add the three columns listed below to the outer query between RECEIPTS.ONTIME_QTY and RECEIPTS.ONTIME_IND:

   RECEIPTS.EARLY_AMT, 
   RECEIPTS.LATE_AMT, 
   RECEIPTS.ONTIME_AMT, 
   

7. Click Ok and save the changes.

   Now the source qualifier will have three new columns between ONTIME_QTY and ONTIME_IND, for these new ports matching W_PURCH_RCPT_F.RECEIVED_AMT as source.

8. Save the Mapping.

9. Open mplt_SIL_PurchaseCycleLineFact_ORA mapplet and add new columns to the INPUT transformation as EARLY_AMT, LATE_AMT and ONTIME_AMT, then click Apply and select Ok.

10. Edit the DATE_TRANS transformation scroll to ONTIME_AMT, EARLY_AMT and LATE_AMT, and click the Input port Check box for all three columns.

11. Map EARLY_AMT, LATE_AMT and ONTIME_AMT incoming columns from INPUT transformation to “DATE_TRANS” EARLY_AMT, LATE_AMT and ONTIME_AMT respectively.

12. Save the mapplet.

13. Switch to SIL_PurchaseCycleLinesFact map drag and drop newly created AMT columns from source qualifier to mplt_SIL_PurchaseCycleLineFact_ORA.

14. Save and run the mapping.

1.3.60 TREE_FLAG Attribute in Segment Dimension W_SEGMENT_D is Derived Incorrectly in Oracle Marketing Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

This is a known issue that affects setting the value of segment tree flag within Oracle Marketing Analytics. This flag is used to determine whether a segment or segment tree that is selected. The ETL is using the following expression, which is causing the issue:

IIF(ISNULL(PAR_CALL_LST_ID) AND SEGMENT_TYPE_I='Tree','Y,'N') 

Workaround

Compare the segment type with "Segment_Tree" string instead of "Tree". The modified expression should be:

IIF(SA_FLG_lv = 'N' AND TREE_FLG_lv ='N' AND PARENT_TYPE_I = 'Segment Tree', 'Y', 'N')  

1.3.61 Marketing - Actual Cost Metric Is Mapped to the Wrong Source Fields in Oracle Marketing Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

Marketing Cost (Actual) metric is not giving the Actual Expense, since it is mapped to the forecasted value. If you try to add this field to any report, it will display the forecasted amount instead of the actual amount.

Workaround

There is no workaround for this issue.

1.3.62 Numeric Overflow Error When Targeting Teradata

This issue applies to Version 7.9.6.1 only.

Several mappings are failing with a numeric overflow error when using Teradata as a source system. The definitions of table length and precision within the DAC metadata to handle the Teradata ETL is not consistent with the data warehouse schema definitions and this can cause the numeric overflow issues.

Workaround

If a numeric overflow issue is encountered, make the necessary changes to the DAC metadata within the custom DAC container to increase the data size manually to avoid numeric overflow.

1.3.63 SIL_EmployeeDimension_SCDUpdate Hangs in Incremental Load With SQL Server 2005

This issue applies to Version 7.9.6.1 only.

SIL_EmployeeDimension_SCDUpdate map hangs in incremental load when using a SQL Server 2005 target database as the warehouse due to locking issues.

Workaround

Turn on the read committed snapshot feature in the target database. This will avoid the locking issues and allow the maps to complete.

1.3.64 How to Secure the Employee Dimension in Oracle HR Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

Oracle HR Analytics secures user data access using security filters applied to fact tables. These security filters restrict a user's access to a subset of the data based on his or her security profile by the securable dimensions e.g. organization, supervisor hierarchy or business group. As delivered, Employee dimension itself is not a securable dimension. This means that when a user browses Employee dimension directly without selecting a metric that is secured by one of the securable dimensions, he or she will see all people in the Employee dimension regardless of his/her security access. However, it is important to point out that a user's data security will be applied once the user includes one or more metrics along with the Employee dimension attributes. By combining metrics with the Employee dimension, it indirectly secures the Employee dimension through the logical join between Employee dimension and the secured fact tables.

However, occasions may arise that require securing the Employee dimension so that a user can only view people within his/her security access when he/she browses the Employee dimension directly. Customers can decide to secure the Employee dimension during implementation.

Workaround

For instructions on how to secure the Employee dimension, please refer to Tech Note How to Secure Employee Dimension in OBIApps 7.9.6 and 7.9.6.1 (Doc ID 948928.1) that is available at My Oracle Support.

1.3.65 DIM - CUSTOMER"."HIERARCHY BASED LOGIN" Defined Incorrectly In The BI Repository RPD

This issue applies to Versions 7.9.6 and 7.9.6.1.

DIM - CUSTOMER"."HIERARCHY BASED LOGIN" is not defined correctly in the BI Repository RPD.

Workaround

For more information about working with this issue, please refer to Tech Note 949432.1, which is available at My Oracle Support.

1.3.66 Issue With Oracle Resources Analytics In PLP_RecruitmentRequisitionAggregate_Load_Update Using Teradata DB as Target

This issue applies to Versions 7.9.6 and 7.9.6.1.

This issue affects any source OLTP and Teradata target warehouse databases. During a full or incremental ETL, the task "PLP_RecruitmentRequisitionAggregate_Load_Update" fails using Teradata as the target data warehouse database. The error message shown in the Informatica session log is:

FnName: Prepare -- [NCR][ODBC Teradata Driver][Teradata Database] Datatype Mismatch in THEN/ELSE expression.

Workaround

1. Wait for the DAC execution plan to fail.

2. Run the following SQL in the target database (Teradata):

   UPDATE W_RCRTMNT_RQSTN_A_TMP2 
   FROM (SELECT A.JOB_RQSTN_WID JOIN_WID,A.EFFECTIVE_FROM_DT JOIN_DT,CASE WHEN MIN(B.EFFECTIVE_FROM_DT) IS NULL THEN CAST('3714-01-01 00:00:00' AS TIMESTAMP  FORMAT 'YYYY-MM-DDBHH:MI:SS') ELSE CAST(MIN(B.EFFECTIVE_FROM_DT) AS TIMESTAMP FORMAT 'YYYY-MM-DDBHH:MI:SS') END EFFECTIVE_TO_DT 
   FROM W_RCRTMNT_RQSTN_A_TMP2 A LEFT OUTER JOIN W_RCRTMNT_RQSTN_A_TMP2 B ON   (A.JOB_RQSTN_WID= B.JOB_RQSTN_WID AND A.EFFECTIVE_FROM_DT < B.EFFECTIVE_FROM_DT) 
   WHERE A.DATASOURCE_NUM_ID = 9 
   GROUP BY A.JOB_RQSTN_WID,A.EFFECTIVE_FROM_DT)  C 
   SET EFFECTIVE_TO_DT = C.EFFECTIVE_TO_DT 
   WHERE JOB_RQSTN_WID = C.JOIN_WID  AND EFFECTIVE_FROM_DT = C.JOIN_DT AND DATASOURCE_NUM_ID = 9
   

   Note: There are two places where the above SQL has the DATASOURCE_NUM_ID hard-coded (DATASOURCE_NUM_ID = 9). Before running the SQL, you should change the value '9' to the appropriate value that you are using for DATASOURCE_NUM_ID.

3. Set the failed DAC task as completed.

4. Restart the Execution plan (this will resume the execution from the previous failure point).

1.3.67 Incremental ETL Fails For Oracle E-Business Suite Version 11.5.10

This issue applies to Versions 7.9.6 and 7.9.6.1.

This issue applies to Source OLTP Oracle E-Business Suite Version 11.5.10, with any Target warehouse database. While running incremental ETL for Human Resources against Oracle E-Business Suite version 11.5.10, the following tasks (and hence the task group) fails:

• Task: SDE_ORA_WorkforceEventFact_Hdcnt

• Task: SDE_ORA_WorkforceEventFact_Asg

• Task: SDE_ORA_WorkforceEventFact_PrsnTyp

• Task: SDE_ORA_WorkforceEventFact_Salary

• Task: SDE_ORA_WorkforceEventFact_FTE

• Task: SDE_ORA_WorkforceEventFact_Perf

• Task Group: TASK_GROUP_Extract_WorkforceEventFact

Workaround

1. Launch Informatica Workflow Manager and log on to the repository and open the folder "SDE_ORA11510_Adaptor".

2. Check out the following reusable sessions:

   • SDE_ORA_WorkforceEventFact_Hdcnt

   • SDE_ORA_WorkforceEventFact_Asg

   • SDE_ORA_WorkforceEventFact_PrsnTyp

   • SDE_ORA_WorkforceEventFact_Salary

   • SDE_ORA_WorkforceEventFact_FTE

   • SDE_ORA_WorkforceEventFact_Perf

3. For each of the above sessions, edit the SQL override and replace:

   TO_DATE('$$HR_WRKFC_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') 
   

   With:

   $$HR_WRKFC_EXTRACT_DATE 
   

4. Save, validate and check-in the sessions.

5. Resume your ETL run from where it failed.

1.3.68 Issue With Oracle Resources Analytics In PLP_RecruitmentRequisitionAggregate_Load Using Teradata DB as Target

This issue applies to Versions 7.9.6 and 7.9.6.1.

The task "PLP_RecruitmentRequisitionAggregate_Load" fails while running incremental ETL using Teradata as the target data warehouse database. The error message shown in the Informatica session log is: FnName: Prepare -- [NCR][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between '(' and the 'SELECT' keyword.

Workaround

1. Wait for the DAC execution plan to fail.

2. Run the following SQL in the target database (Teradata):

   UPDATE W_RCRTMNT_RQSTN_A 
   FROM (SELECT A.JOB_RQSTN_WID JOIN_WID,A.EFFECTIVE_FROM_DT JOIN_DT,CASE WHEN MIN(B.EFFECTIVE_FROM_DT) IS NULL THEN CAST('3714-01-01 00:00:00' AS TIMESTAMP FORMAT  'YYYY-MM-DDBHH:MI:SS')   
   ELSE CAST(MIN(B.EFFECTIVE_FROM_DT) AS TIMESTAMP FORMAT 'YYYY-MM-DDBHH:MI:SS') END EFFECTIVE_TO_DT 
   FROM W_RCRTMNT_RQSTN_A A LEFT OUTER JOIN W_RCRTMNT_RQSTN_A B ON (A.JOB_RQSTN_WID= B.JOB_RQSTN_WID AND A.EFFECTIVE_FROM_DT < B.EFFECTIVE_FROM_DT) 
   WHERE A.DATASOURCE_NUM_ID = 9 GROUP BY A.JOB_RQSTN_WID,A.EFFECTIVE_FROM_DT)  C 
   SET EFFECTIVE_TO_DT = C.EFFECTIVE_TO_DT 
   WHERE JOB_RQSTN_WID = C.JOIN_WID AND EFFECTIVE_FROM_DT = C.JOIN_DT AND DATASOURCE_NUM_ID = 9 AND JOB_RQSTN_WID IN (SELECT DISTINCT JOB_RQSTN_WID FROM W_RCRTMNT_RQSTN_A_TMP1)
   

   Note: There are two places where the above SQL has the DATASOURCE_NUM_ID hard-coded (DATASOURCE_NUM_ID = 9). Before running the SQL, you should change the value '9' to the appropriate value that you are using for DATASOURCE_NUM_ID.

3. Set the failed DAC task as completed.

4. Restart the Execution plan (this will resume the execution from the previous failure point).

1.3.69 Error in ETL - GRFDerive in Teradata

This issue applies to Versions 7.9.6 and 7.9.6.1.

This section is only relevant if the target database of the ETL is Teradata. The GRFDerive mappings (e.g. SDE_ORA_Stage_ARTransactionFact_GRFDerive) return the following error when a subledger distribution record is linked to multiple GL journal lines in OLTP data sources.

Message: Database errors occurred: FnName: Execute -- [NCR][ODBC Teradata Driver][Teradata Database] Duplicate row error in EBSR1211.W_AR_XACT_GIU_FS.

The following mappings will be affected:

• SDE_ORA_Stage_ARTransactionFact_GRFDerive

• SDE_ORA_Stage_APTransactionFact_GRFDerive

• SDE_ORA_Stage_GLCOGSFact_GRFDerive

• SDE_ORA_Stage_GLRevenueFact_GRFDerive

This GIU_FS table stores the INTEGRATION_ID of the subledger fact records that need to be updated with POSTED status. Since this table is used to update the subledger fact table, having duplicate records in this table is not an issue, neither will it cause error in the Oracle data warehouse. In Teradata databases, however, this will cause a duplicate row error.

Workaround

1. Open Informatica Designer and connect to your repository.

2. Open the SDE_ORAR12_Adapter folder.

3. Open the GRFDerive mapping (e.g. SDE_ORA_Stage_ARTransactionFact_GRFDerive)

4. Open the SQL Qualifier transformation and add the DISTINCT keyword as follows:

   SELECT DISTINCT 
   W_STATUS_D.INTEGRATION_ID AS DOC_STATUS_ID, 
   W_ORA_GL_LINKAGE_INFO_AR_TMP.DELETE_FLG, 
   W_ORA_GL_LINKAGE_INFO_AR_TMP.POSTED_ON_DT, 
   W_ORA_GL_LINKAGE_INFO_AR_TMP.INTEGRATION_ID, 
   W_ORA_GL_LINKAGE_INFO_AR_TMP.DATASOURCE_NUM_ID 
   FROM W_ORA_GL_LINKAGE_INFO_AR_TMP, 
   ... 
   

5. Click OK to close the SQL editor.

6. Click OK to close the SQL Qualifier transformation.

7. Save the mapping.

1.3.70 Missing Records When Filtering Reports By Project Organization Name in Oracle Project Analytics

This issue applies to Versions 7.9.6 and 7.9.6.1.

If dashboard users change the value of attribute ORG_INFORMATION2 from 'Y' to 'N' for an organization in the OLTP dimension table HR_ORGANIZATION_INFORMATION, the ETL process that populates table W_INT_ORG_D may return records for which column PROJECT_ORG_FLG is not set to 'Y'. This is not correct and might cause records to be missing from reports when the user is filtering reports by Project Organization Name.

Workaround

The workaround for this issue is to remove the Logical Table Source (LTS) content filter condition for Dimension: "Dim - Project Organization", as follows:

1. In Oracle BI Administration Tool, open the Oracle BI repository file OracleBIAnalyticsApps.rpd.

2. In the Business Model and Mapping layer, do the following:

   1. Double-click the Logical Table in the \Core\ folder named 'Dim - Project Organization', to display the Logical Table - <Name> dialog.

   2. Display the Sources tab.

   3. Edit the source named Dim_E_INT_ORG_D_Project_Organization, to display the Logical Table Source - <Name> dialog.

   4. Display the Content tab.

   5. Delete the text from the 'Use this "WHERE clause" filter to limit rows returned (exclude the "WHERE")' box.

3. Save the details.

1.3.71 ETL Failure In PLP_INVENTORYMONTHLYBALANCE With Teradata Database

This issue applies to Version 7.9.6.1 only.

This issue affects any source system OLTP, and Teradata target warehouse databases. The task "PLP_InventoryMonthlyBalance" fails while running ETL using Teradata as the target data warehouse database. The task contains two steps, a Delete and then a Table population step. They currently are both using the Teradata loader. The step that targets: W_INVENTORY_MONTHLY_BAL_F1 (DELETE) should be changed to use ODBC Relational OLAP.

Workaround

1. Open Informatica PowerCenter Workflow Manager.

2. Open the PLP folder for Teradata (PLP_TD).

3. Check out the Teradata Session: PLP_InventoryMonthlyBalance, and open in the Workflow Designer tool.

4. Display the Mapping Tab, and select the Target named W_INVENTORY_MONTHLY_BAL_F1.

5. In the Writers area, change the value in the Writers field to 'Relational Writer'.

6. In the Connections area, change the value in the Value field to '$DBConnection_OLAP'.

7. Save and Check In.

1.3.72 Rowsize Limitation in MS SQL Server

This issue applies to Version 7.9.6.1 only.

MS SQL Server has a limit on the rowsize of a table. SQL Server database does not report a problem when a table is created. However, if the row data size increases to more than 8060, then an error similar to the following is reported: "Microsoft OLE DB Provider for SQL Server: Cannot create a row of size 8077 which is greater than the allowable maximum of 8060."

This error is a data issue, and reducing the table limits the functionality of the table. Therefore, Oracle's recommended workaround is handled as a customization based on the environment and usage of this table.

Workaround

Possible workarounds are:

• Reduce the columns sizes to accommodate the data.

• Reduce the levels of the position hierarchy that suits the customer implementation.

• Reduce the table size by removing any unused columns with the prefix "Current_" that show the current state and 'As is' values of a hierarchy for each position.

Note: All of the above changes require customization of the ETL to implement the effective data model change.

1.3.73 Quote Item Fact Not Secured By Position Hierarchy

This issue applies to Versions 7.9.6 and 7.9.6.1.

This issue affects customers using Oracle Sales Analytics with a Siebel source system.

Reports that use Quote Item metrics along with Order Item metrics (for example, Shared Folders : Sales : Customers : Accounts / Quote & Order History) fail when accessed by users with data security restrictions.

Additionally, ad-hoc reports created from the following Subject Areas in Oracle Sales Analytics that involve the Quote Item fact will either fail or show incorrect data due to the non-implementation of data security:

• Sales – CRM Customer Overview

• Sales – CRM Quotes

Workaround

1. In Oracle BI Administration Tool, open the Oracle BI repository file OracleBIAnalyticsApps.rpd.

2. In the Business Model and Mapping Layer, expand the Core folder in the Business catalog and navigate to 'Fact - CRM - QuoteItem'.

3. Highlight this logical fact and also select the logical dimension "Dim – Position Security", then right click and choose Business Model Diagram -> Selected Tables Only.

   A Logical Table Diagram window displays the selected logical objects.

4. Create a complex join starting from "Dim – Position Security" to "Fact – CRM – Quote Item".

5. Click OK and close the Pop up window.

6. Double click the "Fact_W_QUOTEITEM_F" (under Sources) from the logical fact table "Fact – CRM – Quote Item" to display the properties dialog.

7. On the properties dialog, display the Content Pane.

8. From the Dimension list, choose Security Dimension and set the Logical Level as "Security Detail".

9. Follow steps 6 - 8 for the other Sources in the logical fact table "Fact – CRM – Quote Item" (that is, Fact_W_QUOTE_F, Fact_W_QUOTEITEM_F_Quarter_Ago, and Fact_W_QUOTEITEM_F_Year_Ago).

10. To modify the data-filter, choose Manage, then Security to display the Security Manager.

11. Select Groups from the Left Pane.

12. In the Right Pane, double click on the group “Primary Employee/Position Hierarchy-based Security” to open the group's properties.

13. Click Permissions to display the User/Group Permissions dialog, and then display the Filters tab.

14. Select Add to Create a new Data filter and choose the logical fact "Fact – CRM – Quote Item".

    Once the selection is made, a new filter for the logical fact is created.

15. Add the business model filter Core."Dim - Position Security"."Hierarchy Based Column" = VALUEOF(NQ_SESSION."USER").

16. Click OK and close the window.

17. Save the RPD file.

1.3.74 Employee Name (From Position Dimension) Shows Incorrectly In Reports

This issue applies to Versions 7.9.6 and 7.9.6.1.

This issue affects customers using out-of-the-box reports in Oracle Sales Analytics (including Usage Accelerator module of Sales Analytics).

In many out-of-the-box reports, the 'Employee Full Name' column is defaulted as the full name of the logged in user, sourced from the Position Dimension. To achieve this, the CHOOSE function is used in the expression builder in the Oracle BI Answers report. However, the CHOOSE function fails to fetch the name of the logged in user correctly, since some changes were made in Oracle BI-EE 10.1.3.4.x. Instead, it displays the name of the top level employee (the first column in the CHOOSE statement), for users at any level. However, the metrics are shown correctly. Examples of reports impacted:

• Shared Folders: Sales: Pipeline: Overview/My Top Stalled Opportunities

• Shared Folders: Sales: Pipeline: Subordinates/Pipeline by Subordinate

Workaround

Note: The IndexCol function in this definition makes the Hierarchy-Based Column default to one of the columns in the Position table based on the value of HIER_LEVEL. So, if the value of HIER_LEVEL is 0, the new column will default to the first column in the list, and so on.

1. Open the report and Click on 'Modify'.

2. On the Criteria tab, go to 'Full Name' column and click on 'fx' to display the 'Edit Column Formula' window.

3. Replace the existing column formula with:

   INDEXCOL(VALUEOF(NQ_SESSION.HIER_LEVEL),Position."Current Top Employee 
   Full Name", Position."Current Level 16 Employee Full Name", Position."Current 
   Level 15 Employee Full Name", Position."Current Level 14 Employee Full Name", 
   Position."Current Level 13 Employee Full Name", Position."Current Level 12 
   Employee Full Name",Position."Current Level 11 Employee Full Name", 
   Position."Current Level 10 Employee Full Name", Position."Current Level 9 
   Employee Full Name", Position."Current Level 8 Employee Full Name", 
   Position."Current Level 7 Employee Full Name", Position."Current Level 6 
   Employee Full Name", Position."Current Level 5 Employee Full Name", 
   Position."Current Level 4 Employee Full Name", Position."Current Level 3 
   Employee Full Name", Position."Current Level 2 Employee Full Name", 
   Position."Current Level 1 Employee Full Name", Position."Current Base 
   Employee Full Name")
   

4. Click OK and save the report.