18 Troubleshoot and Tune Oracle Fusion Reports

To properly troubleshoot a reporting issue, identify which underlying platform or content is being used in the Oracle Fusion application user interface. When the source of an issue is identified, consider the following:

• Oracle Transactional Business Intelligence (OTBI) provides packaged analytical content in real time. Supported by the Oracle Business Intelligence platform, OTBI can be referred to as real-time, current-state, or transactional business intelligence.

• Oracle Fusion Financials also uses Financial Reporting technology.

• Some analyses use Essbase, Real Time Decisions, or Oracle BI Publisher reports.

OTBI is based on the Oracle Business Intelligence platform and can be referred to as real-time, current-state, or transactional business intelligence. OTBI objects analyze very recent data such as today's data or this week's data.

To provide this up-to-the-minute reporting, OTBI provides prebuilt analytical reports and ad-hoc reporting on current transactional data in Fusion applications. OTBI provides the BI Server access to Fusion transactional database schema objects through Oracle Application Development Framework (ADF) View Objects, which are modeled in its repository metadata. Potential points of failure for OTBI include:

• BI Server

• Logical queries

• Physical queries

• ADF View Objects

• ADF View Objects in Oracle BI repository metadata

Figure 18-1 Oracle OTBI Architecture and Common Troubleshooting Areas

Most diagnosis of issues encountered in OTBI is performed using the Oracle BI query log. This and the other tools and utilities described in Table 18-1 may be used to troubleshoot issues across possible points of failure in OTBI.

Enable the NQQuery.log file to diagnose issues encountered in OTBI physical and logical queries. Use one of the following methods to enable the NQQuery.log file:

• Enable query logging from the Oracle BI Administration Tool. For information about enabling the query log file, see section Managing the Query Log in the System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

• Enable the LogLevel system session variable for the repository by accessing the repository and setting the LogLevel system variable to 7. For information about setting this variable, see section About System Session Variables in the Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.

• If you have administrator permissions, log into Presentation Services, access the analysis to troubleshoot, and temporarily change its logging level to 2. This will turn on logging for the analysis even when logging is turned off for all analyses. For more information, see section Setting the Query Logging Level in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

  If this method is used to enable the NQQuery.log file, look for the following items:

  • An error like "no log found." An error like this indicates that there is a known error. Look in the NQQuery.log file, which contains additional logging

  • The timestamp to locate the log entry

  • The logical SQL as shown in the error message. This is the SQL sent by Presentation Services to the BI Server layer

  • The physical SQL related to the above-mentioned logical SQL. Search for physical. The physical SQL is the SQL sent by BI Server through ODBC to the database

  • More detailed errors

Note that the NQQuery.log file is visible but not searchable in the Fusion Applications Control Log Viewer.

The request query executed from Presentation Services will be shown in the NQQuery.log file as follows:

RqList     0 as c1 GB,    Dim - Procurement Item.Category Name as c2 GB,    Dim
 - Procurement Item.Item Description as c3 GB,    # of PO Lines:[DAggr(Fact -
 Purchasing - Order.# of PO Lines by [ Dim - Procurement Item.Category Name, Dim
 - Procurement Item.Item Description] )] as c4 GBOrderBy: c1 asc, c2 asc, c3 asc

This query trace explains how a measure is calculated. It also shows the logical table source used to render the data.

1. Open the analysis in Oracle BI Answers.
2. Click the Administration link.
3. Under Session Management, click the Manage Sessions link.
4. Locate the relevant session in the list, and view its recent Presentation Services logical request in the Statement column.

The query trace shows the view object, its view criteria, and its view links that are used to execute the request. See the following:

<ADFQuery mode="SQLBypass" queryid="14604-3902" locale="en"><Parameters></Parameters>
<Projection>
<Attribute><Name><![CDATA[CategoryName]]></Name>
   <ViewObject><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.
   PurchasingItemP]]></ViewObject>
</Attribute>
<Attribute><Name><![CDATA[ItemDescription]]></Name>
   <ViewObject><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.
   PurchasingItemP]]></ViewObject>
</Attribute>           
<Attribute><Name><![CDATA[PurchasingDocumentHeaderTypeLookupCode]]></Name>
   <ViewObject><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.StandardLinePVO]]>
   </ViewObject>
</Attribute>
<Attribute><Name><![CDATA[PoLineId]]></Name>
   <ViewObject><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.StandardLinePVO]>]
   </ViewObject>
</Attribute>
<Attribute><Name><![CDATA[ItemNumber]]></Name> 
   <ViewObject><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.PurchasingItemP]]>
   </ViewObject>
</Attribute>
</Projection>
JoinSpec>
   <ViewObject>
       <Name><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.PurchasingItemP]]></Name>       
       <ViewLink><Name><![CDATA[oracle.apps.prc.po.publicView.analytics.link.
         PurchasingDocumentLinePVOToPurchasingItemPVO]]></Name></ViewLink>
   </ViewObject> 
   <ViewObject> 
       <Name><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.StandardLinePVO]]></Name>        
       <ViewLink><Name><![CDATA[oracle.apps.prc.po.publicView.analytics.link.
         PurchasingDocumentLinePVOToPurchasingItemPVO]]></Name></ViewLink>
   </ViewObject>
</JoinSpec>
<DetailFilter>
<ViewCriteria>
   <ViewCriteriaRow conjunction="VC_CONJ_AND"> 
   <Attribute><Name><![CDATA[PurchasingDocumentVersionCoSequence]]></Name>
     <ViewObject><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.StandardLinePVO]]>
     </ViewObject>
   </Attribute>
       <Value><![CDATA[0]]></Value>
       <Predicate operator="OPER_EQ"/> 
   </ViewCriteriaRow>
   <ViewCriteriaRow conjunction="VC_CONJ_AND" negated="true">
   <Attribute><Name><![CDATA[PurchasingDocumentVersionCoCanceledFlag]]></Name>
     <ViewObject><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.StandardLinePVO]]>
     </ViewObject>
   </Attribute> 
   <Value><![CDATA[Y]]></Value> 
   <Predicate operator="OPER_EQ"/> 
   </ViewCriteriaRow>
</ViewCriteria>
</DetailFilter>
</ADFQuery>

Both the user and the administrator can review the query trace to check that the desired ADF View Objects and View Links are executed. The execution of the view objects and view must be based on the Logical model code to render the measure or attributes executed in the request.

The SQL Bypass database should be set up in the repository to send the physical query to the database directly, rather than through the ADF Server. If the SQL Bypass database is enabled, the NQQuery.log file shows the physical query sent to the database. The following log file excerpt provides an example of a physical query:

WITH
SAWITH0 AS (select T744257.C501728333 as c1,
   T744257.C281594243 as c2,
   T744257.C469784899 as c3,
   T744257.C168071223 as c4
from
   (SELECT V110617254.CATEGORY_NAME AS C501728333,         V110617254.
    ITEM_DESCRIPTION
 AS C281594243,         V278099157.TYPE_LOOKUP_CODE263 AS C469784899,
V278099157.PO_LINE_ID AS C168071223,         V110617254.ITEM_NUMBER AS
C348883104,         V278099157.CO_SEQUENCE AS C78750419,         V278099157.CO_
CANCELED_FLAG AS C443371219,         V278099157.PO_HEADER_ID1 AS PKA_
PurchasingDocumentHeaderP0,         V278099157.VERSION_ID AS PKA_
PurchasingDocumentVersion0 FROM (SELECT PurchasingDocumentLine.PO_LINE_ID,
PurchasingDocumentHeader.PO_HEADER_ID AS PO_HEADER_ID1,
PurchasingDocumentHeader.TYPE_LOOKUP_CODE AS TYPE_LOOKUP_CODE263,
PurchasingDocumentVersion.CO_CANCELED_FLAG,
PurchasingDocumentVersion.CO_SEQUENCE,
PurchasingDocumentVersion.VERSION_ID,
(DECODE(PurchasingDocumentLine.ITEM_ID, NULL,
DECODE(PurchasingDocumentLine.VENDOR_PRODUCT_NUM, NULL,
(PurchasingDocumentLine.ITEM_DESCRIPTION || '[' || CategoryTranslation.
CATEGORY_NAME || ']'),(PurchasingDocumentLine.VENDOR_PRODUCT_NUM || '[' ||
PurchasingDocumentHeader.VENDOR_ID || ']')),TO_CHAR(PurchasingDocumentLine.
ITEM_ID))) AS ITEM_NAME FROM PO_LINES_ALL PurchasingDocumentLine, 
PO_HEADERS_ALL PurchasingDocumentHeader, PO_VERSIONS PurchasingDocumentVersion
 WHERE (PurchasingDocumentLine.PO_HEADER_ID = PurchasingDocumentHeader.
PO_HEADER_ID AND PurchasingDocumentHeader.PO_HEADER_ID =
PurchasingDocumentVersion.PO_HEADER_ID) AND ( (
(UPPER(PurchasingDocumentHeader.TYPE_LOOKUP_CODE) = UPPER('STANDARD') ) ) ))
 V278099157, (SELECT DISTINCT ( DECODE(PL.ITEM_ID,NULL,DECODE(PL.
VENDOR_PRODUCT_NUM, NULL,(PL.ITEM_DESCRIPTION || '[' || TL1.CATEGORY
_NAME||']'),(PL.VENDOR_PRODUCT_NUM || '[' || PH.VENDOR_ID ||']')), TO
_CHAR(I.ITEM_NUMBER))) AS ITEM_NUMBER,  PL.ITEM_DESCRIPTION, 
PL.CATEGORY_ID,TL1.CATEGORY_NAME, DECODE(PL.ITEM_ID, NULL,DECODE
(PL.VENDOR_PRODUCT_NUM, NULL, 'DESCRIPTION BASED ITEMS','SUPPLIER
 ITEMS'),'INVENTORY_ITEMS') AS ITEMTYPE  FROM PO_LINES_ALL PL, 
EGP_CATEGORIES_TL TL1, PO_HEADERS_ALL PH, PO_SYSTEM_PARAMETERS_ALL SP, 
EGP_SYSTEM_ITEMS_B I WHERE PL.CATEGORY_ID=TL1.CATEGORY_ID AND PL.CATEGORY_ID IS
NOT NULL AND PL.PO_HEADER_ID=PH.PO_HEADER_ID AND PL.PRC_BU_ID=SP.PRC_BU_ID AND
I.INVENTORY_ITEM_ID=PL.ITEM_ID) V110617254 WHERE V278099157.ITEM_NAME =
 V110617254.ITEM_NUMBER( + ) AND ( ( (V278099157.CO_SEQUENCE = 0 ) )  AND 
( NOT ( (V278099157.CO_CANCELED_FLAG = 'Y' ) ) ) )) T744257),
SAWITH1 AS (select D1.c1 as c2,     D1.c2 as c3,
   case when D1.c3 = 'STANDARD' then D1.c4 end  as c4
from
   SAWITH0 D1), SAWITH2 AS (select D1.c2 as c2,     D1.c3 as c3,
   D1.c4 as c4,
   ROW_NUMBER() OVER (PARTITION BY D1.c2, D1.c3, D1.c4 ORDER BY D1.c2 DESC,
   D1.c3 DESC, D1.c4 DESC) as c5from      SAWITH1 D1),SAWITH3 AS (select
   count(distinct case D1.c5 when 1 then D1.c4 else NULL end ) 
   as c1,     D1.c2 as c2,     D1.c3 as c3from SAWITH2 D1
group by D1.c2, D1.c3)
select distinct 0 as c1,     D1.c2 as c2,     D1.c3 as c3,     D1.c1 as c4 from
   SAWITH3 D1
order by c2, c3

The Oracle BI Server gets the view object physical query from the Oracle ADF Server. This query involves the view object query and the security predicate associated to it. Research the WebLogic Server log file, AdminServer-diagnostic.log, if there is an issue in the view object query. This file is located in the WebLogic Server domain associated to Oracle JDeveloper 11g.

The AdminServer-diagnostic.log file shows: the user name, view object, and attributes; the view link source and destination entities; and the view criteria for view objects, including security view criteria. It also contains the roles associated with the user and the security predicate associated with those roles, as well as the composite view object API called for the view object and view links and criteria.

1. In the Physical layer of the Administration Tool, expand the database object for the ADF Business Component data source.
2. Right-click a physical table and click View Data.
3. Check that the appropriate data displays. Note that if you have just imported, check in the new objects before performing this test.

1. Log in to Fusion Applications Control.
2. Open the Business Intelligence node and select coreapplication.
3. Click the Capacity Management page, and then click the Availability tab.
4. Under BI Server, locate coreapplication_obis1 and check its status.
5. If you discover that the BI Server is not running, use the Oracle BI log files to determine the cause of this issue. For more information about checking the Oracle BI log files, see Resolve Problems with Logical and Physical Queries Using BI Logs and Presentation Services and The BI Server Is Not Running: Check Log Files for Hints.

1. Log in to Fusion Applications Control.
2. Open the Business Intelligence node and select coreapplication.
3. Click the Diagnostics tab, and then click the Log Messages subtab.
4. Go to the View/Search Log Files section and click BI Server Log. The Log Messages page displays.
5. Search for an entry similar to the following entry:

   [2011-01-31T20:47:38.000-08:00] [OBIPS] [ERROR:10] [] 
   [saw.security.odbcuserpopulationimpl.initialize] [ecid: ] [tid: ] 
   Odbc driver returned an error (SQLDriverConnectW).State: HY000.  Code: 10058.  
   [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] 
   A general error has occurred.
   [nQSError: 73006] Cannot obtain Oracle BI Servers from either
   the primary Cluster Controller (somemachine.example.com)
   or the secondary Cluster Controller () specified for the
   clustered DSN. (HY000)[[File:odbcuserpoploaderimpl.cpp Line:282
   [2011-01-31T20:47:38.000-08:00] [OBIPS] [ERROR:16] []
   [saw.security.odbcuserpopulationimpl.initialize] [ecid: ]
   [tid: ] Unable to create a system user connection to BI Server during start up. Trying again.
   [[File:odbcuserpoploaderimpl.cpp
   Line:283
   

1. From the navigation pane, expand the BIDomain farm and select WebLogic Domain.
2. Click BIDomain to display the WebLogic Domain page.
3. From the WebLogic Domain menu, choose System MBean Browser.
4. In the System MBean Browser page, expand Application Defined MBeans.
5. Expand Application Defined MBeans, oracle.biee.admin, Domain: BIDomain, BIDomain.BIInstance.PresentationServerConfiguration.
6. Click BIDomain.BIInstance.PresentationServerConfiguration.

   Figure 18-2 System MBean Browser

   
   Description of "Figure 18-2 System MBean Browser"
7. In the Application Defined MBeans: BIDomain.BIInstance.PresentationServerConfiguration page, click the SyncLogonTimeoutSecs attribute.
8. Change the value from 900 to 60 in the Value field and click Apply.

When users log in to Presentation Services and access the Oracle BI Presentation Catalog to browse the shared folders for their reports, the shared folders do not contain any reports. To resolve this issue, consider the following options:

• Confirm that Items Exist on the File System

• Refresh the GUIDs

When users log in to Oracle BI Publisher and access the shared folders containing their reports, they see no reports. This problem can occur even though users can locate their reports in the Presentation Services Catalog's shared folders. To resolve this issue, consider the following options:

• Check User Privileges

• Adjust the BI Publisher Connection to Presentation Services

• Check for Catalog Corruption and Resolve as Needed

To manage the query environment, set query limits (governors) in the Oracle BI repository for users or application roles. See Perform Post-Installation Configuration in the Oracle Fusion Applications Documentation Library.

It is possible to debug and fix OTBI slow-running queries that are not performing well. OTBI is for real-time operations analysis and Oracle does not recommend running queries that do not fall into this category, or to query very large volumes of historical data.

Issues encountered in OTBI queries can be diagnosed in as described in Resolve Problems with Logical and Physical Queries Using BI Logs and Presentation Services.

Additional filters might be added by the application logic and security. Ask your database administrator to help tune the execution plan for this query to see which joins are causing performance issues. Avoid full table scans and hash table joins on large transaction tables. If these tables are seen, attempt to add specific filters for these tables which use existing indexes.

Query caching enablement can dramatically improve query performance on cached queries.

See Caching Options in Oracle Transactional Business Intelligence (OTBI) for information about how to enable caching.

If your Customer Relationship Management (CRM) Performance Servers are servicing requests, the BIAnalytics data source will get suspended if there is a bounce of the bi_server1. If this occurs, you will get a "Resource Dead Exception" error and possibly a "stuck thread" error.

To correct this error, resume the BIAnalytics data source manually.

Caching is turned off by default for the Oracle Transactional Business Intelligence (OTBI) content to ensure the OTBI analyses always return real-time data. If not absolutely necessary, then enable caching to obtain better query performance.

To enable caching and set a cache timeout in OTBI, choose one of the following options:

• Use the Cache Manager to manually manage the cache entries after enabling caching for OTBI view objects. Enable view object level caching by using the Oracle BI Administration tool and editing the master Oracle BI repository as follows:

  • Edit the view object properties and check the Cacheable check box.

    Do not edit the properties for aliases if the source view object property has already been changed. It is reflected in the alias automatically.

  • Set the Cache Persistence Time property to determine how frequently the cache should expire.

• Additionally, use the following options for automating the cache management process:

  • Use the Oracle BI Administration Tool to set the Cacheable and Cache Persistence Time properties for any view object that you want cached.

  • Use the Configure Oracle BI Server Event Polling Tables to purge the cache. Doing so requires a trigger that you would have to implement to populate this table when there are changes to the application's transaction tables, ensuring that you always avoid hitting a stale cache.