Reference variable expressions within Oracle BI Answers

Variables such as System or Session can be referenced witin Oracle BI Answers. To reference a variable with a expression, the following syntax is available for referencing the Login User Name:


@{user.displayName}{default}

@{biServer.variables['NQ_SESSION.USER']}

This can be used within a Narrative View to display information about the current User or Session:

image

The following can be used as example:


[b]User Name:[/b] @{user.displayName}{default} <BR>
[b]User Name:[/b] @{biServer.variables['NQ_SESSION.USER']} <BR>
[b]Roles:[/b] @{biServer.variables['NQ_SESSION.ROLES']} <BR>
[b]Group:[/b] @{biServer.variables['NQ_SESSION.GROUP']} <BR>
[b]Group:[/b] @{biServer.variables['NQ_SESSION.WEBGROUPS']} <BR>

image

For further reference, check the official Documentation:

Advertisements

OBIEE: BY clause for group by

Within Oracle BI Answers (and only within BI Answers and not within the Metadata model), the BY clause can be used in conjunction with a Aggregate function to specify the level at which the metric should be aggregated (group by).

As an Example, the following Analysis displays the shipped amount per Year and product type:

image

Within certain scenarios, it may be useful to add a level of aggregation at a particular leve within a Report. This can be achieved with the BY clause. This clause can be added with a Aggregation function such as MIN, MAX, SUM etc. within the BI Answers formular editor:

image

SUM("Base Facts"."7- Shipped Amount" BY "Time"."T05 Per Name Year")

This, creates a group by for the measure at the level of Year, regardless of other aggregation levels intented by the Report e.g. at the level of produt type:

image

This clause should only be used with caution for possible incorrect or unintended results.

Caching in a High Availibility Cluster

As described in a previous article, the Oracle BI Server maintains a cache entry to improve the response time for further Analysis, which can be satisfied with the cached result. The cached result itself is a binary file, ending with “.tbl”.

Within a clustered Oracle BI environment, a Global Cache can be configured on a shared storage, but each Oracle BI instances will maintain it’s own “local” cached result.
For “regular” BI Answers and Dashboard usage, the cached results will not be propagated to other instances within the cluster. Only when using Oracle BI Agents with the cache seed as target, will be propagated from one instances to another.

Within the Official Documentation the following diagram is shown (e.g. for Version 12.2.1.2) to describe the Global Cache (path to be configured within Oracle BI Enterprise Manager) and the synchronization / propagation of cached results within the Cluster:

image

However, this only applies to Analysis run using Agents and with the target for seeding the Cache:

image

The propagation of cached results will be working for Reports and Dashboard Pages.

Once, an Agent has run to seed the Cache, the cached result (.tbl) file will be placed (propagated/pushed) to the Global Cache (on the shared storage) and other BI instances within the cluster will pull the cached result into their own local cache directory to satisfy further Analysis based on their polling intervals. For regular BI Answer requests, these caching files (.tbl) will not be placed (pushed) on the Global Cache (shared storage), nor will the other BI Servers nodes pull the cache file!

Example Scenario:

Thus, a Business User may be logged into instances 1 and based on his Analysis a cached entry will be created for further Analysis. However, this cached result will not be copied (or propagated) from instances 1 to instances 2. Hence, the same User may log into the system later that day and will be logged into instances 2 and will not received the previously cached result from instances 1. Only if the Analysis has been seeded via a Agent, the cache will be copied and propagated between both instances to satisfy the e.g. second Analysis.

This is also documented with the following Technote to correct/precise the Official Documentation:

Understanding Oracle BI Server Cache

To enable the Oracle BI Server Cache, the Cache needs to be activated within the Oracle Enterprise Manager:
Unbenannt

Additionally, the NQSConfig file contains additional parameter to steer the entry and usage of the Oracle BI Server Cache:
Unbenannt6

If the Cache is enabled, the created Cache entries can be viewed on the hard disk, but also the BI Admin Tool. The Cache files are placed in:

C:\oracle\middleware\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\cache

Unbenannt5

These Cache entries hold the information about the logical SQL used to created the Cache and the returned result set for the query. The Cache Entries can also be viewed from the BI Admin Tool to provide further information about the respective Cache Entry and e.g. the last usage:

Unbenannt7

Some Reports/Analysis may not qualify for a creation of a Oracle BI Server Cache entry. This can also be seen in the Session log, with the following statement:

The logical query block fail to hits or seed the cache in subrequest level due to [[ only one subrequest ]]

In general, this is due to one of the following reasons given from the Official Documentation:

  • Noncacheable SQL element. If a SQL request contains CURRENT_TIMESTAMP, CURRENT_TIME, RAND, POPULATE, or a parameter marker, then it is not added to the cache.
  • Noncacheable table. Physical tables in the Oracle BI Server repository can be marked “noncacheable.” If a query references any noncacheable table, then the query results are not added to the cache.
  • Cache hit. In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache.
  • The exception is query hits that are aggregate roll-up hits. These are added to the cache if the NQSConfig.INI parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to YES.
  • Result set is too big.This situation occurs when you exceed the size set in DATA_STORAGE_PATHS, or if you have rows in excess of the number set in MAX_ROWS_PER_CACHE_ENTRY. See Section A.3.2, “DATA_STORAGE_PATHS” and Section A.3.3, “MAX_ROWS_PER_CACHE_ENTRY” for more information.
  • Query is canceled. This can happen by explicit cancellation from Oracle BI Presentation Services or the Administration Tool, or implicitly through timeout.
  • Oracle BI Server is clustered. Queries that fall into the ‘cache seeding’ family are propagated throughout the cluster. Other queries continue to be stored locally. Therefore, even though a query might be put into the cache on Oracle BI Server node 1, it might not be on Oracle BI Server node 2.

Additionally, one of the following cases may also prevent from entries being created within the Cache:

  • Caching is not enabled (CACHE=N in the NQSConfig.INI file)
  • The WHERE clause in the logical SQL is not semantically the same, or not a logical subset of a cached statement
  • the columns in the SELECT list not yet exist in the cached query, or they are not able to be calculated from them
  • It has not equivalent join conditions, so that the resultant joined table of any incoming query is not the same as (or a subset of) the cached results
  • If DISTINCT is used, the cached copy has to use this attribute as well
  • Aggregation levels are not compatible, since they either have to be the same or more aggregated than the cached query
  • No further aggregation (for example, RANK,MAX) can be used in the incoming query
    (sub request / sub Report)
  • Any ORDER BY clause has to use columns that are also in the cached SELECT list

As an example, the following (main) Report:

cache main report

Is based on the results of another Analysis, which also uses an Aggregation:

cache sub report

The main Report is executed, but no Cache Entry is created:

cache monitor

This can also be analyzed from the Session log for the SQL for this statement:

session log

Since, the Sub Report also contains an Aggregation, no Cache Entry will be created for the Report. This can be seen in the Session log (may be required to set log level to 3 or even 4 to see below message):

cache message

To allow the Oracle BI Server to create a Cache Entry, the Analysis needs to be re-written. E.g. the Sub Report needs to be removed and needs to be part of the (main) Report. In the above case, by creating the Aggregate in the (main) Report and by the “BY” clause within the formula to specify the Group element.

Once, a Cache Entry is created for one User, it may also be shared to fulfill a request from another User. This can be seen from the Oracle BI Admin Tool within the Cache Manager Screen:

cache usage

Sometimes Users are also mislead in terms of Cache usage since Oracle BI provides two cache systems:

And even if the Oracle BI Presentation Server Cache is not enabled (by default not enabled) the Oracle BI Presentation Server still has a Cursor Cache which can be viewed from the Session Monitor (http://localhost:9704/analytics/saw.dll?Sessions) and which can not be disabled and only purged manually from the Session Monitor “Cancel All Cursor” Button.

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.

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.