Data Warehousing and Business Intelligence

25 October 2010

MDX: Filter on Dates Before Today

Filed under: Analysis Services — Vincent Rainardi @ 1:08 am
Tags:

Apologies with the generalisation & simplified nature of this case. I have to protect the identity of the company and make it generic so that it is applicable for a wider audience.

The Issue

A few days ago somebody came to me with a case where he had a cube with a customer dimension. The customer dimension had two attributes called Start Date and End Date. He wanted to filter the customer dimension where the Start Date is <= today and the End Date is >= today. Both the Start Date and End Date attributes are in the form of [YYYYMMDD], an integer, which is an FK to the key of the date dimension.

Say today is 26th Oct 2010. You want to do something like:

select [Measures].[Measure1] on 0 from [Cube]
where
( [Customer].[Start Date].FirstChild:
  [Customer].[Start Date].&[20101016]
);

where the 20101016 is the latest date before today that exist in the end date column.

This is because if you type today’s date like this (26th):

select [Measures].[Measure1] on 0 from [Cube]
where
( [Customer].[Start Date].FirstChild:
  [Customer].[Start Date].&[20101026]
);

the query will return nothing because in the dim customer table there is no end date = 26th Oct 2010.

Create The Cube

Let’s create the tables, and then create the cube. We don’t need to create the date dimension. We just have to create 1 measure group and 1 dimension (customer) which is linked directly to the measure group.

-- create tables
create table f1
( customer_key int,
  measure1 numeric(20,4)
)

create table dim_customer
( customer_key int,
  start_date_key int,
  end_date_key int
)

-- populate tables
truncate table dim_customer
insert into dim_customer values (1, 20101005, 20101010)
insert into dim_customer values (2, 20101011, 20101015)
insert into dim_customer values (3, 20101016, 20101020)

truncate table f1
insert into f1 values (1, 10)
insert into f1 values (2, 20)
insert into f1 values (3, 30)

select c.customer_key, c.start_date_key, c. end_date_key, f1.measure1
from f1 join dim_customer c on c.customer_key = f1.customer_key

Output:
1  20101005   20101010   10.0000
2  20101011   20101015   20.0000
3  20101016   20101020   30.0000

Now create the cube in BIDS. Just create 1 dimension, which is dim customer. No need to create the date dimension. Like this:

Query The Cube

Deploy and process the cube then query it:

select [Measures].[Measure1] on 0 from [Cube]
where ([Customer].[End Date].&[1]:[Customer].[End Date].&[20101015]);
--output: 30 (there is a row in dim_customer with end date = 15/10/10)

select [Measures].[Measure1] on 0 from [Cube]
where ([Customer].[End Date].&[1]:[Customer].[End Date].&[20101016]);
--output: null (there isn't a row in dim_customer with end date = 16/10/10

select [Measures].[Measure1] on 0 from [Cube]
where ([Customer].[End Date].&[1]:[Customer].[Start Date].&[20101005]);
--output: 10 (there isn't a row in dim_customer with start date = 5/10/10

select [Measures].[Measure1] on 0 from [Cube]
where ([Customer].[End Date].&[1]:[Customer].[Start Date].&[20101008]);
--output: null (there isn't a row in dim_customer with start date = 8/10/10

The Solution

The solution is like this:

select [Measures].[Measure1] on 0
from [Cube]
where
( [Customer].[Start Date].[All].FirstChild:
  tail
  ( filter
    ( [Customer].[Start Date].[All].Children,
      [Customer].[Start Date].CurrentMember.Name <= Format(Now(), "yyyyMMdd")
    ), 1
  ).item(0)
);

Explanation:

First we get today’s date using now() function. Then we format the date to yyyyMMdd. Then we get all members of the Start Date attribute which is smaller or equal to today. This is done by comparing the current member of the start date attribute with today’s date. Then we take the last member using tail function. Then we convert from set to member using item(0).

Same principles apply for the end date.

Hope this helps, Vincent Rainardi 26th Oct 2010. As always I’d welcome question and discussion on vrainardi@gmail.com.

Advertisements

6 Comments »

  1. Hi Vincent,

    what i usually do in such cases where you frequently have to query “dates before today” or any other “defined timeperiod”, i create a flag-attribute in my time-dimension and use this flag in my query

    in your case this would mean that i create a new calculated column in my relational time-table as follows:
    CASE WHEN [dateKey] < Convert(int, Convert(varchar, getdate(), 112)) THEN 1 ELSE 0 END AS BeforeToday

    in your query you can then simply use [Customer].[Before Today].&[1] instead of a complex Filter(Tail()) combination

    of course this would mean that you have to process-update your date-dimension every day but i think the performance benefits are worth it:
    – you can use aggregations
    – better use of caching as you do not use now() function

    looking forward to hear your opinion about this
    greets,
    gerhard

    Comment by Gerhard Brueckl — 25 October 2010 @ 8:27 am | Reply

    • Thanks for your comment Gerhard. Yes I’ve heard and used this technique and the benefits are as you said. But on the other hand the drawback is that it depends on the processing time. If the cube is processed once a day at 10 pm then the “today” will be “yesterday”. If the cube is processed once a week then we can’t do it. As alternative I usually use the Date Calculation Dimension that Chris, Marco and Alberto mentioned in chapter 6 of their book: https://dwbi1.wordpress.com/2010/06/29/the-most-useful-ssas-book/

      Comment by Vincent Rainardi — 25 October 2010 @ 8:47 am | Reply

  2. well, but you would only have to process-update your date-dimension and not the whole cube – this should not take too much time and could be done daily even though the cube is usally updated only weekly
    also, if you already process your cube daily, you will do a process-update on the other dimensions anyway so adding the date-dimension to your processing-script should not be a big deal
    regardign the “yesterday”-problem: if you know that you process your cube at 10pm the next day then you can simply subtract “1” from [dateKey] in your calculated column

    in the end it will depend on the exact requirement and the current cube-design
    both designs have their pros and cons regarding implementation, performance and usability
    personally i would of course go for my approach 😉

    greets,
    gerhard

    Comment by Gerhard Brueckl — 25 October 2010 @ 1:45 pm | Reply

  3. Hi vincent. This is was very nice example in filter stage.
    This was very helpful to implement I used.

    Comment by schools — 11 April 2011 @ 11:16 am | Reply

  4. I am new to MDX, can you help me in recreating this query if i want to extract only yesterday’s data only. Thanks Annie

    Comment by Annie — 5 May 2012 @ 6:01 am | Reply

    • Hi Annie, it is:

      select [Measures].[Measure1] on 0
      from [Date Filter]
      where (tail (filter ([Dim Customer].[Start Date].[All].Children,
      [Dim Customer].[Start Date].CurrentMember.Name = Format(Now()-1, “yyyyMMdd”)
      ), 1).item(0));

      Or you can also write without filter:
      select [Measures].[Measure1] on 0
      from [Date Filter]
      where strtomember(“[Dim Customer].[Start Date].&[”
      + Format(Now()-1, “yyyyMMdd”) + “]” );

      Comment by Vincent Rainardi — 13 May 2012 @ 4:06 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: