Monitoring SSAS with Power BI & Kill the Sessions via Dynamic Management Views

Our company, which is a retail business, has thousands of chain stores, and we create enterprise cubes over billions of data for hundreds of people who work and analyze data via OLAP technology in the headquarter. So, we need to monitor what is happening in SQL Server Analysis Services at the moment. Therefore, we have played with DMVs, exclusively DISCOVER_SESSIONS, which helped us to get tabular report of sessions established on Analysis Service as an MDX query. The MDX query is so simple:

SELECT *
FROM $SYSTEM.DISCOVER_SESSIONS

But we want to connect this query to Power BI with Import Data visualize it, and refresh the data behind the report every one minute. Thus we prepare below query which contains our needs.

SELECT 
    [SPID] = SESSION_SPID,
    [USERNAME] = SESSION_USER_NAME,
    [DATABASE] = SESSION_CURRENT_DATABASE,
    [LAST_COMMAND] = SESSION_LAST_COMMAND,
    [STATUS] = SESSION_STATUS,
    [CPU_MINUTE] = SESSION_CPU_TIME_MS / 60000,
    [READ_GB] = SESSION_READ_KB / POWER(2, 20),
    [COMMAND_COUNT] = SESSION_COMMAND_COUNT
FROM 
    OPENQUERY([MD], 
    '
        SELECT *
        FROM $SYSTEM.DISCOVER_SESSIONS
    ')

With OPENQUERY we execute this query on an analysis server which is defined as the linked server. To find guidance for setup SSAS linked server, please refer to this article.

In the next step, we connect Power BI report to the SQL Server through selecting Import and Master DB as optional database.

Just be careful that you should transform SPID and STATUS as Text, because Power BI consider them as number and measure. STATUS means whether the command is running or not when the report query is firing.

Afterward, we design this clear report by using Power BI tools.

Now we can find sessions and users with highly consuming resources (e.g. Disk Read) through time and then we kill the session as we coded below by XMLA command.

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <SPID>12345</SPID>
 <CancelAssociated>true</CancelAssociated>
</Cancel>

Or all the user’s sessions could be terminated by a cursor which we have written in the following: