tim laqua dot com Thoughts and Code from Tim Laqua

21Jul/082

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
FROM (
	SELECT 'Slice1' AS 'SliceName', .8 AS 'Threshold'
	UNION
	SELECT 'Slice2', 0.9
) a
 
DECLARE @tsql NVARCHAR(MAX)
 
DECLARE @slice VARCHAR(255)
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
 
WHILE (@@FETCH_STATUS = 0) 
BEGIN
 
 
	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;'',''
WITH 
MEMBER Measures.[Yesterday Foo] AS SUM
(
	{
		StrToMember("[Time].[Calendar Date].[" + VBA!format(VBA!dateadd("d",-1,VBA![date]()),"yyyy-MM-dd 00:00:00") + "]")
	},
	[Measures].[Foo]		
)
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") + "]")
	},
	[Measures].[Foo]		
)
MEMBER Measures.[Percent Change] AS Measures.[Yesterday Foo]/Measures.[Average 7-Day Foo]
SELECT 
{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)
 
	SELECT  
		@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 
		BEGIN
			--Do Something useful here
 
		END
	FETCH NEXT FROM @slicecursor INTO @slice, @threshold
END
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.