Hi everyone,
As I have said before, I am new to MDX. Any help much appreciated.
I am trying to substitute the Exists() function for the NonEmptyCrossJoin() function, but Exists is not producing the results that I would expect. The test query that I am running is:
SELECT {[Measures].[Value]} ON COLUMNS,
NonEmpty({[Fact].[Name].[All].CHILDREN}) ON ROWS
FROM [AS Test1]
WHERE
NonEmptyCrossJoin({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]},
{[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]})
This is not ideal code, but performs correctly, only listing members with start and end dates with the appropriate values. I tried to substitute that query for this:
SELECT {[Measures].[Value]} ON COLUMNS,
NonEmpty({[Fact].[Name].[All].CHILDREN}) ON ROWS
FROM [AS Test1]
WHERE
Exists(NonEmpty({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]}),
NonEmpty({[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]}))
This query effectively ignores the second set (i.e. the End Date set), listing all values with start dates in the given range. Can anyone tell me what I am doing wrong?
Any help much appreciated.
Edit: Adding a measure group at the end of the Exists function and/or removing the NonEmpty functions do not correct the problem.
Not sure whether you've read this blog entry, which discusses different scenarios in which NECJ was used. The closest equivalent is Exists() with measure group, but it depends on the scenario in which NECJ was being used:
http://sqljunkies.com/WebLog/mosha/archive/2006/10/09/nonempty_exists_necj.aspx
>>
MDX: NonEmpty, Exists and evil NonEmptyCrossJoin
...
NonEmptyCrossJoin(set1, set2, ..., setN, K) is equivalent to Exists(set1*...*setK, set(K+1)*...*setN, "measuregroupname")
where "measuregroupname" is the name of the measure group to which the current measure belongs.
...
>>
So, in your case, it should be like:
Exists({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]}
* {[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]},, "measuregroupname")
|||Hi Ed,
First of all, why are you trying to replace NECJ with Exists rather than NonEmpty - are you setting the NullProcessing property anywhere? I think you're better off using the NonEmpty function here.
Secondly, in my experience you should always include a measure somewhere in either of the sets you pass into NonEmpty - especially if you have multiple measure groups in your cube. If you don't then I think what happens is that you remove all the items in your set which are empty for all measures in the cube, which can be confusing when you're only showing one measure and can also slow your query down.
So... does the following do what you want?
SELECT {[Measures].[Value]} ON COLUMNS,
NonEmpty({[Fact].[Name].[All].CHILDREN},[Measures].[Value]) ON ROWS
FROM [AS Test1]
WHERE
(NonEmpty({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]} *
{[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]},[Measures].[Value]))
HTH,
Chris
|||Thank you very much Chris and Deepak for your replies. I'll give those approaches a try.