Someone asked a question on a “Siebel Analytics” email group a few days ago that I thought was worth discussing briefly here. The question posed was how to put a date prompt on a dashboard and return data for that date and the next 30 days. The solution could not involve presentation or report variables, since these were introduced only in a later version of Siebel Analytics that the person who posed the question did not have.
When you have a problem like this, it usually helps to think about the SQL the BI Server would need to generate. In this case, it would include a WHERE clause with a BETWEEN predicate. That is, the normal key/foreign key equijoin between the fact table and the period table would have to become a join of the fact table foreign key between a date in the period table and that date plus 30 days. This is a good example or a key principle: whenever a join condition needs to change, you should think about creating a new alias that uses that join condition.
Here’s an example of the usual equijoin of the period table (“TIME”) to the fact table (“TIMECOMPAREFACTS”).
Using an alias of the fact table (“TimeSpanFacts”) and a“complex” instead of a physical foreign key join, the join expression changes.
Now the user picks a date with the dashboard prompt and clicks Go.
The BI Server generates the SQL needed as specified in the metadata.
select T463.DATE_ as C1
,Sum(T884.QS) as C2
,Sum(T884.DOLLARS) as C3
from TIME T463
, TIMECOMPAREFACTS T884 /* TimeSpanFacts */
where
(
T463.DATE_ = To_date('2009-04-01','YYYY-MM-DD')
and
T884.DATEKEY between T463.DATE_ and (T463.DATE_ + 30) )
group by T463.DATE_
order by C1
When you have a problem like this, it usually helps to think about the SQL the BI Server would need to generate. In this case, it would include a WHERE clause with a BETWEEN predicate. That is, the normal key/foreign key equijoin between the fact table and the period table would have to become a join of the fact table foreign key between a date in the period table and that date plus 30 days. This is a good example or a key principle: whenever a join condition needs to change, you should think about creating a new alias that uses that join condition.
Here’s an example of the usual equijoin of the period table (“TIME”) to the fact table (“TIMECOMPAREFACTS”).
Using an alias of the fact table (“TimeSpanFacts”) and a“complex” instead of a physical foreign key join, the join expression changes.
Now the user picks a date with the dashboard prompt and clicks Go.
The BI Server generates the SQL needed as specified in the metadata.
select T463.DATE_ as C1
,Sum(T884.QS) as C2
,Sum(T884.DOLLARS) as C3
from TIME T463
, TIMECOMPAREFACTS T884 /* TimeSpanFacts */
where
(
T463.DATE_ = To_date('2009-04-01','YYYY-MM-DD')
and
T884.DATEKEY between T463.DATE_ and (T463.DATE_ + 30) )
group by T463.DATE_
order by C1
Posts
