/*
This t-sql is for MULTIDIMENSIONAL CUBES!
Source code from www.delorabradish.com, March 2017
Use the following t-sql statement to read extended event data previously imported into a SQL Server database table
    1.  Change the [database name] following the USE statement below
	2.  Change the FROM [xEvents_SSAS_MultiD] clause to be the name of your SQL table
	3.  Change the WHERE and ORDER BY statement as needed
*/
USE [SSAS Query Logs]
GO

--find query and storage engine durations from MultiD cube Extended Events
SELECT
	 x.ConnectionID
	,x.NTUserName
	,x.DatabaseName
	,x.QueryID
	,x.QueryDuration

	--user query --> formula engine --> storage engine --> end user

	,p.StorageEngineDuration
	--SE is multi-threaded handling data read and writes; aggregates data requested
	--SE is not in-memory

	,FormulaEngineDuration = CASE WHEN p.StorageEngineDuration > x.QueryDuration THEN NULL ELSE x.QueryDuration - p.StorageEngineDuration END
	--FE is single-threaded, receives query request, determines data needed, requests data from SE and performance calculations
	--FE tries to keep data in-memory

	,y.[Number of SE Queries]
	,y.[Thread Duration of SE Queries]
	,w.[Aggregations Read]
	,x.TextData
FROM
	(
		SELECT 
			 ConnectionID
			,QueryDuration = Duration
			,TextData
			,QueryID = CAST (HashBytes('SHA1', CAST(reverse(CAST(TextData as VarChar(MAX))) AS NVarChar(4000))) AS INT)
			,NTUserName
			,DatabaseName
		FROM [xEvents_SSAS_MultiD]
		WHERE EventClass ='QUERY_END'
	) as x
--Get Query Subcube Ver bose of Non-cache data
LEFT OUTER JOIN (
				SELECT
					 ConnectionID
					,COUNT(*) AS [Number of SE Queries]
					,SUM(Duration) as [Thread Duration of SE Queries]
				FROM [xEvents_SSAS_MultiD] AS a
				WHERE a.EventClass = 'QUERY_SUBCUBE_VERBOSE' 
					AND EventSubclass = 22
				GROUP BY ConnectionID
				) as y ON y.ConnectionID = x.ConnectionID
--Get count of aggregations read
LEFT OUTER JOIN (
				SELECT
					 ConnectionID
					,[Aggregations Read] = COUNT(*)
				FROM [xEvents_SSAS_MultiD]
				WHERE EventClass = 'GET_DATA_FROM_AGGREGATION'
				GROUP BY ConnectionID
				) as w ON w.ConnectionID = x.ConnectionID
--Get SE time
LEFT OUTER JOIN (
				SELECT
					 ConnectionID
					,StorageEngineDuration = SUM(Duration)
				FROM [xEvents_SSAS_MultiD]
				WHERE EventClass = 'QUERY_SUBCUBE'
				GROUP BY ConnectionID
				) as p ON p.ConnectionID = x.ConnectionID

WHERE p.StorageEngineDuration is NOT NULL
ORDER BY [Number of SE Queries] DESC, QueryDuration DESC
