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:
-
Program Name
-
Plan Type
-
Plan Name
-
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.
SQL Query |
select program.name ProgramName, plantype.name PlanType, planname.name PlanName, planoption.name optionname FROM ben_pgm_f program, ben_plip_f planprocessing, ben_pl_typ_f plantype, ben_pl_f planname, ben_opt_f planoption, ben_oipl_f processingoption WHERE program.pgm_id = planprocessing.pgm_id AND planprocessing.pl_id = planname.pl_id AND plantype.pl_typ_id = planname.pl_typ_id AND planname.pl_id = processingoption.pl_id AND planoption.opt_id = processingoption.opt_id AND TRUNC(SYSDATE) BETWEEN program.effective_start_date and program.effective_end_date AND TRUNC(SYSDATE) BETWEEN planprocessing.effective_start_date and planprocessing.effective_end_date AND TRUNC(SYSDATE) BETWEEN plantype.effective_start_date and plantype.effective_end_date AND TRUNC(SYSDATE) BETWEEN planname.effective_start_date and planname.effective_end_date AND TRUNC(SYSDATE) BETWEEN planoption.effective_start_date and planoption.effective_end_date AND TRUNC(SYSDATE) BETWEEN processingoption.effective_start_date and processingoption.effective_end_date UNION select program.name ProgramName, plantype.name PlanType, planname.name PlanName, planoption.name optionname FROM ben_pgm_f program, ben_plip_f planprocessing, ben_pl_typ_f plantype, ben_pl_f planname WHERE program.pgm_id = planprocessing.pgm_id AND planprocessing.pl_id = planname.pl_id AND plantype.pl_typ_id = planname.pl_typ_id AND planname.pl_id NOT IN (select boiplf.pl_id from ben_oipl_f boiplf where TRUNC(SYSDATE) BETWEEN boiplf.effective_start_date and boiplf.effective_end_date) AND planoption.opt_id = processingoption.opt_id AND TRUNC(SYSDATE) BETWEEN program.effective_start_date and program.effective_end_date AND TRUNC(SYSDATE) BETWEEN planprocessing.effective_start_date and planprocessing.effective_end_date AND TRUNC(SYSDATE) BETWEEN plantype.effective_start_date and plantype.effective_end_date AND TRUNC(SYSDATE) BETWEEN planname.effective_start_date and planname.effective_end_date AND TRUNC(SYSDATE) BETWEEN planoption.effective_start_date and planoption.effective_end_date AND TRUNC(SYSDATE) BETWEEN processingoption.effective_start_date and processingoption.effective_end_date |
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”.
Issued SQL |
SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1';SELECT 0 s_0, "Benefits - Setup Real Time"."- Plan Basic Details"."Plan Name" s_1, "Benefits - Setup Real Time"."- Program Basic Details"."Program Name" s_2, "Benefits - Setup Real Time"."Plan Options"."Option Name" s_3, "Benefits - Setup Real Time"."Plan Type"."Plan Type Name" s_4 FROM "Benefits - Setup Real Time" ORDER BY 1, 3 ASC NULLS LAST, 5 ASC NULLS LAST, 2 ASC NULLS LAST, 4 ASC NULLS LAST FETCH FIRST 75001 ROWS ONLY |
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!
Comments
RSS feed for comments to this post