OTBI Logical SQL complex query assembler - Updated to 10 subject areas

Maxime Pignot-Oracle

Summary

Combine analyses from up to 10 different subject areas

Content

While building a report on OTBI, you will encounter some cases where the Subject Area will only provide you partial information, and you need to complete that data with some others which are located on an entirely different Subject Area.

Sometimes, even within the same subject area, the biais arbitrarily taken by OTBI when selecting some columns will render no results because of the joins decided by people creating the subject area columns are not compatible or are reporting on 2 realms that cannot communicate together (for example, getting some course related info and offering related info at the same time from the learning subject area).

In short: you have 2 analyses which work perfectly fine on their own, but you have no way to combine them together to work it as a single comprehensive report.

The attached spreadsheet will guide you to associate these 2 analyses by making you follow several easy steps:

1. On each of your working analyses, you need to pick up the generated SQL by going into the Analysis "Advanced" tab, and copy the "Generated SQL"
2. Paste both these SQL on the "Queries" Tab as indicated in the example.
3. Give a name to your columns
4. This spreadsheet need to identify the 2 separate queries and assemble them in 2 different temporary tables. Give a name to these tables.
5. Select which column should be linked together
6. Pick up the final query. First paste it in a word document (so it does not pick up any doublequotes where you do not want them)
7. Then from the Word, copy and paste it when creating a new analysis based on a custom logical SQL going into an analysis/advanced tab/new analysis button.

 

NOTES:

• This assembler should account for specific filters present in each of the 10 queries and render them as they should be.
• It supports up to 30 columns on each of the 10 queries
• It will not account for any column and view formating/reordering you have already done on either of the 2 queries individually. This is just generating the basic assembled query on which you can then work to make it look better.

Logical SQL Assembler V5.xlsx

Uploaded Mar 18, 2025287.29 KB

Prudence K

I cannot wait to explore this more - thank you so much!

Aaron Leggett

This looks awesome, writing Logical SQL manually can be a cumbersome task. Perhaps this could be turned into an app using Oracle Cloud's Free Tier + Apex? You might even be able to go as deep as creating a visual format that allows you to drag and drop the logical column names by importing the OTBI lineage docs.

I always thought a good enhancement would be an app that allows you to insert HTML + CSS markup visually (like a lot of no/low-code tools allow you to do) to a report that auto-applies the formatting to the XML, alleviating the pain of manually editing the format for each column (import from template doesn't always work!)

Maxime Pignot-Oracle

Thank you for the feedback. Likely if I had an Apex creation/edition permission somehow i'd definitely spend the time studying it. Obviously having this somewhat directly in OTBI would be awesome instead of the current join feature that actually does not work.

When it comes to formating, that could be a next step for that spreadsheet, and issue 2 different statements, one for SQL, one for XML.

XML would be pretty hard to parse though, because all views, ordering, formating, aggregation rules, column properties are in there. So I would have to constrict that to very simple formating option or that will be a nightmare.

Jagdish Varadraj

Great. Can i add more Queries Like Query 3 / Query 4 to it. will the excel macro work?

Thanks
Jag

Maxime Pignot-Oracle

hey Jag,

I havent dwelled into that yet. If I manage to know how a query would look like i can probably adjust the parser accordingly.

Anders W. Langholm

Hi.

I have used the query assembler and I'm able to get the right result, but when I'm trying to add any prompts to the prompts-tab, it just returns the following:

"Failed to load(Odbc driver returned an error (SQLExecDirectW).)"

How do I figure out what is wrong, when all I am left with is this pretty generic message?

Hope you're able to point me in the right direction

Kind regards

Anders

Srinivas Pendyala-Oracle

Please attach the Nqquery.log file of the report.

Anders W. Langholm

Hi Srinivas

Here you go ? Thanks in advance for your help.

//Anders

nqquerylog_issue_with_CIP_Assets.txt

Marybeth Snodgrass

@Anders W. Langholm were you ever able to figure out how to get prompts to work? i am having this issue too

@Maxime Pignot-Oracle

CA Nirmal Choudhary

Hi Marybeth Snodgrass,

You can dashboard prompt -

1. Make column filter as prompted
2. Create Dashboard prompt with same column
3. add both of them in dashboard page