Oracle Business Intelligence Applications

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Business Intelligence Applications

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Nesting PERIODROLLING in OBI

Received Response
3
Views
5
Comments
3182650
3182650 Rank 2 - Community Beginner
in Oracle Business Intelligence Applications

  

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.

Answers

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    Use of information drive physical data model design ... AND always design for performance.  If you hare forced to nest logical functions like this you have a bad physical design given the requirements.  You can either go the route of creating a fact table that has all this pre-calculated or you could re-up your date dimension table to have the keys for these agos on same row as the date ... then you model the ago via joins.

    To sum up quarters you don't need nested ago functions ...

  • 3182650
    3182650 Rank 2 - Community Beginner

    Thanks for your reply.

    I will look into the design.

    But is it possible to nest PERIODROLLING?

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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?  <strong>AGO("License Revenue"."Revenue (Constant Dollar)",2)</strong>  <-- 2 periods AGO; based on the granularity of the query when at the quarter grain

  • 3182650
    3182650 Rank 2 - Community Beginner

    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
    Thomas Dodds Rank 8 - Analytics Strategist

    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.