Managing Server Side Analysis Services Profiler Trace

I have been asked how to run Analysis Services Profiler Trace as Server side Trace. There are few ways of running trace on server like astrace tool. In this post, I am going to show you how you can create server side trace using xmla and stop trace.

 

First Step is to create XMLA for your profiler trace events. Here are steps:

-Open SQL Server Profiler Trace

-Connect to Analysis Services Instance you would like to capture events for.

-Choose your events

-Click Run and then stop trace in case you don’t want to add any overhead of running GUI.

-Now we have events in hand we want to collect.

-Next step is to export these events as XMLA file.

- Click File->Export->Script Trace Definition->For Analysis Services 2005-SQL11->Save as Profiler.XMLA

 

-Now we have generated script to create trace on server side. With few modification, you can specify rollover information. Quick note, although you choose rollover options in Profiler trace GUI, when you export Trace events as XMLA script, it doesn’t save rollover info to xmla file. You need to manually attributes.

-Let’s open XMLA in Management Studio first:

-Connect your Analysis Services instance through Management Studio.

-Click XMLA button shown below and drag and drop your xmla file into this window.

 

-You will see similar Trace definition like below:

 

Here are text version of attributes in case you may want to copy directly from this posting:

<ID>DBA Performance Trace</ID>

<Name>DBA Performance Trace</Name>

<!--UPDATE YOUR OUTPUT PATH!!!!!!!-->

<LogFileName>C:\OLAP_TRACE.trc</LogFileName>

<LogFileAppend>1</LogFileAppend>

<AutoRestart>0</AutoRestart>

<!--Logfilesize is in MB-->

<LogFileSize>5000</LogFileSize>

<LogFileRollover>1</LogFileRollover>

-You can add File location, rollover options just after Name attribute like below

-Delete following tag (first line of your script) from script as you don’t need to specify <?xml> tag in your xmla command. Delete following line from your script then execute script

-Once you execute script, Server Side trace is created. There are two ways of checking running traces:

1- DMVs : select * from $system.discover_traces

 

2-Discover Commands:

<!--By Default you will get trace for Flight Recorder-->
<!--This script will give you a list off all running traces-->
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
    <RequestType>DISCOVER_TRACES</RequestType>
    <Restrictions>
        <RestrictionList>
        </RestrictionList>
    </Restrictions>
    <Properties>
        <PropertyList>
        </PropertyList>
    </Properties>
</Discover>

 

-Now ,we created trace and monitor. Now it is time to stop our server side trace.

Following Script stops Profiler Trace

<!--How to Drop the Trace which you had created-->

<Delete xmlns="https://schemas.microsoft.com/analysisservices/2003/engine" xmlns:soap="https://schemas.xmlsoap.org/soap/envelope/">

<Object>

<TraceID>DBA Performance Trace</TraceID>

</Object>

</Delete>

if we look at running traces on server again, we will see that our server side trace is stopped now. You can go to path and review your trace file. Only trace you will see running is Flight Recorder for Analysis Services.

You can also create SQL Server Agent job and add Job Step which executes XMLA Command to manage server side trace. I have seen some customers creating these jobs to manager server side tracing for Analysis Services.

 

If you have any questions feel free to contact me

 

Thanks

 

Kagan Arca

Comments

• Anonymous
  August 05, 2013
  Hi Kagan, Is there any way to output events into csv rather than trc file? Thanks

• Anonymous
  August 06, 2013
  Hi Lukasz,  Not I am aware of. however, you can save trace file as table then export from table to CSV format? If you can give more details what you are trying to achieve, may be we may look for other solutions? Thanks Kagan

• Anonymous
  September 10, 2013
  Hi Kagan, I am trying to automate the server side trace on analysis server. I have analysis services on one server and database services on one server, wanted to know how to keep a sql agent job to run XMLA. Require an Attribute for saving trace data to SQL tables. Thanks

• Anonymous
  September 10, 2013
  The comment has been removed

• Anonymous
  September 11, 2013
  Thanks for your reply... I will store the trace in common share..but my concern is scheduling the server side trace, as SSAS doesnt have job activity monitor to schedule, i am looking for a process where trace should run 247. My objective to run AS trace is to find out who all accessed the cubes 247. It will be helpfull if you provide the info

• Anonymous
  September 11, 2013
  First of all, I wouldn't save trace to common share directly as this may fail due to network/ connectivity issues. I would save locally and once profiler completes writing, then you can copy to share or another machine. By this way, you can capture what you want without any issues. About creating job for SSAS, you should be able create a job step which Analysis Services Command Type in your step and use code above. You can also create stop job with Analysis Services Command Type step which basically deletes traces. By this way, you can automate tracing through sql server agent job. Thanks Kagan

• Anonymous
  March 31, 2014
  Hi All, This approach is very useful and can be used for many thing but is an important step missing. How can I import Analysis Service Trace File into a database? There are some functions in SQL but only support  SQL server trace files. For sure I'm not the only one with this problem. Anyway find out any approach/method to import SSAS trace files into database. I really appreciate some help. Thanks, José  

• Anonymous
  May 06, 2014
  José, I'm just in the process of doing the same myself so can't yet say how effective it is, but try this: www.sqlis.com/.../Trace-File-Source-Adapter.aspx Regards, Nigel

• Anonymous
  October 06, 2014
  I downloaded the 2012 Trace File Source Adapter but its not showing up in my toolbox.  In previous versions you had to add items but you don't have that option in 2012.  I also restarted SSIS service.

• Anonymous
  October 08, 2014
  When i run select * from $system.discover_traces in SSAS then i am able to view the running traces but creation time column shows incorrect time. how to troubleshoot this?

• Anonymous
  March 30, 2015
  Does anybody know how to create Xmla script to save trace data to SQL table directly?

• Anonymous
  August 04, 2015
  I have a problem, how  to save analysis services trace to db table using a script as fn_trace_gettable does not work with AS trace. does anybody can help, please ?