MDX date range query with a missing boundry date

7
votes

I need an MDX query for Mondrian filtered by date, where one or both of the boundry dates may not exist. I'm using the query below that works as long as both 2013-01-01 and 2013-01-08 dimensions exist. If one of the two dates does not exist then it returns no results, even though the dimensions in between do exist. How would I get this query to work even in the case of a missing boundry date dimension?

SELECT
NON EMPTY {Hierarchize({[Measures].[Number of Something]})} ON COLUMNS,
NON EMPTY {[Date].[2013-01-01]:[Date].[2013-01-08]} ON ROWS
FROM [Users]
3
mdxdata-warehouseolappentahomondrian
Is your dimension degenerate or a separate table? If it's a separate table then usually it would be pre-populated, therefore you can make sure all the dimensions do exist.. (Even if there's no associated facts). There's a PDI Kettle transformation in the samples folder for generating a date dimension. – Codek

3 Answers

1
votes

MDX is built with the assumption that every member that you refer to exists; it is best then to make sure all conceivable date dimension members do exist by having a separate table with these values precomputed.

You could get tricky and implement that table as a stored procedure but date dimensions don't take up a lot of space in the grand scheme of things so you'd hardly ever do this.

I don't know of any other way to solve your problem.

0
votes

try to eliminate the NON EMPTY

0
votes

Even I haven't yet understand the reason of implementing this logic, you can hide this by adding . If you add custom member in Mondrian try it. 

    /* Exclude Missing Member */
Create Set CurrentCube.[MissingMemberSet] As
iif(IsError(StrToMember("[Dimension].[Hierarchy].&[MEMBER]")),
{}, {[Dimension].[Hierarchy].&[MEMBER]});

Create Member CurrentCube.Measures.[Calculation on Missing Member]
AS
IIF ([MissingMemberSet].Count > 0,
([Dimension].[Hierarchy].&[MEMBER],Measures.[X Measure]),
0
)
,
FORMAT_STRING = "Currency",
LANGUAGE = 1033,
NON_EMPTY_BEHAVIOR = { [X Measure] },
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Display Folder'  ;

Also you can implement in using IIF(IsError or IIF(Exists MDX functions.