Emulate in OBIEE an SQL query with UNION to combine multiple query results

Question

Hi Everyone,

I have a report to build based on employee statuses in the HR system. An indicative I have attached herewith. I am able to get the desired result with plain SQL. I am presently using this SQL script in a direct database request and getting desired results in a dashboard with a prompt on a particular month.

I am however not sure if this is the best way to do it. I am wondering if only I could do this by creating objects (variables) in the RPD. I am using OBIEE 11.1.1.7

Look forward to your expert advise and suggestions.

An excerpt of the code is as follows:

/*Total Count of employees*/select 'Grand Total' as Head,T.PREVIOUSMONTH as Mon, count(STD.Emp_No) as CountsFROM STD_RATES STDINNER JOIN Time_Dim T on STD.DATE_ID = T.PREV_date_id where T.MON = '@{month}{Apr}'group by T.PREVIOUSMONTHUNIONselect 'Grand Total' as Head,T.Mon as Mon, count(STD.Emp_No) as CountsFROM STD_RATES STDINNER JOIN Time_Dim T on STD.DATE_ID = T.date_id where T.MON = '@{month}{Apr}'group by T.MonUNION/* New Joinees*//* No employee in M1, joined in M2 */select 'New Joinees' as Head,T.PREVIOUSMONTH as Mon, NULL as CountsFROM STD_RATES STDINNER JOIN Time_Dim T on STD.DATE_ID = T.PREV_date_idWHERE T.MON = '@{month}{Apr}' UNIONselect 'New Joinees' as Head, T.Mon as Mon, count(Emp_No) as CountsFROM STD_RATES STDINNER JOIN Time_Dim T on STD.DATE_ID = T.date_id --and ACTIVE = 'A'and T.DATE_ID = to_number(to_char(trunc(STD.DOJ,'mm'),'yyyymm')) and T.MON = '@{month}{Apr}'group by T.MONUNION/*Exits*/ /* Final settlement in M1, and does not feature in M2 */select 'Exits' as Head, T.PREVIOUSMONTH as Mon, count(Emp_No) as CountsFROM STD_RATES STDINNER JOIN Time_Dim T on STD.DATE_ID = T.PREV_date_idAND ACTIVE = 'X' AND T.MON = '@{month}{Apr}'AND NOT EXISTS (SELECT 1FROM STD_RATES STD2INNER JOIN Time_Dim T on STD2.DATE_ID = T.Date_idWHERE STD.EMP_NO = STD2.EMP_NO--AND ACTIVE = 'A')group by T.PREVIOUSMONTHUNIONselect 'Exits' as Head, T.Mon as Mon, NULL as CountsFROM STD_RATES STDINNER JOIN Time_Dim T on STD.DATE_ID = T.Date_idWHERE T.MON = '@{month}{Apr}'

rmoff · Answer

Amen, @Christian Berg.

@Rahul Huilgol Step away from the SQL, and into the world of logical modelling and RPDs. Only then, may you look at the SQL

[Deleted User] · Answer

Yes. That's the whole point of the RPD. I take it you haven't worked with OBI at all yet? We do not want SQL queries. We want models which dynamically spawn SQL queries for maximum efficiency and flexibility.

Especially since you want to do things like time series which in your example is done with logic in the code and which the RPD would to on a logical basis so it wouldn't just give you one specific month but first of all any AGO measure plus TODATE and PERIODROLLING on any level of time you want.

But I digress. You should definitely at minimum look at this tutorial:

https://apexapps.oracle.com/pls/apex/f?p=44785:24:108740784839686::NO:RP,24:P24_CONTENT_ID,P24_PREV_PAGE:12166,2

Your question is not something that's "Yes / No" or "click that button" but more about:

"You can combine all the information through logical table sources of your facts and measures but actually it's a much wider question since if you're doing things clever and properly - and since your data is actually in ONE table - you most like don't even need a lot of weird logic since the RPD would do everything on its own just based on the table definition."