Time Format on non-Date Dimensions in SSAS

I ran into a rather strange situation recently while attempting to set up some new calculated measures in an SSAS cube. I’m still new to MDX, so I ended up chasing my tail around for a while (and crying out for help on both StackOverflow and MSDN) before I eventually tracked down the culprit and solved the issue. Basically, I was trying to create a calculated measure, which, using a couple of date attributes from one dimension, would apply to my [Time] dimension and filter the value of a specified measure to match. Not hard, right?

See the screenshot below for how I went about testing this expression in the excellent MDX Studio (by former SSAS developer Mosha Pasumansky):

What’s going on?

using hh in the FORMAT_STRING seems to evaluate time differently in Date and Regular dimensions

Looking at my MDX expression above, there doesn’t seem to be anything wrong. The syntax is all correct, as are the string formats and the attribute hierarchies. So why would the [Time] dimension not be filtered to the specified date?

Eventually, by working through each component of my expression one by one, I started experimenting with the format strings that I was using in the StrToMember function. It’s here that I discovered a rather strange mismatch in behaviour between my [Time] dimension (With the Dimension Type property set to “Date”) and my [Dim Account] dimension (With the “Regular” Dimension Type).

It seems that the format string I was using ("yyyy-mm-ddThh:mm:ss") was behaving differently on each dimension.

The [Time] dimension’s date attribute evaluates to: “2012-11-26T00:00:00”.

This suggests that it’s using 24-hour formatted time and is midnight on the date in question.

However, the [Dim Account] dimension’s date attributes evaluate to: “2012-11-26T12:00:00”.

This suggests that it’s either using 24-hour formatted time and is defaulted to midday OR the format string evaluates to 12-hour formatted time when used on a non-date dimension type. Strangely, using the built-in Long Time format also outputs the time as “T00:00:00”, suggesting it’s something to do with the way the custom format string applies to each dimension, rather than the underlying data itself.

Solution?

So, I played about with the format string a little and stumbled upon the suggestion of changing the hour part of the time format string to a capital.

Using the resulting string: “yyyy-mm-ddTHH:mm:ss”, seemed to force BOTH dimensions to display 24-hour formatted time, and both started to evaluate to: “2012-11-26T00:00:00”.

This gave me the match I was looking for and fixed the whole issue. So why did it take me so long to find this solution? Well, the HH behaviour seems to be completely undocumented on MSDN. As it turns out, using hh in the MDX FORMAT_STRING function seems to time evaluate differently in Date and Regular dimensions, while the undocumented HH format enforces the 24-hour time format no matter what the dimension type.

Really odd behaviour, but perhaps this is something I’ve caused by the way I’ve designed my dimensions. Like I said, I’m only really just getting started with MDX. Anyone out there come across this issue before?