Time accumulations

building semantic layer
1

X-cc: Eric_Sidharta@amp.com.au, Amanda_Eggins@amp.com.au

Background :
One of the main dimension periods we use is week (1 to 52) on which we report a number of measures.

We have a requirement to produce a “moving 4 weekly average” time dimension on a report
(Basically wk4 = (wk1+wk2+wk3+wk4)/4 … wk52 = (wk49+wk50+wk51+wk52)/4.)

Initially I created a time object which generated sql to accumulate the weeks based on an alias of our time table i.e.

Select week_num Wk_Mvg_4_Acc, Sum(Amt)

From tbl.Time Mvg_4,
tbl.Time Normal
tbl.fact
Where Fact.time_key = Normal.Time_key
and Normal.Year_num = Mvg_4.Year_num
and Normal.Week_num between (Mvg_4.Week_num - 3) and Mvg_4.Week_num
and Mvg_4.Week_num > 3

Which worked fine against small fact tables and even large fact tables (with heavy conditioning),

HOWEVER once you start using this object against large fact tables (with little conditioning, ie hitting most rows) Oracle performance really starts becoming a significant performance factor

Question 1.
Has anybody thought of a smarter way of approaching this type of object via designer (I’ve already used short cut joins), Same goes for YTD and rolling 12 month accumulation objects. We decided against pre-accumlating measures in the database as they are “semi-additive”.
I realise I would like to do the accumulation on a returned result set, but don’t know how to do this (I do have full support from DBA’s, PLSQL contractors & the like)

Question 2.
Although the primary distribution of information is via the Webi Tool, we do have a few Full client reports for our portfolio style reporting. We use the pre-built “RunningSum” function to do our YTD accumulations reporting, however I haven’t worked out a way to calculate this more specific formula

Environment :
Business Objects 4.1.5
Oracle 8.04

Thanks for your attention

Simon

Listserv Archives (BOB member since 2002-06-25)

2

Simon,

In moments like these, I defer to decode, no alias, no multiple contexts, as Oracle performance seems to prefer this to multiple passes through the database.

I have an extract from a Technical Paper I put together a little while ago, which I’ll send under separate email direct to you; this should explain how to set this up. Meanwhile, I’ve included hereunder a sample SQL utilising the sign() function. This object retrieves the Moving Annual Turnover for Last Year.

SELECT
sum(decode(sign((@Prompt(‘2. Select Year’,‘N’,‘TIME SERIES\Year’,)-2)*100

  • @Prompt(‘1. Select Month’,‘N’,‘TIME SERIES\Month #’,) - table.FINANCIAL_PERIOD), -1,
    decode(sign(table.FINANCIAL_PERIOD
    -((@Prompt(‘2. Select Year’,‘N’,‘TIME SERIES\Year’,)-1)*100 + @Prompt(‘1. Select Month’,‘N’,‘TIME SERIES\Month #’,)+1)), -1, nvl(table.value,0),0),0))
    WHERE
    table.FINANCIAL_PERIOD
    BETWEEN ((@Prompt(‘2. Select Year’,‘N’,‘TIME SERIES\Year’,)-2)*100 + 1) AND (@Prompt(‘2. Select Year’,‘N’,‘TIME SERIES\Year’,)*100 + @Prompt(‘1. Select Month’,‘N’,‘TIME SERIES\Month #’,))

The WHERE is imperative to limit the search and void a table scan … makes all the difference to the performance of retrieval.

If you need further info, please advise.

Regards,
Ang.

Listserv Archives (BOB member since 2002-06-25)