SSAS (ROLAP) with Azure SQL Data Warehouse

Let's consider you are using SQL Server Analysis Services in MOLAP (Multi-Dimensional Online Analytical Processing) Storage Mode with Azure SQL Data Warehouse and everything has been working as expected. Due to a requirement, the Storage Mode was changed to ROLAP (Relational Online Analytical Processing) Mode.

Now when you try to execute a simple MDX Query (In our case we are using Adventure Works)

Query:

SELECT [Measures].[Internet Sales Amount] ON 0,

NON EMPTY {[Date].[Calendar Year].members} ON 1

FROM [Adventure Works]

You would receive the following error:

Executing the query ...

Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: Parse error at line: 1, column: 51: Incorrect syntax near '@ResultCode'..

Run complete

You would receive the same error when you browse the Cube from SSMS:

To understand the type of query causing this issue, we tried to capture a SQL profiler trace, but since this is Azure SQL Data Warehouse we don't have that option. The only other option was to capture a Memory Dump of the process to check the command text of the query being executed when this error occurred.

This is the Exception from the Dump:

Exception type:   System.Data.SqlClient.SqlException

Message:         Parse error at line: 1, column: 51: Incorrect syntax near '@ResultCode'.

StackTrace:

System_Data!System.Data.SqlClient.SqlConnection.OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1<System.Action>)+0x11a

System_Data!System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(System.Data.SqlClient.TdsParserStateObject, Boolean, Boolean)+0x327

System_Data!System.Data.SqlClient.TdsParser.TryRun(System.Data.SqlClient.RunBehavior, System.Data.SqlClient.SqlCommand, System.Data.SqlClient.SqlDataReader, System.Data.SqlClient.BulkCopySimpleResultSet, System.Data.SqlClient.TdsParserStateObject, Boolean ByRef)+0x13e1

System_Data!System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()+0x5c

System_Data!System.Data.SqlClient.SqlDataReader.get_MetaData()+0x6a

System_Data!System.Data.SqlClient.SqlCommand.FinishExecuteReader(System.Data.SqlClient.SqlDataReader, System.Data.SqlClient.RunBehavior, System.String)+0x12b

This is the SQL Query:

DECLARE @ResultCode INT;

DECLARE @TraceId INT;

EXEC @ResultCode = sp_trace_create @TraceId OUTPUT, 1;

SELECT @ResultCode as ResultCode, @TraceId as TraceId;

As you can see above, the query being executed is sp_trace_create which is the Stored Procedure used to create a SQL profiler Trace. As mentioned above, Azure SQL Data Warehouse doesn't support creating SQL Traces which is the cause of the above error.

But the question here is, why is SSAS executing a Stored Procedure to create a SQL Trace?

The answer is simple, SSAS in ROLAP Mode doesn't have pre-processed data and therefore dynamically generates and executes queries against the data source to retrieve the required data which is cached locally. Now that the data is cached, Analysis Services will continue to use the cached data until it gets notified that the backend data has changed.

This notification is handled by creating a SQL Trace, to check if any DML statements have been executed against the SQL Table.

By default, when you enable ROLAP Storage Mode, proactive caching is enabled and the notification is set to the SQL Server option. If you open this property you would see the following warning:

The current notification method is set to SQL Server. This option requires that either the data source or the Analysis Services service account be configured to use an account with either System Administrator or ALTER TRACE privileges on the SQL Server.

From our MSDN Documentation: https://msdn.microsoft.com/en-us/library/ms183681.aspx

Therefore to work around not having SQL Trace permissions, you would need to use either Client Initiated or Scheduled Polling notification.

Please follow our MSDN documentation to choose the preferred option for your business requirements:

• Notifications (Analysis Services - Multidimensional Data): https://msdn.microsoft.com/en-us/library/ms183681.aspx

Author:         Kane Conway - Support Escalation Engineer, SQL Server BI Developer team, Microsoft

Reviewer:     Sarath Babu Chidipothu - Support Escalation Engineer, SQL Server BI Developer team, Microsoft