SQL Trace (Save to file)

Click [XMLA] menu on SQL Tabular instance

 

 

Copy & Paste the below text.. and Click [F5] button

<!-- This script supplied by Bill Anton http://byobi.com/blog/2013/06/extended-events-for-analysis-services/ -->
<Create
	xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
	xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
	xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"
	xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"
	xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
    <ObjectDefinition>
		<Trace>
			<ID>TraceUsageTabular</ID>
			<Name>TraceUsageTabular</Name>
			<ddl300_300:XEvent>
				<event_session      name="xeas"
									dispatchLatency="1"
									maxEventSize="4"
									maxMemory="4"
									memoryPartitionMode="none"
									eventRetentionMode="allowSingleEventLoss"
									trackCausality="true">
					
					<!-- ### COMMAND EVENTS ### -->
					<!--<event package="AS" name="CommandBegin" />-->
					<!--<event package="AS" name="CommandEnd" />-->
					
					<!-- ### DISCOVER EVENTS ### -->
					<!--<event package="AS" name="DiscoverBegin" />-->
					<!--<event package="AS" name="DiscoverEnd" />-->
					
					<!-- ### DISCOVER SERVER STATE EVENTS ### -->
					<!--<event package="AS" name="ServerStateDiscoverBegin" />-->
					<!--<event package="AS" name="ServerStateDiscoverEnd" />-->
					
					<!-- ### ERRORS AND WARNING ### -->
					<!--<event package="AS" name="Error" />-->
					
					<!-- ### FILE LOAD AND SAVE ### -->
					<!--<event package="AS" name="FileLoadBegin" />-->
					<!--<event package="AS" name="FileLoadEnd" />-->
					<!--<event package="AS" name="FileSaveBegin" />-->
					<!--<event package="AS" name="FileSaveEnd" />-->
					<!--<event package="AS" name="PageInBegin" />-->
					<!--<event package="AS" name="PageInEnd" />-->
					<!--<event package="AS" name="PageOutBegin" />-->
					<!--<event package="AS" name="PageOutEnd" />-->
					
					<!-- ### LOCKS ### -->
					<!--<event package="AS" name="Deadlock" />-->
					<!--<event package="AS" name="LockAcquired" />-->
					<!--<event package="AS" name="LockReleased" />-->
					<!--<event package="AS" name="LockTimeout" />-->
					<!--<event package="AS" name="LockWaiting" />-->
					
					<!-- ### NOTIFICATION EVENTS ### -->
					<!--<event package="AS" name="Notification" />-->
					<!--<event package="AS" name="UserDefined" />-->
					
					<!-- ### PROGRESS REPORTS ### -->
					<!--<event package="AS" name="ProgressReportBegin" />-->
					<!--<event package="AS" name="ProgressReportCurrent" />-->
					<!--<event package="AS" name="ProgressReportEnd" />-->
					<!--<event package="AS" name="ProgressReportError" />-->
					
					<!-- ### QUERY EVENTS ### -->
					<!--<event package="AS" name="QueryBegin" />-->
					<event package="AS" name="QueryEnd" />
					
					<!-- ### QUERY PROCESSING ### -->
					<!--<event package="AS" name="CalculateNonEmptyBegin" />-->
					<!--<event package="AS" name="CalculateNonEmptyCurrent" />-->
					<!--<event package="AS" name="CalculateNonEmptyEnd" />-->
					<!--<event package="AS" name="CalculationEvaluation" />-->
					<!--<event package="AS" name="CalculationEvaluationDetailedInformation" />-->
					<!--<event package="AS" name="DaxQueryPlan" />-->
					<!--<event package="AS" name="DirectQueryBegin" />-->
					<!--<event package="AS" name="DirectQueryEnd" />-->
					<!--<event package="AS" name="ExecuteMDXScriptBegin" />-->
					<!--<event package="AS" name="ExecuteMDXScriptCurrent" />-->
					<!--<event package="AS" name="ExecuteMDXScriptEnd" />-->
					<!--<event package="AS" name="GetDataFromAggregation" />-->
					<!--<event package="AS" name="GetDataFromCache" />-->
					<!--<event package="AS" name="QueryCubeBegin" />-->
					<!--<event package="AS" name="QueryCubeEnd" />-->
					<!--<event package="AS" name="QueryDimension" />-->
					<!--<event package="AS" name="QuerySubcube" />-->
					<!--<event package="AS" name="ResourceUsage" />-->
					<!--<event package="AS" name="QuerySubcubeVerbose" />-->
					<!--<event package="AS" name="SerializeResultsBegin" />-->
					<!--<event package="AS" name="SerializeResultsCurrent" />-->
					<!--<event package="AS" name="SerializeResultsEnd" />-->
					<!--<event package="AS" name="VertiPaqSEQueryBegin" />-->
					<!--<event package="AS" name="VertiPaqSEQueryCacheMatch" />-->
					<!--<event package="AS" name="VertiPaqSEQueryEnd" />-->
					
					<!-- ### SECURITY AUDIT ### -->
					<!--<event package="AS" name="AuditAdminOperationsEvent" />-->
					<event package="AS" name="AuditLogin" />
					<!--<event package="AS" name="AuditLogout" />-->
					<!--<event package="AS" name="AuditObjectPermissionEvent" />-->
					<!--<event package="AS" name="AuditServerStartsAndStops" />-->
					
					<!-- ### SESSION EVENTS ### -->
					<!--<event package="AS" name="ExistingConnection" />-->
					<!--<event package="AS" name="ExistingSession" />-->
					<!--<event package="AS" name="SessionInitialize" />-->
					
					
					<target package="Package0" name="event_file">
						<!-- Make sure SSAS instance Service Account can write to this location -->
						<parameter name="filename" value="D:\SSASExtendedEvents\TraceUsageTabular.xel" />
					</target>
				</event_session>
			</ddl300_300:XEvent>
		</Trace>
	</ObjectDefinition>
</Create>

 

If you can see below error message.. then you should create folder (SSASExtendedEvents)

 

[F5] Try again..

You can see the below success message.

 

Click [MDX] menu on SQL Tabular instance

 

SELECT * FROM $system.discover_traces

 

You can see as the below image.

 

 

 

 

When you open the file, you can look like the below image.

 

 

Copy & Paste the below text.. and Click [F5] button

SELECT
        xe.TraceFileName
      , xe.TraceEvent
      , xe.EventDataXML.value('(/event/data[@name="EventSubclass"]/value)[1]','int') AS EventSubclass
      , xe.EventDataXML.value('(/event/data[@name="ServerName"]/value)[1]','varchar(50)') AS ServerName
      , xe.EventDataXML.value('(/event/data[@name="DatabaseName"]/value)[1]','varchar(50)') AS DatabaseName
      , xe.EventDataXML.value('(/event/data[@name="NTUserName"]/value)[1]','varchar(50)') AS NTUserName
      , xe.EventDataXML.value('(/event/data[@name="ConnectionID"]/value)[1]','int') AS ConnectionID
      , xe.EventDataXML.value('(/event/data[@name="StartTime"]/value)[1]','datetime') AS StartTime
      , xe.EventDataXML.value('(/event/data[@name="EndTime"]/value)[1]','datetime') AS EndTime
      , xe.EventDataXML.value('(/event/data[@name="Duration"]/value)[1]','bigint') AS Duration
      , xe.EventDataXML.value('(/event/data[@name="TextData"]/value)[1]','varchar(max)') AS TextData
  FROM
  (
  SELECT
        [FILE_NAME] AS TraceFileName
      , OBJECT_NAME AS TraceEvent
      , CONVERT(XML,Event_data) AS EventDataXML
  FROM sys.fn_xe_file_target_read_file ( 'D:\SSASExtendedEvents\TraceUsageTabular*.xel', null, null, null )
  ) xe

 

 

You can see as the below image.

 

Once you are ready to stop the trace, execute the following XMLA:

<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
      <Object>
          <TraceID>TraceUsageTabular</TraceID>
      </Object>
</Delete>

 

Create Jobs Schedule: Usage hits for Excel BI(Tabular)

-- This script Usage hits for Excel BI(Tabular)

-- Date: 10/25/2016

USE XXXX_META

GO

IF OBJECT_ID('TraceUsageTabular','U') IS NOT NULL

DROP TABLE TraceUsageTabular

SET QUOTED_IDENTIFIER ON

CREATE TABLE TraceUsageTabular

( TraceFileName VARCHAR(MAX)

, TraceEvent VARCHAR(50)

, EventSubclass INT

, ServerName VARCHAR(50)

, DatabaseName VARCHAR(50)

, NTUserName VARCHAR(50)

, ConnectionID INT

, StartTime DATETIME

, EndTime DATETIME

, Duration BIGINT

, TextData VARCHAR(MAX)

)

INSERT INTO TraceUsageTabular

SELECT

xe.TraceFileName

, xe.TraceEvent

, xe.EventDataXML.value('(/event/data[@name="EventSubclass"]/value)[1]','int') AS EventSubclass

, xe.EventDataXML.value('(/event/data[@name="ServerName"]/value)[1]','varchar(50)') AS ServerName

, xe.EventDataXML.value('(/event/data[@name="DatabaseName"]/value)[1]','varchar(50)') AS DatabaseName

, xe.EventDataXML.value('(/event/data[@name="NTUserName"]/value)[1]','varchar(50)') AS NTUserName

, xe.EventDataXML.value('(/event/data[@name="ConnectionID"]/value)[1]','int') AS ConnectionID

, xe.EventDataXML.value('(/event/data[@name="StartTime"]/value)[1]','datetime') AS StartTime

, xe.EventDataXML.value('(/event/data[@name="EndTime"]/value)[1]','datetime') AS EndTime

, xe.EventDataXML.value('(/event/data[@name="Duration"]/value)[1]','bigint') AS Duration

, xe.EventDataXML.value('(/event/data[@name="TextData"]/value)[1]','varchar(max)') AS TextData

FROM

(

SELECT

[FILE_NAME] AS TraceFileName

, OBJECT_NAME AS TraceEvent

, CONVERT(XML,Event_data) AS EventDataXML

FROM sys.fn_xe_file_target_read_file ( 'D:\SSASExtendedEvents\TraceUsageTabular*.xel', null, null, null )

) xe