Get a Descriptive Name for SQL Server Analysis Services Profiler Event Class and Event Subclass

By:   |   Comments (5)   |   Related: > Analysis Services Performance


A customer provides an SQL Server Analysis Services trace file to analyze. You save this file into a trace table and execute T-SQL code to analyze the results. Rather than getting descriptive values for the EventClass and EventSubclass data, you get numerical values. How do you resolve these numerical values to meaningful descriptive names?  Check out this tip to learn more.


Microsoft provides the description for the EventClass and EventSubclass in a tracedefinitionNNN.xml file, where NNN is the current database version. This file is located in the installations MSQL.x\OLAP\bin\Resources\1033 directory. There are several solutions to this issue, both of which require using the trace definition file. One solution is quick and dirty, but not very flexible and can be pretty cumbersome. The other solution is more difficult, but provides a better long-term and reusable approach.

Solution 1 - CASE Expression

This first solution uses a CASE expression to map the numerical value for each EventClass to its corresponding descriptive name. This same approach is also applied to the EventSubclass as demonstrated in the code sample below.

SELECT RowNumber, 
      'EventClass' = 
         WHEN EventClass = 11 THEN 'Query Subcube'
         WHEN EventClass = 81 THEN 'QueryDimension'
         CAST(EventClass AS VARCHAR(10)) 
       'EventSubclass' = 
         WHEN EventClass = 11 AND EventSubclass = 1  THEN 'Cache data'
         WHEN EventClass = 11 AND EventSubclass = 2  THEN 'Non-cache data'
         WHEN EventClass = 81 AND EventSubclass = 1  THEN 'Cache data'
         WHEN EventClass = 81 AND EventSubclass = 2  THEN 'Non-cache data'
         CAST(EventSubclass AS VARCHAR(10)) 
       CAST(CPUTime/1000.0 AS float) AS 'CPUTime(sec)'
FROM dbo.my_as_trace
ORDER BY RowNumber;

This approach is cumbersome, because it requires iteratively searching the trace definition file to find the correct description for the given EventClass and EventSubclass. Care must be taken when searching for both the EventClass and EventSubclass since these values are not guaranteed to be unique within the tracedefinition XML file. As the above example demonstrates, the <EventSubclass> sub elements are unique within a given <EventClass> element, but not unique across <EventClass> elements. This solution is good if the Analysis Services trace contains only a small number of EventClass and EventSubclass.

Solution 2 - Shred the Trace Definition XML

This next solution requires shredding the trace definition XML. Before executing the code block below, you need to make two changes. First, you need to copy and paste the content of the trace definition XML file to the @tracedef variable. Second, you need to escape the single quote by searching and replacing each single quote with two single quote in the XML content. These are:

  • object's object''s
Executing the T-SQL code below will create a temporary table, ##tracetable, containing four columns for the event id, event name, event subclass id, and event subclass name.

 DECLARE @tracedef xml
SET @tracedef='<replace with content from tracedefinition xml file>'
SELECT T1.evt.value('./ID[1]', 'int')               AS event_id,
       T1.evt.value('./NAME[1]', 'varchar(max)')    AS event_name, 
       T2.subevt.value('./ID[1]', 'int')            AS subevent_id, 
       T2.subevt.value('./NAME[1]', 'varchar(max)') AS subevent_name
INTO ##tracetable
FROM @tracedef.nodes(
  CROSS APPLY T1.evt.nodes(
    AS T2(subevt)

Finally, the query to analyze the Analysis Services trace data in solution 1 is modified to join to this temporary table to retrieve the event name and event subclass name.

    t2.event_name    AS EventClass, 
    t3.subevent_name AS EventSubclass,
    CAST(CPUTime/1000.0 AS float) AS 'CPUTime(sec)'
FROM dbo.my_as_trace t1
    (select distinct event_id as event_id, event_name as event_name from ##tracetable) t2
    ON t1.EventClass = t2.event_id
 INNER JOIN ##tracetable t3
    ON (t1.EventClass = t3.event_id and t1.EventSubclass = t3.subevent_id)
ORDER BY RowNumber;
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Peter Tran Peter Tran is a Senior Performance Tech Lead @ PROS Revenue Management;(NYSE: PRO), where he focuses on server-side (Java, JDBC, database) performance on SQL Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, February 13, 2013 - 4:25:39 PM - Peter Tran Back To Top (22108)

Thanks for the update on the query and spelling issue.

Wednesday, February 13, 2013 - 1:51:15 PM - Scott Cameron Back To Top (22105)

MS has fixed the spelling error, so no more need to do the find/replace

Wednesday, February 13, 2013 - 1:50:04 PM - Scott Cameron Back To Top (22104)

The SQL code to load ##tracetable excludes events that don't have a corresponding subevent, e.g. Event ID = 17 Error or Event ID = 17 Query Cube Begin.

The code to join the trace events to the event names drops trace events where the subevent is null.

This SQL will load all events:

with Events as
  EventId = T1.evt.value('./ID[1]', 'int')
  ,EventName = T1.evt.value('./NAME[1]', 'varchar(200)')
  ,EventDescription = T1.evt.value('./DESCRIPTION[1]', 'varchar(300)')
,SubEvents as
  EventId = T1.evt.value('./ID[1]', 'int')
  ,EventName = T1.evt.value('./NAME[1]', 'varchar(200)')
  ,EventDescription = T1.evt.value('./DESCRIPTION[1]', 'varchar(300)')
  ,SubEventId = T2.subevt.value('./ID[1]', 'int')
  ,SubEventName = T2.subevt.value('./NAME[1]', 'varchar(max)')
  ,SubEventDescription = T2.subevt.value('./DESCRIPTION[1]', 'varchar(max)')
 Events e
 left outer join SubEvents se
  on e.EventId = se.EventId
order by

This SQL will select all trace records:

 dbo.SSASTrace t
 left outer join dbo.SSASEventSubEvent e
  on t.EventClass = e.EventId
  and coalesce(t.EventSubclass, -1) = coalesce(e.SubEventId,-1)


Friday, October 12, 2012 - 3:11:00 PM - Peter Tran Back To Top (19899)

Thanks! Wow...that's crazy they have that spelling mistake. How hard is it to copy the file over?  :) Thank you for pointing this out.

Wednesday, October 10, 2012 - 10:42:27 AM - Ian Clarke Back To Top (19854)

This is brilliant and a great time-saver. Thanks a million! I did notice an issue with SSAS 2012. There is a spelling mistake in the trace definition file so in the query to extract the classes you need to change './EVENTCOLUMNLIST/EVENTCOLUMN/EVENTCOLUMNSUBCLASSLIST/EVENTCOLUMNSUBCLASS' to'./EVENTCOLUMNLIST/EVENTCOLUMN/EVENTCOLUMNSUBCLASSLIST/EVENTCOLUMSUBCLASS'.


get free sql tips
agree to terms