Referencing Oracle BI System Session variables

Oracle BI (OBIEE) provides multiple categories of Variables, such as:

  • Session
  • Repository
  • Presentation
  • Global

These different categories of variables need to be referenced using a specific syntax. However, the required syntax depends on the category of variable and the context. Below are examples for referencing Session variables in Analysis/Reports and within the narrative View. This is often useful for debugging scenarios or for performing unit test when creating complex formulas.

To reference a Session Variable within a Report use: VALUEOF(NQ_SESSION.GROUP) like below:

image

To reference a Session Variable within a Narrative View use: @{biServer.variables[‘NQ_SESSION.GROUP’]} like below:

image

The complete code to reference the different Session variables (User, Group, Roles) is as follows:


[b]Derzeitiger User:[/b] @{biServer.variables['NQ_SESSION.USER']} [br/][br/]
[b]Derzeitiger Anzeigename:[/b] @{biServer.variables['NQ_SESSION.DISPLAYNAME']} [br/][br/]
[b]Derzeitiger Gruppen:[/b] @{biServer.variables['NQ_SESSION.GROUP']} [br/][br/]
[b]Derzeitiger Rollen:[/b] @{biServer.variables['NQ_SESSION.ROLES']} [br/]

For a exessive list of available Session variables check:

For general Overview check:

For short reference card check:

For Official Documentation check (11g):

Advertisements

Protecting Filters

The Protecting Filters Option in Oracle BI or Oracle Analytics Cloud is usually not used or needed. However, it may be required when using multiple Filters on the same Column (Subject area Field) and using Action Links as Drill-down navigation or in conjunction with Dashboard Prompts.

image

The following is an example scenario without protecting the Filters: On the Detailed Report, the following Operators are defined:

image

When making the Drill-down from the Top Report using the Action-link Drill-down navigation:

image

The Filter Operators are overwritten by the internal Background process for navigating to the Detail Report (compare Operator which is “is equal to / is in” which should have been “is less than or equal to” from Report definition):

image

After selecting “Protect Filter” the Operator is protected from the Background navigation process and the intended Operator is used on the Detailed Report:

image

This feature is also described in the following article:

OAC: specifying the Connection string within rpd model

The following is a step-by-step guide to determine the Connection string for Oracle Analytics Cloud and Oracle Database Cloud.

When logging into your Oracle Cloud Service using your identity Domain, a similar page is being displayed:

image

Click on the Action Menu of the Database Cloud Service and select “Open Service Console”.

This Page shows details about your Cloud Services such as OCPUs, Storage or Memory:

image

click on the Database Service Name to get to the Details Page:

image

This reveals the Public IP of the Oracle Cloud Database and the required Service Name.

Within the Oracle BI Administration Tool, the connection string needs to be created in the Data Source Name Field:

image

Using the following format:

image

</code>

(
DESCRIPTION=
(ADDRESS=
(PROTOCOL=tcp)
(HOST=xxx)
(PORT=1521)

)
(CONNECT_DATA=(SERVICE_NAME=PDB1.xxx.oraclecloud.xxx))

)

<code>

After modelling the data and saving the .rpd File, the Model can be uploaded to the OAC Cloud:

image

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:

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 no LTS is available, it is not possible to use the Content Tab of the LTS to specify a Where Clause to be applied to limit the Rows.

· 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.

· Joins between Fact and Dimension Tables may only be based on a single criteria. Joins can’t be specified using a second criteria using the AND logic. And Joins can only be Inner Joins.

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.