Accessing data in Oracle Fusion Applications Cloud for Analytics

The following diagram shows the high-level options to access data within Oracle Fusion Applications (FA) Cloud for Analytical purposes:

Please note: that this diagram is of April 2018 and may not apply in full in future.

For more details on how to connect to Oracle Fusion Applications (FA) Cloud using Visual Analyzer (VA) and DV Desktop (DVD), please reference the previous article.

The details for connecting to FA using ODI (with BI Applications) or BI Data-sync will be described in future articles.

Connecting to Oracle Fusion Applications (FA) Cloud using Visual Analyzer(VA) and DV Desktop(DVD)

The following Article is written as of April 2018. Since, the Cloud is evolving rapidly, it may not apply in full, in future.

The aim of the Article is to demonstrate the possibilities of connecting to Oracle Fusion Applications (FA) Cloud (via OTBI) without any data extraction (ETL). Thus, only using declarative approaches offered by Visual Analzer (VA) of Oracle Business Intelligence Cloud Service (BICS) /  Oracle Analytics Cloud (OAC) and the downloadable version of VA, the Data Visualization (DV) Desktop (DVD). The following diagram re-iterates the scope of this Article:

image

The following Version and respective features are used as baseline for this Article (as available of April 2018)

 

Product Version
Oracle Fusion Applications Cloud / OTBI R13
BICS 18.1.6.0.0
OAC 18.1.3
DVD 12.2.4.2.0

Oracle Fusion Applications (FA) Cloud comes bundled with OTBI, which provides pre-built BI Subject Areas to access the data stored in FA:

image

However, OTBI does have some limitations when it comes to extensibility (no access to metadata model) and adding of additional data sources (limited to data connection of respective FA instance).

The Oracle Cloud offering for “stand-alone” analytics are BICS (and in the future OAC). Hence, there maybe the need to connect from BICS/OAC to Fusion Applications. This is possible by two general Options:

This Article only focuses on the latter three options (declaring the connection details without any data movement).

Connecting from DV Desktop to Oracle Fusion Applications (FA) via OTBI

DV Desktop represents a offline Version of Visual Analyzer and can be downloaded from OTN. Once  installed, it can be used to create a Connection to Oracle Fusion Applictions from the top right create Menu:

image

One of the Options from the “select connection type” list is “Oracle Application”:

image

This “Oracle Application” is the reference to Oracle Fusion Application’s Cloud via the OTBI URL !

image

It will not work with only the Oracle Fusion Application Cloud URL e.g. https://<IdentityDomain>.fs.em3.oraclecloud.com/homePage/faces/FuseWelcome , but only the respective OTBI URL e.g.: https://<IdentityDomain>.fa.em3.oraclecloud.com/analytics

If the URL is entered incorrectly, e.g. when using a trailing “/” it will not save the connection:

image

Once the correct OTBI URL, Username and Password are entered and saved, the connection is added to the list of available Data Connections:

image

As stated before: These “Oracle Applications” connections will only work with the respective OTBI URL, since the metadata (such as subject areas or Report from Catalog) is retrieved from this URL only. Once the connection is created, the Subject areas are available and the Analysis can be created in DVD:

image

Connecting from Visual Analyzer (VA) of BICS to Oracle Fusion Applications (FA) via OTBI

The equivalent of DVD for the web is Visual Analyzer (VA), which is part of OBIEE 12c as-well as BICS (and OAC). The same connection to Fusion Applications Cloud can also be created within VA of BICS. To do this, the VA Projects needs to be selected from the BICS (Classic) Home Page:

image

Within VA, click on “Add Connection” under the “Add new Data Source”:

image

And choose “Oracle Application” as connection type once again:

image

For the web version, the list of available data connections is also much smaller (only Oracle Applications and Oracle Database) compared to DVD. The connection details need to be entered in a similar way for VA e.g. as shown below:

image

After the Connection was saved successfully, the Connection can be used to create a Visual Analysis. However, VA of BICS will only provide the possibility to access saved Reports, but not the Subject area’s itself (compared to DVD)!

image

Connecting from Visual Analyzer (VA) of OAC to Oracle Fusion Applications (FA) via OTBI

The steps for connecting from VA of OAC to Oracle Fusion Applications (FA) are very similar to VA of BICS. Select VA from the OAC Homepage:

image

And create the connection in the same way. After successfully creation of the connection, it is listed as Connection in the Data list:

image

VA of OAC is not limited to create Analysis on predefined Reports (compared with VA of BICS). It is also possible to create an Analysis based on the subject area (similar to DVD):

image

The screenshot shows, the data preview from the Subject areas:

image

Important for Reports and Dashboards

(as of April 2018) The possibility of connecting from BICS/OAC to Oracle Fusion Applications (FA) Cloud is only available with VA. This is not (yet) available for Reports and Dashboards:

image

For Reports and Dashboards only a Database Connection can be added, but not a Oracle Applications connection.

Conclusion

  • Oracle Oracle Applications connection Type refers to the OTBI URL of Oracle FA
  • The possibility of connecting from BICS/OAC to Oracle FA is limited to VA
  • VA can be used to connect to OTBI, but cannot be used to connect to the underlying database
  • VA (via DVCS) can be used to connect to OBIEE .

Link

New Features of Oracle Analytics Cloud

The new features of the Oracle Analytics Cloud (OAC) are listed at the following site:

https://docs.oracle.com/en/cloud/paas/analytics-cloud/acswn/index.html#ACSWN-GUID-CFF90F44-BCEB-49EE-B40B-8D040F02D476

These include enhancements such as, public storage container for data visualizations, enhanced delivers functionality (similar to OBIEE), Oracle Identity Cloud Service integration, Migrate content from Oracle BI Enterprise Edition 11g/12c, Smart View.

Disabling the Query Cache for BICS

Similar to OBIEE the Query Cache can be disabled on Report Level within BICS.

However, the approach is slightly different to OBIEE, since BICS doesn’t provide the Pre or Post-fix box on the Advanced Tab of the Report Builder (Answers).

The below example shows the configuration and Behavior of disabling the Query Cache in a Step-by-step approach:

When creating a (simple) Query, a Cache entry is created for the returned result set:

image

For any subsequent Report, the Cache is analyzed if it can be used to fulfill the subsequent Report. E.g. when adding a Filter for Channel “Catalog”, the above Result set could be used:

image

Giving the following Result:

image

When analyzing the Query log, the Cache hit is also shown in the log itself:

image

In order to bypass the Query Cache, the Query Cache needs to be disabled for the respective Report by adding the following command (setting of internal server variable) into the Report:

SET VARIABLE DISABLE_CACHE_HIT=1;

To do this, the SQL displayed on the Advanced Tab of the Report Builder (Answers) needs to be copied to the clipboard and clicking the new Analysis button.

image

The previously copied SQL needs to be pasted in the popup Windows with the Disable Cache Hit command as prefix and the 0 needs to be removed from the pasted SQL:

image

The Presentation Server Cache can also by bypassed by checking the check-box at the top of the screen. When navigating to the Results tab of Answers, the Report is run again and the Query log can be checked again:

image

The Query log also shows that now the Query is not retrieved from the Cache, but retrieved from the Database.

A Technote also exist for further information on Oracle Support.

configuring Master Detail Analysis

An Analysis within BICS can utilize Master Detail linking. This allows to update a second Table or Graph based on a click within the Master Report. The below example shows this for the OOTB Sample Dashboard:

image

Once a user clicks into the Master Report (Step 1) the linked Analysis are also updated to reflect the context of the clicked figure (Step 2 +3). This can be configured by sending an event from the Measure column of the Master Report to the receiving Graph (Step 2) and receiving Table (Step 3) of the receiving Analysis.

On the Master Report, the Measure needs to send the event on a specific “channel”:

image

While the receiving Report Graph and Table need to be configured to listen on the respective “channel”:

image

This also requires to expose the linked columns as Graph or Tables Prompts:

image

Otherwise, the data will not be restricted on the linked Analysis!

BICS: Data Filters and BI Cloud Service Administrators Role

Similar to the on-Premise.rpd File, Data Filters can be configured using the Oracle BICS web modeller. These Data Filters are added to the Analysis to apply a particular data visibility for respective Application Roles. E.g. Users belonging to Application Role “Sales Managers West” should only be able to see data from the West Region, but not middle or East.

Within the web modeller of BICS this can be done using the Data Filters Tab on a Dimension or Fact logical table:

image

This is similar to OBIEE’s .rpd file, where the Data Filters have been defined also on the Application roles:

image

Once, the logical Dimension Table is used within an Analysis the defined Data Filter is appended for the defined Application Roles to the Analysis to restrict visibility as row level security.

However, if the User is also a member of the BI Cloud Service Administrators Role then the Data Filters are not applied. Users who are members of this Role are not restricted in terms of data visibility.

image

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.