PAS and MDX errors

I’ve been working with a Client to release a Microsoft BI Solution, they started to have problems with ProClarity pages not rendering in a Performance Point Dashboard and producing a message saying the “Your request could not be completed.”

Running the Profiler on the AS Server highlighted the fact that the mdx was causing an error.

The set in the WHERE clause cannot contain multiple measures.

Searching the knowledge based return an interesting article on the error and how to get around it.




Turns out PAS really doesn’t like errors. If a published PAS report produces an error, it crashes the IIS worker process thus rendering the PAS Service useless. The only way I could see to get this working again was to recycle the application pool.



Back to my error, from the article there are quite a few scenarios that could produce this error, the example below produces the error because Berkshire isn’t a city in Canada (or anywhere for that matter, a county perhaps) and the measure is in the where clause.



SELECT
[Product].[Category].MEMBERS ON COLUMNS
FROM
[Adventure Works]
WHERE
([Measures].[Order Count],
[Geography].[City].&[Berkshire]&[ENG],
[Geography].[Country].&[Canada])


The whole scenario is not ideal and does limit the type of reports that can be published to PAS. I’m sure if I ask Microsoft they would tell me ProClarity was designed that way like so many of their other features I've stumbled across. So just be careful if you are using PPS filters or ProClarity filters on you PAS reports, end users might bring down your PAS service.