Monitoring Cube Performance and Usage - Expert Cube Development with SSAS Multidimensional Models - page 350

Monitoring usage with trace data
Collecting trace data in a SQL Server table can be a very useful way to determine the
most active users, or the most frequent or expensive queries, over a long period of
time. The most important event to capture is Query End / MDX Query . The Duration
column for this event can be used to identify the most expensive queries, and it
allows us to see the actual MDX that was run for a query. However, it's very often
the case that users will run several very similar MDX queries—for example, they
might run the same query, but slice by a different date—and it is very difficult to
identify this kind of query pattern with just the MDX, so we may also choose to
capture lower-level trace events too, such as Get Data From Aggregation or even
Query Subcube .

Another important piece of information in the Query End / MDX Query event is the
NTUserName column, which identifies the user who ran the query. The only issue
here is that if we are using some form of application-level security, it might be that
the same user is used for all Analysis Services queries. In this case, we would need to
create a dedicated log at the application level because Analysis Services doesn't have
enough information in the trace to identify the end user.

Monitoring usage with Performance Monitor
counters
From the Performance Monitor counter point of view, there are specific counters that
give us information about current connections and running queries. Of course, the
entire Memory category of counters we saw earlier also play an important part in
any usage monitoring strategy. We may also add the following counters:

• MSOLAP: Connection

° Current connections : This is the number of active client
connections at the current point in time.
° Current user sessions : This is the number of active user sessions;
a connection is always tied to a session, but there could be sessions
without active user connections.

More detailed information about which connections are available using the DMVs is
discussed next.