Measures as Dimensions: Banding Approach Help

[ctorrey]

I have a standard fact table with a number measures; several of which are in the form of time (e.g., minutes) and others in the form of integers. The fact table and related dimensions are exposed to users via BusinessObjects XI v2 Web Intelligence. One of the recent requests from the user community is to provide a means to make measures function like dimensions. For example, suppose we have measure, Turnaround Time Minutes (TAT) which is the elapsed time between when an order is taken and fulfilled. The user would like to create a cross tabbed report with Country on the Y axis and TAT banded into categories along the X axis (e.g., 0-10 mins; 11-30 mins; 31-60 mins; 61+ mins) with the measure being order count.

Because we have specified TAT as a measure in the dimensional model and the BusinessObjects “universe”, it is not possible to work with these values as dimensions, even if we create a custom dimension object based on the TAT measure with the banding defined using if-then-else logic.

Obviously, the easiest way to resolve this situation would be to create a generic “banding” dimensional table and assign the appropriate band key to the fact record for TAT. However, there are still several outstanding questions:
1. There are approximately 6-7 measures that we would like to “band”. Does having 6-7 new keys on the fact table referencing the aliased banding dimension pose any concerns?
2. Knowing that not every banding/data aggregation need can be easily predicted, my data modeler would like to maintain the actual TAT minutes as a dimension in addition to setting up bands. His reasoning is that it would provide the granularity to allow users to develop their own banding definitions within the context of the report. Is there a prevailing opinion on this approach?
3. What is the best way to handle the “unknown” member situation when we have a banded value that has not yet been computed (late arriving fact)? For example, we have not fulfilled an open order yet, so we cannot compute TAT. Is the best approach to create a banded record where the low and high value is 0/NULL?

[DilMustafa]

I will create a time dimension roleplay it with time_order_taken and and time_order_fulfilled, will do a calculation at BO level.
Do a -1 "no data" solution for late arriving facts.

[BrianJarrett]

Your analysis appears to be very thorough and the solutions you're considering sound pretty solid. I haven't done a lot with banding but hopefully I can provide at least a little help with your questions.

1. There are approximately 6-7 measures that we would like to "band". Does having 6-7 new keys on the fact table referencing the aliased banding dimension pose any concerns?

I've worked with fact tables with 15 foreign keys making up the composite fact key with good results. In my experience most users aren't pulling every single dimension very often so having a lot of foreign key joins (or joins via aliases here) hasn't really been a problem for me. I suppose it could be an issue if someone pulls a lot of dimensions in a single query but I really don't see a better way at this point.

I'd probably stay away from a many to many bridge table due to the complexity and overhead that comes with them. That would eliminate altering the fact table and creating more foreign keys but it introduces another join you'll pay for, plus creates duplicates that your users must handle.

Without testing it personally I can't say that the aliased tables will or won't work. I would have some reservations about it, primarily from a performance standpoint. It seems easier on the surface to just create a single band dimension and use role-playing versus creating a distinct table for each band but either way you're in for some work. Using role-playing, a new band requires an alter to the fact table, a view to do the alias (or an alias in the universe), a modification to the ETL process, and new classes/dimensions for the aliased band dimension.

Using real tables still requires altering the fact table, creating new objects in the universe, and a new or modified ETL process to populate the new table. Honestly it seems like about the same amount of work either way but with real tables you avoid the potential performance issues a view/alias might cause. Again, this is all theoretical; testing the concept will provide the proof.

2. Knowing that not every banding/data aggregation need can be easily predicted, my data modeler would like to maintain the actual TAT minutes as a dimension in addition to setting up bands. His reasoning is that it would provide the granularity to allow users to develop their own banding definitions within the context of the report. Is there a prevailing opinion on this approach?

I know you mentioned creating a custom dimension object based on the TAT "measure" but I'm not sure if you're trying to build this dimension on a BO measure within a report or building it from the actual database table field directly in the universe. What I'm saying is essentially you'd end up with two objects in the universe pointing to the same field; one a "measure" and one a "dimension". I haven't tested this but in theory it should allow you to accomplish almost the same thing your data modeler is recommending with the dimension. I don't have time right now to mock it up but I figured I'd throw it out there to see what you thought.

I'm not sure I'd create an entire dimension for these values. At best they're degenerate dimensions since they have no attributes. A degenerate dimension would live in the fact table only, just like your facts. Based on my suggestion above it seems like you could just define the field a second time as a dimension and let BO define the role within the report.

3. What is the best way to handle the "unknown" member situation when we have a banded value that has not yet been computed (late arriving fact)? For example, we have not fulfilled an open order yet, so we cannot compute TAT. Is the best approach to create a banded record where the low and high value is 0/NULL?

As long as the band's foreign key is not part of a unique constraint on the fact table I'd probably assign it to a "known unknown" value, like -1. Here I'd probably push in a banding range of 0/0 rather than using null. Normally I try to avoid nulls wherever I can.
If it is part of the composite fact key or unique constraint I'd consider taking it out of the constraint. Since it's a banded dimension I'm not sure it's needed to make the record unique, particularly since it's really just a derived dimensional representation of facts in the end.

Let me know if this helps any.

[Sponsored content]