Understanding Oracle BI EE Presentation Server Cache(s)

Within the Oracle BI System, multiple components provide a caching mechanism to re-use previously executed Reports to improve query performance of subsequent requests. The following diagram provides an Overview of the main Architecture and main caching components:

Oracle BI Server Cacheing cropped

By default, the Oracle BI Presentation Server Cache is not actived. However, the Oracle BI Presentation Server additionally provides a Oracle BI Presentation Server Cache which can not be deactived. This “Cursor” Cache can (only) be viewed from the Session Monitor Administration Page http://localhost:9704/analytics/saw.dll?Sessions:

session monitor

Thus, even when the Oracle BI Presentation Server Cache is not actived, subsequent execution of the same Analysis maybe retrieved from the Oracle BI Presentation Server Cursor Cache which may often mislead the perception of cacheing.

For the first execution of an Analysis a new Cursor and Cursor Cache maybe created eventually:

cache monitor

A subsequent execution of the same Report will return instantly (even with no Presentation Server Cache configured) since the Report can be retrieved from the Presentation Server Cursor Cache. No additional Cursor will be created and only the time of execution will be updated for the previous Cursor.

cache monitor 2

As said before, the Cursor Cache can not be disabled and the Cursor Cache can only be purged manually from the Session Monitor. However, a Report/Analysis can be foreced to bypass the Presentation Server Cursor Cache (and Presentation Server Cache) by checking the bypass Presentation Server Cache option on the Advanced Tab of a Analysis.

bypass

The disk directory of the Cursor Cache is not documented by Oracle, but they are stored under the query cache folders of:

C:\oracle\middleware\instances\instance1\tmp\OracleBIPresentationServicesComponent\coreapplication_obips1\querycache_0

Unbenannt2

Which places a result set file for the respective Presentation Server Cursor Cache:

Unbenannt4

To active the regular Presentation Server Cache, the instanceconfig.xml needs to be edited. The instance config can typically found here (for 11g):

C:\oracle\middleware\instances\instance1\config\OracleBIPresentationServerComponent\coreapplication_obips1

To active the Caching the following xml elements need to be added to the file:

<ServerInstance>
  <Cache>
    <Query>
      <MaxEntries>100</MaxEntries>
      <MaxExpireMinutes>60</MaxExpireMinutes>
      <MinExpireMinutes>10</MinExpireMinutes>
      <MinUserExpireMinutes>10</MinUserExpireMinutes>
    </Query>
  </Cache>
<ServerInstance>

After restarting the Presentation Server, the Cache Files will be created in the following directory:

C:\oracle\middleware\instances\instance1\tmp\OracleBIPresentationServicesComponent\coreapplication_obips1\obis_temp

Summary: Even with no Presentation Server Caching enabled, the Presentation Server Cursor Cache will cache result sets to fulfill subsequent Analysis requests from the Client.

Advertisements

Tip: Using Row-wise variables and Initialization Blocks

Aside

Tip: Using Row-wise variables and Initialization Blocks

It is often required to use row-wise variables (array’s) to store a list of items. E.g. For the security/visibility it may be required to store the Organization a User has access to. This can be achieved using row-wise variables and Initialization Blocks from OBIEE.

SELECT ‘ActiveOrgs’, OrganizationTable.Name
FROM OrganizationTable
WHERE OrganizationTable.Use = ‘:USER’

This will initialize the Variable ActiveOrgs with the results of the Initialization Blocks. This variable can than be used at other places to filter the respective Organizations.

 

detecting Report (Analysis) Orphans in OBIEE

A Dashboard typically represent a combination of different Reports to provide summary information about a Business Process or Sub-Process e.g. with HR: A Recruiting Dashboard may contain Reports about hiring sources and the respective Time to Hire or a Dashboard provides Reports about certain balance information E.g. current capital in Fixed Assets. Over time these Reports may be replaced or exchanged and many reports may only be created for Testing purposes. The management of all these Reports becomes very difficult and the IT Department may want to understand the structure of the Web Catalog in terms of Report usage on Dashboard and Report Orphans. The Oracle BI Sample App provides a example of how this can be implemented under the Lifecycle and Admin section:

image

 

Pre-built Reports exist that Analyse the structure of the Webcat to determine unused Analysis:

image

These samples require to execute scripts on the BI instance, which is also documented within the Sample App Image under /app/oracle/SAScripts/ReferenceChecker.

Oracle BI12c: placing custom images in BI Server and reference using fmap

For Oracle BI 12c custom content such as images or pdf help files needs to be placed in the following directory:

/domains/bi/servers/obips1/tmp/earmanager/analytics/../res/

where /../ is a dynamic folder name such as v-PLM12aFeu7Y.

Within a Report or Dashboard this path may be referenced using the following syntax:

fmap:file_name

or:

fmap:foldername/file_name (if a subfolder “foldername” was created below /res)

the below links provide further information on using fmap or placing custom resources within Oracle BI:
Storing Custom Files Locally and Using the fmap Function to Reference Them
How to configure static content in OBIEE 12c

fixing: BAR file export for non UTF-8 char set

The following Error message was display when exporting the BAR file from Oracle BI 12c:

SEVERE: Error while reading application-role.
apr 12, 2017 1:53:22 PM oracle.bi.jazn.bar.JaznFileExport ProcessApplicationRole

SEVERE: Error while reading application-role.
apr 12, 2017 1:53:22 PM oracle.bi.jazn.bar.JaznFileExport RemoveUnrequiredNodes
SEVERE: Failed on removing unrequired node during export.com.sun.org.apache.xerc
es.internal.impl.io.MalformedByteSequenceException: Invalid byte 2 of 3-byte UTF
-8 sequence.
apr 12, 2017 1:53:22 PM oracle.bi.jazn.bar.plugin.JaznMetadataManagerGenericPlug
inImpl exportServiceInstance
SEVERE: Failed in modifying content of migrated jazn file D:\workDir\exportSICus
tomization\ssi\metadata\authmodel\jazn\jazn-data.xml
apr 12, 2017 1:53:22 PM oracle.bi.bar.si.framework.handler.JaznServiceInstanceHa
ndler exportServiceInstance
SEVERE: Failed running exportServiceInstance API of oracle.bi.jazn.bar.plugin.Ja
znMetadataManagerGenericPluginImpl
oracle.bi.bar.si.framework.plugin.MetadataPluginException: Failed in modifying c
ontent of migrated jazn file D:\workDir\exportSICustomization\ssi\metadata\authm
odel\jazn\jazn-data.xml
at oracle.bi.jazn.bar.plugin.JaznMetadataManagerGenericPluginImpl.export
ServiceInstance(JaznMetadataManagerGenericPluginImpl.java:359)
at oracle.bi.jazn.bar.plugin.JaznMetadataManagerGenericPluginImpl.export
ServiceInstance(JaznMetadataManagerGenericPluginImpl.java:66)
at oracle.bi.bar.si.framework.handler.JaznServiceInstanceHandler.exportS
erviceInstance(JaznServiceInstanceHandler.java:525)
at oracle.bi.bar.si.framework.ServiceInstanceFrameWorkImpl.exportService
Instance(ServiceInstanceFrameWorkImpl.java:370)
at oracle.bi.bar.si.ServiceInstanceLifeCycleImpl.exportServiceInstance(S
erviceInstanceLifeCycleImpl.java:665)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.python.core.PyReflectedFunction.__call__(Unknown Source)
at org.python.core.PyMethod.__call__(Unknown Source)
at org.python.core.PyObject.__call__(Unknown Source)
at org.python.core.PyObject.invoke(Unknown Source)
at org.python.pycode._pyx133.exportServiceInstance$4(/D:/Oracle/Obiee12c
/bi/lib/bi-bar.jar!/wlstScriptDir/ServiceInstanceLifeCycle.py:84)
at org.python.pycode._pyx133.call_function(/D:/Oracle/Obiee12c/bi/lib/bi
-bar.jar!/wlstScriptDir/ServiceInstanceLifeCycle.py)
at org.python.core.PyTableCode.call(Unknown Source)
at org.python.core.PyTableCode.call(Unknown Source)
at org.python.core.PyFunction.__call__(Unknown Source)
at org.python.core.PyObject.__call__(Unknown Source)
at org.python.pycode._pyx165.f$0(&lt;console&gt;:1)
at org.python.pycode._pyx165.call_function(&lt;console&gt;)
at org.python.core.PyTableCode.call(Unknown Source)
at org.python.core.PyCode.call(Unknown Source)
at org.python.core.Py.runCode(Unknown Source)
at org.python.core.Py.exec(Unknown Source)
at org.python.util.PythonInterpreter.exec(Unknown Source)
at org.python.util.InteractiveInterpreter.runcode(Unknown Source)
at org.python.util.InteractiveInterpreter.runsource(Unknown Source)
at org.python.util.InteractiveInterpreter.runsource(Unknown Source)
at weblogic.management.scripting.utils.WLSTInterpreter.runsource(WLSTInt
erpreter.java:1095)
at weblogic.management.scripting.WLST.main(WLST.java:227)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at weblogic.WLST.main(WLST.java:47)

apr 12, 2017 1:53:23 PM oracle.bi.bar.si.framework.ServiceInstanceFrameWorkImpl
exportServiceInstance
SEVERE: Error during export service instanceoracle.bi.bar.si.exception.ServiceIn
stanceLifeCycleException: Failed running CreateServiceInstance API of oracle.bi.
jazn.bar.plugin.JaznMetadataManagerGenericPluginImpl
apr 12, 2017 1:53:23 PM oracle.bi.bar.si.ServiceInstanceLifeCycleImpl exportServ
iceInstance
SEVERE: Failed in running all the handlers during export service instance.
Traceback (innermost last):
File "&lt;console&gt;", line 1, in ?
File "/D:/Oracle/Obiee12c/bi/lib/bi-bar.jar!/wlstScriptDir/ServiceInstanceLife
Cycle.py", line 84, in exportServiceInstance
at oracle.bi.bar.si.ServiceInstanceLifeCycleImpl.exportServiceInstance(S
erviceInstanceLifeCycleImpl.java:669)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)

oracle.bi.bar.si.exception.ServiceInstanceLifeCycleException: oracle.bi.bar.si.e
xception.ServiceInstanceLifeCycleException: BAR-00005

&nbsp;

To fix this error the special characters needed to be removed from the Application Role name, such as the following example:

image

After the removal of the special character from the Application Role name the BAR file could be created successfully.

changing the default data type of number formats

to change the default format used by the Oracle BI EE 11g the following configuration file needs to be changed:

/home/oracle/Middleware/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/catalog/SampleAppLite/root/system/metadata

image
E.g. for two digits behind the number separator:

<dataFormat minDigits="2" maxDigits="2" commas="true" 
negativeType="minus" xsi:type="saw:number";

After saving and restarting the BI Server, the numbers are displayed with two digits behind the number separator.

fixing: Data filter expression VALUEOF(NQ_SESSION.”variable”; for Applicaton Role is considered as non conditional, error: [nQSError: 23006]

After migrating from Oracle BI EE 11g to Oracle BI 12c the RPD consistency checker shows the following Error message with Error # 38197:

Data filter expression VALUEOF(NQ_SESSION.”variable”) for Applicaton Role is considered as non conditional, error: [nQSError: 23006] The session variable, NQ_SESSION.variable, has no value definition
image
Within OBI EE 11g consistency checker this was neither thrown as a Error message nor as a Warning.

Within the consistency checker of 12c, this is throwing as a error. To fix this a default value needs to be specified for each variable besides the Authorization Block. For this go to Manage| Variables |  Session |Initialization Blocks and double click on a Name e.g. Business Groups.

image
And open “Edit Data Target” within the variable Target area and assure that the Default Initializer as a default value as shown below:
image
After running the consistency checker again, the error message disappears.

Bug: creation of Windows service for Oracle BI 12.2.1.2.0 within installation

During the installation of Oracle BI 12c (Version 12.2.1.2.0) a Windows service called “Oracle Business Intelligence [bi]” is automatically created to start & stop the BI services (Weblogic + BI Domain) as a Windows service e.g. to auto startup the BI Services in case the complete windows server is re-booted.

For the current version 12.2.1.2.0 a bug exist which configures the Environment variables incorrectly when the Oracle BI installation is not done to the C:\ drive, but another hard drive within the server, such as D:\ . The issue only applies to starting and stopping the BI Services via the Windows Service method and not to starting/stopping via from the start.cmd command line (as the Windows service points to a different start script located in the oracle.bi.sysman directory and does not use the regular bitools/bin/start.cmd script). The incorrect setting of the Environment variables prevents the Windows service from starting the BI Services and the following error is written to the windows services log file:

BI_PRODUCT_HOME set as c:\windows\system32
ORACLE_HOME set as C:\Windows
[…]
The system cannot find the path specifiedimage
This is caused by an within the start.cmd script which is called from the Windows service “Oracle Business Intelligence [bi]” and located in the following directory:

D:\Oracle\Obiee12c\bi\modules\oracle.bi.sysman\scripts\start.cmd

The script uses the windows shell command pushd and popd to write the current working directory into the session, but doesn’t catch if the installation is done to a different drive from C:\ when performing a cd (change directory) command. This can be seen by the fact that the BI_PRODUCT_HOME and ORACLE_HOME are referencing (pointing) to the patch of the windows cmd tool C:\windows\system32 rather then the correct Oracle BI installation directory.

As a workaround the pushd and popd have been removed from the start.cmd script and the path has been specified for path Environment variables BI_PRODUCT_HOME & ORACLE_HOME.

SET BI_PRODUCT_HOME=D:\Oracle\Obiee12c\bi\
ECHO BI_PRODUCT_HOME set as %BI_PRODUCT_HOME%
SET ORACLE_HOME=D:\Oracle\Obiee12c
ECHO ORACLE_HOME set as %ORACLE_HOME%
SET default_biDomainHome=%ORACLE_HOME%\user_projects\domains\bi


after saving the file like above the Windows service uses the correct environment variable path, as seen by comparing both highlighted areas below:

Examining OBIEE 11g Presentation Service Privileges and precedence

The Official Documentation for the 11g Oracle Business Intelligence (Security Guide) mentions the following key rules about the evaluation of the Presentation Service Privileges:

“Presentation Services privileges control the rights that users have to access the features and functionality of Presentation Services. Privileges are granted or denied to specific application roles, individual users, and Catalog groups using a privilege assignment table.”

The Presentation Service privileges can be access from the following URL: http://hostname:port/analytics/saw.dll?PrivilegeAdmin

image

“privileges are either explicitly set or are inherited through role or group membership, explicitly denying a privilege takes precedence over any granted, inherited privilege. Meaning that even if Application roles are nested and thus forming a hierarchy any denied privilege takes precedence over inherited grants or directly assigned grants.”

Additionally, the following example is provided within the D. Appendix section of the same Documentation page:

inherit_dl_privscatgrp

With the following statements regarding the evaluation:

    • User1 explicitly has the Executive role, and thus implicitly has Finance role and also Sales role.
    • User1 also explicitly has the BI Author role, and thus also implicitly has BI Consumer role.
    • User1’s flattened list of application roles is: Executive, BI Author, Finance, Sales and BI Consumer.

The effective privileges from Executive Role are Denied Administration privilege […] the Sales’ Denied Administration privilege takes precedence over Executive’s granted privilege, as Deny always takes precedence.”

As a showcase, the above has example has been re-created in a simplified version with all Application roles and the Presentation Service privilege for “Access to Administration” within the 11.1.1.7 Oracle BI Version:

image

The five Application roles of the above diagram have been configured within the Enterprise Manager (EM) Application role configuration:

image
All five Application roles are also correctly displayed for the User1 within the Oracle Business Intelligence “My Account” view:
image
Afterwards, the privileges have been configured (with Granted and Denied) for the Application roles within the Presentation Service privileges Administration page:image
However, contrary to the rule and the example the “Administration Page” privilege is not denied:
image

The “Administration Page” privilege is granted for User1 due to the Membership of the Executive Application Role.

For testing purposes the five Application roles of the example have been enhanced to the following six:

image

With the following test cases and results:

# User Privilege Executive Finance Sales Sales North Result
1 User1 Admin
Page
Granted / Denied / Granted
2 User1 Admin
Page
/ Denied Granted / Denied
3 User1 Admin
Page
Denied Granted Granted / Denied
4 User1 Admin
Page
/ Granted / Denied Denied
5 User1 Admin Page / Denied / Granted Denied
6 User1 Admin Page / Granted Denied / Denied

deriving the following rules for the determination of the Presentation Service privileges:

  • if a privilege is set (either with denied or granted) for a Application role directly assigned (member of/next) to the User this takes precedence over everything else (see #1+#3)
  • for siblings on the level (within the same hierarchy level) the more restrictive is applied (#2+ #6)
  • In case of inherited ancestor privileges the more restrictive is applied (#4+#5)

Links to the 11g Documentation:

Example of Determining a User’s Privileges with Application Roles 11.1.1.7

Example of Determining a User’s Privileges with Application Roles 11.1.1.9

Example of Determining a User’s Privileges with Application Roles 12.2.1.1