Business Intelligence – Oracle

Oracle BI EE 10.1.3.4 and Hyperion Essbase 11.1.1.0 – Ago and ToDate functions – Custom MDX and Evaluate_Aggr – Achieving Time based Shifting and Period To Date Calculations

Posted by Venkatakrishnan J on March 30, 2009

One of the few topics that i have not covered so far in my BI EE and Essbase connectivity series is the support of Ago and ToDate functions. As you would probably know, Ago and ToDate are BI EE rpd specific functions which can basically help in achieving Time based shifting, Period To Date kind of calculations. Depending on the underlying database, BI EE automatically function ships these 2 functions into the corresponding relational database’s sql queries. I have covered this before for a relational source here and here. Now with the support for Essbase data sources in BI EE, Ago and ToDate functions are function shipped automatically to MDX specific functions. Lets try to understand what these 2 functions do and how they work on an Essbase data source.

1. Ago – This function basically calculates the value of the measure where the Time member would actually be shifted by an index specified in the Function.
2. ToDate – This function basically calculates the Period To Date value for every intersection of the Time dimension. i.e if you have Jan, Feb in your report, the Period to Date would calculate Jan, Jan + Feb.

For the above functions to work, you need a dimension marked as Time in your Essbase outline. So, lets start with the Demo->Basic cube.

       

Now, go to the BMM of this imported Essbase cube in the BI EE repository. Mark Year as the Time Dimension and also set the chronological key.

       

       

Once this is done, go to the fact table and create a custom column. This custom column would basically be used for doing periodic shift of prior 2 months. In the column enter the formula as shown below

Ago("Demo"."Basic"."Actual" ,  "Demo"."Year"."Gen3,Year" , 2)

       

Now, include this column in the presentation layer and create a report as shown below. Ensure that the grain of the report matches the Ago function.

       

As you see, the Ago function automatically creates a custom metric calculating the value of the metric by shifting the month by 2 for every time member. Lets look at the MDX query.

       

With
set [Year3] as '[Year].Generations(3).members'
member [Scenario].[MS1] as '(ParallelPeriod([Year].[Gen3,Year],2,[Year].currentmember),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[Actual], [Scenario].[MS1] } on columns,
NON EMPTY {{[Year3]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Demo.Basic]

As you see above, the Ago function is automatically function shipped to an MDX function called ParallelPeriod. The major drawback with Ago is that it hardcodes the ParallelPeriod function and hence all the capabilities of the ParallelPeriod function is not leveraged (like Hierarchy filters etc). If those are required, then Evaluate would have to be used. We shall see how this can be done later. Now that we are clear on how Ago works, lets move on to ToDate function. Go to the fact logical table in the BMM and add a custom column. In the custom column enter the formula shown below

 TODATE(Demo.Basic.Actual, Demo."Year"."Gen2,Year")

       

       

If we look at the MDX query generated, you would notice that ToDate is automatically function shipped to PeriodsToDate MDX function.

With
set [Year3] as '[Year].Generations(3).members'
member [Scenario].[MS1] as 'AGGREGATE({PeriodsToDate([Year].[Gen2,Year],[Year].currentmember)},Scenario.[Actual])', SOLVE_ORDER = 100
member [Scenario].[MS2] as '(ParallelPeriod([Year].[Gen3,Year],2,[Year].currentmember),Scenario.[Actual])', SOLVE_ORDER = 100
member [Scenario].[MS3] as 'Rank([Year].Generations(3).dimension.currentmember,[Year].Generations(3).dimension.members)'
select
{ [Scenario].[Actual], [Scenario].[MS1], [Scenario].[MS2], [Scenario].[MS3] } on columns,
NON EMPTY {{[Year3]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Demo.Basic]

If you want to achieve the same functionality of Ago and Todate without actually using them, use them as Evaluate functions, as shown below

EVALUATE_AGGR('AGGREGATE({PeriodsToDate([Year].[Gen2,Year],%1.dimension.currentmember)},Scenario.[Actual])' , "vejanaki-lap.in.oracle.com"."Demo".""."Basic"."Gen3,Year")
EVALUATE_AGGR('(ParallelPeriod([Year].[Gen3,Year],2,%1.dimension.currentmember),Scenario.[Actual])',"vejanaki-lap.in.oracle.com"."Demo".""."Basic"."Gen3,Year")

       

Its good that BI Server automatically function ships these functions back to Essbase in the form of MDX. But the drawback as noted above is the fact that one does not have the flexibility to support all the parameters of MDX. This hopefully should be available in the coming releases.

7 Responses to “Oracle BI EE 10.1.3.4 and Hyperion Essbase 11.1.1.0 – Ago and ToDate functions – Custom MDX and Evaluate_Aggr – Achieving Time based Shifting and Period To Date Calculations”

  1. Hi Venkat,

    You beat me to it. Just was about to blog on that. Well off to topic 2 in that case😉

    Cheers,
    Christi@n

  2. Colin said

    If I’ve two dimensions – Year and Period(Quarter, month,date),how can I use these functions?

  3. Collin,

    Both dimensions need to be set up as time dimensions. Then you can use Ago and ToDate on both of them.

    Cheers,
    C.

  4. Eileen said

    can AGO and TODATE be used together? like this:
    AGO(TODATE(citic2.citic.GP, citic2.Dim_Time.”Gen3,Dim_Time”), citic2.Dim_Time.”Gen3,Dim_Time”, 1)

    The aim is to get last month’s accumulative measure.

    I tested it but there was essbase error.

  5. Rao said

    Hi Venkat,

    I am getting obsolete error in consistency check for both columns using Evaluate_Agg function. I checked both in 10.1.3.4 and 10.1.3.4.1 versions(same error).

    have they became obsolete in new versions?

    Thanks

    Rao

  6. Gowtham said

    Hello,

    my scenario is
    display last 4 months from selected month of year.

    example is selected month is — mar 2009

    requirement is — nov 2008,dec 2008,jan 2009,fib 2009

    so,they used below code in a column at criteria in answers in obiee
    ————————–
    case when ( @{var_period}{4}<4 and ( ( V_ProfitLossHead.yr =@{var_year}{2009} and V_ProfitLossHead.period (12-(4-@{var_period}{4})) ) ) ) or ( @{var_period}{4}>3 and ( ( V_ProfitLossHead.yr =@{var_year}{2009} and V_ProfitLossHead.period >=@{var_period}{4}-3 and V_ProfitLossHead.period <@{var_period}{4}+1 ) ) ) then 0 else 1 end

    —————————–

    now,i want to use this in repository to reduce the time of loading.
    and generally how to do the better performence tuning in obi using ms sql server

    i need help for this,so reply as soon as possible.

    thank you obi intelligence.

    thank you

  7. Usman said

    Respectable!

    I am new to OBIEE. It becomes a challenge for me to pass variables from dashboard prompts and to catch their value in Answer’s Request.

    I would like to illustrate it with an example.
    Request based on the following SQL statement

    Select NAME, AGE, CITY, ADDRESS
    From emp
    Where DOB = ‘@{p_date}’

    Now DOB is in TIMESTAMP format,
    it looks like ‘11/1/2009 12:00:00 AM’.

    Dash board Prompt also depends upon the same column and have also same TIMESTAMP format.

    In next step
    I create a dashboard page
    Place prompt and report there
    Saved the dashboard
    Now I select a date from prompt list and press go button
    It shows no result.

    I tried several combinations in @1 syntax e.g.

    Where DOB = ‘@{p_date}’
    Where DOB = ‘@{p_date}{1-nov-2009} ’
    Where DOB = cast (@{p_date} as date)
    Where DOB = cast(to_char(@{p_date}, ‘dd-mmm-yyyy’) as date)
    …………………….
    And many other combinations

    I am looking for any help/tips in this regard

    Thanking in anticipation

    usman fawad

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: