UI for Logical SQL
Description
Logical SQL is a powerful tool that we can use to help us join subject areas that might not otherwise return useful data using the standard cross join analysis. However it is not user friendly and many users can't create logical sql without a technical resource, even after reviewing samples and training videos. Having a business functional end user interface, to assist in creation of logical SQL, would really help customers and increase adoption of OTBI.
Use Case and Business Need
When joining Salary to Assignment or Performance, and many other use cases, it may be necessary to use Logical SQL.
Please see:
https://cloudcustomerconnect.oracle.com/posts/8e9a22fb6d
https://cloudcustomerconnect.oracle.com/posts/132e0068a8
https://cloudcustomerconnect.oracle.com/posts/fc68b59a3b
https://cloudcustomerconnect.oracle.com/posts/de43145427
https://cloudcustomerconnect.oracle.com/posts/c238315b66
https://cloudcustomerconnect.oracle.com/posts/e68146186f
https://cloudcustomerconnect.oracle.com/posts/5884ed4854
https://cloudcustomerconnect.oracle.com/posts/c02be4a127
https://cloudcustomerconnect.oracle.com/posts/d79ae98106
https://cloudcustomerconnect.oracle.com/posts/a0edcf37c1
https://cloudcustomerconnect.oracle.com/posts/5ee5e41623
https://cloudcustomerconnect.oracle.com/posts/5ee5e41623
Original Idea Number: 3dccb24a0e
Comments
-
We recently moved to HCM Cloud and finding that the standard OTBI Subject Areas are limited in functionality. Having a tool to write LSQL or help guide building LSQL would be very helpful with using OTBI with out having very technical resources.
0 -
This would be a great feature!
0 -
Great idea! We are currently using LSQL in following sceanrios:
1) Seeing the before and after Changes in employees records.
2) Performance Rating completion rates - percentage of employees with Rating out of all those eligible for a Rating.
3) Absence Rates - percentage of hours employees are absent against total Normal Working hours and how it Trends over time (i.e. Employee record needs to be correct as of the start date of the absence)
Having a UI here for creating and amending would be really helpful.
0 -
Fetching previous row data
0 -
OTBI is ideal for our less technical users, and BIP is for our more technical users. LSQL is in between but leans toward the more technical users and can push off the less technical users. Would be nice to make it more user friendly for our less technical users.
0 -
This perfectly describes the niche for this enhancement, and illustrates why it is ideal for a cloud solution and the business end user.
0 -
I concur, this is definitely comming handy in later stages of implementation.
Usually the way subject areas work gives limited results and obliges us to combine 2 or more different subject areas, and even sometimes do 2 or more analysis and combine them.
Having a LSQL easy builder would be extremely useful.
0 -
For anyone needing immediate assistance there is a very helpful workaround posted here:
https://cloudcustomerconnect.oracle.com/posts/18820e4bbe0 -
This seems helpful indeed.
Any thoughts of either incorporating UI for Logical SQL or incorporating something like this excel sheet functionality in OTBI?
Thanks!0 -
We face the same issues described by everyone else.
0 -
Yes this is a very good idea :-)
0 -
This is a helpful feature to make it easier for end users to use LSQL
0 -
This is actually a big deal -- and a very good idea, indeed.
For the record, the workbook from Maxime Pignot-Oracle is a great start, but it's definitely not a solution for the problem Gail Langendorf-Oracle describes (the OP). It is a stop-gap workaround, at best, for what remains a fairly narrow slice of end users. Here's why.
- It's complicated. While straightforward, the process is "geeky" and fairly easy to mess up. It involves concepts most functional users know little or nothing about -- "blindly" following instructions they don't understand, and hoping they do so correctly.
- It's "fiddly" -- particularly the last step (copy/paste first into Word, then into Notepad++).
- It doesn't always work. For example, it fails when a source query contains "double columns" (e.g. DESCRIPTOR_IDOF) -- a common feature of many queries.
The value from combining Subject Areas can be significant, the need so ubiquitous, and the alternatives so demanding, I'm surprised there isn't a more straightforward, and less complicated solution already built-in. Here's hoping one day soon there will be.
That said, a genuine thanks to everyone who made this workbook workaround a reality in the meantime.
0 -
I would like to add here, that the assembler may allow a table name alias that is not accepted by the system, for example WORKER and POSITION as discussed here, in that case the error codes are ambiguous: https://community.oracle.com/customerconnect/discussion/comment/636924#Comment_636924
0 -
Not a UI, but there is a Logical SQL reference here: https://docs.oracle.com/middleware/12212/biee/BIESQ/BIESQ.pdf
0 -
This is a good idea and would be a helpful feature for the teams.
0 -
How does this have only 2 votes with so many comments?
Very much needed to add this feature to the application. The current way of combining subject areas inside the application is way too volatile and error-prone and the logical sql combiner is not suitable for 'regular' users in my experience.
0
