Tag Archives: obiee facts

Avoid Non-numeric facts!

Yes, I’ve done this mistake more than once, usually unwilfully. Using varchar columns in fact table.

Everyone knows that using anything but numeric facts is a bad idea. However, there’re situations when it might be deemed necessary, especially when dealing with reporting at the detailed level. For example, client once wanted Condition Grade for previous year using AGO function in Fact Table. There was a need for previous year reporting using AGO and TODATE functions. Also, there was a complex function evaluating variance in condition. Since there’re limited aggregation functions available for varchars in OBIEE  (FIRST and LAST..I don’t countCOUNT – since it doesn’t really do it for that report). And you need to set some aggergation, because otherwise, you can’t use those column in time-series functions since those require aggergated metrics.

Well, what do you know. Query performance for the whole dataset was very slow (it’s supposed to be, since nobody should get inital report of more than a page) . About 20 minutes. Setting up bitmap indexes reduced it to 4 minutes. And setting textual facts’ aggregation setting to “None” reduced that time further acceptable 20 seconds. However, time-series functionality was lost.

So, please don’t use textual facts – they won’t make you any good:

a) You can’t really get any meaning out of them except on the most detailed level. How do you measure A vs B ?

b) You run into performance issues.

What to do:

I guess it reallty depends on the situation and your gut feeling.  Ideally, you want to convert letter grades to numeric alternative.  Such as A=4, B=3, etc. then you can make all kinds of fun analysis with it.  If that’s not an option, then you should try  pushing that data to your dimension (using an aliased combo table if you don’t have that dimension). I understand there might be more viable solutions. I’m going to find my post and discussion on OTN.