Monday is YesSQL day at Kscope15 and to show that OBIEE can party with the best, here is some useful implementation of SQL in the OBIEE front end.
Ok, so it’s Logical SQL but the principle is the same.
Below I will show you how to join datasets using just SQL. Yes SQL!
Lets take a std query created using the Usage Tracking Subject Area.
Using the Column Selector we pick Start Date and Number of Queries.
And the SQL for this is:
SELECT “Query Time”.”Start Date” saw_0, “Physical SQL”.”# of Logical Queries” saw_1 FROM “U – Usage Tracking”
(Taken from the Advanced Tab).
But, can you see the problem?
Time is not smoothed, days with no use are not shown. So, we could wrongly assume the we have been busy!
SQL to the rescue.
First lets get all the days you want to view.
SELECT
“Time”.”T00 Calendar Date” saw_0,
Monthname(“Time”.”T00 Calendar Date”) saw_1,
MONTH(“Time”.”T00 Calendar Date”) saw_2
FROM “A – Sample Sales”
WHERE “Time”.”T00 Calendar Date” BETWEEN date ‘2015-01-01’ AND date ‘2015-06-30’
Now we join the two SQL together, using a LEFT OUT JOIN statement
select
ALL_TIME.dt,
ALL_TIME.MthNum,
Queries
from
(SELECT “Time”.”T00 Calendar Date” dt,
Monthname(“Time”.”T00 Calendar Date”) Mth,
MONTH(“Time”.”T00 Calendar Date”) MthNum
FROM “A – Sample Sales”
WHERE “Time”.”T00 Calendar Date” BETWEEN date ‘2014-01-01’ AND date ‘2015-06-30’) ALL_TIME LEFT OUTER JOIN
(SELECT
“Query Time”.”Start Date” dt,
“Physical SQL”.”# of Logical Queries” Queries
FROM “U – Usage Tracking”) USAGE
ON ALL_TIME.dt = USAGE.dt
Paste the statement into the SQL box on the advanced tab, run the results and create your graph.
Now we see every day, even if there is no activity.
The principle shown is that you can use SQL to build datasets from different Subject Areas, and create results that the RPD is not modelled to produce.
SQL Saves the day.
4 days to go….