Cumulative Sum - Choosing Portions of Hierarchy

Question

I have a bit of an interesting problem.

I required the cumulative sum on a set that is created by pieces of a Time dimension. The time dimension is based on hours and minutes. This dimension begins at the 0 hour and minute and ends at the 23 hour and 59 minute.

What I need to do is slice out portions from say 09:30 AM - 04:00 PM or 4:30PM - 09:30 AM. And I need these values in order to perform my cumulative sums. I'm hoping that someone could suggest a means of doing this with standard MDX. If not is my only alternative to write my own stored procedure which forms my Periods to date set extraction using the logic described above?

Thanks in advance!

Answer 1

You can create a secondary hiearchy in your time dimension with only the hour and filter the query with it.

[Time].[Calendar] -> the hierarchy with year, months, day and hours level
[Time].[Hour] -> the 'new' hierarchy with only hours level (e.g.) 09:30 AM.

The you can make a query in mdx adding your criteria as filter :

SELECT
   my axis...
WHERE ( SELECT { [Time].[Hour].[09:30 AM]:[Time].[Hour].[04:00 PM] } on 0 FROM [MyCube] )

You can also create a new dimension instead of a hierarchy, the different is in the autoexists behaviour and the performance.