Steve Hindmarsh's SQL Blog

SSAS - What is running ?

I saw a tweet today from respected blogger john sansom asking how a SQL DBA should attempt to performance tune SSAS.

I've been through a similar scenario - and one of the tools I developed as part of my troubleshooting was my own 'SSAS version' of sp_who.

I've written a stored procedure which I run on a SQL Server instance, that connects to an SSAS instance via a linked server, I then wrote an SSRS report to use this stored proc to allow developers and support staff access to SSAS cube activity.

 It's quite a basic/simple solution but it does help expose what is running - I must say on a fairly low-use SSAS server. I don't know how well this scales on a heavily used SSAS implementation.

To add an SSAS  linked server first of all to your SQL Server..

 EXEC master.dbo.sp_addlinkedserver @server = N'SSAS_LINKED_SERVER', @provider=N'MSOLAP', @datasrc=N'ISSAS ServerName'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSAS_LINKED_SERVER',@useself=N'False',@locallogin=NULL,@rmtuser=N'Account to run command via' ,@rmtpassword='########'

Then I created a stored procedure :-

 CREATE PROC [dbo].[SSAS_whoIsActive]

AS

SELECT ssasSessions.SESSION_SPID AS [SPID],

ssasSessions.SESSION_USER_NAME AS [User ID],

ssasSessions.SESSION_CURRENT_DATABASE AS [Database],

ssasConnections.connection_host_name AS 'Client IP address',

ROUND(CAST(ssasCommands.COMMAND_CPU_TIME_MS AS int)/1000, 2) AS [Command Time(in sec)],

ssasCommands.COMMAND_START_TIME AS [Command Start Time],

ssasCommands.COMMAND_END_TIME AS [Command End Time],

ssasCommands.COMMAND_TEXT AS [MDX Command],

connection_host_application AS 'Application',

CHARINDEX('</Process>',ssasCommands.COMMAND_TEXT) AS ProcessRunning

FROM OPENQUERY(SSAS_LINKED_SERVER,'select * from $system.discover_sessions') AS ssasSessions

LEFT JOIN OPENQUERY(SSAS_LINKED_SERVER,'select * from $system.discover_commands') AS ssasCommands ON ssasSessions.SESSION_SPID = ssasCommands.SESSION_SPID

LEFT JOIN OPENQUERY(SSAS_LINKED_SERVER,'select * from $system.discover_connections') AS ssasConnections ON ssasSessions.SESSION_connection_id = ssasConnections.connection_id

I use the 'ProcessRunning' column to idenitfy if a cube process command is running, and then display this MDX command in a different colour (red) in my report.

This can be done by adding an expression to the report 'MDX Command' text box properties.  =IIF(Fields!ProcessRunning.Value > 0, "Red", "Black")

The MDX command column can sometimes be very large in this report output - it can be quite a shock for a DBA to see how complicated MDX generated by SSAS sometimes can be ! I usually change the font size on this output column to be 8pt.

Note my sproc is named in honour of the famous sp_whoIsActive written by Adam Machanic.