Build Your Own Time Hierarchy

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).

The Single Most Important Thing to Know About the OBI RPD

Over my OBI career I’ve had to review dozens of customer and consultant developed RPDs as part of either a formalized health check engagement or simply a developer code review.  Frequently the customer has some problems that they can’t solve, or that they have solved with a work-around of some sorts.  In some cases this is simply due to not fully understanding the concepts and what you can do with those concepts, but sometimes there is something far worse at play: bad advice from supposed experts.

Twice this year I have come across customers who have been told by other “experts” that Dimensional Hierarchies aren’t that important or they should leave the Content tab empty.  One of these so called experts was even an actual Oracle employee!  This is disastrous advice as I will demonstrate in this post.

OBI’s Nested Aggregation > Ralph Kimball

Ok so I put a provocative title on this post for a reason.  This post will explore why some of Kimball’s concepts may be out dated when newer query generation or database capabilities are taken into account.  Specifically, I’m going to discuss the OBI concept of Nested Aggregation, also known as Dimension Based Aggregation, in detail.  Using this OBI Modeling technique you can relax a bit on one of the Kimball Dimensional Modeling rules.  I’ll show how it works in a simplified manner, plus identify some things to be careful with and how it does not alleviate you from doing real modeling work.

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.

Degenerate Dimensions and Aggregates

An interesting topic came up recently on my latest project that I think is quite informative on how OBI works under the hood.  It involves how to model degenerate dimensional fields into OBI when there are aggregates present.  Individually each of these two topics are relatively well know with solutions for how to handle them, but when combined they can get pretty tricky.  This is especially true when you are using the BI Apps, which to a large degree still rely on the old method of manually mapping Time Series (Ago & ToDate) metrics (as opposed to the OBI 10.x + technique of Time Series functions) which require an explosion of Logical Table Sources (LTSs).

The core of this post concerns what do you do when some of your aggregates don’t have all of the degenerate fields that your base fact has?  What do you need to do to model them properly?  In the explanation I will shed a bit more light on how OBI “thinks” so that you too can model things properly the first time.

Making Dims & Facts Work Together

This is a very common RPD modeling question on IT Toolbox – it comes up every week it seems.  The problem is stated something like this:

I have 2 fact tables and 3 dimension tables.  One of the dimension tables doesn’t work with Fact #2 while the other 2 dimension tables work with both facts.  When I make a report with all 3 dimensions and both facts, Fact #2 is incorrect or missing.

Advanced MetaData Presentation

I gave this presentation recently at Rittman Mead forum in Atlanta.  Its about some common MetaData problems that people encounter.  It even includes some 11g stuff in there, so its up-to-date.  This preso is more like a training deck than an overview of technology and features – I try to dig deeper into the Zen of Logical Table Sources a bit more.  Hope you enjoy!  McQuigg_Advanced MetaData Topics

Webinar on Performance, June 7th, 1pm EST

I’ll be doing a webinar called Planning for High Performance OBI.  I’ve given this preso a few times before, but it’ll be good to do it on the web for a change.  I discuss some of the basics you should have when beginning your designs.  It is not a performance tuning workshop, but rather what to do first to get it done properly the first time.  Register through KPI Partners website.

It’s Not ‘To Stitch or Not to Stitch’, But Rather Where to Stitch

It’s been a while since I’ve posted anything aside from an announcement – in fact for the entire year.  That’s was due to being swamped on my current project where I was involved in a number of things.  But now that we’ve deployed our first 2 releases, things are starting to settle in a bit, so I have some time to touch on some topics.  Sorry about the ugly formatting – I’m no expert with this editor.

The first topic came out of the end of the project when we were performing a few weeks of load testing.  Our performance numbers at load (100 concurrent users) were pretty terrible.  After a few realizations, I decided to try a tweak in OBI that resulted in enormous impacts on our results.

Physical Layer Tips and Gotcha’s

Originally published 5/23/2007

I wanted to talk about our much ignored friend, the Physical Layer.  I say much ignored, because it generally is.  After you import, set up a PK and some joins, you usually are done with the physical layer – aside from the occasional alias.  But you should pay more attention to it, as it can have some unforeseen problems if you overlook the details.