MDX Query to Filter by Date Dimensions

1
votes

I am new to MDX queries and am trying to figure out how to filter a result set using date dimensions.

Let's take a cube structured like this (contrived example):

Cube Structure

I would like to give the user a list of projects to select, and display the cost of all events that occurred during the selected projects (i.e. between start date and end date). However, the events are not linked to projects.

Using the query:

SELECT NON EMPTY
{
    [Measures].[Cost]
}
ON COLUMNS,
NON EMPTY
{
    (
        [Project Details].[Project].[Project].ALLMEMBERS
        * [Project Details].[Start Date].[Start Date].ALLMEMBERS
        * [Project Details].[End Date].[End Date].ALLMEMBERS
        * [Event Details].[Date of Occurrence].[Date of Occurrence].ALLMEMBERS
    )
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM [Cube]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I can get a list of items like this:

Project    Start Date    End Date    Date of Occurrence    Cost
------------------------------------------------------------------
Project 1  01-Jan-15     31-Jan-15   27-Dec-14             750
Project 1  01-Jan-15     31-Jan-15   01-Jan-15             680
Project 1  01-Jan-15     31-Jan-15   02-Jan-15             320
Project 1  01-Jan-15     31-Jan-15   03-Jan-15             150
Project 1  01-Jan-15     31-Jan-15   01-Feb-15             700
Project 1  01-Jan-15     31-Jan-15   05-Feb-15             175

If I run the query for Project 1 only, it should exclude the first event and last 2 events.

Would the best approach be to use a WHERE or FILTER? And because these are dimensions and not measures, how would I do a comparison of WHERE [Date of Occurrence] BETWEEN [Start Date] AND [End Date]?

Any help is much appreciated.

3
ssasmdxcube
Have you considered solving this problem in your ETL? It should be easy to add a new attribute to your "Event Details"-dimension, to indicate whether an event happened between the start- and end-date of the corresponding project. Then use this new attribute to filter out events that fall outside this interval. – Dan
Unfortunately I am dealing with a 3rd party cube and will not be able to modify it :( – João Lourenço

3 Answers

1
votes

I would try something like this:

WITH MEMBER [Measures].[Cost in period] AS 
    IIF(
        [Event Details].[Date of Occurrence].CurrentMember.Properties('Key') >= 
          [Project Details].[Start Date].CurrentMember.Properties('Key') &&
        [Event Details].[Date of Occurrence].CurrentMember.Properties('Key') <= 
          [Project Details].[End Date].CurrentMember.Properties('Key'),
    [Measures].[Cost], NULL)
SELECT NON EMPTY
{
    [Measures].[Cost in period]
}
ON COLUMNS,
NON EMPTY
{
    (
        [Project Details].[Project].[Project].ALLMEMBERS
        * [Project Details].[Start Date].[Start Date].ALLMEMBERS
        * [Project Details].[End Date].[End Date].ALLMEMBERS
        * [Event Details].[Date of Occurrence].[Date of Occurrence].ALLMEMBERS
    )
}
ON ROWS
FROM [Cube]

Basically, you create a calculated measure which is NULL when the Date of Occurrence lies outsite the Start Date - End Date interval. Thanks to NON EMPTY on the Row members, the data should be filtered out of the result.

0
votes

If [Event Details] and [Project Details] are role playing dimensions, you can put the LINKMEMBER MDX function to use to help you. Also, I am assuming you would be using some sort of front end(maybeSSRS) to give the user the freedom to choose the start and end dates(as parameters or calendar control). In that case, they will enter the MDX query as strings. STRTOMEMBER function converts those string to members.

Using LINKMEMBER, once I generate a set of dates, I am using AGGREGATE function to get the aggregated value of measure for this set of dates.

with set [Start Date] as
linkmember(STRTOMEMBER('[Project Details].[Start Date].[Start Date].&[01/01/2014]'), [Event Details].[Date of Occurrence])

set [End Date] as 
linkmember(STRTOMEMBER('[Project Details].[End Date].[End Date].&[01/01/2015]'), [Event Details].[Date of Occurrence])

set ListOfDate as
{[Start Date].item(0):[End Date].item(0)}


member [Measure.NetCost] as
aggregate(ListOfDates, [Measures].[Cost])

SELECT NON EMPTY
{
    [Measure.NetCost]
}
ON COLUMNS,
NON EMPTY [Project Details].[Project].[Project].ALLMEMBERS  

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM [Cube]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

DISCLAIMER: As stated, it would work only if the [Event Details] and [Project Details] are role playing dimensions.

I am not sure if this code would work in your scenario, but I have found this function quite handy at times. To read more on LINKMEMBER function, see here.

0
votes

Not tested. I've not used && before - maybe just the keyword AND would suffice:

SELECT NON EMPTY
{
    [Measures].[Cost]
}
ON COLUMNS,
NON EMPTY
{
    (
        [Project Details].[Project].[Project].ALLMEMBERS
        * [Project Details].[Start Date].[Start Date].ALLMEMBERS
        * [Project Details].[End Date].[End Date].ALLMEMBERS
        * [Event Details].[Date of Occurrence].[Date of Occurrence].ALLMEMBERS
    )
}
HAVING [Event Details].[Date of Occurrence].CurrentMember.MEMBERVALUE >= 
          [Project Details].[Start Date].CurrentMember.MEMBERVALUE 
        && //<< OR IS THIS JUST "AND"?
        [Event Details].[Date of Occurrence].CurrentMember.MEMBERVALUE <= 
          [Project Details].[End Date].CurrentMember.MEMBERVALUE
ON ROWS
FROM [Cube]