Every single BI system has a time/date/period/calendar hierarchy in it. If you use the BI Apps, it actually comes with 2 different tables for this purpose alone, along with a ton of OOB capability. However, when you are faced with a pure custom system from scratch, how do you build one? What does it look like? What sort of data should it have in it?
I’m going to lay out what a decent Time Hierarchy looks like from an OBI and data model perspective. From there I’ll then demonstrate how to actually build one yourself instead of relying on complex code. Along the way we’ll mix in some best practices and theory as well.
Your mileage may vary depending on the specifics of your system, but my goal is to make the design and building of your hierarchy take only 3 hours and last the lifetime of your project (or career). Read the rest of this entry
I’ve been spending a lot of time recently working on performance tuning projects. Sometimes the BI apps are slow, sometimes it’s custom, sometimes it’s a mix. I’ve gotten the chance to see what works in both Oracle and SQL Server.
My conclusion about both of these databases is that they are like a cat or dog that gets fooled when you play hide the ball; they aren’t very smart sometimes. The only way you can really truly ensure database engines, even modern advanced ones, do things the right way to is to make it as simple and easy for them to understand as possible. I guess this is nothing new; the KISS principle comes from Kelly Johnson of The Lockheed Skunk Works, the guys who created the SR-71 Blackbird spy plane. I think he knew a few things about complexity in systems and how they tend to break or become difficult to maintain. (BTW more on that topic as it pertains to OBI and the BI Apps at a later date.)
As I’ve been looking at performance tuning many reports and queries over these days, I find that a lot of time is spent trying to get the database to do the smart thing. Too much time in fact. Usually this is due to some small piece of non-simple SQL that causes the problem. In more unusual cases I’ve seen something on one table completely break down the query plan, even something that should be trivial and very innocuous.
<Vent>For example, not being able to use an index on a table with 2,000 records should not radically alter the query plan, but in fact it will do that on you. After you spend hours upon hours with it, after you’ve called up the DBA for help to dig into the extreme nitty-gritty details of the query plan, you make a change it works for that one query but not any others. Then you decide to write this article because you’ve spend 10 hours on something so minuscule that in the end doesn’t even work consistently. If only the query had been clean to begin with…</Vent>
In this brief article I’m just going to lay out a few things to consider to help make you system simpler for the database engine to understand and therefore do a better, faster job in answering a query.
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. Read the rest of this entry