OBIEE – Time Series Functions

I was asked today how to implement Time Series functions in OBIEE, namely Ago and ToDate.  This blog simply takes you through the steps of implementing the ToDate function and points out the hazards on the way; although I am pretty sure this stuff is already documented.  The Ago is implemented in much the same way.  I plan to publish a discussion on the various methods available for implementing time series functions and this blog will act as a good precursor to that.
 Please ensure at this point that the Date Dimension has a hierarchy defined that is defined as a time dimension and has a key defined at each level as a chronological key.  If you are unsure about verify this then please see my blog on the obiee date dimension.

The function Itself

The base fact, in this case Amount, must be defined in the Logical Fact Table.  Create a new logical column that uses an existing logical column as the source as shown below.

Time Series Logical Column

The syntax for the ToDate function is specified as TODATE(Logical Base Measure, Date Hierarchy Level).  In this example we have created a logical column that will sum the Amount Column by the Month level of the Date Dimension.

Writing the Request

If we create a request in the usual way you can see that for the date shown, and based on the data in the fact table, the Amount column shows the data for the specified date, whilst the MTD column shows the Month To Date value despite the previous dates being excluded by the filter.

The Result in Answers

It really is as simple as that.  If your implementation is not working in the same fashion then more than likely your time dimension is not set correctly;