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

n 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

1. 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.
2. 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

1. 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.

2. 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.

in

OBI EE, Time Dimensions and Time-Series Calculations

I’m currently working on a paper on Oracle BI Suite Enterprise Edition (OBI EE) for Discoverer users, for the upcoming ODTUG conference at Daytona Beach, and a tricky area that I’ve come up against recently is getting the metadata layer in a state suitable for performing time-series queries. One of the strengths of Discoverer is it’s support for analytic and time-series (lag, lead, window etc) queries and any customer looking to migrate to Answers and Interactive Dashboards would want to make these new tools have similar capabilities.

Taking a look through the OBI EE documentation, it appears there is some basic support for time-series queries, in the form of AGO and TODATE. AGO gives you, for example, the value of sales one month ago or one quarter ago, whilst TODATE gives you the total of sales month-to-date, or quarter-to-date, or year-to-date. Both of these time-series functions rely on the existence of a time dimension in your business model and mapping layer, with the period you can refer to in the AGO and TODATE functions being determined by what levels you have in this time dimension. I found, however, the time dimension a bit tricky to set up, but now I’ve got it working, I thought I’d jot down some notes on how the process works.

As a quick recap: dimensions in OBI EE define drill paths in your data, and are a prerequisite if you want uses to be able to drill down from say all customers, to region, then warehouse and finally ship to.

Each level has at least one logical key, the top level is marked as the “grand total” level, and you can drag other attributes in to each level and have OBI EE display those, rather than the level key, when users drill down in reports. At present, you have to create these dimensions manually when setting up your business model and mapping layer, although there is talk about automatically creating these when importing Oracle dimensions or OLAP structures, or when using the soon-to-be-delivered Discoverer migration utility. In my case though, I create them all manually which is usually a five or ten minute job at most.

For the migration paper I’m working on for the ODTUG event, I’m bringing across the Videostore example dataset that comes with Discoverer Administrator, on the basis that everyone running Discoverer has got it and most people are familiar with its contents. To keep things simple, I’m initially working with the main sales fact table, SALES_FACT and the three dimension tables it links to, TIMES, PRODUCT and STORE. One additional step I had to carry out though to get everything working was to create two additional views in the VIDEO5 schema:

A view over the TIMES table to add additional month and quarter attributes that are used to display month and quarter names, and to permit their sorting (like alternate sorts in Discoverer)

create or replace view times_view
       (time_key
,      transaction_date
,      day_of_week
,      holiday_flag
,      month_mon_yyyy
,      month_id_yyyy_mm
,      year_yyyy
,      quarter_q_yyyy
,      quarter_id_yyyyq)
AS
       select time_key
,      transaction_date
,      day_of_week
,      holiday_flag
,      substr(to_char(transaction_date,'MON'),1,1)
       ||lower(substr(to_char(transaction_date,'MON-YYYY'),2))
       as month_mon_yyyy
,      to_char(transaction_date,'YYYYMM') as month_id_yyyy_mm
,      to_char(transaction_date,'YYYY') as year_yyyy
,      'Q'||to_char(transaction_date,'Q-YYYY')
       as quarter_q_yyyy
,      to_char(transaction_date,'YYYY')
       ||to_char(transaction_date,'Q') as quarter_id_yyyyq
from   times;

And a view over the SALES_FACT table to allow it to be joined to the TIMES_VIEW view on the transaction_date column (a date datatype) rather than the TIME_KEY column (a number datatype) – this is because I found I needed to make the primary key for the logical TIMES table a date, rather than a number, to get the time-series offset calculations working:

create or replace view sales_fact_view
      (cost
,      customer_count
,      product_key
,      profit
,      sales
,      store_key
,      transaction_date
,      unit_sales)
as
select cost
,      customer_count
,      product_key
,      profit
,      sales
,      store_key
,      transaction_date
,      unit_sales
from   sales_fact, times
where  sales_fact.time_key = times.time_key;

Theoretically, at least the view over the time dimension shouldn’t be necessary, as you can create virtual (calculated) columns within OBI EE, but when I tried to do this, although the columns created OK I had trouble using them with the AGOand TODATE functions; it may also be the case that you can use non-date columns for the time dimension primary key, but again I had problems getting this working. What I’m showing here is what eventually worked for me, but feel free obviously to try and build things without these views, and let me know if you get it working.

Anyway, once the views are created, I then imported them in to the physical layer of the common enterprise information mode, created a primary key key on the TIMES_VIEW.TRANSACTION_DATE column, and foreign keys on theSALES_FACT_VIEW.PRODUCT_KEY, TRANSACTION_DATE and STORE_KEY columns, linking back to the relevant tables/views. Once this was done, I dragged the whole set of Videostore tables and views over to the business model and mapping view to create a set of logical tables, like this:

After running the rename utility to put all the column and tables names in to sentence case, remove the underscores and so on, it was time to create the time dimension, which I called TimesDim.

Creating a time dimension is initially the same as creating a regular dimension, except on the dimension properties dialog you tick the “Time dimension” tickbox, like this:

Then, I created five levels for the dimension:

1. All Years
2. Year
3. Quarter
4. Month
5. Day

and then dragged the TRANSACTION_DATE column from the TIMES_VIEW logical table on to the day level, the MONTH_IDYYYYMM column on to the month level, the QUARTER_ID_YYYYQ on to the quarter level, and left the year level empty as it’s a grand total level. For the day, month and quarter levels, the columns dragged on to them were registered as logical level keys and also as the primary key for each level – the rationale here being that in DATE, YYYYMM and YYYYQ format, column values were sortable which is a requirement for performing rolling-window time-series calculations.

Next, to finish off the dimension, I dragged the MONTH_MON_YYYY column on to the month level, added it as a level key but kept the previous value as the primary key, like this:

I then edited each level key and ensured the “Use for drilldown” checkbox was unchecked for the YYYYMM value, but checked for the second, MON-YYYY value, so that the correct column was used for organizing the level values but users saw friendly looking month names when they drilled into time dimension values.

After doing the same for the Quarter dimension, I then went back to the day, detail level and carried out one more task, and the one that’s essential for a time dimension – marking one of the logical keys as being the Chronological Key. In my case, I marked the key created using the TRANSACTION_DATE column as being the Chronological key, which worked for me.

According to the docs, the chronological key has to be a “monotonically increasing value” which stumped me a bit; I tried using just a number field but I couldn’t get the time-series queries to output the correct values for month ago sales and so on, but this could more be down to my unfamiliarity with the feature – again, if you know better, let me know.

Once everything was done, and I added the other detail day-level attributes into the day level, my dimension looked like this:

with each level containing the following columns:

1. All Years Level (grand total level) – no keys or columns added
2. Year Level – YEAR_YYYY column, set as primary key, set as logical key
3. Quarter Level – QUARTER_ID_YYYYQ set as primary key and logical key but with “use for drilldown” disabled, together with QUARTER_Q_YYYY set as a logical key and enabled for drilldown
4. Month Level – MONTH_ID_YYYYMM set as primary key and logical key but with “use for drilldown” disabled, together withMONTH_MON_YYYY set as a logical key and enabled for drilldown
5. Day Level – TRANSACTION_DATE set as primary key and logical key and enabled for drilldown, DAY_OF_WEEK andHOLIDAY_FLAG set as attributes and not used for drilldown.

The next step was to tell OBI EE to sort the MONTH_MON_YYYY column by the MONTH_ID_YYYYMM column, and the QUARTER_Q_YYYYcolumn by the QUARTER_ID_YYYYQ column, as whilst the latter columns were more readable (“Q3 2000”,”Jun-1999” and so on) they don’t sort properly by default (the year would start “Apr-1998”, followed by “Aug-1998” and so on). To do this, I double-clicked on the columns to be sorted and picked the other columns as the one to sort them by, like this:

Now the time dimension and logical table were set up, I could create my time-series functions. To start off, I used the AGOfunction to calculate the sum of sales one month ago, based on the SALES_FACT.SALES column, using the following formula:

AGO(Videostore."Sales fact view".Sales, Videostore.TimesDim."Month", 1)

…with "Sales Fact View".Sales being the base measure, Videostore.TimesDim.Month indicating that within our time dimension, we’re going to use months as the offset, and 1 indicating that we’re going back 1 month.

So, now it’s time to test it out. After dragging all the logical columns to the presentation layer and saving the changes to the repository, I started up Oracle BI Answers and ran a query showing sales month-by-month with last months sales next to them, like this:

Not bad. Running it at the day level gave similar results.

Using the expression editor, I created another logical column, this time using the TODATE function to create month-to-date values, using the expression:

ToDate("Videostore"."Sales fact view"."Sales" , "Videostore"."TimesDim"."Month" )

with "Sales fact view"."Sales" being the base measure, and "Videostore"."TimesDim","Month" indicating we’re going a month-to-date (as opposed to, say, year-to-date) total.

Then, running the day-level report created a moment ago, along with this new month-to-date attribute, gave the following results:

Looking back, the tricky bits were firstly, adding all the derived month and quarter values for the times logical table, which I tried doing using the EXTRACT and CAST features of BI Server SQL, but in the end found much easier just deriving as part of an Oracle view. The second tricky part was working out that the chronological key was only defined at the bottom (day) level, and that it should be a date datatype – this might be wrong, but it’s the only way I got it working. The final tricky bit was sorting the month and quarter descriptive columns by the YYYYMM and YYYYQ columns – without these, either the months and quarters were listed in the wrong order, or worse, they were in the right order but the time-series calculations were sometimes wrong. Going down the route I’ve outlined in this article made it work for me, but as I say if there’s a simpler way just let me know.

i