Oracle BI EE 10.1.3.4.1 – Do we always need AGO and ToDate functions for Time Series Analysis?

Venkatakrishnan J

Jun 9, 2010 • 16 min read

In my last blog post, i basically showed how we can do multi-dimension OLAP aware reporting by altering the physical data-warehouse model. Along similar lines, one other common thing that i have found recently in many implementations is the extensive use of AGO and ToDate functions. AGO and ToDate are probably one of the most important functions within the BI EE repository that provide time series based reporting. But sometimes the queries generated by AGO and ToDate could really be bringing down the performance of the reports. Hence, it becomes necessary to use them judiciously(only when required). There are ways to tune the AGO and ToDate queries (to an extent) by altering our RPD models. I will not be covering them in this blog post. Instead this blog post primarily showcases when and where we can avoid AGO and ToDate functions.

As we all know, AGO and ToDate functions work on the premise of identifying a time dimension and then identifying the chronological key (which should strictly be in chronological order). There are 2 main drawbacks in using AGO and ToDate functions

We do not have a similar such feature in the UI – So we have to rely on what is setup in the repository. This can be very limiting when we want custom time traversal that is not defined in the repository.
If we have, say 50 measures in the RPD with each measure requiring at-least 5 ToDates (YTD, MTD, QTD etc) and 4 AGOs (Day Ago, Month Ago etc), the number of measures can increase dramatically.

So wherever possible, i always try to avoid using AGO and ToDate. Though it is not possible in all cases, but in the recent couple of implementations that i was part of we were able to avoid it for 90% of the reports that required time based reporting. Every report that requires time series functions can be classified in to 2 types

A report that does not contain any Time related attributes as a column in the report. For example, the report shown below

does not have any time attributes as a column in the report.

A report that contains Time-related attributes as a column in the report. For example, the report shown below

has Fiscal Year as one of its columns.

In order to achieve the 2nd type of report shown above, we need to use AGO and ToDate functions. The main reason is for a single record dimensioned by time, we need to bring the values from another row dimensioned by time. So, for such kind of reports we will have to use AGO and ToDate functions.

But for the Reports of Type 1, there is actually no need for using AGO and ToDate functions. For example, the most common use case is given below

As you see, its a very simple report within a dashboard containing a prompt for Day. Based on the day chosen, the values of Prior Year, Prior Week, Prior Month, YTD, WTD and MTD are calculated.

Such kind of reports do not need AGO and ToDate functions. We can arrive at the numbers by using simple FILTER functions and presentation variables. Before we look at how we can use FILTER functions, lets look at the query generated by using AGO and ToDate functions for the same report above.

WITH
SAWITH0 AS (select T12710.TIME_ID as c3,
     T12710.FISCAL_MONTH_ID as c4,
     ROW_NUMBER() OVER (PARTITION BY T12710.FISCAL_MONTH_ID ORDER BY T12710.FISCAL_MONTH_ID DESC) as c5,
     ROW_NUMBER() OVER (PARTITION BY T12710.FISCAL_MONTH_ID, T12710.TIME_ID ORDER BY T12710.FISCAL_MONTH_ID DESC, T12710.TIME_ID DESC) as c6
from
     TIMES T12710),
SAWITH1 AS (select Case when case SAWITH0.c5 when 1 then SAWITH0.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH0.c5 when 1 then SAWITH0.c3 else NULL end  ASC NULLS LAST ) end as c1,
     Case when case SAWITH0.c6 when 1 then SAWITH0.c3 else NULL end  is not null then Rank() OVER ( PARTITION BY SAWITH0.c4 ORDER BY case SAWITH0.c6 when 1 then SAWITH0.c3 else NULL end  ASC NULLS LAST ) end as c2,
     SAWITH0.c3 as c3,
     SAWITH0.c4 as c4
from
     SAWITH0),
SAWITH2 AS (select distinct min(SAWITH1.c1) over (partition by SAWITH1.c4)  as c1,
     min(SAWITH1.c2) over (partition by SAWITH1.c4, SAWITH1.c3)  as c2,
     SAWITH1.c3 as c3
from
     SAWITH1),
SAWITH3 AS (select T12710.FISCAL_MONTH_ID as c3,
     T12710.TIME_ID as c4,
     T12710.FISCAL_YEAR_ID as c5,
     ROW_NUMBER() OVER (PARTITION BY T12710.FISCAL_MONTH_ID ORDER BY T12710.FISCAL_MONTH_ID DESC) as c6,
     ROW_NUMBER() OVER (PARTITION BY T12710.FISCAL_MONTH_ID, T12710.TIME_ID ORDER BY T12710.FISCAL_MONTH_ID DESC, T12710.TIME_ID DESC) as c7
from
     TIMES T12710),
SAWITH4 AS (select Case when case SAWITH3.c6 when 1 then SAWITH3.c4 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH3.c6 when 1 then SAWITH3.c4 else NULL end  ASC NULLS LAST ) end as c1,
     Case when case SAWITH3.c7 when 1 then SAWITH3.c4 else NULL end  is not null then Rank() OVER ( PARTITION BY SAWITH3.c3 ORDER BY case SAWITH3.c7 when 1 then SAWITH3.c4 else NULL end  ASC NULLS LAST ) end as c2,
     SAWITH3.c3 as c3,
     SAWITH3.c4 as c4,
     SAWITH3.c5 as c5
from
     SAWITH3),
SAWITH5 AS (select distinct min(SAWITH4.c1) over (partition by SAWITH4.c3)  as c1,
     min(SAWITH4.c2) over (partition by SAWITH4.c3, SAWITH4.c4)  as c2,
     SAWITH4.c3 as c3,
     SAWITH4.c4 as c4,
     SAWITH4.c5 as c5
from
     SAWITH4),
SAWITH6 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4
from
     (select sum(T12697.AMOUNT_SOLD) as c1,
               T12662.PROD_DESC as c2,
               SAWITH5.c5 as c3,
               SAWITH5.c3 as c4,
               ROW_NUMBER() OVER (PARTITION BY T12662.PROD_DESC, SAWITH5.c3 ORDER BY T12662.PROD_DESC ASC, SAWITH5.c3 ASC) as c5
          from
               PRODUCTS T12662,
               SALES T12697,
               SAWITH2,
               SAWITH5
          where  ( T12662.PROD_ID = T12697.PROD_ID and T12697.TIME_ID = SAWITH2.c3 and SAWITH5.c1 = SAWITH2.c1 and SAWITH5.c4 = TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') and SAWITH5.c2 >= SAWITH2.c2 )
          group by T12662.PROD_DESC, SAWITH5.c3, SAWITH5.c5
     ) D1
where  ( D1.c5 = 1 ) ),
SAWITH7 AS (select T12710.TIME_ID as c3,
     T12710.FISCAL_YEAR_ID as c4,
     ROW_NUMBER() OVER (PARTITION BY T12710.FISCAL_YEAR_ID ORDER BY T12710.FISCAL_YEAR_ID DESC) as c5,
     ROW_NUMBER() OVER (PARTITION BY T12710.FISCAL_YEAR_ID, T12710.TIME_ID ORDER BY T12710.FISCAL_YEAR_ID DESC, T12710.TIME_ID DESC) as c6
from
     TIMES T12710),
SAWITH8 AS (select Case when case SAWITH7.c5 when 1 then SAWITH7.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH7.c5 when 1 then SAWITH7.c3 else NULL end  ASC NULLS LAST ) end as c1,
     Case when case SAWITH7.c6 when 1 then SAWITH7.c3 else NULL end  is not null then Rank() OVER ( PARTITION BY SAWITH7.c4 ORDER BY case SAWITH7.c6 when 1 then SAWITH7.c3 else NULL end  ASC NULLS LAST ) end as c2,
     SAWITH7.c3 as c3,
     SAWITH7.c4 as c4
from
     SAWITH7),
SAWITH9 AS (select distinct min(SAWITH8.c1) over (partition by SAWITH8.c4)  as c1,
     min(SAWITH8.c2) over (partition by SAWITH8.c4, SAWITH8.c3)  as c2,
     SAWITH8.c3 as c3
from
     SAWITH8),
SAWITH10 AS (select T12710.FISCAL_MONTH_ID as c3,
     T12710.TIME_ID as c4,
     T12710.FISCAL_YEAR_ID as c5,
     ROW_NUMBER() OVER (PARTITION BY T12710.FISCAL_YEAR_ID ORDER BY T12710.FISCAL_YEAR_ID DESC) as c6,
     ROW_NUMBER() OVER (PARTITION BY T12710.FISCAL_YEAR_ID, T12710.TIME_ID ORDER BY T12710.FISCAL_YEAR_ID DESC, T12710.TIME_ID DESC) as c7
from
     TIMES T12710),
SAWITH11 AS (select Case when case SAWITH10.c6 when 1 then SAWITH10.c4 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH10.c6 when 1 then SAWITH10.c4 else NULL end  ASC NULLS LAST ) end as c1,
     Case when case SAWITH10.c7 when 1 then SAWITH10.c4 else NULL end  is not null then Rank() OVER ( PARTITION BY SAWITH10.c5 ORDER BY case SAWITH10.c7 when 1 then SAWITH10.c4 else NULL end  ASC NULLS LAST ) end as c2,
     SAWITH10.c3 as c3,
     SAWITH10.c4 as c4,
     SAWITH10.c5 as c5
from
     SAWITH10),
SAWITH12 AS (select distinct min(SAWITH11.c1) over (partition by SAWITH11.c5)  as c1,
     min(SAWITH11.c2) over (partition by SAWITH11.c5, SAWITH11.c4)  as c2,
     SAWITH11.c3 as c3,
     SAWITH11.c4 as c4,
     SAWITH11.c5 as c5
from
     SAWITH11),
SAWITH13 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4
from
     (select sum(T12697.AMOUNT_SOLD) as c1,
               T12662.PROD_DESC as c2,
               SAWITH12.c5 as c3,
               SAWITH12.c3 as c4,
               ROW_NUMBER() OVER (PARTITION BY T12662.PROD_DESC, SAWITH12.c3 ORDER BY T12662.PROD_DESC ASC, SAWITH12.c3 ASC) as c5
          from
               PRODUCTS T12662,
               SALES T12697,
               SAWITH9,
               SAWITH12
          where  ( T12662.PROD_ID = T12697.PROD_ID and T12697.TIME_ID = SAWITH9.c3 and SAWITH12.c1 = SAWITH9.c1 and SAWITH12.c4 = TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') and SAWITH12.c2 >= SAWITH9.c2 )
          group by T12662.PROD_DESC, SAWITH12.c3, SAWITH12.c5
     ) D1
where  ( D1.c5 = 1 ) ),
SAWITH14 AS (select T12710.TIME_ID as c3,
     T12710.FISCAL_MONTH_ID as c4,
     ROW_NUMBER() OVER (PARTITION BY T12710.FISCAL_MONTH_ID ORDER BY T12710.FISCAL_MONTH_ID DESC) as c5,
     ROW_NUMBER() OVER (PARTITION BY T12710.FISCAL_MONTH_ID, T12710.TIME_ID ORDER BY T12710.FISCAL_MONTH_ID DESC, T12710.TIME_ID DESC) as c6
from
     TIMES T12710),
SAWITH15 AS (select Case when case SAWITH14.c5 when 1 then SAWITH14.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH14.c5 when 1 then SAWITH14.c3 else NULL end  ASC NULLS LAST ) end as c1,
     Case when case SAWITH14.c6 when 1 then SAWITH14.c3 else NULL end  is not null then Rank() OVER ( PARTITION BY SAWITH14.c4 ORDER BY case SAWITH14.c6 when 1 then SAWITH14.c3 else NULL end  ASC NULLS LAST ) end as c2,
     SAWITH14.c3 as c3,
     SAWITH14.c4 as c4
from
     SAWITH14),
SAWITH16 AS (select min(SAWITH15.c1) over (partition by SAWITH15.c4)  as c1,
     min(SAWITH15.c2) over (partition by SAWITH15.c4, SAWITH15.c3)  as c2,
     SAWITH15.c3 as c3
from
     SAWITH15),
SAWITH17 AS (select distinct SAWITH16.c1 + 1 as c1,
     SAWITH16.c2 as c2,
     SAWITH16.c3 as c3
from
     SAWITH16),
SAWITH18 AS (select T12710.FISCAL_MONTH_ID as c3,
     T12710.TIME_ID as c4,
     T12710.FISCAL_YEAR_ID as c5,
     ROW_NUMBER() OVER (PARTITION BY T12710.FISCAL_MONTH_ID ORDER BY T12710.FISCAL_MONTH_ID DESC) as c6,
     ROW_NUMBER() OVER (PARTITION BY T12710.FISCAL_MONTH_ID, T12710.TIME_ID ORDER BY T12710.FISCAL_MONTH_ID DESC, T12710.TIME_ID DESC) as c7
from
     TIMES T12710),
SAWITH19 AS (select Case when case SAWITH18.c6 when 1 then SAWITH18.c4 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH18.c6 when 1 then SAWITH18.c4 else NULL end  ASC NULLS LAST ) end as c1,
     Case when case SAWITH18.c7 when 1 then SAWITH18.c4 else NULL end  is not null then Rank() OVER ( PARTITION BY SAWITH18.c3 ORDER BY case SAWITH18.c7 when 1 then SAWITH18.c4 else NULL end  ASC NULLS LAST ) end as c2,
     SAWITH18.c3 as c3,
     SAWITH18.c4 as c4,
     SAWITH18.c5 as c5
from
     SAWITH18),
SAWITH20 AS (select distinct min(SAWITH19.c1) over (partition by SAWITH19.c3)  as c1,
     min(SAWITH19.c2) over (partition by SAWITH19.c3, SAWITH19.c4)  as c2,
     SAWITH19.c3 as c3,
     SAWITH19.c4 as c4,
     SAWITH19.c5 as c5
from
     SAWITH19),
SAWITH21 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4
from
     (select sum(T12697.AMOUNT_SOLD) as c1,
               T12662.PROD_DESC as c2,
               SAWITH20.c5 as c3,
               SAWITH20.c3 as c4,
               ROW_NUMBER() OVER (PARTITION BY T12662.PROD_DESC, SAWITH20.c3 ORDER BY T12662.PROD_DESC ASC, SAWITH20.c3 ASC) as c5
          from
               PRODUCTS T12662,
               SALES T12697,
               SAWITH17,
               SAWITH20
          where  ( T12662.PROD_ID = T12697.PROD_ID and T12697.TIME_ID = SAWITH17.c3 and SAWITH20.c1 = SAWITH17.c1 and SAWITH20.c2 = SAWITH17.c2 and SAWITH20.c4 = TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') )
          group by T12662.PROD_DESC, SAWITH20.c3, SAWITH20.c5
     ) D1
where  ( D1.c5 = 1 ) ),
SAWITH22 AS (select T12710.TIME_ID as c3,
     T12710.FISCAL_YEAR_ID as c4,
     ROW_NUMBER() OVER (PARTITION BY T12710.FISCAL_YEAR_ID ORDER BY T12710.FISCAL_YEAR_ID DESC) as c5,
     ROW_NUMBER() OVER (PARTITION BY T12710.FISCAL_YEAR_ID, T12710.TIME_ID ORDER BY T12710.FISCAL_YEAR_ID DESC, T12710.TIME_ID DESC) as c6
from
     TIMES T12710),
SAWITH23 AS (select Case when case SAWITH22.c5 when 1 then SAWITH22.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH22.c5 when 1 then SAWITH22.c3 else NULL end  ASC NULLS LAST ) end as c1,
     Case when case SAWITH22.c6 when 1 then SAWITH22.c3 else NULL end  is not null then Rank() OVER ( PARTITION BY SAWITH22.c4 ORDER BY case SAWITH22.c6 when 1 then SAWITH22.c3 else NULL end  ASC NULLS LAST ) end as c2,
     SAWITH22.c3 as c3,
     SAWITH22.c4 as c4
from
     SAWITH22),
SAWITH24 AS (select min(SAWITH23.c1) over (partition by SAWITH23.c4)  as c1,
     min(SAWITH23.c2) over (partition by SAWITH23.c4, SAWITH23.c3)  as c2,
     SAWITH23.c3 as c3
from
     SAWITH23),
SAWITH25 AS (select distinct SAWITH24.c1 + 1 as c1,
     SAWITH24.c2 as c2,
     SAWITH24.c3 as c3
from
     SAWITH24),
SAWITH26 AS (select T12710.FISCAL_YEAR_ID as c3,
     T12710.TIME_ID as c4,
     T12710.FISCAL_MONTH_ID as c5,
     ROW_NUMBER() OVER (PARTITION BY T12710.FISCAL_YEAR_ID ORDER BY T12710.FISCAL_YEAR_ID DESC) as c6,
     ROW_NUMBER() OVER (PARTITION BY T12710.FISCAL_YEAR_ID, T12710.TIME_ID ORDER BY T12710.FISCAL_YEAR_ID DESC, T12710.TIME_ID DESC) as c7
from
     TIMES T12710),
SAWITH27 AS (select Case when case SAWITH26.c6 when 1 then SAWITH26.c4 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH26.c6 when 1 then SAWITH26.c4 else NULL end  ASC NULLS LAST ) end as c1,
     Case when case SAWITH26.c7 when 1 then SAWITH26.c4 else NULL end  is not null then Rank() OVER ( PARTITION BY SAWITH26.c3 ORDER BY case SAWITH26.c7 when 1 then SAWITH26.c4 else NULL end  ASC NULLS LAST ) end as c2,
     SAWITH26.c3 as c3,
     SAWITH26.c4 as c4,
     SAWITH26.c5 as c5
from
     SAWITH26),
SAWITH28 AS (select distinct min(SAWITH27.c1) over (partition by SAWITH27.c3)  as c1,
     min(SAWITH27.c2) over (partition by SAWITH27.c3, SAWITH27.c4)  as c2,
     SAWITH27.c3 as c3,
     SAWITH27.c4 as c4,
     SAWITH27.c5 as c5
from
     SAWITH27),
SAWITH29 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4
from
     (select sum(T12697.AMOUNT_SOLD) as c1,
               T12662.PROD_DESC as c2,
               SAWITH28.c3 as c3,
               SAWITH28.c5 as c4,
               ROW_NUMBER() OVER (PARTITION BY T12662.PROD_DESC, SAWITH28.c5 ORDER BY T12662.PROD_DESC ASC, SAWITH28.c5 ASC) as c5
          from
               PRODUCTS T12662,
               SALES T12697,
               SAWITH25,
               SAWITH28
          where  ( T12662.PROD_ID = T12697.PROD_ID and T12697.TIME_ID = SAWITH25.c3 and SAWITH28.c1 = SAWITH25.c1 and SAWITH28.c2 = SAWITH25.c2 and SAWITH28.c4 = TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') )
          group by T12662.PROD_DESC, SAWITH28.c3, SAWITH28.c5
     ) D1
where  ( D1.c5 = 1 ) ),
SAWITH30 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4
from
     (select sum(T12697.AMOUNT_SOLD) as c1,
               T12662.PROD_DESC as c2,
               T12710.FISCAL_YEAR_ID as c3,
               T12710.FISCAL_MONTH_ID as c4,
               ROW_NUMBER() OVER (PARTITION BY T12662.PROD_DESC, T12710.FISCAL_MONTH_ID ORDER BY T12662.PROD_DESC ASC, T12710.FISCAL_MONTH_ID ASC) as c5
          from
               TIMES T12710,
               PRODUCTS T12662,
               SALES T12697
          where  ( T12662.PROD_ID = T12697.PROD_ID and T12697.TIME_ID = T12710.TIME_ID and T12697.TIME_ID = TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') and T12710.TIME_ID = TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') )
          group by T12662.PROD_DESC, T12710.FISCAL_MONTH_ID, T12710.FISCAL_YEAR_ID
     ) D1
where  ( D1.c5 = 1 ) ),
SAWITH31 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5,
     D1.c6 as c6,
     D1.c7 as c7,
     D1.c8 as c8
from
     (select case  when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH30.c2 is not null then SAWITH30.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH21.c2 is not null then SAWITH21.c2 when SAWITH29.c2 is not null then SAWITH29.c2 end  as c1,
               SAWITH30.c1 as c2,
               SAWITH29.c1 as c3,
               SAWITH21.c1 as c4,
               SAWITH13.c1 as c5,
               SAWITH6.c1 as c6,
               case  when SAWITH21.c3 is not null then SAWITH21.c3 when SAWITH30.c3 is not null then SAWITH30.c3 when SAWITH29.c3 is not null then SAWITH29.c3 when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 end  as c7,
               case  when SAWITH29.c4 is not null then SAWITH29.c4 when SAWITH13.c4 is not null then SAWITH13.c4 when SAWITH21.c4 is not null then SAWITH21.c4 when SAWITH6.c4 is not null then SAWITH6.c4 when SAWITH30.c4 is not null then SAWITH30.c4 end  as c8,
               ROW_NUMBER() OVER (PARTITION BY case  when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH30.c2 is not null then SAWITH30.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH21.c2 is not null then SAWITH21.c2 when SAWITH29.c2 is not null then SAWITH29.c2 end , case  when SAWITH21.c3 is not null then SAWITH21.c3 when SAWITH30.c3 is not null then SAWITH30.c3 when SAWITH29.c3 is not null then SAWITH29.c3 when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 end , case  when SAWITH29.c4 is not null then SAWITH29.c4 when SAWITH13.c4 is not null then SAWITH13.c4 when SAWITH21.c4 is not null then SAWITH21.c4 when SAWITH6.c4 is not null then SAWITH6.c4 when SAWITH30.c4 is not null then SAWITH30.c4 end  ORDER BY case  when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH30.c2 is not null then SAWITH30.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH21.c2 is not null then SAWITH21.c2 when SAWITH29.c2 is not null then SAWITH29.c2 end  ASC, case  when SAWITH21.c3 is not null then SAWITH21.c3 when SAWITH30.c3 is not null then SAWITH30.c3 when SAWITH29.c3 is not null then SAWITH29.c3 when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 end  ASC, case  when SAWITH29.c4 is not null then SAWITH29.c4 when SAWITH13.c4 is not null then SAWITH13.c4 when SAWITH21.c4 is not null then SAWITH21.c4 when SAWITH6.c4 is not null then SAWITH6.c4 when SAWITH30.c4 is not null then SAWITH30.c4 end  ASC) as c9
          from
               (
                    (
                         (
                              SAWITH6 full outer join SAWITH13 On SAWITH6.c2 = SAWITH13.c2 and SAWITH6.c4 = SAWITH13.c4) full outer join SAWITH21 On SAWITH21.c2 = case  when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 end  and SAWITH21.c4 = case  when SAWITH6.c4 is not null then SAWITH6.c4 when SAWITH13.c4 is not null then SAWITH13.c4 end ) full outer join SAWITH29 On SAWITH29.c2 = case  when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH21.c2 is not null then SAWITH21.c2 end  and SAWITH29.c4 = case  when SAWITH6.c4 is not null then SAWITH6.c4 when SAWITH13.c4 is not null then SAWITH13.c4 when SAWITH21.c4 is not null then SAWITH21.c4 end ) full outer join SAWITH30 On SAWITH30.c2 = case  when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH21.c2 is not null then SAWITH21.c2 when SAWITH29.c2 is not null then SAWITH29.c2 end  and SAWITH30.c4 = case  when SAWITH6.c4 is not null then SAWITH6.c4 when SAWITH13.c4 is not null then SAWITH13.c4 when SAWITH21.c4 is not null then SAWITH21.c4 when SAWITH29.c4 is not null then SAWITH29.c4 end
     ) D1
where  ( D1.c9 = 1 ) )
select SAWITH31.c1 as c1,
     SAWITH31.c2 as c2,
     SAWITH31.c3 as c3,
     SAWITH31.c4 as c4,
     SAWITH31.c5 as c5,
     SAWITH31.c6 as c6
from
     SAWITH31
order by c1

This is one massive query. That too this is for a very simple report. Imagine the kind of queries that we might start seeing in more complex reports.

To simplify this, we start with creating a report where all time-shifting will be achieved through a combination of FILTER functions and presentation variables. For example, in the above report to look at Prior Year values we will use the function given below

FILTER(Sales."Amount Sold"
USING
(Times."Time ID" =
TIMESTAMPADD(SQL_TSI_YEAR,-1,timestamp '@{Day}{2009-01-01 00:00:00}')))

Similarly for attaining Year to Date, we use the following function

FILTER(Sales."Amount Sold"
USING
(Times."Time ID" BETWEEN
TIMESTAMPADD(SQL_TSI_DAY, -1 * DAYOFYEAR(timestamp '@{Day}{2009-01-01 00:00:00}'),
timestamp '@{Day}{2009-01-01 00:00:00}')
AND timestamp '@{Day}{2009-01-01 00:00:00}'))

As you see all that we are doing here is, we are applying a between filter on Time for that specific measure from start of the year to the current date.

And further, in the filter of the report we will be applying a filter shown below. Basically the filter for the full report will span the maximum time span of any one of the measures.

Times."Time ID"
BETWEEN
TIMESTAMPADD(SQL_TSI_DAY, -1 * DAYOFYEAR(timestamp '@{Day}{2009-01-01 00:00:00}'),
timestamp '@{Day}{2009-01-01 00:00:00}')
AND timestamp '@{Day}{2009-01-01 00:00:00}'
OR Times."Time ID" =
TIMESTAMPADD(SQL_TSI_YEAR,-1,timestamp '@{Day}{2009-01-01 00:00:00}')

Day is the presentation variable that is set from the prompt. Lets look at the final query generated by our report above

select T12662.PROD_DESC as c1,
     sum(case  when T12710.TIME_ID = TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') then T12697.AMOUNT_SOLD end ) as c2,
     sum(case  when T12710.TIME_ID = ( CAST(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') as DATE)  + -7 ) then T12697.AMOUNT_SOLD end ) as c3,
     sum(case  when T12710.TIME_ID = ADD_MONTHS(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS'), -1) then T12697.AMOUNT_SOLD end ) as c4,
     sum(case  when T12710.TIME_ID = ADD_MONTHS(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS'), -1 * 12 ) then T12697.AMOUNT_SOLD end ) as c5,
     sum(case  when T12710.TIME_ID between ( CAST(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') as DATE)  + TO_NUMBER(TO_CHAR(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS'), 'D'), '99') * -1 ) and TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') then T12697.AMOUNT_SOLD end ) as c6,
     sum(case  when T12710.TIME_ID between ( CAST(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') as DATE)  + TO_NUMBER(TO_CHAR(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS'), 'dd'), '99') * -1 ) and TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') then T12697.AMOUNT_SOLD end ) as c7,
     sum(case  when T12710.TIME_ID between ( CAST(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') as DATE)  + TO_NUMBER(TO_CHAR(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS'), 'DDD'), '999') * -1 ) and TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') then T12697.AMOUNT_SOLD end ) as c8
from
     TIMES T12710,
     PRODUCTS T12662,
     SALES T12697
where  ( T12662.PROD_ID = T12697.PROD_ID and T12697.TIME_ID = T12710.TIME_ID and (T12697.TIME_ID in (ADD_MONTHS(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS'), -1 * 12 )) or T12697.TIME_ID >= ( CAST(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') as DATE)  + TO_NUMBER(TO_CHAR(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS'), 'DDD'), '999') * -1 )) and (T12697.TIME_ID in (ADD_MONTHS(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS'), -1 * 12 )) or T12697.TIME_ID <= TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS')) and (T12710.TIME_ID in (ADD_MONTHS(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS'), -1 * 12 )) or T12710.TIME_ID >= ( CAST(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') as DATE)  + TO_NUMBER(TO_CHAR(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS'), 'DDD'), '999') * -1 )) and (T12710.TIME_ID in (ADD_MONTHS(TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS'), -1 * 12 )) or T12710.TIME_ID <= TO_DATE('1999-05-05 00:00:00' , 'YYYY-MM-DD HH24:MI:SS')) )
group by T12662.PROD_DESC
order by c1

As you see this is a very simple query with only one pass to the fact table. Whereas the query above (using AGO and ToDate) had to do multiple passes and then it had to stitch them together in the outer query. As long as the report does not contain any time attributes in the column, this method can be used extensively to solve most time-series requirements.

Sign up for more like this.