none
Use of IF..THEN combined with OPENROWSET (MDX)

    Question

  • Hi All,

    I have been struggling with the following (simplified) query:

    -- Simplified test query --
    
    -- Create target table
    CREATE TABLE #TEMP_PROCESS_DATE
    (DB varchar(50))
    
    -- Create and set test variable
    DECLARE @ASdatabaseProcessed bit = 0
    
    -- If @TEST = 1 (processed) then insert into target table using OPENROWSET 
    -- If @TEST = 0 (not processed) then insert some fixed values in target table
    
    IF @ASdatabaseProcessed = 1
    
    BEGIN
    	INSERT INTO #TEMP_PROCESS_DATE
    	SELECT * FROM
    	OPENROWSET('MSOLAP', 'DATASOURCE=MBPEVDO;Initial Catalog=BIBreda_AS',N'
    	WITH 
    	MEMBER [Measures].DBName AS [Measures].DefaultMember.Properties("CATALOG_NAME") 
    	SELECT {[Measures].DBName} ON 0 FROM [BI Breda]
    	') 
    END	
    
    ELSE
    
    BEGIN
    	INSERT INTO #TEMP_PROCESS_DATE 
    	VALUES ('DatabaseName')
    END
    
    -- Select to check the outcome
    SELECT * FROM #TEMP_PROCESS_DATE
    
    -- Cleanup
    DROP TABLE #TEMP_PROCESS_DATE

    When I run this with the variable set to 0 I would expect the query to skip the openrowset part en jump to the insert of the fixed values. Regardless if the Analysis Database is processed or not.

    It does not, it still tries to execute the openrowset part (and fails as the AS database that is queried is not processed).

    When I run the same query after the AS database has been processed, it runs just fine giving me "DatabaseName" as the outcome (when the variable is set to 0) or "BIBreda_AS" (when the variable is set to 1)

    The following version does work:

    --- Working version ---
    
    -- Create target table
    CREATE TABLE #TEMP_PROCESS_DATE
    (DB varchar(50))
    
    -- Create and set test variable
    DECLARE @ASdatabaseProcessed bit = 0
    
    -- If @TEST = 1 (processed) then insert into target table using OPENROWSET 
    -- If @TEST = 0 (not processed) then insert some fixed values in target table
    
    -- Declare a storage variable for the SQL
    DECLARE @SQL varchar(2000)
    SET @SQL =
    
    '
    	INSERT INTO #TEMP_PROCESS_DATE
    	SELECT * FROM
    	OPENROWSET(''MSOLAP'', ''DATASOURCE=MBPEVDO;Initial Catalog=BIBreda_AS'',N''
    	WITH 
    	MEMBER [Measures].DBName AS [Measures].DefaultMember.Properties("CATALOG_NAME") 
    	SELECT {[Measures].DBName} ON 0 FROM [BI Breda]
    	'') 
    '
    IF @ASdatabaseProcessed = 1
    
    BEGIN
    	EXEC (@SQL)
    END
    
    ELSE
    
    BEGIN
    	INSERT INTO #TEMP_PROCESS_DATE (DB)
    	VALUES ('DatabaseName')
    END
    
    -- Select to check the outcome
    SELECT * FROM #TEMP_PROCESS_DATE
    
    -- Cleanup
    DROP TABLE #TEMP_PROCESS_DATE

    When I put the openrowset part in a separate variable it does work (with a non processed AS database and the variable set to 0). Now it skips the openrowset part and just inserts the fixed values.

    But somehow I think this workaround should not be neccesary as both queries essentially do the same. Or am I missing something here?

    Any ideas on this?

    Cheers, Erik

    Tuesday, July 31, 2012 12:44 PM

Answers

  • I can see the mdx query being sent to SSAS, from Profiler, even if the value of the variable is zero and this element as part of the XMLA "<ExecutionMode>Prepare</ExecutionMode>". So my guess is that SQL Server insists in geting metadata information about the resultset during compilation time.

    I would stick to the dynamic execution to be safe. SQL Server will inquire for metadata but only if it is executed.


    AMB

    Some guidelines for posting questions...

    Tuesday, July 31, 2012 3:11 PM
    Moderator

All replies