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.