Nesting PERIODROLLING in OBI

Question

I'm trying to nest PERIODROLLING time series functions in OBIEE 11g. The same can be done with AGO

Example Ago:

AGO(AGO("License Revenue"."Revenue (Constant Dollar)",1),1)

Purpose : Find revenue of 1 quarter back using inner AGO. Then find 1 more quarter back (i.e. 2 quarter back).

Example Period Rolling:

PERIODROLLING(PERIODROLLING("License Revenue"."Revenue (Constant Dollar)", -3,0),-1,-1)

Purpose: Find sum of last 4 quarters. Then find the revenue of 5th quarter from current quarter.

Error Received:

[nQSError: 42023] Illegal aggregate nesting in query. (HY000)

Reason why am doing this: We will have nested columns. I.e. in one analysis one will use the inner PEROIDROLLING in col 'A' and in the other analysis we will have column 'B', which will be the outer PERIODROLLING on column 'A'.

We currently have AGO columns nested, but we needed multiple AGO functions to sum up quarters. Which is hampering performance. We would like to use PERIODROLLING as it fires single SQL to find sum of multiple quarters.

Thomas Dodds · Answer

Users who are using Answers should be well trained ... else you'll have people building stuff that doesn't work or doesn't perform well which affects the quality of your BI Portfolio.

The advanced calculations that users need should be built in the RPD or if possible pushed back to the database.  Front end manipulation of data is the most flexible, but it is the least performing; conversely calculated values stored are the best performing but are the least flexible.  Seek a balance between performance and flexibility.

3182650 · Answer

Ok, so as you're saying its not possible to nest 2 PERIODROLLING functions.

Yes it is always a better option to use one AGO instead of nested functions.

These nested ones are being created by users and am new to OBIEE so I had to check if this was even possible.

So that I can tell them how to proceed.

Thanks for your reply.

Thomas Dodds · Answer

I think the error you get is telling you NO ... the timeseries functions should need to be nested to achieve what you are trying to do. AGO(AGO("License Revenue"."Revenue (Constant Dollar)",1),1) why not? AGO("License Revenue"."Revenue (Constant Dollar)",2) <-- 2 periods AGO; based on the granularity of the query when at the quarter grain