Analysis Services - How to monitor the progress of cube processing by SQL Server

Hey guys!
In this article I want to share with you a tip that most people who work with Analysis Services don't know about this possibility, although they always wanted to have something like this, which is to monitor the progress of cube processing by SQL Server.

Most BI professionals these days just monitor how long it took to process the cube/database as a whole, but they don't know how long it took each dimension, each fact, and each cube within a database and this is what I'm going to show you how. do in this post.

The first thing we're going to do is open the SQL Server Profiler to monitor cube processing:

On the login screen, remember to change the connection type to “Analysis Services”, enter the name of the instance you are going to connect to and configure the authentication data

Remember that Analysis Services only accepts Windows authentication (on-premises Active Directory) or Azure Active Directory.

After connecting, you will be able to define a name for the monitoring and where it will be stored (bank table or physical file)

I usually save to disk and then create a reading routine via Job, just to not be writing data to the database all the time, but for traces coming from Analysis Services, the function ::fn_trace_gettable, which is used to read the generated files, does not work:

So I recommend already configuring the monitoring to save the data in a database table directly.

The data export table is configured:

Now click on the “Event Selection” tab to select the events you will monitor.

Generally I mark only the “Error” and “End” events from the categories below:

• Command Events
• Errors and Warnings
• Progress Reports
• Queries Events
• Query Processing

You can change these captured events depending on the level of detail you want to monitor.

By clicking on the “Column filters” button, you can apply filters like user name, software name, hostname, session ID, etc.

In the case below, I will apply a filter to return only queries made by my user. This is useful to avoid catching events from other users accessing the cube while you are processing and skewing the results.

I click on “Run” to start monitoring and now I start processing the cube.

You will see a screen like the one below, showing the monitoring result.

Although it's easy to visualize the data, I prefer to query through SQL Server, where I can group, add, filter and transform the data as needed.

Doing a basic query on the created table, we can already visualize a good part of the data:

But now we need to include descriptions of event classes and subclasses, to make the data easier to understand.

First, let's create 2 tables: ProfilerEventClass and ProfilerEventSubClass, which will store the descriptions of the events that occur during the processing of a cube and insert the most common types of events.

Script for creating tables and inserting data: