Introduction
OTBI (Oracle Transactional Business Intelligence) is a easy to use tool designed to create quick reports. But while it is easy to use sometimes it might not work as expected as the subject areas and the embedded folders within it work on the simple concept of equijoin. What this means is that if you add any column to your analysis which is not directly related with your core Subject Area attributes chances are you might not get data for those columns even if the application has data for those.
An example being, if you are trying to find list of all employees who belong to different departments and then try to extend the analysis by adding employee specific details like employee’s manager’s full name , employee’s manager’s person number, employee’s job code etc then there are chances that you might not get data for such fields and it does appears fine from an architectural point of view (this is how the OTBI analysis works) but sadly for consultants this cannot be a response they can give to their customers. We have to come up with an alternative which at times could be BIP Report (SQL Based datasource) but it would still be nice to achieve such reports in OTBI analysis. Well tough situation but hang on we have something called “Logical SQL” option.
Let-us see how we can create an analysis based on Logical SQL then.
Worked Example
We have a specific business requirement here where the customer is interested to have a report based on Succession Plan attributes. Specific data columns of interest being:
Column Name |
Meaning |
Plan Name |
Succession Plan Name |
Plan Type |
Succession Plan Type |
Incumbent Count |
Number of Incumbents on the Succession Plan |
Risk of Loss |
Risk of Losing the Incumbents |
Impact of Loss |
Impact of Losing the Incumbents |
Incumbent Full Name |
Full Name of the Incumbent |
Business Unit Name |
Business Unit to which the Incumbent belongs |
Job Code |
Job Code of the Incumbent |
Manager Name |
Manager Name of the Incumbent |
The meaning of the different color in the above table are described below:
We can verify what we are saying by creating a simple analysis, which will hold all the above columns.
Analysis XML |
<saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sawx="com.siebel.analytics.web/expression/v1.1" xmlVersion="201201160"> <saw:criteria xsi:type="saw:simpleCriteria" subjectArea=""Workforce Succession Management - Incumbent Plans Real Time"" withinHierarchy="true"> <saw:columns> <saw:column xsi:type="saw:regularColumn" columnID="cc00f0a4902a53166"> <saw:columnFormula> <sawx:expr xsi:type="sawx:sqlExpression">"Succession Plan Details"."Incumbent Full Name"</sawx:expr></saw:columnFormula></saw:column> <saw:column xsi:type="saw:regularColumn" columnID="c2b608274e904cd19"> <saw:columnFormula> <sawx:expr xsi:type="sawx:sqlExpression">"Succession Plan Details"."Plan Type"</sawx:expr></saw:columnFormula></saw:column> <saw:column xsi:type="saw:regularColumn" columnID="c289e359d29b21624"> <saw:columnFormula> <sawx:expr xsi:type="sawx:sqlExpression">"Succession Plan Details"."Plan Name"</sawx:expr></saw:columnFormula></saw:column> <saw:column xsi:type="saw:regularColumn" columnID="c47964982f0ff1fce"> <saw:columnFormula> <sawx:expr xsi:type="sawx:sqlExpression">"Incumbent Plan Measures"."Count of Incumbents"</sawx:expr></saw:columnFormula></saw:column> <saw:column xsi:type="saw:regularColumn" columnID="c6dc7fb9cd5c6f489"> <saw:columnFormula> <sawx:expr xsi:type="sawx:sqlExpression">"Worker"."Manager Name"</sawx:expr></saw:columnFormula></saw:column> <saw:column xsi:type="saw:regularColumn" columnID="c539da6f409f5e697"> <saw:columnFormula> <sawx:expr xsi:type="sawx:sqlExpression">"Business Unit"."Business Unit Name"</sawx:expr></saw:columnFormula></saw:column> <saw:column xsi:type="saw:regularColumn" columnID="c3bf27dd3ec7e59ce"> <saw:columnFormula> <sawx:expr xsi:type="sawx:sqlExpression">"Job"."Job Code"</sawx:expr></saw:columnFormula></saw:column> <saw:column xsi:type="saw:regularColumn" columnID="cd97c0bb350db43ab"> <saw:columnFormula> <sawx:expr xsi:type="sawx:sqlExpression">"Risk of Loss"."Risk of Loss"</sawx:expr></saw:columnFormula></saw:column> <saw:column xsi:type="saw:regularColumn" columnID="cb1510a45cfab0a16"> <saw:columnFormula> <sawx:expr xsi:type="sawx:sqlExpression">"Risk of Loss"."Impact of Loss"</sawx:expr></saw:columnFormula></saw:column></saw:columns></saw:criteria> <saw:views currentView="0"> <saw:view xsi:type="saw:compoundView" name="compoundView!1"> <saw:cvTable> <saw:cvRow> <saw:cvCell viewName="titleView!1"/></saw:cvRow> <saw:cvRow> <saw:cvCell viewName="tableView!1"/></saw:cvRow></saw:cvTable></saw:view> <saw:view xsi:type="saw:titleView" name="titleView!1"/> <saw:view xsi:type="saw:tableView" name="tableView!1" scrollingEnabled="false"> <saw:edges> <saw:edge axis="page" showColumnHeader="true"/> <saw:edge axis="section"/> <saw:edge axis="row" showColumnHeader="true"> <saw:edgeLayers> <saw:edgeLayer type="column" columnID="cc00f0a4902a53166"/> <saw:edgeLayer type="column" columnID="c2b608274e904cd19"/> <saw:edgeLayer type="column" columnID="c289e359d29b21624"/> <saw:edgeLayer type="column" columnID="c47964982f0ff1fce"/> <saw:edgeLayer type="column" columnID="c6dc7fb9cd5c6f489"/> <saw:edgeLayer type="column" columnID="c539da6f409f5e697"/> <saw:edgeLayer type="column" columnID="c3bf27dd3ec7e59ce"/> <saw:edgeLayer type="column" columnID="cb1510a45cfab0a16"/> <saw:edgeLayer type="column" columnID="cd97c0bb350db43ab"/></saw:edgeLayers></saw:edge> <saw:edge axis="column" showColumnHeader="rollover"/></saw:edges></saw:view></saw:views> <saw:prompts scope="report" subjectArea=""Workforce Succession Management - Incumbent Plans Real Time""/></saw:report> |
And the output would appear as below:
We could clearly see that Manager Name, Business Unit Name and Code fields are Blank.
Let-us try to now use the below logical sql and check results:
Logical SQL |
select PlanName saw_0,PlanType saw_1,IncumbentCount saw_2,IncumbentFullName saw_3,ImpactOfLoss saw_4,RiskOfLoss saw_5,BuName saw_6,JobCode saw_7,ManagerName saw_8 from ( select A.s_1 ImpactOfLoss,A.s_2 RiskOfLoss,A.s_3 IncumbentFullName,A.s_4 PlanName,A.s_5 PlanType,A.s_6 IncumbentCount,B.t_1 BuName,B.t_2 JobCode,B.t_4 ManagerName from ( SELECT "Workforce Succession Management - Incumbent Plans Real Time"."Risk of Loss"."Impact of Loss" s_1, "Workforce Succession Management - Incumbent Plans Real Time"."Risk of Loss"."Risk of Loss" s_2, "Workforce Succession Management - Incumbent Plans Real Time"."Succession Plan Details"."Incumbent Full Name" s_3, "Workforce Succession Management - Incumbent Plans Real Time"."Succession Plan Details"."Plan Name" s_4, "Workforce Succession Management - Incumbent Plans Real Time"."Succession Plan Details"."Plan Type" s_5, "Workforce Succession Management - Incumbent Plans Real Time"."Incumbent Plan Measures"."Count of Incumbents" s_6 FROM "Workforce Succession Management - Incumbent Plans Real Time" ) A, (SELECT "Workforce Management - Worker Assignment Real Time"."Business Unit"."Business Unit Name" t_1, "Workforce Management - Worker Assignment Real Time"."Job"."Job Code" t_2, "Workforce Management - Worker Assignment Real Time"."Worker"."Employee Name" t_3, "Workforce Management - Worker Assignment Real Time"."Worker"."Manager Name" t_4, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Business Unit"."Business Unit Name") t_5 FROM "Workforce Management - Worker Assignment Real Time" ) B where A.s_3 = B.t_3 ) final order saw_0,saw_1,saw_2,saw_3,saw_4,saw_5,saw_6,saw_7,saw_8 |
And when we check the results:
We can see the columns which were not fetching data before are doing so now.
So this is how we can make use of Logical SQL to fetch data into OTBI Analysis.
Hope this was an interesting read and you all enjoyed reading it.
Thanks for your time, have a nice day!
Comments
Regardless, I'm definitely delighted I stumbⅼed upon it and
I'll be book-mɑrking it and checking back often!
My blog post: Kassіe: http://Baguep.Free.fr/index.php?article24/encore-un-compresseur
firestick they don't want to tell me how to get it to work it's called digicel play multiscreen
and I am impressed! Very helpful info specifically the last part :
) I care ffor such information a lot. I was looking for this certain information for a long time.
Thank you and best of luck.
Also visit my web sit 토토사이트: https://Cglescorts.com/user/profile/676721
It’s commonly equitable pentad multiplication longer.
It’s okeh to summarize
you are offer and you allow for it aside completely release.
I like sightedness blog that comprehend the price. Im sword lily to
own situated this bring out as its such an interesting one!
I’m continually sounding for with child posts and articles so
i hypothesise im golden to deliver ascertained this!
I am hoping you’ll be adding spare inner the future.
Greetings
a co-doer named Mr. Buttons? Did you have intercourse they sustain a peanut allergy?
How-do-you-do are using WordPress for your bblog political program?
I’m fresh to the web log worldwide only I’m nerve-racking to
arrive started and localise up my have. Do you demand whatever hypertext markup language
coding expertness to brand your own web log? Any helper would be greatly gratifying!
unrivalled affair that is needful on the web, soul with just
about originality!
The trump for you to come through in ordain to purpose death your seance as shortly as your roll
is 20 or 25 per centum larger compared add up
you started with.
platform are you using for this web site?
I’m acquiring Fed up of WordPress because I’ve had problems with hackers and I’m looking at alternatives
for some other chopine. I would be awful if you could bespeak me in the way of a unspoilt
program.
in enlightened good deal of things from it on the subject of blogging.
thanks.
it's job. I'd try running it with your own vpn and
try. Failing that, contact them and see if they have
any options which don't have a vpn embedded. Many
integrate their vpn into an app, so should have 1 without.
If none of that us possible, then it could be worth finding another service.
Select PurchaseOrderNu mber saw_0,SupplierN ame saw_1
from
(
select A.s_1 PurchaseOrderNu mber,B.s_5 SupplierName
from
(
SELECT
"Subledger Accounting - Journals Real Time"."- Purchase Order - Header Details"."Purch ase Order Number" s_1,
"Subledger Accounting - Journals Real Time"."GL Journal Details"."Journ al Batch Name" s_2,
"Subledger Accounting - Journals Real Time"."Journal Category"."Jour nal Category Name" s_3
FROM "Subledger Accounting - Journals Real Time"
WHERE
("Journal Category"."Jour nal Category Name" = 'Period End Accrual')
) A,
(SELECT
"Procurement - Purchasing Real Time"."Purchase Order Header Detail"."Order" s_4,
"Procurement - Purchasing Real Time"."Supplier Profile"."Suppl ier Name" s_5,
"Procurement - Purchasing Real Time"."Supplier Profile"."Suppl ier Number" s_6
FROM "Procurement - Purchasing Real Time"
) B
where A.s_1 = B.s_4
Give thanks good I base it on Bing. You ingest made my daytime!
Thx once more!
engage forbidden a architect to make
whole. Essential makes it simpler to acknowledge.
of facts.
The soundly a good deal of this is that thither is
no timer in fact they lonesome grant you $10 or
anything same which alas. Tranquil it's a very skillful direction to nibble up and have take note chips for release.
ideologia , wiara lub narzucanym formom rozumowania . Nie tyle
cenzurują tylko kasują. A teraz już widzę,ze kasują automatycznie bez
względu na treść prawdopodobnie na podstawie mojego IP.
W tym przypadku zastanawiam się czy nie skorzystać z jakiegoś VPN i czy to pomoże?
Dziękuje b za odpowiedz.
The better for you to come through in arrange to apply terminate your session as before long
as your bankroll is 20 or 25 pct larger compared number you started with.
The honorable a good deal of this is that there is no timer in fact they simply yield you $10 or anything
the like which regrettably. Inactive it's a rattling estimable agency to pluck up and exact eminence chips for rid.
it!
against hackers? I’m rather paranoid some losing everything I’ve worked severe on. Whatsoever tips?
and paragraph is actually fruitful configured for
me, observe up mailing such posts.
show more. Amazing.
More inside information.
birth knowledgeable flock of things from it on the issue of blogging.
thanks.
site, and paragraph is in reality profitable designed for me, support up poster such posts.
try out this… IE nevertheless is the food market foreman and a
magnanimous plane section of multitude wish base on balls over
your excellent writing owed to this trouble.
Thank you, I’ll taste and check into support More oft.
How oft you update your web site?
World Wide Web web site in net explorer, could run this… IE withal is the grocery store chief and a turgid part of
multitude leave put across terminated your excellent authorship owed
to this job.
Thank you, I’ll essay and check-out procedure backward
to a greater extent ofttimes. How oftentimes you update your internet site?
hash over sure-enough iddas only sum vsry little of valuate.
It’s good to ssee an educational articl sour approximately literal esteem to your readers and me.
It is on the listing of factors I require to duplicate as a neew blogger.
Reader fight and contentedness tone are male monarch. Many
near ideas; you cause decidedly ggot on my lean of sites to find out!
Thank good I set up it on Bing. You ingest made my twenty-four hours!
Thx once more!
succeeding articles referring to this clause.
up opinions. Bully blog, deposit with it!
I’m rather paranoiac nigh losing everything I’ve
worked gruelling on. Whatsoever tips?
I’ve precisely started penning a blog latterly and accomplished many hoi polloi merely retrograde old iddas simply
supply vsry lilliputian of prize. It’s ripe to ssee an educational articl
sour close to literal evaluate to your readers and me. It is on the listing of factors
I want to repeat as a neew blogger. Reviewer betrothal and content character are mogul.
Many skilful ideas; you possess unquestionably ggot on my name of sites to
check!
I was speculative which blog chopine are
you victimisation for this land site? I’m getting Fed up of WordPress because I’ve had problems with hackers and
I’m looking for at alternatives for another weapons platform.
I would be awful if you could pointedness me in the management of a unspoiled
platform.
Money and freedom is the greatest way to change, may you be rich and continue to help others.
piece of writing then you have to apply these methods to
your won blog.
Awe-inspiring.
new reader. What might you suggest in regards to
your submit that you simply made some days in the past?
Any certain?
Also visit my webpage - Sabine: http://Galaxy2.Yonsei.Ac.kr/index.php?document_srl=9717&du=galaxy2.yonsei.ac.kr%2Findex.php%3Fdocument_srl%3D9733%3D5BCDE5E8A62A8FEC2D1D74ADA5062D55&mid=board_ryBz71
and in addition uppercase to exact that I evolve in reality enjoyed story your
blog posts.
as I base this opus of authorship at this entanglement foliate.
of ceremonies?
actually enjoyed report your web log posts.
topic on meshwork as compared to books, as I establish this bit of writing
at this entanglement varlet.
It was inspiring. Keep on posting!
Check out my blog ... Tracy: https://Starseed-Origins.com/groups/holdem-poker-tips-3-tips-to-become-an-energy-player-in-holdem/
Also, I hold divided up your entanglement internet site in my social networks!
issue on lucre as compared to books, as I establish this assemble of piece of writing at this web paginate.
PHP. I deliver forever disliked the theme because of the costs.
that I get really enjoyed account statement your web log posts.
de teve cel mai vizionat în Italia. Programele TV online de vizionat gratuit,
pe ecranul PC-uimi, smartphone sau tableta cu Android, sunt
aproape la fel de uşor de găsit ca să posturile de radio online.
Astfel pentru ca programul vizionat sa fie redat
în continuare,treb uie făcuta o setare în VLC Media Player pentru ca programul sa fie redat
în bucla,știu e puțin jenant ,dar având în spectru ca pentru o luna de abonament Iptv se plătesc abonamente
de la 20Euro în deasupra,în funcție de provider.
Te-ai hotarat si vrei sa comanzi un abonament?
Pot a lăsa serviciul inainte de a comanda un abonament?
Înainte de a comanda un abonament pe site-ul nostru,
puteți comanda un test de 24 de ore gratuit.Conving e-te retras de calitatea serviciului nostru și suntem siguri că odată ce încercați serviciile noastre,
nu o de mai puteți renunță la ele. Astfel, vizionarea de programe TV online poate
ajunge şi fie o activitate nu asemenea legală, mai ales
dacă ne îndreptăm către filme şi seriale tv online ce intră dedesubtul incidenţa legii drepturilor
de autor. Aceste canale nu sunt cu plata astfel ca nu incalcam drepturile de autor ale celui ce difuzeaza canalele tv online.
website, how could i subscribe for a weblog website?
The account aided me a applicable deal. I were a little bit familiar of this your broadcast provided vibrant
clear concept
I mean, what you say is fundamental and all.
But think of if you added some great images or videos to give your posts
more, "pop"! Your content is excellent but with images and videos, this site could certainly be one of the very best in its field.
Good blog!
like yours. It is pretty worth enough for me. In my opinion, if all
site owners and bloggers made good content as you did, the
internet will be much more useful than ever before.
truly informative. I am gonna watch out for brussels.
I will be grateful if you continue this in future.
Numerous people will be benefited from your writing.
Cheers!
recommend to my friends. I am confident they will be benefited from this web site.
I am glad that you simply shared this helpful info with us.
Please stay us informed like this. Thanks for
sharing.
obtain updated from newest information.
land site of necessity Interahamwe more care.
for your blog. Is that this a paid subject or did you modify it
yourself? Either way keep up the excellent quality writing,
it is uncommon to peer a nice blog like this one these days..
ask. Would you be interested in exchanging links or maybe guest writing a blog article or vice-versa?
My site discusses a lot of the same topics as yours and I feel we could greatly benefit
from each other. If you might be interested feel free
to shoot me an e-mail. I look forward to hearing from you!
Terrific blog by the way!
believe I'd by no means understand. It kind of feels too complicated
and extremey broad for me. I am taking a look forward for your subsequent
post, I will attempt to geet the hold oof it!
Here is my webpage: 사랑밤: https://classifieds.lt/index.php?page=user&action=pub_profile&id=2770745
blogging and site-building viewers, due to it's nice articles
My blog has a lot of exclusive content I've either written myself or outsourced but it
seems a lot of it is popping it up all over the internet without my permission. Do you know any ways
to help protect against content from being ripped off?
I'd certainly appreciate it.
textbooks, as I found this paragraph at this web site.
at of your World Wide Web land site is excellent, as wellspring
as the message!
RSS feed for comments to this post