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:

Essbase in OAC and Oracle EPM (SaaS) Cloud

The first offerings from Oracle for EPM within the Cloud have been the SaaS Subscriptions for:

  • FCCS (Financial Consolidation and Close Cloud)
  • TRCS (Tax Reporting Cloud)
  • ARCS (Account Reconciliation Cloud)
  • EPBCS (Enterprise Planning and Budgeting Cloud Service)

Since March 2017, Oracle also Offers a PaaS Cloud Service with Oracle Analytics Cloud (OAC) which can complement the SaaS Cloud Offering, since it provides a Essbase in the Cloud Service (EssCS).

image

The main purpose of OAC is to provide BI as-well as Essbase (EssCS), within a single Cloud Offering (since Essbase was not available with BICS) for Reporting purposes. It is also supported to migrate on-Premise Essbase cubes into EssCS.

Within EPBCS, Essbase (technology) is also used for Planning purposes. To provide these Planning capabilities, EPBCS not only consists of Essbase cubes, but also relational tables and UI Templates for the Business User to enter the planning data. Since, EPBCS is a SaaS service, the underlying Essbase Cube is not accessible directly and can only be accessed with certain exposed interfaces, whereas the Essbase Cubes from EssCS of OAC is completely accessible, since it is a PaaS service on dedicated instance for a particular customer.
Former Hyperion Planning Applications should be migrated into EPBCS, since this is the equivalent Cloud offering for Planning.

For Reporting purposes of Planning cubes, these can be migrated from EPBCS to EssCS of OAC using EPM automate for extraction and importing via the CLI (Command Line interface). The EPM (SaaS) Cloud also offers a Reporting Cloud Service with EPRCS with a focus on narrative Reporting and XBLR Reports for external stakeholders. EPRCS can source data from EPBCS, FCCS, TRCS, ARCS as well as EssCS.

differences between OBIEE Admin Tool (rpd) and web modeler

The Oracle BI Cloud Service provides the possibility to upload metadata models (via lift & shift) created with the OBIEE Admin Tool (rpd file), but also comes with a new web modeler to create metadata models directly within the web UI. These web model files are not compatible with the rpd file and are stored internally as json files. The BICS instance is also limited to an either or approach. Once, a rpd is uploaded as a model e.g. containing the data source connections, the web modeler can not be used anymore.

image

Thus, the following article lists the differences between the metadata model created within the rpd and the web modeler:

· Authorization Blocks for populating variables are not available with web modeler

· The web modeler only supports a single Physical Table /View for a logical table (Fact or Dimension) since no LTS (Logical Table Sources) are available.

· Since there is no concept for LTS, the Aggregate navigation configuration is not possible within web modeler to make use of aggregated tables which contain fewer records for increasing performance

· after Renaming of Presentation Tables/Columns Aliases are set within rpd, but not in web modeler, hence a Report referencing the column will be invalid

· No renaming wizard in web modeler to quickly change the physical table names into meaningful business names.

· Star schema (ER Diagrams) are not shown when using the web modeler and only within the OBIEE Admin Tool

· Cross Subject area Analysis does only seem to work with rpd lift and shift. When using web modeler the option of “adding other Subject areas” is always greyed out. This influences the modelling of the Models, since it is possible to create a united Model having all required tables. However, this “united” model maybe a duplicate and must be created from scratch again.

Obviously, the above list is non exhaustive and should only provide a first level information, since any BICS instance is limited to only one approach.

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.