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.

Advertisements

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
  • DVD can be used to connect to OTBI, but cannot be used to connect to BICS/OAC directly, only to the underlying database

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.

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

BI Publisher now available with OAC

Until today, Oracle BI Publisher was only available as a (SaaS) bundle with Oracle Fusion Applications Cloud and OTBI, see also a previous Article. However, with Oracle Analytics Cloud, Oracle BI Publisher is now also available as a “first degree” Platform Cloud Service (PaaS) giving more flexibility especially in terms of Data Sources. This allows to create highly formatted Reports/Documents such as Invoices, Purchase Orders, Dunning Letters without the context of Oracle Fusion Applications Cloud.

See the full announcement here.

BI Publisher and BICS?

Within the on-Premise World BI Publisher (BIP) was bundled with Oracle BI EE (OBIEE). However, the respective Oracle BI Cloud Service (BICS) does not come with a bundled Version of BI Publisher as Cloud Service.

A user may only choose one of the following options to create new items:

image 

However, Oracle Transactional Business Intelligence (OTBI) does come with a bundled Version of BI Publisher. Once the User logs into OTBI, the following options are available to create new Analytical items:

image

For further reference check Oracle Support note: Is BI Publisher (BIP) Available as part of BICS (Doc ID 2104841.1)

BICS development life-cycle

Within an on-premise scenario, the OBIEE development life-cycle was quite simple:

  1. connect to a data warehouse and import the warehouse tables (metadata import to physical layer)
  2. build OBIEE metadata model (RPD)
  3. upload the RPD into Weblogic using the Deployment screen

Within a cloud scenario the BICS development life-cycle is quite the same, but requires some additional steps and some decisions on the development model: either using web-based BICS developer or upload of full RPD file developed using OBIEE Admin Tool.

While it’s possible to develop the complete metadata model within the cloud using the BICS web-based Modeller, like below:

image

the capabilities of this web-based Modeller are more limited. Due to this there is no option to edit or change the uploaded RPD file within the web-based BICS modeller afterwards. The process to upload a locally developed (OBIEE) metadata model (.rpd file) to the BICS Cloud is called “lift&shift”.

The required setup for this OBIEE to BICS development life-cycle using lift & shift is described in below diagram:

image

For the BICS development life-cycle setup the data of the Fusion Cloud e.g. HCM or ERP is assumed to be already present within a Oracle Database Cloud (DBaaS/DBCS). For this setup all components (formally probably referred to as “server” components) are provisioned on the Oracle Public Cloud behind a firewall and only the IT Development Tools (IDE) are installed on a developer machine like a desktop or laptop.

The following integrations need to be setup:

Integration point Integration point Description documentation/links
BICS DBaaS Connect the BI Cloud (BICS) to the Database Cloud (DBaaS) to analyse the stored data e.g. extracted Fusion HCM data How Does Oracle BI Cloud Service Integrate with Oracle Database Cloud Service?

Managing Database Connections

(local) SQL Developer DBaaS Connect the local SQL Developer e.g. installed on a Computer or Laptop to the Database Cloud (DBaaS) Accessing a Database Cloud Service instance using Oracle SQL Developer

Connecting to a Database Cloud Service (DBaaS) Instance Through an SSH Tunnel

(local) BI Admin Tool  (RPD file) BICS upload the local developed .rpd file (using the OBIEE Admin Tool) into the BI Cloud (BICS) Uploading Data Models from Oracle BI Enterprise Edition

“Lift and Shift” On-Premise RPD to BI Cloud Service (BICS)

How to Upload OBIEE RPD to Oracle BICS

BICS Lift and Shift of BIAPPS Content

To create Backup as part of the development life-cycle refer to the following article:

BIAPPS on PAAS – Backup and Restore – Introduction

To connect between DBaaS and the Fusion Application Cloud (SaaS):

BIAPPS on PAAS – Source Connectivity

The above scenario uses the Oracle Database Cloud for storing the data. If the Data is not stored in the Oracle Database Cloud, but the Oracle Schema Cloud Service which is bundled for-free with BICS then the data must be loaded into BICS using the BICS Data Sync tool:

image

Within the all above scenario the (“server”) components are provisioned on the Oracle Public Cloud. For a scenario where some components like the Database still reside on-premise the following tool needs to be installed on the server hosting the Oracle Database:

image

as described in this article from Oracle.