SSAS: Running mulitple MDX Queries

This tip came up during a discussion amongst the Analysis Services Stored Procedure team and Chris Webb just posted about it too.

Apparently you can use the GO keyword to separate MDX batches in SSMS, in much the same way that you can with T-SQL batches

eg.

SELECT MEASURES.MEMBERS ON 0 FROM
[Adventure Works]

GO

SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Day of Week].MEMBERS ON
1
FROM [Adventure Works]

This ability is built in to SSMS, “GO” is not a valid MDX keyword. It is SSMS that is breaking up the script and sending it as separate queries. Apparently the ability to execute multiple statements was explicitly omitted from MDX in order to prevent injection attacks. So even if you are building your query using string concatenation (which is often what you need to do) there is simply no way for a user to insert a semi-colon and break-up your query in order for them to execute a statement of their own, you simply get back the error that "Mulitple statements are not allowed".

 

Print | posted on Monday, August 14, 2006 8:20 PM

Comments on this post

# re: SSAS: Running mulitple MDX Queries

Requesting Gravatar...
Hi Darren,
I am newbie in MDX and had a query if we can implement something like this or not.

Say we have a query with parmameters and it queries the cube based on the parameter.

Now depending on what was the number of records from which we queried the data , we should return the result.

Else if the number of records was less than a specifed number then we change the parameter and widen the search and continue till the number of records exceed the min specified number.

This will require multiple MDX statements to be executed and there is no way we can create Stored Procedures in SSAS.

And i am planning to pass these queries through WebServices , so firing these query multiple times depending on result set may have hit the performance .

Is there anyway to implement this on the Analysis Services side.
Left by Vikash on Jun 23, 2010 12:17 AM

# re: SSAS: Running mulitple MDX Queries

Requesting Gravatar...
Hello Darren,

Whenever I try to run the below given multiple MDX queries on server through SQL JOb, it through error. It works fine in case of single query, but fails in case of multiple queries. I want to do this for warmup of Servers. Please suggest something to me on this.

WITH MEMBER [Measures].[DW_ID] AS [ClaimHandler].[ClaimHandler].currentmember.member_key MEMBER [Measures].[DisplayName] AS [ClaimHandler].[ClaimHandler].currentmember.name MEMBER [Measures].[ManagerName] AS "" SELECT { [Measures].[DW_ID], [Measures].[DisplayName], [Measures].[ManagerName], [Measures].[NetPromoter%] } ON COLUMNS, Filter( [ClaimHandler].[ClaimHandler].children, [Measures].[Response_TotalNumericCount] > 0 ) ON ROWS FROM [TravCSAT] WHERE ( [Time Calculations].[Rolling 12 Months], [Date].[Calendar].[Date].&[20100805], [Questions].[Type_Question].[Question].&[4], [LOB].[LOB].&[1], [Office].[Office].&[60], [UnitManager].[UnitManager].&[2675] )
GO
WITH MEMBER [Measures].[DW_Col_ID] AS [Product].[Product].currentmember.member_key MEMBER [Measures].[Col_DisplayName] AS [Product].[Product].currentmember.name MEMBER [Measures].[DW_Row_ID] AS [ClaimHandler].[ClaimHandler].currentmember.member_key MEMBER [Measures].[Row_DisplayName] AS [ClaimHandler].[ClaimHandler].currentmember.name SELECT { [Measures].[DW_Col_ID], [Measures].[Col_DisplayName], [Measures].[DW_Row_ID], [Measures].[Row_DisplayName], [Measures].[NetPromoter%], [Measures].[Goal], [Measures].[Response_TotalNumericCount] } ON COLUMNS, NONEMPTY( Filter( [Product].[Product].children, [Measures].[Response_TotalNumericCount] > 0 ) * Filter( [ClaimHandler].[ClaimHandler].children, [Measures].[Response_TotalNumericCount] > 0 ) ) ON ROWS FROM [TravCSAT] WHERE ( [Time Calculations].[Rolling 12 Months], [Date].[Calendar].[Date].&[20100805], [Questions].[Type_Question].[Question].&[4], [LOB].[LOB].&[1], [Office].[Office].&[60], [UnitManager].[UnitManager].&[2675] )
GO
Left by Nikesh on Aug 17, 2010 10:23 PM
comments powered by Disqus