MDX Query Builder

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

esl - 21 Dec 2005 14:23 GMT

Hi All,
I'm looking for a tool that would help me to build mdx queries and
display the resulting mdx so I can copy/paste it wherever I want.
The Report Server in SQL Server 2005 does have one but for some reasons
it does not allow generation of matrix result within the request.
For example, is there any way to view the request performed by the AS
Server when browsing a cube ?
Thanks for your help !

Eric

Reply to this Message

Erik Veerman - 21 Dec 2005 15:03 GMT

Assuming you are using AS 2005 (given RS 2005 with MDX), you could
always run a Profile session against Analysis Services. Then you could
capture the MDX being sent from the built-in browser tool in SSMS and
BIDS (its based on OWC11, I believe).

My favorite 3d party tool for creating more complicated MDX is
Proclarity. View | MDX shows you the MDX sent to the server.

Cheers,
Erik
----
Erik Veerman
erik (at) solidqualitylearning.com

-----Original Message-----
From: esl [mailto:esl@tilbury.com]
Posted At: Wednesday, December 21, 2005 9:23 AM
Posted To: microsoft.public.sqlserver.datawarehouse
Conversation: MDX Query Builder
Subject: MDX Query Builder

Hi All,
I'm looking for a tool that would help me to build mdx queries and
display the resulting mdx so I can copy/paste it wherever I want.
The Report Server in SQL Server 2005 does have one but for some reasons
it does not allow generation of matrix result within the request.
For example, is there any way to view the request performed by the AS
Server when browsing a cube ?
Thanks for your help !

Eric

Reply to this Message

esl - 22 Dec 2005 19:08 GMT

Thanks a lot ! I can now get the query from the log...
The only problem is that when I try to run it in the query editor of
SSMS against the cube I get the following error:
"Results cannot be displayed for cellsets with more than two axes"

Basically, the request is

SELECT
    NON EMPTY {[Cycle].[Brand - Name].[All]}
    DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
    NON EMPTY {[Cycle].[Type - Name].[All]}
    DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON ROWS,
    {
        [Measures].[Unit Count]
    }
    ON PAGES
    FROM [CycleCube2005]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

Would it be possible that the result pane of SSMS is not OWC11 based
and therefore could not displayed this kind of result ?
Thanks !
Eric

> Assuming you are using AS 2005 (given RS 2005 with MDX), you could
> always run a Profile session against Analysis Services. Then you could
[quoted text clipped - 27 lines]
>
> Eric

Reply to this Message