Tuning Time Series Metrics

In this post I am going to explore some performance issues related to OBI’s time series functions.  Released back in OBI 10g, the ToDate() and Ago() functions brought a significant improvement to the process of easily creating a variety of time series metrics.  In older versions of Siebel Analytics, creating time series was a very manual effort involving a lot of aliases and special joins that could at time become a little confusing to the developer.  They did have a wizard called the Time Series Wizard to assist, but if you are like me you never use wizards J.  The Time series functions however solved that; using them is a piece of cake, requiring only a minor enhancement to the Date dimension.

All is rosy with the world then, correct?  Well not so fast.  The reality is that these functions do some very strange things behind the scenes in order for them to work properly.  So strange in fact that the database engine typically has some difficulty figuring out what to do.  One thing I’ve learned over the years when it comes to database engine performance – keep it simple if you want it to run fast.

As it turns out these strange things that OBI does for the Time Series functions in fact cause a decent performance hit when compared with the old technique.  This short post will discuss this in more depth.

Performance Tuning and Financial Analytics

In the past I’ve written and presented on OBI performance from the ‘before perspective’: before you begin development, what are the things you should plan on and how should you implement them.  This paper however is with the ‘after perspective’; what to do if you are stuck with a performance problem that you need to solve quickly.  It will use the Financial Analytics (All versions) application from Oracle’s BI Applications (aka the BI Apps) to walk through a plan of attack and demonstrate specific changes using the Oracle Database 10x.  Thus, this paper has two purposes:

1. Specifically to document actual changes for Financial Analytics
2. Generally to walk through a performance tuning effort that you yourself may undertake

Note: You do not need to be working on Financial Analytics or even the BI Apps for the concepts in this article to apply.  It merely uses Financial Analytics as its example, and where appropriate I will explain the model.

I’m going to do something a bit different with this article in that I will tell the story of a recent performance tuning project for a client.  A previous integrator had delivered Financial Analytics and it was up and running in a production environment, but the performance was terrible.  Many queries were over 8 minutes.  We were asked to tune the GL Transactions star, but the lessons learned here will work for all modules of Financial Analytics, regardless of version.  In fact, implementing them for only one star actually boosted the performance of the other Financial Analytics Subject Areas.