Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

CONTACT US

Fusion PayRoll
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new

Search Courses

Introduction

Many a times we need to create a multi data set sql query based data model and there may be scenario where a specific data field used in one data set might be required (either in the select clause or in the where clause) in another data set. We do not need to repeat the entire SQL again in the second data set, rather this can be easily accomplished using a simple trick.

We would demonstrate the same in this example.

Worked Out Example

For this example, we will have two data sets. Let’s name then PersonBasicDetails_ds and PersonNameDetails_ds respectively. We would assume that PersonBasicDetails_ds would act as a Master Data Set ( there is nothing like a Master or Child Data Set here, but we refer this data set as a Master Data Set because this is the place where we reference the re-usable data columns for the first time).

PersonBasicDetails_ds (SQL Query)

select papf.person_id,

     papf.person_number

from   per_all_people_f papf

where trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

PersonNameDetails_ds (SQL Query)

select ppnf.display_name,

     :person_id nds_person_id,

     :person_number nds_person_number

from   per_person_names_f ppnf

where ppnf.name_type = 'GLOBAL'

and sysdate between ppnf.effective_start_date and ppnf.effective_end_date

and ppnf.person_id = :person_id

We would need to create element links too.

G_1.PERSON_ID = G_2.NDS_PERSON_ID

G_1.PERSON_NUMBER = G_2.NDS_PERSON_NUMBER

Where G_1 is the Group Name for PersonBasicDetails_ds and G_2 is the Group Name for PersonNameDetails_ds

One important point to note is that while creating the Child Data Set (PersonNameDetails_ds) we would be referring to the re-usable data columns as a bind variable (like :person_id and :person_number) , but this is just for reference purpose and one should not tick the checkbox.

Demonstration

We would login to the application. Navigate to BI Catalog (Navigator -> Reports And Analytics) and create a new data model.

We would create two data sets (as per details above) and even create links. Once all the above setups are done the data model would appear as below:

The Groups when expanded would show the data field details.

And now as a last step when we click on “View Data” (highlighted in screenshot below) , we will see below data:

Inference / Summary

So this is how we can make use of a data field of one data set into another data set within the same data model. One may extend this idea further too. Typical examples being a scenario where we have one data set which is having columns (created using sub-queries) and then each of these columns can be referenced in the subsequent data sets using an “:” suffixed by the actual data column name.

Do give a try and share your observations.


Ashish Harbhajanka

Add comment


Security code
Refresh

About the Author

Ashish Harbhajanka

 

Oracle Fusion HCM Techno Functional Consultant with overall 10 years of Experience in software industry with 5 years in EBS HRMS and rest 5 in Fusion HCM.

My areas of intesrest in Fusion HCM include :

a) Inbound Outbound Integration using FBL/HDL or BIP/HCM Extracts.

b) Fast Formula

c) BIP Reports

d) OTBI Reports

e) RESTFUL API / Web Service Call

f) Functional Setup

g) End to End Testing

h) Regression Testing

i) Preparing COnfiguration Workbooks

j) Creating Speed Solutions

k) Preparing User Guides

l) UPK

........

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Mar 2024  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
      1  2  3
  4  5  6  7  8  910
11121314151617
18192021222324
25262728293031

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner