MS SQL Server OLAP - Seeking Explanation: Why do calc. members in the WHERE clause behave like sets?

Author

Seeking Explanation: Why do calc. members in the WHERE clause behave like sets?

Keith

2006-03-29, 1:30 pm

Hello,

Could some explain how/why calculated members behave like sets when used in
the WHERE clause of a query?
For example (using Foodmart 2000):

** Query A
WITH MEMBER [Product].[ProdAgg] as 'Aggregate({[Product].[All
Products].[Drink],[Product].[All Products].[Non-Consumable]})'

SELECT {[Store].[Store State].[CA]} on 0, {[Measures].[Store Sales]} on 1
from [Sales]

Result: 159,167.84

** Query B
WITH MEMBER [Product].[ProdAgg] as 'Aggregate({[Product].[All
Products].[Drink],[Product].[All Products].[Non-Consumable]})'

SELECT {[Store].[Store State].[CA]} on 0, {[Measures].[Store Sales]} on 1
from [Sales]

WHERE ([Product].[ProdAgg])

Result: 43,974.67

** Query C
WITH MEMBER [Product].[ProdAgg] as 'Aggregate({[Product].[All
Products].[Drink],[Product].[All Products].[Non-Consumable]})'

SELECT {[Store].[Store State].[CA]} on 0, {[Measures].[Store Sales]} on 1
from [Sales]

WHERE ([Product].[All Products].[Drink])

Result: 14,203.24

** Query D
WITH MEMBER [Product].[ProdAgg] as 'Aggregate({[Product].[All
Products].[Drink],[Product].[All Products].[Non-Consumable]})'

SELECT {[Store].[Store State].[CA]} on 0, {[Measures].[Store Sales]} on 1
from [Sales]

WHERE ([Product].[All Products].[Non-Consumable])

Result: 29,771.43

Query C + Query D = Query B

So in the WHERE clause, the calc. member behaves like a filter set
(side-stepping the unique dimensionality rule of tuples), but it you put it
on an axis you get the value of the aggregation.

Does the calc. member only store the definition of its value (the set used
to produce the value), and return either the value or its definition
depending on the context of its use? I'm not suggesting this behavior is
bad - it is actually very useful since you can't use sets to slice in the
WHERE clause - but is it documemented anywhere? Is my analysis of this
behavior close to correct?

Perhaps some of the MDX gurus and MVP's could shed some light on this for
me.

Thanks!

Keith

Deepak Puri

2006-03-30, 3:26 am

Hi Keith,

It's not clear what distinction you're drawing between the calculated
member in the where clause versus on a query axis. For example, if query
B is restructured to place [Product].[ProdAgg] on Axis(1), the value
returned is still the same (43,974.67):
[color=darkred]
WITH MEMBER [Product].[ProdAgg] as
'Aggregate({[Product].[All Products].[Drink],
[Product].[All Products].[Non-Consumable]})'

SELECT {[Store].[Store State].[CA]} on 0,
{[Product].[ProdAgg]} on 1
from [Sales]

WHERE ([Measures].[Store Sales])[color=darkre
d]

If you're still working with AS 2000, you can refer to Chapter 6 of the
MDX Solutions book (1st Edition) for a discussion of the ways that
values can be calculated (the recently released 2nd Edition covers AS
2005 as well).

However, with AS 2005 you actually can specify a set in where clause;
and a calculated member like [Product].[ProdAgg] will be treated as a
set "behind the scenes" - see this discussion on Mosha's blog:

http://sqljunkies.com/WebLog/mosha/...tiselect_friend
ly_mdx.aspx[color=darkred]
...
The key for the understanding is the query calculated member [XL_QZX]
that is generated by Excel. It is Aggregate over WA and CA, and it is
put into the WHERE clause of MDX query.
...
From the very first version, OLEDB for OLAP had a provision for provider
to specify not just a single tuple in the WHERE clause, but a set. The
thinking was that by using set in the WHERE clause client applications
could implement multiselect functionality. However, neither OLAP
Services 7.0 nor Analysis Services 2000 ever implemented sets in the
WHERE clause
...
Analysis Services 2005 has completed most of OLEDB for OLAP compliance,
and properly implements sets in the WHERE clause. AS's query engine
recognizes the shape of the queries where there is query calculated
member doing Aggregate over constant single grain set, and this
calculated member (or members if there are multiple multiselects in
different hierarchies) is in the WHERE clause. And when AS detects this
situation, it replaces the calculated member in the WHERE clause with
the corresponding set.
...[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Keith

2006-03-30, 1:30 pm

"Deepak Puri" < deepak_puri@progress
ive.com> wrote in message
news:%23FGNwa6UGHA.2492@TK2MSFTNGP11.phx.gbl...
> Hi Keith,
>
>
> It's not clear what distinction you're drawing between the calculated
> member in the where clause versus on a query axis. For example, if query
> B is restructured to place [Product].[ProdAgg] on Axis(1), the value
> returned is still the same (43,974.67):
>

Deepak,

Thanks for the reply. I'm aware that if I put the calc. member on an axis I
get the same value. But then why don't I get the same results when I
explicitly use the value? For example, if I use

WITH [Product].[ProdAgg] as 'StrToVal("43,974.67")'

and use ProdAgg in the WHERE clause, I don't get the same behavior.

I guess I'm just confused by the fact that calc. members sometimes return
values, and sometimes the set that produces that value.

k

Deepak Puri

2006-03-30, 8:27 pm

Well, here's a query with the explicit value:
[color=darkred]
WITH MEMBER [Product].[ProdAgg] as
'StrToVal("43974.67")',
FORMAT_STRING = 'Currency'

SELECT {[Store].[Store State].[CA]} on 0,
{[Measures].[Store Sales]} on 1
from [Sales]

WHERE ([Product].& #91;ProdAgg])[color=
darkred]

So, I guess I'm confused by why you think that a set is being returned..

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Mosha Pasumansky [MS]

2006-03-30, 8:27 pm

Such calculated members when put in WHERE clause are indeed sometimes are
treated as sets in WHERE clause.
For more detailed discussion please check this article:

http://www.mosha.com/msolap/article...selectcalcs.htm

--
====================
==========_=========
===========
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.
====================
==========_=========
===========
"Keith" <keith@alh.com> wrote in message
news:%23AW8NG2UGHA.5652@TK2MSFTNGP09.phx.gbl...
> Hello,
>
> Could some explain how/why calculated members behave like sets when used
> in the WHERE clause of a query?
> For example (using Foodmart 2000):
>
> ** Query A
> WITH MEMBER [Product].[ProdAgg] as 'Aggregate({[Product].[All
> Products].[Drink],[Product].[All Products].[Non-Consumable]})'
>
> SELECT {[Store].[Store State].[CA]} on 0, {[Measures].[Store Sales]} on 1
> from [Sales]
>
> Result: 159,167.84
>
> ** Query B
> WITH MEMBER [Product].[ProdAgg] as 'Aggregate({[Product].[All
> Products].[Drink],[Product].[All Products].[Non-Consumable]})'
>
> SELECT {[Store].[Store State].[CA]} on 0, {[Measures].[Store Sales]} on 1
> from [Sales]
>
> WHERE ([Product].[ProdAgg])
>
> Result: 43,974.67
>
> ** Query C
> WITH MEMBER [Product].[ProdAgg] as 'Aggregate({[Product].[All
> Products].[Drink],[Product].[All Products].[Non-Consumable]})'
>
> SELECT {[Store].[Store State].[CA]} on 0, {[Measures].[Store Sales]} on 1
> from [Sales]
>
> WHERE ([Product].[All Products].[Drink])
>
> Result: 14,203.24
>
> ** Query D
> WITH MEMBER [Product].[ProdAgg] as 'Aggregate({[Product].[All
> Products].[Drink],[Product].[All Products].[Non-Consumable]})'
>
> SELECT {[Store].[Store State].[CA]} on 0, {[Measures].[Store Sales]} on 1
> from [Sales]
>
> WHERE ([Product].[All Products].[Non-Consumable])
>
> Result: 29,771.43
>
> Query C + Query D = Query B
>
> So in the WHERE clause, the calc. member behaves like a filter set
> (side-stepping the unique dimensionality rule of tuples), but it you put
> it on an axis you get the value of the aggregation.
>
> Does the calc. member only store the definition of its value (the set used
> to produce the value), and return either the value or its definition
> depending on the context of its use? I'm not suggesting this behavior is
> bad - it is actually very useful since you can't use sets to slice in the
> WHERE clause - but is it documemented anywhere? Is my analysis of this
> behavior close to correct?
>
> Perhaps some of the MDX gurus and MVP's could shed some light on this for
> me.
>
> Thanks!
>
> Keith
>
>
>

Sponsored Links