YesSQL! for OBIEE

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.

Usage by Date from Usage Tracking
Usage by Date from Usage Tracking

 

 

 

 

 

 

 

 

 

This results in some data as shown below.

Std Query Graph UT

 

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.

 

Left Joined Graph
Left Joined 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….