Given the following:



Dimension Name: Issues

Measure: Issue Count

Dimension Attributes: Priority (Values 1,2,3,4)

Severity (Values 1,2,3,4)



How can I format an MDX statement to select Issues Where (Priority = 1 or
Priority = 2) and (Severity = 1 or Severity = 2)?



When I try the following:



select [Measures].[Issue Count] on columns,

([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows

From [Issues DB]

where

{

([Issues].[Priority].&[1]),([Issues].[Priority].&[2]),

([Issues].[Severity].&[1]), [Issues].[Severity].&[2]

}



I get the error referenced below:



"Members belong to different hierarchies in the function."



Any pointers would be greatly appreciated!



~Steven

Here is how you can do it:


select [Measures].[Issue Count] on columns,

([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows

From [Issues DB]

where

{

([Issues].[Priority].&[1],[Issues].[Severity].&[2]),

([Issues].[Priority].&[2],[Issues].[Severity].&[1])

}


--
==============================*=================== =
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL*og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================*=================== =
"Steven" <stevenheinz57 (AT) hotmail (DOT) com> wrote

Correct me if I am wrong Mosha, but I would have thought that would only
give you tuples where (priority = 1 and severity = 2) OR (priority = 2
and severity = 1). It will not give you everything where priority is 1
or 2 and severity is 1 or 2

I would have thought you would either have had to include all
combinations like so:

select [Measures].[Issue Count] on columns,
([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows
From [Issues DB]
where
{
([Issues].[Priority].&[1],[Issues].[Severity].&[2]),
([Issues].[Priority].&[2],[Issues].[Severity].&[1]),
([Issues].[Priority].&[1],[Issues].[Severity].&[1]),
([Issues].[Priority].&[2],[Issues].[Severity].&[2])
}

Or create them with a crossjoin

select [Measures].[Issue Count] on columns,
([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows
From [Issues DB]
where
{
CROSSJOIN({[Issues].[Priority].&[1],[Issues].[Priority].&[2]},{[Issues].
[Severity].&[1]),[Issues].[Severity].&[2]})
}

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Yes Darren - I think you are right, I misread the original question, and
tried to answer different problem.
Your answer is correct. However, it will not perform as efficient, as the
query rewrite below. Basically, if you can describe the set as crossjoin of
other sets (preferable single attribute ones) - you should always do it, but
especially if you put this set into WHERE clause and/or subselect.

select [Measures].[Issue Count] on columns,
([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows
From [Issues DB]
where
{ [Issues].[Priority].&[1], [Issues].[Priority].&[2] } * {
[Issues].[Severity].&[1] * [Issues].[Severity].&[2] }

--
==============================*=================== =
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL*og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================*=================== =
"Darren Gosbell" <xxx (AT) xxx (DOT) com> wrote

I did offer 2 suggestions, one listing out the tuples and one using the
CROSSJOIN() function. Are you saying that the * operator is faster than
the CROSSJOIN() function or did you not notice my other alternative?
I find CROSSJOIN() easier to read if I only have 2 sets to cross.

I mainly listed out the first alternative to show all the different
combinations. I did think not think that for this small example that
there would be much difference in performance between the two
approaches.

Would I be right in saying that it is the auto exists feature that is
boosting the performance in AS2k5 this case?

(I also prefer the concise nature of the crossjoined sets, I think it
makes the query easier to read and understand)

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

No - it just shows that I am not in good shape. Somehow I only saw first
half of your replay. There is no difference in performance between CROSSJOIN
and * operator.

Well, I am still glad that I missed the second part of your reply, because
it allowed me to make this important note about performance.

Could you clarify more on this point please.The autoexist operation would've
produced same results for both sets (although it is still faster to do it
when sets were crossjoin'ed for the similar reasons as I listed), but
autoexist is really not playing any role here - sets in WHERE clause for
that query are purely applying visual totals on the measure values.

--
==============================*=================== =
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL*og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================*=================== =

I kind of guessed as much I can only imagine what it would be like at
MS so close to launch after a 5 year gap between major releases.

I was thinking that as opposed to listing explicit tuples, when you
crossjoin 2 sets, autoexists would kick in and only evaluate tuples that
existed.

I am just trying to understand why evaluating tuples that were a result
of a crossjoin would be faster than evaluating an explicit list of
tuples. On a more complicated query I could see that autoexists might be
a factor.

If it is simply that that the MDX engine works better with sets I'm cool
with that.

Thanks for taking the time to respond.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell