Your Quick Introduction to Extended Events in Analysis Services from Blog Posts - SQLServerCentral

The Extended Events (XEvents) feature in SQL Server is a really powerful tool and it is one of my favorites. The tool is so powerful and flexible, it can even be used in SQL Server Analysis Services (SSAS). Furthermore, it is such a cool tool, there is an entire site dedicated to XEvents.

Sadly, despite the flexibility and power that comes with XEvents, there isn’t terribly much information about what it can do with SSAS. This article intends to help shed some light on XEvents within SSAS from an internals and introductory point of view – with the hopes of getting more in-depth articles on how to use XEvents with SSAS.

Introducing your Heavy Weight Champion of the SQLverse – XEvents

With all of the power, might, strength and flexibility of XEvents, it is practically next to nothing in the realm of SSAS. Much of that is due to three factors: 1) lack of a GUI, 2) addiction to Profiler, and 3) inadequate information about XEvents in SSAS. This last reason can be coupled with a sub-reason of “nobody is pushing XEvents in SSAS”. For me, these are all just excuses to remain attached to a bad habit.

While it is true that, just like in SQL Server, earlier versions of SSAS did not have a GUI for XEvents, it is no longer valid. As for the inadequate information about the feature, I am hopeful that we can treat that excuse starting with this article. In regards to the Profiler addiction, never fear there is a GUI and the profiler events are accessible via the GUI just the same the new XEvents events are accessible. How do we know this? Well, the GUI tells us just as much, as shown here.

In the preceding image, I have two sections highlighted with red. The first of note is evidence that this is the gui for SSAS. Note that the connection box states “Group of Olap servers.” The second area of note is the highlight demonstrating the two types of categories in XEvents for SSAS. These two categories, as you can see, are “profiler” and “purexevent” not to be confused with “Purex® event”. In short, yes Virginia there is an XEvent GUI, and that GUI incorporates your favorite profiler events as well.

Let’s See the Nuts and Bolts

This article is not about introducing the GUI for XEvents in SSAS. I will get to that in a future article. This article is to introduce you to the stuff behind the scenes. In other words, we want to look at the metadata that helps govern the XEvents feature within the sphere of SSAS. In order to, in my opinion, efficiently explore the underpinnings of XEvents in SSAS, we first need to setup a linked server to make querying the metadata easier.

EXEC master.dbo.sp_addlinkedserver
   @server = N'SSASDIXNEUFLATIN1' --whatever LinkedServer name you desire
 , @srvproduct=N'MSOLAP'
 , @provider=N'MSOLAP'
 , @datasrc=N'SSASServerSSASInstance' --change your data source to an appropriate SSAS instance
 , @catalog=N'DemoDays' --change your default database
 go
 
EXEC master.dbo.sp_addlinkedsrvlogin
   @rmtsrvname=N'SSASDIXNEUFLATIN1'
 , @useself=N'False'
 , @locallogin=NULL
 , @rmtuser=NULL
 , @rmtpassword=NULL
GO

Once the linked server is created, you are primed and ready to start exploring SSAS and the XEvent feature metadata. The first thing to do is familiarize yourself with the system views that drive XEvents. You can do this with the following query.

SELECT lq.*
 FROM OPENQUERY(SSASDIXNEUFLATIN1, 'SELECT * FROM $system.dbschema_tables') as lq
WHERE CONVERT(VARCHAR(100),lq.TABLE_NAME) LIKE '%XEVENT%'
	OR CONVERT(VARCHAR(100),lq.TABLE_NAME) LIKE '%TRACE%'
ORDER BY CONVERT(VARCHAR(100),lq.TABLE_NAME);

When the preceding query is executed, you will see results similar to the following.

In this image you will note that I have two sections highlighted. The first section, in red, is the group of views that is related to the trace/profiler functionality. The second section, in blue, is the group of views that is related the XEvents feature in SSAS. Unfortunately, this does demonstrate that XEvents in SSAS is a bit less mature than what one may expect and definitely shows that it is less mature in SSAS than it is in the SQL Engine. That shortcoming aside, we will use these views to explore further into the world of XEvents in SSAS.

Exploring Further

Knowing what the group of tables looks like, we have a fair idea of where we need to look next in order to become more familiar with XEvents in SSAS. The tables I would primarily focus on (at least for this article) are: DISCOVER_TRACE_EVENT_CATEGORIES, DISCOVER_XEVENT_OBJECTS, and DISCOVER_XEVENT_PACKAGES. Granted, I will only be using the DISCOVER_XEVENT_PACKAGES view very minimally. From here is where things get to be a little more tricky. I will take advantage of temp tables  and some more openquery trickery to dump the data in order to be able to relate it and use it in an easily consumable format.

Before getting into the queries I will use, first a description of the objects I am using. DISCOVER_TRACE_EVENT_CATEGORIES is stored in XML format and is basically a definition document of the Profiler style events. In order to consume it, the XML needs to be parsed and formatted in a better format. DISCOVER_XEVENT_PACKAGES is the object that lets us know what area of SSAS the event is related to and is a very basic attempt at grouping some of the events into common domains. DISCOVER_XEVENT_OBJECTS is where the majority of the action resides for Extended Events. This object defines the different object types (actions, targets, maps, messages, and events – more on that in a separate article).

Script Fun

Now for the fun in the article!

IF OBJECT_ID('tempdb..#SSASXE') IS NOT NULL
	BEGIN
		DROP TABLE #SSASXE;
	END;
IF OBJECT_ID('tempdb..#SSASTrace') IS NOT NULL
	BEGIN
		DROP TABLE #SSASTrace;
	END;
SELECT CONVERT(VARCHAR(MAX), xo.Name) AS EventName
     , xo.description AS EventDescription
     , CASE
           WHEN xp.description LIKE 'SQL%' THEN
               'SSAS XEvent'
           WHEN xp.description LIKE 'Ext%' THEN
               'DLL XEvents'
           ELSE
               xp.name
       END AS PackageName
     , xp.description AS CategoryDescription --very generic due to it being the package description
     , NULL AS CategoryType
     , 'XE Category Unknown' AS EventCategory
     , 'PureXEvent' AS EventSource
     , ROW_NUMBER() OVER (ORDER BY CONVERT(VARCHAR(MAX), xo.name)) + 126 AS EventID
	INTO #SSASXE
	FROM
	(
		SELECT *
		FROM OPENQUERY
			 (SSASDIXNEUFLATIN1, 'select *
			From $system.Discover_Xevent_Objects')
	) xo
		INNER JOIN
		(
			SELECT *
			FROM OPENQUERY
				 (SSASDIXNEUFLATIN1, 'select * FROM $system.DISCOVER_XEVENT_PACKAGES')
		) xp
			ON xo.package_id = xp.id
	WHERE CONVERT(VARCHAR(MAX), xo.object_type) = 'event'
		  AND xp.ID <> 'AE103B7F-8DA0-4C3B-AC64-589E79D4DD0A'
	ORDER BY CONVERT(VARCHAR(MAX), xo.[name]);
SELECT ec.x.value('(./NAME)[1]', 'VARCHAR(MAX)') AS EventCategory
     , ec.x.value('(./DESCRIPTION)[1]', 'VARCHAR(MAX)') AS CategoryDescription
     , REPLACE(d.x.value('(./NAME)[1]', 'VARCHAR(MAX)'), ' ', '') AS EventName
     , d.x.value('(./ID)[1]', 'INT') AS EventID
     , d.x.value('(./DESCRIPTION)[1]', 'VARCHAR(MAX)') AS EventDescription
     , CASE ec.x.value('(./TYPE)[1]', 'INT')
           WHEN 0 THEN
               'Normal'
           WHEN 1 THEN
               'Connection'
           WHEN 2 THEN
               'Error'
       END AS CategoryType
     , 'Profiler' AS EventSource
	INTO #SSASTrace
	FROM
	(
		SELECT CONVERT(XML, lq.[Data])
		FROM OPENQUERY
			 (SSASDIXNEUFLATIN1, 'Select * from $system.Discover_trace_event_categories') lq
	) AS evts(event_data)
		CROSS APPLY event_data.nodes('/EVENTCATEGORY/EVENTLIST/EVENT') AS d(x)
		CROSS APPLY event_data.nodes('/EVENTCATEGORY') AS ec(x)
	ORDER BY EventID;
SELECT ISNULL(trace.EventCategory, xe.EventCategory) AS EventCategory
     , ISNULL(trace.CategoryDescription, xe.CategoryDescription) AS CategoryDescription
     , ISNULL(trace.EventName, xe.EventName) AS EventName
     , ISNULL(trace.EventID, xe.EventID) AS EventID
     , ISNULL(trace.EventDescription, xe.EventDescription) AS EventDescription
     , ISNULL(trace.CategoryType, xe.CategoryType) AS CategoryType
     , ISNULL(CONVERT(VARCHAR(20), trace.EventSource), xe.EventSource) AS EventSource
     , xe.PackageName
	FROM #SSASTrace trace
		FULL OUTER JOIN #SSASXE xe
			ON trace.EventName = xe.EventName
	ORDER BY EventName;

Thanks to the level of maturity with XEvents in SSAS, there is some massaging of the data that has to be done so that we can correlate the trace events to the XEvents events. Little things like missing EventIDs in the XEvents events or missing categories and so forth. That’s fine, we are able to work around it and produce results similar to the following.

If you compare it to the GUI, you will see that it is somewhat similar and should help bridge the gap between the metadata and the GUI for you.

Put a bow on it

Extended Events is a power tool for many facets of SQL Server. While it may still be rather immature in the world of SSAS, it still has a great deal of benefit and power to offer. Getting to know XEvents in SSAS can be a crucial skill in improving your Data Superpowers and it is well worth the time spent trying to learn such a cool feature.

Interested in learning more about the depth and breadth of Extended Events? Check these out or check out the XE website here.

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the seventh article in the 2020 “12 Days of Christmas” series. For the full list of articles, please visit this page.

The post Your Quick Introduction to Extended Events in Analysis Services first appeared on SQL RNNR.

The post Your Quick Introduction to Extended Events in Analysis Services appeared first on SQLServerCentral.