I was asked recently about Logging SSAS Tabular models to see who was using the model I created. I have been working in Corporate America so I needed to come up with several options based on what they will and will not allow in their environment. In this blog I want to make a few suggestions on different methods for SSAS Tabular Logging. There maybe more and I would love to hear about them if you want to share.
I first began looking at the SSAS QueryLog that is built into SSAS because it works for the Multidimensional models and I found that it would not work for Tabular. Upon setting it up it just doesn’t log to the table you configure. Here is a blog I read where they had a similar problem SSAS Tabular QueryLog not working. So I began to search and I found some interesting methods that people have used and each one has its plus and minus factor depending on your needs.
- Data Management View Query – This method will get current session connection information.
Note: You will need to have ETL running to get users sessions every 5 min because it only current info.
- Activity Viewer (CodePlex) – Open source application that shows current SSAS activity.
Note: This is a nice GUI application tool to see current activity which pulls from the DMVs. Remember its only current activity not something logged over time.
- ASTrace2012 (CodePlex) – Creates a utility that runs as a Windows service, connecting to Analysis Services 2012 and logging trace events into a SQL Server table using the SQL Server Profiler format.
Note: This is a tool that generates a service which can start and stop traces. The nice thing about traces is they can track information over time as long as the profiler trace is running.
- SQL Server Extended Events (XEvents) to Monitor Analysis Services – Using XMLA to run the trace and delete the trace which logs the data to a Microsoft SQL Server Extended Event log file (.xel). This can be parsed in a simple SQL statement to get the columns from the file.
Note: This is very powerful you can create the start and stop XMLA scripts to begin the profiler trace and configure any elements in the XMLA to be logged. The advantage is less overhead and resources then the GUI.
- SQL Profiler Trace – Run the SQL Profiler to for Analysis Services and log to a database/file. This can be run at command line, GUI, SQL Stored Procedures, and SQL Agent Jobs.
Note: There a few different ways to get a trace going and the GUI has the most overhead and uses the most resources. The command line and stored procedures have less overhead using less resources but require more configuration.