MDX: Converting Second of Day to Standard Time Notation

Had a bit of fun with Pentaho Analyzer recently. In the release of Pentaho 5.2, we have introduced the ability to define filters across a range of time, which is really handy when your dataset is millions of records per second.

My use case included keying our time dimension on seconds per day, which results in 86,400 (60 seconds * 60 minutes * 24 hours) unique records; one to represent each unique second in a day. While this is great for simplifying query predicates, it does not help the usability or intuitiveness of the analysis report you are presenting to the user. For instance, who would intuitively understand that 56725 represents 15:45:25 in time?

So I came up with this user-defined calculation that will convert seconds in a day to standard time notation. Would love to hear from anyone who can optimize this:)  This is a valid MDX calculation that Mondrian will process. Since I needed to know the minimum and maximum second per hour in the display, I used the second of day number as a measure.

Format(Int([event scnd of day min]/3600), "00:") || 
  
  Format(Int(([event scnd of day min] - 
        (Int([event scnd of day min]/3600))*3600)/60), "00:") ||
    
    Format([event scnd of day min] - 
          ((Int([event scnd of day min]/3600)*3600) + 
          (Int(([event scnd of day min] - 
          (Int([event scnd of day min]/3600)*3600))/60)*60)), "00")


Here's what it looks like in Analyzer.  The columns Minimum & Maximum Second of Hour have the calculation applied to them. Note the time filter range in the filter panel. Super sweet.