Indexing by Range Dimension (Banded)

Classical dimension binds members and fact tables with an id relationship (db foreign key). This is a good option in most cases, however, sometimes we would like to replace this one-to-one relationship with a more relaxed version. What happens if we want to use a double as a key ?


Let's work through an example. We have a list of temperatures which we would like to convert into a hierarchy. Each of this range is defined with a [min,max) range:
        Hot                   [20,
            Unbearable hot    [35,
            Very Hot          [30,35)
            Mildly Hot        [25,30)
            Nice              [20,25)
            Nicely cold       [15,20)
            Cold              [5,15)
            Freezing              5)

The current solution, if our datasource is a database, would be to use a SWITCH/CASE or DECODE function to sort these ranges in the database thereby converting them into an internal id: Hot=1, Unbearable hot=2, and so on. Although this may be a solution for small cases, once our structure grows it will become more and more difficult to maintain.

In icCube we have the ability to define that a dimension is linking facts to members with ranges. Instead of looking for a perfect id match, the member with a matching range will be used.

So, in this example, a temperature of 22.5 will be linked with the [Nice] member of the [Temperature] dimension.

Time Pyramid Example

A more complex case may be a time dimension that is defined as a pyramid. As an example, we are interested in the last 20 years of data but our interest in the details is decaying with the age of this data. We would like month details only for the last 10 years, day details only for the last 4 years, and hourly details for the last month. This real-life problem can be solved easily through icCube with a Time Wizard Dimension that is indexed by range.

Note that dimensions that are indexed by range are unbound. For any fact index a matching member will be found.


1) First, let's define a pyramidal Wizard time dimension as previously described:

2) We can browse the dimension, see icons, and check that we are getting the expected results:


Both the schema and the data of this example are available for download here.

Next chapter: Many-to-Many Dimensions