Introduction

One of the most common (and probably the most difficult too) question asked to a technical consultant is which one among OTBI or BIP Report should one choose while building a Report in Oracle Cloud Application.

Honestly, speaking there is no straight answer to this. Many a times one would prefer building a BIP Report while at time building an OTBI Report would suffice.

Each one of the above tools has its own set of pros and cons but one significant feature (one may call a disadvantage of using an OTBI Report.. if they wish to say so) is that an OTBI Analysis always performs a equijoin among the various attribute fields. What it means that if we are joining multiple folders and at any point of time we join a data attribute which doesn’t holds data then the entire data row is skipped.

We can illustrate this very well with the help of an example.

Worked Out Example

In this example we will try to create a very simple report ( we will name the Report as “Benefit Setup Report”) which would contain the following fields:

1. Program Name

2. Plan Type

3. Plan Name

4. Option Name

Now Program Name could be considered as the topmost data attribute which will have multiple Plan Types associated with it. Each Plan Type can have multiple plans and each plan may have multiple plan options.

Now, there could be some plans which may not have any plan options configured. In such scenario if we build a OTBI Report then consisting of all above mentioned four fields we would only get records which have data in all the fields. All the rows where option name is not configured will not be shown in OTBI Report. However, in this specific scenario we may make use of BI Report and fetch data for even those records which are not having any plan options configured.

Creating a BI Report

As a first step, we would create a simple SQL query based data set to fetch program name, plan type, plan name and option name.

The catalog folders used can be downloaded from below link:

<Data Model Catalog Folder>

<BI Report Catalog Folder>

The output of the BI Report would look as shown below:

Note: That for some records the Option Name field is Blank.

Creating OTBI Analysis

Once we are done creating the BI Report we can create an OTBI report using “Benefit – Setup Real Time”.

The catalog folders can be downloaded from below link:

<OTBI Catalog Folder>

Note: We could see that all the four fields namely Program Name, Plan Type Name, Plan Name and Option Name are populated. What this also implies that any record in which any of the above fields was not having data (NULL) the record is skipped.

Comparing BI Report Data with OTBI Report Data

In this step we would keep a snapshot of both the records side by side and understand the differences:

Inference / Summary

We can clearly see from the above screenshot that while the BIP Report fetched records where there was no Option Name ( for Plan Names “AU Lifestyle Allowance” and “AU SGC Superannuation”) but the records didn’t even appeared in the corresponding OTBI Report.

So we can infer from the above example that while the BIP Report allows us to use the Outer Join feature to display records (even in case some of the data fields are not holding data) an OTBI Analysis completely skips such record (works on equijoin concept).

And with this I have come to the end of this article.

I hope this was a interesting read and you folks had a good time going through this.

Thanks for all your time , have a great day ahead!

Ashish Harbhajanka

Add comment