tim laqua dot com Thoughts and Code from Tim Laqua


Dynamic MDX queries in TSQL

Say you want to run the same MDX query for each row in a given rowset. I needed to do this for alerting purposes, where there were different alert thresholds for different attribute values in a given dimension attribute. After struggling with passing a variable to the query argument of the OPENROWSET command, I finally found the documentation that clearly stated that the query argument CAN'T be a variable. Or a concatination of a string and a variable. I still don't understand why... but the suggested workaround is to construct a giant TSQL string and run it using the EXEC command.

Ok - but how do we get the results of the query? Basically, the only way to do this is to create a temporary table in the current scope and do an INSERT INTO that temp table in your giant TSQL query. It all ends up looking something like this:

DECLARE @PercentOfAverage AS DECIMAL(10, 2)
DECLARE @Yesterday AS DECIMAL(10, 2)
DECLARE @SevenDayAverage AS DECIMAL(10, 2)
SELECT SliceName,Threshold
INTO #Temp_SliceList
	SELECT 'Slice1' AS 'SliceName', .8 AS 'Threshold'
	SELECT 'Slice2', 0.9
) a
DECLARE @threshold DECIMAL(10,2)
DECLARE @slicecursor CURSOR
SET @slicecursor = CURSOR FOR
	SELECT slicename,threshold FROM #Temp_SliceList
OPEN @slicecursor
FETCH NEXT FROM @slicecursor INTO @slice, @threshold
	CREATE TABLE #Temp_MdxResults 
	  (Yesterday DECIMAL(10,2),
		SevenDayAverage DECIMAL(10,2),
		PercentOfAverage DECIMAL(10,2))
	SET @tsql = '
INSERT INTO #Temp_MdxResults (Yesterday, SevenDayAverage, PercentOfAverage)
SELECT    ISNULL("[Measures].[Yesterday Foo]", 0) AS ''Yesterday'',
						ISNULL("[Measures].[Average 7-Day Foo]", 0) AS ''SevenDayAverage'',
						ISNULL("[Measures].[Percent Change]", 0) AS ''PercentOfAverage''
			  FROM      OpenRowset(''MSOLAP'',
								   ''DATA SOURCE=SERVER\INSTANCE; Initial Catalog=SSASDB;'',''
MEMBER Measures.[Yesterday Foo] AS SUM
		StrToMember("[Time].[Calendar Date].[" + VBA!format(VBA!dateadd("d",-1,VBA![date]()),"yyyy-MM-dd 00:00:00") + "]")
MEMBER Measures.[Average 7-Day Foo] AS AVG
		StrToMember("[Time].[Calendar Date].[" + VBA!format(VBA!dateadd("d",-2,VBA![date]()),"yyyy-MM-dd 00:00:00") + "]"):StrToMember("[Time].[Calendar Date].[" + VBA!format(VBA!dateadd("d",-9,VBA![date]()),"yyyy-MM-dd 00:00:00") + "]")
MEMBER Measures.[Percent Change] AS Measures.[Yesterday Foo]/Measures.[Average 7-Day Foo]
{Measures.[Yesterday Foo], Measures.[Average 7-day Foo], Measures.[Percent Change]} ON 0,
	[Slice Dimension].[Slice Attribute].&[' + @slice + ']
}ON 1
FROM [Cube]'') a'
	EXEC (@tsql)
		@PercentOfAverage = CAST(tot.PercentOfAverage AS DECIMAL(10, 2)),
		@Yesterday = CAST(tot.Yesterday AS DECIMAL(10, 2)),
		@SevenDayAverage = CAST(tot.SevenDayAverage AS DECIMAL(10, 2))
	FROM    #Temp_MdxResults tot
	DROP TABLE #Temp_MdxResults
	IF @PercentOfAverage < @Threshold 
			--Do Something useful here
	FETCH NEXT FROM @slicecursor INTO @slice, @threshold
Comments (2) Trackbacks (0)
  1. Thanks, for the great sample. You really have a knack for getting right to the heart of it.

Leave a comment

No trackbacks yet.