OBIEE 11g Time Series Analysis against Microsoft Analysis Services Cube

Recently, I got an opportunity to explore the OBIEE integration with Microsoft Analysis Services. I came across some advantages and some limitations too. In our environment, we are using OBIEE 11g, which supports time-series analysis against Cubes. Even in case of OBIEE 10g, the functionality was supported after applying a patch from Oracle. So in this post, we'll have a look on two ways to achieve time series analysis against Cubes -

1. By using OBIEE functions (I'll use Ago for this post).
2. By using MDX lag function (Passing this from OBIEE's EVALUATE_AGGR).

Let's start with OBIEE AGO function -

I created a test cube, with just one dimension i.e. time, and some measures by using Microsoft BI Development Studio. The cube is shown below:


Figure 1 - Cube

I imported the cube in the Physical layer of the RPD, and then simply dragged it to the logical layer. In the logical layer I created a derived measure by using AGO function. The name of this derived column is 'Amount Previous Period':

Figure 2 - Logical Column for AGO

Although, the syntax for using Ago is -  Ago(<<Measure>>, <<Level>>, <<Number of Periods>>), but it can be used without specifying the <<Level>>, if we want to make it dynamic. In this way, it can be used with all the levels in the time dimension.

Now I want to create a simple analysis with just three columns - Year, Amount, and Amount Previous Period.

As you can see, its working as expected. Same analysis, but with Month (which is a chronological key in the Business Model) will look like below:

Below is the MDX which got generated for the above analyses:



//
With 


    set [Dim Date Test.Hierarchy3]  as '{[Dim Date Test.Hierarchy].[Monthseq].members}'
set [Axis1Set] as '{[Dim Date Test.Hierarchy3]}'
  
  member [Measures].[MS2] as 'SUM({ParallelPeriod([Dim Date Test.Hierarchy].[Monthseq], 1,[Dim Date Test.Hierarchy].currentmember)},[Measures].[Amount])'
  
select 
  {[Measures].[Amount],[Measures].[MS2]} on columns,
  NONEMPTY({[Axis1Set]},{[Measures].[Amount],[Measures].[MS2]}) on rows 
from 
  [Test]

//

As we can see, in the background its 'ParallelPeiod' which is doing the calculation for OBIEE.


Now let's have a look at the MDX lag function, and how to pass it from OBIEE using EVALUATE_AGGR:


Below is the logical column for this:

The hierarchy which is passed to  EVALUATE_AGGR, can be picked from the physical layer of the RPD. Currentmember makes it dynamic across levels. lag(1) takes it one period back.

Below is the same analysis for year, amount, and amount for prev period - lag

As we can see, its exactly the same result. Below is the MDX which got generated this time:

//

With 


    set [Dim Date Test.Hierarchy2]  as '{[Dim Date Test.Hierarchy].[Calendar Year].members}'
set [Axis1Set] as '{[Dim Date Test.Hierarchy2]}'
  
  member [Measures].[MS2] as '([Dim Date Test.Hierarchy].currentmember.lag(1), [Measures].[Amount])'
  
select 
  {[Measures].[Amount],[Measures].[MS2]} on columns,
   NONEMPTY({[Axis1Set]},{[Measures].[Amount],[Measures].[MS2]}) on rows 
from 
  [Test]
//

As we can see in the MDX, this time the function used is lag.

Both of these approaches seem equivalent as they tend to the same results. In case of using 'Ago' in OBIEE, we need a chronological key to be exposed from the cube, which may not always be the case in some environments. I prefer the way to do it in MDX since we have much control over it.

Hope this post will be useful to the readers. In the next post, I am planning to write about aggregations in Cube/OBIEE. The post can be found here. Thanks.