1 Reply Latest reply: Nov 13, 2012 3:31 PM by tlue2 RSS

    Month Range Selection in OBIEE against Essbase

    user126573
      OK. I am pretty knowledgeable on the Hyperion side, but new to the OBIEE world so I am looking for some major assistance in getting this to work.

      We currently have an Essbase outline that is built with the Year and Periods combined like the below example from 2001 - 2015:

      YrPeriod (generation 1)
      -----2011 (generation 2)
      ----------201101 (generation 3)
      ----------201102
      ----------201103
      ----------201104
      ----------201105
      ----------201106
      ----------201107
      ----------201108
      ----------201109
      ----------201110
      ----------201112
      -----2012
      ----------201201
      ----------201202
      ----------201203
      ----------201204
      ----------201205
      ----------201206
      ----------201207
      ----------201208
      ----------201209
      ----------201210
      ----------201211
      ----------201212

      ETC...

      What we are trying to do in OBIEE is build a dashboard that shows multiple reports (analysis) that are graphing 25 months of data (customer request). I have tried to:

      1. use the dashboard prompt to select a Start_Month and a prompt for the End_Month. These were selecting the Gen3 members of the YrPeriod dimension and saving to a Presentation variable. This allows the users to select from the dropdown list all the Gen3 members of the dimension which I want. Those presentation variables where then selected in the BETWEEN filter for the Analysis on the YrPeriod dimension. HOWEVER, it seems to only select the first member in the range which is the START_MONTH presentation variable. Not sure if this approach will work or not.

      SO I tried to do it a different way.

      2. I created Session_Variables, but I am not seeing a way to use them in the BETWEEN filters on the Analysis for YrPeriod dim. It only allows you to select Presentation variables or Request Variables.The only way I have gotten them to work was to add a sql on the Initialization block for the session variable:
      One FOR THE START MONTH - select to_char(add_months(sysdate,-25),'yyyymm') start_month from dual ;
      AND THE END_MONTH - select to_char(add_months(sysdate,-1),'yyyymm') Ending_Month from dual AND) .

      When added to the BETWEEN filter this works in getting the full 25 month range!
      HOWEVER, I would like to give the users the ability to change the run period range and not be stuck with just the system date.

      Ideally, I would like to have just 1 prompt for the Run period and have it update the range based on the selection.

      I hope I have given you all enough info to let you know what we are trying to do and what I have tried to this point. Hopefully this is something relatively simple and I am just missing it completely!

      Thank you in advance for any and all your help with this!

      Robert

      Edited by: user627522 on Nov 12, 2012 3:08 PM
        • 1. Re: Month Range Selection in OBIEE against Essbase
          tlue2
          A web search for "OBIEE rolling months" comes back with i.e. this one: http://shivabizint.wordpress.com/2008/09/19/rolling-months-data-for-year-month-prompt-in-obiee/
          or this one: http://obieetutorialguide.blogspot.ie/2012/02/modeling-time-series-function-in-obiee.html

          In earlier Siebel days, one also used to define multiple alias in OBIEE physical layer for DIM_DATE with different joins to different months. I needed to go this route in a project where we had MS-SQL as the back end where the at that time relatively new AGO and TODATE were performing very badly.

          But now to solve your problem at hand:
          In the very latest release the new OBIEE function PERIODROLLING was introduced. Not sure though how this is function shipped to Essbase, if at all.
          You can read some more details about it i.e. here: http://gerardnico.com/wiki/dat/obiee/function_time
          Thanks for letting us know if it works at all with Essbase in physical layer and if it works, what query is actually being sent to Essbase.