OLAP Query Log and Subcube Vectors

The OLAP Query Log is a table that collects details of queries executed against the SSAS database. To be more precise, the records in this table represent the details of storage engine requests fired off by the Analysis Services engine during query execution – the often missed implication being that a single MDX query might generate multiple subcube requests. It’s a SSAS server-level configuration option that I’ve already covered in a bit of detail in this post.

The records in this table contain a field named Dataset which contains weird binary-looking string of 1’s and 0’s like what you see below:

These values are referred to as Subcube Vectors because they represent the details of a subcube needed to resolve a query (or part of a query). Each comma-delimited group of digits represents a different cube dimension. And each individual digit in the group represents an attribute in the corresponding dimension. A value of “1” indicates the attribute was used in a subcube request.

Note: Technically, all attributes are used in a subcube request. However, unless the attribute is referenced directly (or indirectly) in a context other than the “AllMember” (which is typically the default member of an attribute), then it is not special and will show up as a zero in the Subcube Vector.

So from the screenshot above, we have the following order of dimensions:

[Order Date], [Customer], [Due Date], [Ship Date]

And here’s how the attributes are ordered for each dimension:

[Order Date] : [Date] [Month] [Quarter] [Year]

[Customer] : [Customer Id] [Gender] [Education] [Number Cars Owned] [Commute Distance]

[Due Date] : [Date] [Month] [Quarter] [Year]

[Ship Date] : [Date] [Month] [Quarter] [Year]

Note: In order to know the order of dimensions and attributes in the coded string of 1’s and 0’s, you can interrogate the DDL of the cube. E.g. To find the order of the dimensions, you can right-click the cube object in SSMS object explorer and choose “Script Cube as” –> “CREATE To” and walk the XML…and you can do the same to find the order of the attributes in each dimension by displaying the XML for each dimension object. Another option is to run a profiler trace and capture the “Query Subcube Verbose” event (which I’ll show a bit further down).

Now that we know the order of dimensions and attributes, we can see how the following 2 subcube vectors from the query log table correspond (respectively) to the 2 MDX queries that follow:

SELECT    {
             [Measures].[Sales Amount]
            ,[Measures].[Total Product Cost]
        } ON 0,
        {
            [Customer].[Gender].[Gender] *
            [Order Date].[Year].[Year]
        } ON 1
FROM    [Adventure Works DW2012]

SELECT    {
             [Measures].[Sales Amount]
            ,[Measures].[Total Product Cost]
        } ON 0,
        {
            [Customer].[Education].[Education]
        } ON 1
FROM    [Adventure Works DW2012]

Pretty straightforward right?

As a some-what long side note… for those that have done some SSAS tracing w/ profiler, you might recognize this string of 1’s and 0’s because they show up in the Query Subcube and Query Subcube Verbose event. This is the same information found in the query log table.

In the second screenshot above – for the Query Subcube Verbose event – you’ll notice that the subcube vector has been broken out into the actual dimension/attribute names. Very cool!

Note: In the Query Subcube Verbose event, you might see a number of different characters instead of a “1” which provides a bit more detail about the actual members (of the dimension attribute) being requested. The whitepaper Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services by Carl Rabeler (b | t) and Eric Jacobsen has a good breakdown of the different values and what they mean.

Ok, so what does one actually “do” with this information. Well the most obvious application is for usage-based optimization…which is just a fancy term for a wizard that auto-magically generates aggregations based on the actual subcube requests that hit the SSAS database. I’ve already blogged about this in quite a bit of detail throughout the Aggregation Design Life-Cycle series of posts (start here).

Another application, and one I helped implement at a client last year, was to use the information in conjunction with cube meta-data to visualize cube “hot-spots”. In that solution (which I promise to eventually share in more detail) involved decoding the information captured in the OLAP query log table, combining it with cube meta-data, and displaying it in bus-matrix format with a heat-map overlay via SSRS (heavily based on this solution by Alex Whittles @PurpleFrogSystems).