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.

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

Issue when using Report prompts within OTBI Answers and OTBI Fusion

A strange Issue exists with OTBI in terms of Report prompts. If a User adds a prompt directly within the Report, the prompts appear correctly within BI Answers. However, if the same Report is opened from within Oracle Cloud, the prompts are not shown to the User.

Prompts added to Report:

image

Prompts appearing in BI Answers of OTBI when running (opening) the Report:

image

However, when opening the same Report from Fusion (OTBI embedded) the Report executes without showing the prompts, even though the prompt has been specified as “required”:

image

When clicking on the “Edit” button of the Fusion Catalogue Browser, the following warning message is being displayed:

image

The standard provides prompted Analysis usually of Type “Dashboard”, hence this Issue is not applying for standard content. E.g. the Analysis on Department details also provides filters as Dashboard prompts:

image

Translation of multi-lingual attributes within Oracle Fusion Cloud and OTBI

Oracle Fusion Cloud allows to enable text fields as multi lingual enabled attributes. As part of the batch upload process e.g. from Fusion Product Hub, these enabled attributes can be specified within different languages:

image 

Within Oracle Fusion Cloud, these attributes are displayed in  the respective Language of the logged in User. These is also true for OTBI. E.g. for US the respective item description is displayed in US:

image

If a user logges into OTBI with RUS language, the item description is automatically displayed with RUS description:

image

How to create a direct database query with OTBI?

Within Oracle Fusion OTBI go to New > Analysis and click on Create Direct Database Query:

image

Within the Editor the connection Pool information needs to be entered:

image

The following connection pools can be entered:

  • HCM – “HCM_OLTP”.”Connection Pool”
  • CRM – “CRM_OLTP”.”Connection Pool”
  • FIN and PROC – “FSCM_OLTP”.”Connection Pool”

After that, a query may be entered for direct database execution on the Oracle Fusion Cloud (bundled) Database (identified by the Connection pool supplied before):

image 

For a listing of all Physical Tables please refer to (only available for HCM): https://cloudcustomerconnect.oracle.com/posts/ac54c31fcf 

This contains a Excel mapping with the Physical OTBI Tables:

image

 

For a full support note please refer to: How To Create A Report with Direct Database Query in OTBI/OBIEE Analysis, Who Can Access Direct Database Query (Doc ID 2141412.1)

For a similar approach using BI Publisher please check:  Creating a BI Publisher Report using SQL Query in Oracle Fusion HCM Cloud

To check the general TechNote for OTBI and Fusion HCM, please refer to: Oracle Fusion HCM: Resources for Oracle Transactional Business Intelligence (Doc ID 2023155.1)

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)

Link

Link to information about business questions and answers for Oracle Transactional Business Intelligence (OTBI) Embedded Analytics Cloud, HCM Subject areas:  http://www.oracle.com/technetwork/documentation/otbi-hcm-2225528.html

And for OTBI-E subject areas. The advantage of this is, that it is indeed very similar to Oracle BI Applications (OBIA) and can easily be applied for the same:

http://www.oracle.com/technetwork/documentation/otbi-e-2247768.htm

provide access to OTBI and BI Administration Role

Fusion Application Cloud users can access the Reporting and Analytics (OTBI) module via the Fusion Sitemap listed under “Tools”:

image

This opens the embedded Analytics Catalog which contains all items for each domain such as Human Capital Management:

image

To grant access to Reporting and Analytics, the Cloud Admin User or another User having received the “IT Security Manager” Role can provide access to OTBI:

image

In this example a new Employee called “OTBIUser” should receive access to OTBI. The User should receive the following Roles by the Cloud Administrator:

image

To grant the User “OTBIUser” access requires to follow the TechNote (Oracle Fusion BI: How to Add the BI Administrator Role to a user in Release 12. (Doc ID 2238277.1) from Fusion Application Cloud R12 and onwards as the standard BI Roles (such as BI Author or BI Consumer) can’t be assigned to Users directly any more. Instead, it is required to create a custom Role based on the standard (OOTB) Role and assign those custom role to the User to act e.g. as BI Administrator.

image

After the User has received the role, an Administrator must run the “Retrieve Latest LDAP Changes” ESS Job to reflect these changes. This process may actually take up to 8 hrs. for the changes to take effect.