Is there a way to see the underlying MDX when querying through the cube
browser, either through Management Studio or Analysis Project?

I tried with SQL Profiler, and am seeing something that is not too
useful:

SELECT
NON EMPTY [{E551D847-D2CF-4C00-81AE-A8BA98DF2363}Pivot54Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
{
[Measures]...
}
ON ROWS
FROM [Adventure Works]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

What you're seeing is indeed the MDX generated by the cube browser -
unfortunately most ad hoc query tools don't generate very clear MDX. Are you
looking to learn MDX? You're probably better off buying a book - here's a
list of AS books which are available or will be soon:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!322.entry

Trying to be 100% impartial, the consensus is that for absolute MDX
beginners then 'Fast Track to MDX' is a good place to start; various other
general AS books will have an introductory chapter or two on MDX. The second
edition of 'MDX Solutions' (of which I'm a co-author), which will be
published fairly soon, is I believe the only book that will go in-depth on
AS2005 MDX.

HTH,

Chris

--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Rose" wrote:

Hi Chris,

I have bought 2 books on MDX:
'Fast Track to MDX - 2nd Edition' and 'MDX Solution - 1st edition'

As we need the knowledge last year, we could not wait for the 2nd
edition to be released. Do you know which distribution/publish give
better offers for the 2nd edition, e.g. free copy of ebook is included
for those order directly from them, etc..?


Most of my team members are from OLTP/ETL background and are very
comfortable with TSQL. We are struggling/suffering from MDX, as it
looks like TSQL but is actually very different. We are struggling with
even very simple things sometimes, e.g.
1. finding out if there's a ISNULL() equivalent in MDX that can help
us handle the invalid dimension dimension elegantly (I posted my
question here
http://groups.google.com/group/micro...620eeb057390a),
and also
2. the equivalent for <>, e.g. in TSQL it will be WHERE UserID <> 100.
We found a solution using EXCEPT(set1, set2) but are not sure if
that's the right way..... you can imagine the frustrations.....

Don't worry, I think we all felt the same way when we started learning MDX!
But when it all clicks, you'll never want to go back to SQL...

I have no idea if any of the books I mentioned have offers for people who
already own the first edition, I'm afraid.

Regarding your questions:
1) Where is the list of valid values you're using for your report parameter
coming from? It sounds like you're getting the list from the relational
source - if you got them from the cube instead you wouldn't have this
problem. For example you could use an MDX query which returned all the
Product Category members on rows (it should be easy to do this in the MDX
query designer) and bind that to your parameter.

The error you're seeing is a Reporting Services error so you won't be able
to tweak your MDX query to handle it better (in fact, you'll find that AS2005
handles this particular problem quite elegantly - see
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!166.entry
); I don't know whether it's possible to get RS to display a custom error in
this case though.

2) The EXCEPT function is the right way to go if you want to exclude members
whose names you already know; something like the FILTER function can be used
to filter sets based on measure values.

HTH,

Chris

--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Rose" wrote:

1) The report parameter is coming from AS indeed, and Reporting
Services does auto-generate a MDX that retrieves a list of the valid
values as you described. All is well, except that the consumer
web-application can invoke a report with a URL access without first
validating if that's an valid Dimension member.

Yes, you are absolutely right, the error is from Reporting Services,
because the parameter value received is not in the available value
list!
Now, if I can have another parameter that is accepting the value and is
not bound to the available value list, and when processing a call with
an invalid value, the error displayed is now
"Query (1, 598) The restrictions imposed by the CONSTRAINED flag in the
STRTOSET function were violated. "

Now, that's an AS error, because [Product].[Category].&[5] (a
non-existing Dimension Member) has been used to make such a query:

WITH MEMBER [Measures].[Profit] AS
'[Measures].[Sales Amount]-[Measures].[Standard Product Cost]'
SELECT NON EMPTY { [Measures].[Internet Sales Amount],
[Measures].[Internet Total Product Cost],
[Measures].[Internet Order Quantity] } ON COLUMNS,
NON EMPTY { ([Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS *
[Sales Territory].[Sales Territory Group].[Sales Territory
Group].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME,
[Sales Reason].[Sales Reason].[Sales Reason].KEY,
[Sales Territory].[Sales Territory Group].[Sales Territory Group].KEY
ON ROWS
FROM ( SELECT ( STRTOSET('[Product].[Category].&[5]', CONSTRAINED) ) ON
COLUMNS FROM [Adventure Works])
WHERE ( IIF( STRTOSET('[Product].[Category].&[5]', CONSTRAINED).Count =
1,
STRTOSET('[Product].[Category].&[5]', CONSTRAINED),
[Product].[Category].currentmember ) )


In your posting, 'IIF(VBA!ISERROR(STRTOVALUE("MEASURES.[THIS WONT
WORK]")), 1,0)' is able to catch an invalid MEASURE. I suspect maybe
the following can help me to detect invalid dimension members?
WITH MEMBER MEASURES.TEST AS
'IIF(VBA!ISERROR(STRTOSET("[Product].[Category].&[1]",
CONSTRAINED).COUNT),1,0)'
MEMBER MEASURES.TEST2 AS
'IIF(VBA!ISERROR(STRTOSET("[Product].[Category].&[5]",
CONSTRAINED).COUNT),1,0)'
SELECT {MEASURES.TEST, MEASURES.TEST2} ON 0 FROM [Adventure Works]

Then, if I have prepared a 'DEFAULT' Dimension Member
([Product].[Category].&[-999] in the example below) to handle all the
invalid calls, then I may be able to tweak the MDX to the following?

WITH MEMBER [Measures].[Profit] AS '[Measures].[Sales
Amount]-[Measures].[Standard Product Cost]'
SELECT NON EMPTY { [Measures].[Internet Sales Amount],
[Measures].[Internet Total Product Cost], [Measures].[Internet Order
Quantity] } ON COLUMNS,
NON EMPTY { ([Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS )
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME,
[Sales Reason].[Sales Reason].[Sales Reason].KEY, [Sales
Territory].[Sales Territory Group].[Sales Territory Group].KEY ON ROWS
FROM ( SELECT ( IIF( STRTOSET(@ProductCategory).Count = 1,
STRTOSET(@ProductCategory, CONSTRAINED), [Product].[Category].&[-999] )
) ON COLUMNS
FROM [Adventure Works]) WHERE ( IIF( STRTOSET(@ProductCategory).Count =
1, STRTOSET(@ProductCategory, CONSTRAINED),
[Product].[Category].currentmember ) )



2. Chris, what's the function of the CONSTRAINED flag?
I found that if I include this CONSTRAINED flag into IIF(
STRTOSET(@ProductCategory, CONSTRAINED).Count = 1, the query above
stops working.

The CONSTRAINED flag is a security feature, and from what I understand it was
introduced in AS2005 MDX to stop MDX injection attacks (which I guess might
now be technically possible). What it does is restrict the StrToXXX family of
functions so that they no longer accept any MDX expression which evaluates to
a set - you have to use explicitly stated members. So, for example, StrToSet
has to take a string containing a list of unique names of members, and in
your example the fact that one of the members doesn't exist, which might
ordinarily be ignored, is now treated as a possible security problem and
instead raised as an error.

Of course as you recognise the real problem is that your app can invoke a
report without checking that the parameter relates to a real dimension
member. You probably ought to fix this rather than using StrToSet without
CONSTRAINED; at least, I don't want to be held responsible for any future
problems if you did use StrToSet without CONSTRAINED!
--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Rose" wrote:

Got your point!

OK, lets get the consuming application to first query AS to verify if
the member exists, would something like this be the correct kind of
query to be used?

WITH MEMBER [Measures].[X]
AS
STRTOSET('[Product].[Category].&[1000]', CONSTRAINED).Count
SELECT [Measures].[X] ON 0
FROM [Adventure Works]

My testing shows the count=1 is returned if the member is valid,
otherwise #ERROR is returned.

Yes, that seems ok if you're happy you can trap the error in your code.
--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Rose" wrote:

As there is no way to see the underlying MDX generated...., what
shall I do to get AS to return the data that is same when the "Show
Empty Cells" is enabled in a cube browser?

For example, "Show Empty Cells" is enabled when filtering by Country:
Country City Sales
UK London $100,000.00
UK Liverpool (blank)
.....

When it is disabled, only UK->London is return because the data is not
blank.

I tried with including/excluding "NON EMPTY" in the MDX, there is no
difference

I'm a bit confused about what you're asking here, can you explain further
with an example query?
--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Rose" wrote: