MDX: Filter on Dates Before Today

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 26^th 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 (26^th):

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 = 26^th 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 26^th Oct 2010. As always I’d welcome question and discussion on vrainardi@gmail.com.

Parser: The query contains … parameter, which is not declared

This error happens when a 2008 cube is deployed and processed on 2005 SSAS server.

The deploy itself is OK. The error happens when the cube (not the database, not the dims) when we do “process structure”. Process structure is part of “process full”, so the error also happens at the end of “process full”.

The calculation tab of BIDS 2008 has Display Folder and Associated Measure Group text boxes. These two are new in 2008 as 2005 doesn’t have them. However, I guess it’s for backward compatibility reason, the Calculation Properties dialog box 2008 BIDS still has Display Folder and Associated Measure Group and Description columns. So, if you open a 2005 cube project in 2008 BIDS, then fill in the Display Folder or Associated Measure Group, you make your cube incompatible with 2005.

When that cube is deployed and processed to a 2008 server, no problem. When that cube is then deployed to a 2005, no problem occurs. But when it is processed, this error message appears: “Parser: The query contains the … parameter, which is not declared.”

Hence it is important to ensure that your production, test, UAT, staging, development servers have the same version of SSAS. Having some of your servers in 2005 and some in 2008 is only looking for trouble in my opinion.

“But we can set it to be compatible with 2005”, some people say.  True, we do have “Deployment Server Version” in BIDS’ Configuration Properties dialog box:

And we can set it to 9.0 (2005) instead of 10.0 (2008), then deploy the cube to that 2005 production server and process it there. Very true, you can deploy it to 2005 server that way, but my question to you is: do you want to risk it? In Dev you use 2008 server and it works fine. In UAT/test you use 2008 server and it tested OK. But the Production you uses 2005 server and you don’t know whether it will work 100% OK or not. To me it sounds like a recipe for a trouble.

Yet some companies do that: dev in 2008, prod in 2005. Or worse: Prod in 2008, DR in 2005. I’d advise to upgrade all the AS servers in the same fortnight period. It’s 2010 now, 2 years after 2008 AS was released, and many companies have done it (upgrading 2005 AS to 2008). Any upgrade issues would have been logged on MS support and MSDN forums. And those upgrade issues would have been resolved by now. I myself did it (upgrade to 2008) during the first half of last year and it went smoothly.

You’ve got to test it of course. But you’ve got to test it quickly, i.e. upgrade Dev, deploy prod cubes there and test using your client tool (Excel, Strategy Companion, Tableau, ProClarity). Then upgrade the production servers.

If you use ProClarity which is not the latest version (6.3 SP3 = 6.32.2222), be careful. 2008 AS is not compatible with your ProClarity (say you use 6.2 or 6.3 RTM = 6.3.209). The issue is with the total. From memory I think ProClarity 6.2 uses “sum” MDX keyword instead of “aggregate”, which causes the total to be incorrect. Reading the KB article on SP2 and SP3 would give you the details of the issues fixed in each SP. Some of these issues are related to upgrading AS from 2005 to 2008.

As always I welcome questions and discussion on vrainardi@gmail.com

Hope this helps, Vincent Rainardi, 7/10/10.