Nothing better than a “light” topic as warm-up to start the new year : OBIEE Time Series functions (inspired by a thread on OTN forum of few days ago). Actually, it was a “light” topic and started growing while writing …

The thread on OTN I had in mind when writing this post was a question about how to use time series functions without having a date column. The poster had a case with only months and weeks but no daily data, so missing a real date (day) column in the database.


Spoiler Alert

OBIEE doesn’t use or need any time/date function for Time Series, you can use these functions on any kind of data as long as you define chronological keys to be used to sort data.

Time Series functions: a “must have” for an analytical platform

I still didn’t see a single OBIEE model without implementing time series functions soon or late, using the embedded functions or modelling them directly in the RPD or producing the data in the data source by ETL.

Any business need to compare data at a given point in time with the same point in time a month or a year before, or to sum together all the current months of the year to see how close to a yearly forecasted value the reality is. It is also quite common to business rules not using a fixed January 1 to December 31 calendar: the business of an organization doesn’t finish at the end of the year to restart from scratch the day after with the new year, so looking at data by adopting period rolling measures it’s a real life need.

All these are what OBIEE calls Time Series functions: AGO, TODATE and PERIODROLLING.

How does Time Series functions work in OBIEE?

To explain how OBIEE calculate Time Series functions let’s take a really simple example: 2 tables, one is a calendar dimension and the other is a fact table with amount sold and quantity sold, the 2 tables are linked with FK_CALENDAR from the fact table linking to PK_CALENDAR int the dimension.

The example is: month by month, I want to see amounts sold and previous month amounts sold next to it.

When selecting the correct chronological key the result is “as expected”

The result is a simple table with 3 columns: Month, Amount sold and AGO(Amount sold, “Month”, 1). This last column has a formula in the analysis directly using the AGO function: AGO(“Fact Sales”.”Amount sold”, “Dim Calendar”.”Hierarchy Calendar”.”Month”, 1) :

  • “Fact Sales”.”Amount sold” : is the measure I want to retrieve in the past
  • “Dim Calendar”.”Hierarchy Calendar”.”Month” : is the logical level name of the time dimension in the RPD (not the month column, but the logical level name of the hierarchy, the UI in the OBIEE front-end doesn’t allow you to select this value in the formula editor window, so you must take note of the name before)
  • 1 : is the number of steps to move backward (positive number) or forward (negative number)

How does the physical query generated by OBIEE looks like? How does it retrieve the “AGO” value? What kind of time/date function does it use to move in the past?
Let’s look at the physical SQL :

For a simple analysis OBIEE generates a query with 5 WITH blocks and finally the main query, not the shortest code …

Where are the time/date functions and calculations?

OBIEE doesn’t use any time/date calculation

Do not try to find them, you will not find any! OBIEE doesn’t use any time/date calculation. Time Series functions in OBIEE are produced by performing operations moving a given number of steps forward or backward from a relative position in the dataset at a given level (logical hierarchy level). It’s a bit like windowing functions in Oracle Database working on a “window” of your dataset.

Looking more in details in the code you will see that SAWITH0 and SAWITH2 use a ranking function (my data source is an Oracle database, so DENSE_RANK) to generating a ranking column. In my example the ranking is performed based on the “MONTH_CHRON_KEY” column: what’s that? Why this column? It’s the chronological key I set in the RPD.

The “Month” logical level keys: level key (and display) and chronological key

The Chronological key: the driver of Time Series functions

In my example I explicitly created a column in my dataset to highlight what is used by OBIEE when generating the queries, my column as the same format of the “Month” column, so values like YYYY-MM, “2013-01”, “2013-02” etc.

As this column when sorted return the correct order for periods the ranking function assign numbers starting with the first period and increment the value for each row: January 2013 is 1, February 2013 is 2 etc. The physical key is included in SAWITH0 as it’s the column needed to make the join with the fact table later.

By selecting a correct chronological key the sorting of rows is correct

The next step OBIEE does in SAWITH1 is to apply the AGO function to align the previous month fact (returned by AGO) with the relative month position. It performs this operation by doing a simple +1 on the ranking column, so January 2013 is now at position 2, February 2013 position 3 etc.

SAWITH2 will then perform a ranking again but this time only retrieving the column I want to display on the screen, not taking into consideration the physical keys. This part of the query is to make sure to retrieve the right “label” for the AGO value (remember: in AGO 1 month the value of January 2013 must be associated with the label “2013-02” because it represents the AGO value for February).

SAWITH3 is performing the real query to get the AGO value, so it queries the fact table, it uses the new ranking created in SAWITH1 and the “label” from SAWITH2 to return the AGO values related to “new” months labels.

SAWITH4 is the normal query you would have when not using Time Series functions, so it’s the current value for a month.

The main query is finally putting these 2 datasets, SAWITH3 and SAWITH4, one next to each other to return the AGO value on the same row as the current value.

As you see there are many steps, and the very first is sorting the time dimension values and assigning to each one a unique contiguous (no gaps) number based on the chronological key of the logical level selected by the Time Series function. The logic will then move around based on that ranking by adding or subtracting positions (a simple mathematical addition or subtraction).

The impact of wrong or poor chronological key

To highlight the fact everything is done based on the chronological key defined in the RPD let’s change my model and set as Chronological another column of the following format: 2013-1, 2013-2, …, 2013-10, etc. It’s really similar to the previous but the month doesn’t have the leading zero.

When using a chronological key that can’t be sorted correctly

Interesting result, the AGO function seems to jump around, from January to October, then it’s correct till December and then back to February and again correct till September.

Why does it happen? Because of a poor chronological key…

The logic OBIEE applies with a wrong chronological key

The ranking on this column is calculated by sorting the strings, and in a string sort 2013-10 comes after 2013-1 and before 2013-2, so October, November and December comes after January. After December the ranking goes back to February.

OBIEE doesn’t care at all if it makes sense from a calendar point of view or not, the tool isn’t aware of that because it’s not a problem. A chronological key has been set, OBIEE uses it without looking at the business meaning of that column.

Reductio ad absurdum: Time Series functions on Country dimension

Because OBIEE translate these functions into backward or forward movements based on the relative position you can also use Time Series functions on something not all time/calendar related.

What about calculating a “one country ago” measure? Not sure why you would like to compare a country with the one just before but … technically you can do it !

Let’s be honest: I’m not saying there is any logic into a “one country ago” formula, it’s just to demonstrate OBIEE doesn’t care about what kind of data you feed it with for Time Series functions.

The only possible context to do Time Series on a not calendar dimension I can imagine is if you do your forecasting/budgeting iterations with Excel or APEX to feed a database table. You then have an analysis to compare deltas between different versions of your forecasting/budgeting iterations. I saw something similar in Planning / Essbase, where the version and scenario dimension are often compared.

Do you want Time Series functions on countries? Set the dimension as time and define the chronological keys

From a formula point of view nothing change, just use the “Country” level in the AGO function

Sales by country vs previous country using Time Functions on countries

As you can see OBIEE doesn’t care at all, it applies what the RPD says: I modelled a Country Dimension as time dimension, I set chronological keys on countries, subregions and regions, I use the AGO function at the country level: I have my results!

Curious about the physical SQL? Exactly the same as before when using months, simply replaced references to the calendar dimension by country dimension.

Just a detail: do not try to flag many dimensions as time in the RPD, the server doesn’t like it too much (the consistency check didn’t complain, I was surprised, but the check-in didn’t like it at all !). You end up with a kind of inconsistent result and some strange metadata in your RPD. The best way to clean up is probably to delete the hierarchies completely and rebuild them.

To resume, and some advice

  • OBIEE Time Series functions do not need any real date (physical data type column)
  • The “key element” of a correct time dimension is the Chronological key
  • The Chronological key must be sortable, the sort result defines the behaviour of Time Series functions
  • Always set a chronological key for each level of your time dimension hierarchy to avoid unpredictable results
  • If your business requires the logic of Time Series functions on something else than a calendar dimension you can do it
  • Do not set more than one hierarchy as “time” in the RPD, not accepted by the system
Share This