/*
This t-sql is for TABULAR MODELS!
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 tabular model Extended Events
SELECT
	 x.ConnectionID
	,x.NTUserName
	,x.DatabaseName
	,x.QueryID
	,x.QueryDuration

	,s.StorageEngineDuration
	,FormulaEngineDuration = x.QueryDuration - s.StorageEngineDuration
	,CoresUsed = CASE WHEN s.StorageEngineDuration > 0 THEN s.CPUTime / s.StorageEngineDuration ELSE NULL END

	,x.TextData
FROM
	(
		SELECT 
			 ConnectionID
			,QueryDuration = Duration
			,TextData
			--there is no query Id, so to test a single query you must rely on TextData
			,QueryID = CAST (HashBytes('SHA1', CAST(reverse(CAST(TextData as VarChar(MAX))) AS NVarChar(4000))) AS INT)
			,NTUserName
			,DatabaseName
		FROM [xEvents_SSAS_Tabular]
		WHERE EventClass ='QUERY_END'
	) as x
--Get storage engine duration; will be NULL if query was able to get all data from cache
LEFT OUTER JOIN (
				SELECT
					 ConnectionID
					,StorageEngineDuration = SUM(Duration)
					,CPUTime = SUM(CPUTime)
				FROM [xEvents_SSAS_Tabular]
				WHERE EventClass = 'XM_SEQUERY_END'
					AND EventSubclass = 0		--VertiPaq Scan
				--grouping by ConnectionID combines multiple queries
				GROUP BY ConnectionID
				) as s ON s.ConnectionID = x.ConnectionID

--WHERE s.StorageEngineDuration is NOT NULL
ORDER BY QueryDuration DESC
