Here is a technique for enabling the user to set join columns with a dashboard prompt.
An example of a case where this technique could be useful is when there are multiple date columns in a fact table. For example, a purchase order might have a purchase order date, a ship by date, and a cancel by date. The dashboard prompt would allow the user to set a date range in the period table and then determine which column in the fact table the period table would join to. By changing the join column the user changes the set of purchase orders that match the date prompt.
The technique described here may not be the only way to accomplish this, and I’d welcome any other suggestions for better techniques. But here is one that works.
The sample fact table I’ll use is a basic representation of a purchase order line.
The metadata defines a non-system session variable called JoinColumn which has an initialization block with the following SQL.
select 'CANCELBYDATE' from dual
The session variable also has a default value, which is the string ‘CANCELBYDATE’. (Of course, the default value could be different, and the init block could select a different value.)
The metadata allows any user to “set the value” – i.e. over write the session variable value by setting a request variable with the same name. (This overwriting occurs for queries that execute on a dashboard within the scope of the dashboard prompt).
In the physical layer, the table is defined by a Select statement that uses ValueOf(NQ_SESSION.JoinColumn) as one of the column names. ValueOf(NQ_SESSION.JoinColumn) can become PODATE, SHIPBYDATE, or CANCELBYDATE depending on the value of the session variable JoinColumn. The SQL gives the column the alias JCol.
Select ValueOf(NQ_SESSION.JoinColumn) as JCol
, ID
, Product
, Qty
from POS
For simplicity, the column UNITPRICE is ignored here. The table looks like this in the physical layer. Note that the data type of JCol is Date.
The physical join from the period table to the fact table is straightforward – JCol joins to the DATE_ column in TIME.
The dashboard has a prompt that allows the user to set a date range in the TIME table as well as the value of the session variable JoinColumn.
To show the set up of the dashboard prompt more clearly, here are two pictures of it. The request variable name must match the name of the session variable (case sensitive).
The values of the join column show up in a drop-down list using this SQL:
SELECT case when 1=0 then TIME."MONNAME" else 'PODATE' end FROM SetJoin
union all
SELECT case when 1=0 then TIME."MONNAME" else 'SHIPBYDATE' end FROM SetJoin
union all
SELECT case when 1=0 then TIME."MONNAME" else 'CANCELBYDATE'end FROM SetJoin
The default value is set to a specific value, CANCELBYDATE.
As the user makes different selections of the join column, the SQL generated matches those selections.
select T463.DATE_ as C1
,Sum(T946.QTY) as C2
from time T463
,(select CANCELBYDATE as JCOL
,id
,PRODUCT
,QTY
from POS) T946
where (T463.DATE_ = T946.JCOL
and T463.DATE_ between To_date('2009-07-31','YYYY-MM-DD') and To_date('2009-09-30','YYYY-MM-DD')
and T946.JCOL between To_date('2009-07-31','YYYY-MM-DD') and To_date('2009-09-30','YYYY-MM-DD'))
group by T463.DATE_
order by C1
select T463.DATE_ as C1
,Sum(T946.QTY) as C2
from time T463
,(select SHIPBYDATE as JCOL
,id
,PRODUCT
,QTY
from POS) T946
where (T463.DATE_ = T946.JCOL
and T463.DATE_ between To_date('2009-07-31','YYYY-MM-DD') and To_date('2009-09-30','YYYY-MM-DD')
and T946.JCOL between To_date('2009-07-31','YYYY-MM-DD') and To_date('2009-09-30','YYYY-MM-DD'))
group by T463.DATE_
order by C1
