ntroduction
Most of us must have read SET Theory and its concepts during our high-school days, while some of us have a good time with the topic some of us must have struggled. For those who understood the concept well might wonder how the same is applied and used while creating an OTBI Analysis, for those who were not able to understand the same then I hope they would understand the same this time.
We must have seen the use of SET operators like UNION, MINUS in SQL query but it is interesting to know that they can be used in an OTBI Analysis too.
This kind of Analysis is most commonly used when we want to combine the results of One or More Result Sets. The result sets could be from the Same Subject Area or from a different subject area but as long the number of columns and the column data-type are same.
Some classic use case examples of SET operations in OTBI Analysis are:
-
Tracking Individuals who have not created A Performance Document
The system tracks the Performance Document which are created in the system but there is no way to track the non-creation of the same. In this case if we create an Analysis for all individuals who are eligible for Creating a Performance Document (say ‘Individuals Eligible to Create a Performance Document’) and another analysis for those who have actually created a Performance Document (say ‘Individuals Who Created Performance Documents) then by excluding the Result Set (‘Individuals Who Created Performance Documents’) from the Result Set (‘Individuals Eligible to Create a Performance Document’ we would get the final results ( say ‘Individuals who have not created a Performance Document’)
-
Identifying Employees who are not enrolled in any Benefit Plans
Similarly, there could be a scenario where only certain employees (based on specific assignment terms/ contracts) would be eligible for Benefits. If we make an Analysis on Benefit Subject Area we can easily fetch all employees who are enrolled in one or another benefits, But if we need a list of all such individuals who are there not enrolled in any benefits plan again the SET operation comes to rescue. In this case if we take the ‘List of All Employees’ as first Result Set and ‘List of All Employees Enrolled in Benefit Plans’ as second Result Set and perform a MINUS operation on the second set from the first set then we will get ‘All Employees who are not enrolled in Any Benefit Plans’
-
Employees who have neither created a Performance Document nor are enrolled in Benefit Plan
Say suppose we have already created the above two analysis (a and b ) we can use the INTERSECT Set Operator to find all Employees who have neither created a Performance Document nor are enrolled in Benefit Plan.
A tabular representation is as follows:
Operation / Action |
Result |
Individuals Eligible to Create a Performance Document MINUS Individuals who created Performance Document |
Individuals who have not created Performance Document |
List of All Employees MINUS All Employees who are enrolled in Benefit Plan |
Employees who are not enrolled in any Benefit Plan |
Individuals who have not created Performance Document INTERSECT Employees who are not enrolled in any Benefit Plan |
Employees who have neither created a Performance Document nor are enrolled in any Benefit Plan |
Similarly we can have varied scenarios where we can use different SET Operators on various Result Sets and get desired results , but for now let-us try to create a simple OTBI analysis and understand how to use the same (SET Operators) in Oracle Fusion Application.
Worked Example
As a first step we would have to login to the application with an User who has major roles related with HCM Application ( For this example we will use ROBERT.JACKMAN)
Next click on the Navigator icon ( on top left hand side of page) and select the ‘Reports and Analytics’ link under ‘Tools’
Once you click on the page this would take you to ‘Reports and Analytics’ page. Click on the ‘Book’ icon which resembles the BI Catalog
This would take us to the next screen
Next select the ‘New’ option
And then choose the ‘Analysis’ option from the drop down list
As a part of this example we would use the ‘Workforce Management – Worker Assignment Real Time’ subject area
Once we click the subject area the following screen will appear
In this example we will fetch the following columns:
Columns in OTBI Analysis |
|
Folder Name |
Attribute Name |
Location |
Country |
Worker |
COUNT (DISTINCT “Worker”.”Person Number” |
The Analysis would look as:
We could notice that the analysis displays that Total Number of Employees who have a work Location Country as Algeria is 9.
Now lets say we want to use a MINUS SET Operator such that it should not display any data for Algeria.
For this we need to go back to the ‘Criteria’ tab and use the SET (icon) on the right hand side
Once we click on the icon this would open up a list of Subject Areas which we may choose
For this example we would use ‘Workforce Management – Worker Assignment Real Time’ and once done the criteria screen would appear:
Now we need to put a filter criteria on the second instance of the Subject Area such that it would only include records for ‘Algeria’
Filter Criteria |
|
Attribute Name |
Attribute Value |
Column |
Country |
Operator |
Is equal to / is in |
Value |
Algeria |
Once we click Ok , we could see that the filter criteria is added to the second subject area. Also, we would notice that we need to select the fields from the second subject area
Next we would add the ‘Location’ and the ‘Person Number’ attribute columns and then using the Edit Formula option change the ‘Person Number’ field to COUNT (DISTINCT ‘Worker’. ‘Person Number’)
Next we need to select the MINUS Set Operators but before makes sense to find out all the available set operators
List of Available SET Operators in OTBI Analysis |
|
Operation Name |
Symbolic Representation |
UNION |
|
UNION ALL |
|
INTERSECT |
|
MINUS |
|
For selecting the MINUS Operator please click on the select icon ( as shown) and choose Minus from the available options:
Once done we should click on the results and hopefully we would not see any record for Algeria
We can see that there is no data returned for ‘Algeria’ as a MINUS set Operation has been performed on the first analysis.
You may even try other operations like UNION, UNION ALL and INTERSECT and check the behavior of the same too.
We would now conclude this article with the hope that it was useful and gave you all an idea about how to use SET Operators while creating OTBI Analysis.
Thanks for your time and have a good day!!
Comments
When I look at your blog site in Firefox, it looks fine but when opening in Internet Explorer, it has some overlapping.
I just wanted to give you a quick heads up! Other then that, fantastic blog!
Feel free to surf to my website: MENANGBARENG: https://Ggasoftware.com/
all the time.
What might you recommend about your put up that you just made some days ago?
Any certain?
its quality contents.
I'm happy that you shared this helpful information with us.
Please keep us up to date like this. Thanks for sharing.
few of the pictures aren't loading correctly. I'm not sure why but I think its a linking issue.
I've tried it in two different browsers and both show the same results.
website with us so I came to take a look. I'm definitely enjoying the information. I'm book-marking and
will be tweeting this to my followers! Excellent blog and wonderful
design.
a lot about this, like you wrote the book in it or something.
I think that you could do with a few pics to drive the message home a bit, but other than that, this is
great blog. An excellent read. I'll certainly
be back.
I would like to see more posts like this .
in support of me.
a impressive source of facts.
a lot approximately this, like you wrote the book in it or something.
I feel that you simply can do with some % to force the
message house a little bit, however other than that, that is magnificent blog.
A great read. I'll definitely be back.
and yours is the best I have came upon so far.
However, what in regards to the bottom line? Are you sure about the source?
and yours is the best I have came upon so far.
However, what in regards to the bottom line? Are you sure about the source?
Also, thank you for permitting me to comment!
meal kit delivery business
donate for ukraine: https://www.aid4ue.org/about/
learn actually enjoyed chronicle your web log posts.
capital letter to exact that I learn really enjoyed business relationship your
web log posts.
the make for you write.
.meshwork from PHP. I give always disliked the estimation because of the costs.
compared to books, as I plant this tack of written material at
this net Page.
one's skin your affiliate link up to your Host?
pay a visit this webpage on regular basis to take updated from latest news update.
aid ukraine: https://www.aid4ue.org/about/
know a few of the images aren't loading properly.
I'm not sure why but I think its a linking issue.
I've tried it in two different web browsers
and both show the same outcome. donate for ukraine: https://www.aid4ue.org/about/
i was just wondering if you get a lot of spam feedback?
If so how do you protect against it, any plugin or
anything you can recommend? I get so much lately it's driving me crazy so any assistance is very much appreciated.
wp plugins: http://pump-life.com/__media__/js/netsoltrademark.php?d=www.blogexpamder.com
wordpress themes: http://centerpointenergyarkansas.biz/__media__/js/netsoltrademark.php?d=www.blogexpamder.com
donate to this outstanding blog! I guess for now i'll settle for bookmarking and adding your
RSS feed to my Google account. I look forward to fresh updates and will talk about this website with my Facebook group.
Talk soon!
wordpress themes: http://wwwsextantio.it/__media__/js/netsoltrademark.php?d=www.blogexpamder.com
wordpress
themes: http://coatescaribbean.biz/__media__/js/netsoltrademark.php?d=www.blogexpamder.com
I am truly eager of reading this web site's post
to be updated on a regular basis. It carries good
information.
wordpress themes: http://dpstraining.com/__media__/js/netsoltrademark.php?d=www.blogexpamder.com
wordpress plugins: http://yodhaba.com/__media__/js/netsoltrademark.php?d=www.blogexpamder.com
Feel ffree tto surf to my web blog - mgm online casino - Lacy: https://www.google.al/url?q=https://zemaox.webbuzzfeed.com/16481753/betmgm-ohio-bonus-code-bookies-2022-assessment-launch,
old will travel to US and applying for tourist visa. Shes with my mother in law not with
her parents. What are the requirements for her and step by step procedure
my web blog :: EB2 NIW business plan company (Niki: https://local.enquira.co.uk/suggest-listing.php?id=465&ref=https://www.immigrationbusiness-plans.com/eb2-niw-business-plan-company-alaska/)
RSS feed for comments to this post