Screenshot of MDX Studio containing my expression

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):

Screenshot of MDX Studio containing my expression

My MDX expression as written in MDX Studio

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?

Solutions Architect at Indicia. More years than I care to remember wrangling SQL Server and .NET, now mainly AWS. Data geek, football fan, and Xbox gamer. Check out my other blog Press B To Parent if you're a gamer.

Leave a Reply

2 Comments

  1. BTW, you might find the performance of the approach you show in your screenshot isn’t good for larger queries. I would try using the LinkMember() function instead of trying to construct unique names using StrToMember().

    • Graham

      Thanks for the feedback, I’m still getting to grips with MDX so it’s much appreciated! I’ll check out the LinkMember() function as an alternative.

Next ArticleMicrosoft BI 2012: A Year In Review