Data Warehousing and Business Intelligence

25 December 2010

Who Are Using The Cubes Now?

Filed under: Analysis Services — Vincent Rainardi @ 1:26 pm
Tags:

To find out who are accessing the cubes right now, there are several ways:

  1. Query the SSAS query log table
  2. Use Activity Viewer application
  3. Query the Discover_Connection DMV (or the schema rowset XMLA)

1. Query log table

select MSOLAP_Database, max(StartTime) as last_access_time,
MSOLAP_User, count(*) as hits, sum(duration) as duration
from OlapQueryLog
where MSOLAP_User is not null –system/internal
and StartTime >= getdate()-0.5/24 –in the last 1/2 hour
and MSOLAP_User <> ‘Domain\MyUser’ –exclude me
and MSOLAP_User <> ‘Domain\ServiceUser’ –exclude service account
group by MSOLAP_Database, MSOLAP_User
order by MSOLAP_Database desc, max(StartTime) desc

Note:

  1. We need to activate the query log first. See William E. Pearson III article here and Edward Melomed article here, both on how to configure SSAS Query Log.
  2. If you do enable the query log on a busy production machine, I would advise to archive the entries to a “usage mart” (see here), and truncate the table every day/week.
  3. To see individual log entry remove the group by clause.
  4. Too see all queries by a particular user:
    select * from OlapQueryLog where MSOLAP_User = ‘Domain\User1’ order by StartTime desc
  5. To find out what was the user executing on #4 above, look at the Dataset column. It is a series of 0 and 1 with several commas, like this:
    0100,00000,010,00,000
    Which means: the measure group has 5 dimensions (the group of numbers separated by commas). The query is using dim1 and dim3 (the one with 1 on them). Dim1 has 4 attributes, dim2 has 5 attributes, dim3 has 3 attribute, dim4 has 2 attributes and dim5 has 3 attributes.
  6. To know what dim1 and dim2 are in #5 above, look at the cube structure tab in BIDS. That’s the order. Alternatively you can script the cube to XMLA and look under Cube Dimension section. Not the database dimension but the cube dimension. I should mention that sometimes the order of the dimension in the Query Log doesn’t match the cube dimension order. I have seen cases where the order of dims in cube dimension are: 1,2,3,4,5,6,7,…,20,21,22 but the order in the Dataset column in the query log is: 1,2,3,4,5,6,9,11,12,13,14,8,10,15,16,17,18,19,20,7,21,22. So I would advice to test: query the cube with just 1 dim on the row and compare the value on the dataset column.
  7. In #5 above, if all attributes in the dim will have the value of 1, it means that one of the attribute on that dim has a default value. For example:
    0100,00000,010,00,111111
    Dim 5 has all attributes = 1, which means that 1 (or more) attribute on Dim5 has a default value.
  8. The number of digit in the attribute count in #5 above includes hidden attributes and disabled attributes, but excludes the hierarchies. For example: Dim product has 3 attribute. Attribute 1 is disabled (enabled = false), attribute 2 is hidden (visible = false) and attribute 3 is active. The number of digits for dim product is 3, not 2 or 1, i.e. 000, not 00 or 0. If dim product has a hierarchy, the number of digits is still 3, i.e. 000 not 0000.

2. Use Activity Viewer application

Activity Viewer is an application that enables us to see what is currently happening the SSAS server, i.e. users, sessions, queries, I/O and CPU. It is written by Luis Ballesteros, Alli Curley, Malini Jagannadhan, Meera Srinivasan, and others. The 2008 version is here. The 2005 version is here.

What I describe here is for the 2008 version. When started for the first time, it asks for the SSAS server name. This server name is stored in an XML file in the same folder as the exe. We can add more than 1 server.

Then click on “Users” on the left. This displays who’s currently connecting to the SSAS server. If a user has high memory or CPU, click on that user then click on Details at the bottom. This displays the session details. Scroll to the right to find ‘Session Last Command’ column, which contains the MDX executed by that user. Unfortunately we can’t copy the MDX.

If we want to terminate a session (I usually combine with OLAP Heart Beat), click on Cancel Session on the bottom right. Click ‘Back’ to go back to the user list.

3. Query the Discover_Connection DMV

Open a DMX window and type “select * from $system.discover_connections”

For details see SSAS DMV.

Advertisements

1 Comment »

  1. […] How do I track who’s using the cube every day? Setup query log table. See this article: link […]

    Pingback by 5 minutes SSAS Admin for SQL DBAs | Data Warehousing and Business Intelligence — 31 May 2014 @ 9:46 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: