Indexing by Range Dimension - II

The former help page showed how to associate a single member with a continuous value. In this page we're going to show an extension that allows to associate a fact row with multiple members (like we do with in a many-to-many relation). The first step consist in defining two key values (e.g. one for start and another for end). Then, all members that belong to the range [start,end] will be associated with the same fact row. This, as for a many-to-many relation, breaks the natural hierarchical aggregation (a parent might be not anymore the sum of his children as the same row might be associated with different children).

Example

Let's work through an example. Inspired by Chris Webb example (link) we'll create a shipping order table with an order- and a shipping date :

       Id, Customer,Order Date, Ship date
       1,  John,    1/1/2013,   6/1/2013
       2,  Grace,   3/1/2013,   4/1/2013
       2,  Grace,   4/1/2013,   5/1/2013
       3,  Paul,    6/1/2013,   12/1/2013
       4,  Paul,    30/1/2013,  2/2/2013
       5,  Pierre,  1/2/2013,   6/2/2013
       6,  Lidia,   4/2/2013,   6/2/2013
       7,  Grace,   7/2/2013,   9/2/2013
       8,  John,    10/2/2013,  11/2/2013
       9,  Pierre,  13/2/2013,  14/2/2013

Our goal is to calculate the number of open orders for each day. First we need to create the dimensions Customer and Calendar (don't forget the indexing by range option). Once done, we'll create our cube and it's in the Fact definition that the difference is made :

Instead of linking our dimension with a single column, we will do it with both 'Order Date' and 'Ship date' columns. They will define the start and end. Note that both dates will be included in our range.

After deploying the schema we can run the following MDX

         SELECT
            NON EMPTY [Calendar].[Calendar].[Day] ON 1,
            [Customer].[Customer].members  ON 0
         FROM [Cube]

We can see how the result show the expected values :

The [Calendar] dimension behaves as a many-to-many one, in order to better understand the differences you can run the following MDX statement and check the difference between icCube's aggregation and a calculated measure that has been defined as the sum of the days.

            SELECT
                {([Measures].[Open Orders]),([Measures].[Sum Daily Open Orders])} on 0,
                Non empty {[Calendar].[Calendar].[Quarter].&[2013-01-01].children} on 1
            FROM [Cube]

The difference is that the [Open Orders] measures aggregates over rows without adding twice the same one. On the other hand, the calculated measure is going to add for each day, therefore adding several times the same fact row.

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

Next chapter: Many-to-Many Dimensions