date literals in MDX

I'm wondering if there's a way to put a date literal into an MDX statement. The only way I've been able to figure out to accomplish this is to use the Double datatype equivalent of the date datatype. Any other way?

The reasoning behind this question is that I have a dimension attribute which is a datetime value and has over 250,000 members. I need to select a range of dates, but I can't use the : (colon/range) operator because there's no guarantee that a particular member with the value of midnight will exist. So I have to do a Filter statement on MemberValue. Using a "date literal" improves performance 10x, but the code isn't very readable because that literal is a number, not a date. Compare performance of the following (which is the best I could do to demonstrate against Adventure Works):

select {} on 0,

Filter(

[Date].[Date].[Date].Members

,[Date].[Date].CurrentMember.MemberValue > VBA!DateSerial(2004,7,18)

)

on 1

from [adventure works]

select {} on 0,

Filter(

[Date].[Date].[Date].Members

,[Date].[Date].CurrentMember.MemberValue > 38186

)

on 1

from [adventure works]

The second query performs 10x faster than the first and returns equivalent results. It's just hard to read.

There is no Date literal representation in MDX. It's a nice trick you have with double, but it actually does type conversion (which is still very fast as you discovered). For a more readable, yet performant way - you can use CDate function as following:

select {} on 0,

Filter(

[Date].[Date].[Date].Members

,[Date].[Date].CurrentMember.MemberValue > CDate("7/18/2004")

)

on 1

from [adventure works]

The reason it will work much faster than your first query with DateSerial, is because CDate is one of few VBA functions on the list of.

Now your scenario got me interested - having 250,000 dates is enough to cover about 685 years. But even if this was at the hour granularity, it is still 29 years. And since you say that some of the dates could be missing, it is likely to be at least twice as big (otherwise what is the reason to have missing dates). So what is this company you are working for if it has data from before America was discovered (if day is the granularity) or before computers were invented (if hour is the granularity) ?

|||

Mosha. Thanks. You're absolutely right. It's about 25% faster than the number literals I was using, so that's the best trick.

To fill in your statement about CDate, it's one of the few VBA functions that have internal implementations as specified in Irina's list here:

http://www.e-tservice.com/downloads.html

As for why there are so many, the attribute is actually a datetime attribute, not just a date attribute. (It's got minutes and seconds.)

Thanks again.