SSAS OlapQueryLog – Analyzing Dimension Attribute Usage

ssas OlapQueryLog AnalysisAnalysis Services (SSAS) comes with a great feature called “Usage Based Optimization”. When enabled, SSAS will log queries to a SQL table. This table will subsequently be used for designing aggregations based upon what users are querying. This can greatly speed up a large cube and cut down on unnecessary space-consuming aggregations. This feature is well documented on the MSDN site.

As a nice side-effect to enabling this feature, you can also garner insight into how your users are using your cube. Analyzing the contents of this table can help you answer usage and performance questions that may otherwise be difficult to collect. Are they really using that complex dimension and measure group that you created for them last month? The one that they needed ASAP? Are they really needing those very granular dimension attributes? Are the queries performing well?

With a query-logging table you might think that it would provide an intuitive wealth of information with a mere select statement…Not so fast! The OlapQueryLog table does contain valuable information but it is unfortunately designed to be read by SSAS and not necessarily us humans. For example, the column that contains the information about which dimensions and attributes are being used in the query is encoded like this:

SELECT [Dataset]
FROM [dbo].[OlapQueryLog]

00000000,000,000,101000000000000010000,111101000000000000000,00,000000000000000000000,000000000000000000000,000000000000000000000,00

This is essentially a list of dimensions separated by commas with a bit for each attribute. If the attribute is used in the query it is shown as a 1. So in order to decode this you would need to know the order of dimensions along with the order of each attribute within the dimensions.

I have spent some time trying to decode this and have come up with a somewhat small application in C#. The remainder of this post will walk through the logic and implementation. The final solution can be found at my github account here:

The OlapSummary Table

As the queries are decoded, they are stored in a table with the following structure:

CREATE TABLE [dbo].[OlapQuerySummary](
	[olapQuerySummaryID] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
	[queryDate] [datetime] NULL,
	[measureGroupName] [nvarchar](100) NULL,
	[cubeDimensionID] [nvarchar](100) NULL,
	[cubeDimensionAttributeName] [nvarchar](100) NULL,
	[loadDate] [datetime] NULL DEFAULT (getdate()),
	[queryDuration] [int] NULL
) ON [PRIMARY]

The Dimension and Attribute Classes

There are 2 classes used to hold the query data.  The first of these is the Attribute class:

public class Attribute
  {
     public string attributeName {get; set;}
     public int index {get; set;}
  }

And the Dimension class is composed of a list of Attributes along with an ID and index:

public class Dimension
 {
     public string id {get; set;}
     public List<Attribute> attributes {get; set;}
     public int index { get; set; }
 }

The Attribute Decoding Method

The concept of the decoding method is to loop through all dimensions and assign them to a list of Dimension class objects. Then a loop is done through the measure groups as well as a loop through the aggregation designs within those measure groups. Those are assigned to a list of Attribute class objects as well.

List<Dimension> cubeDimensions = new List<Dimension>();

int dimensionIndex = 0;
string measureGroupName = "";
            
// Loop through the aggregation designes to figure out which attributes the bits map to...
foreach(MeasureGroup mg in Cube.MeasureGroups)
{
                
                    
    if (mg.ID == measureGroupID)
    {
        measureGroupName = mg.Name;
        Console.WriteLine(mg.Name);
        Console.WriteLine(mg.AggregationPrefix);
        foreach (AggregationDesign ad in mg.AggregationDesigns)
        {
            // Loop through aggregation dimensions
            foreach (AggregationDesignDimension dd in ad.Dimensions)
            {
                List<Attribute> cubeDimensionAttributes = new List<Attribute>();
                Dimension dim = new Dimension();
                dim.id = dd.Dimension.ToString();
                dim.index = dimensionIndex;
                dimensionIndex++;
                int attributeIndex = 0;

                // Loop through aggregation attributes
                foreach (AggregationDesignAttribute a in dd.Attributes)
                {
                    Attribute att = new Attribute();

                    att.attributeName = a.Attribute.ToString();
                    att.index = attributeIndex;
                    cubeDimensionAttributes.Add(att);
                    attributeIndex++;
                }

                dim.attributes = cubeDimensionAttributes;
                cubeDimensions.Add(dim);
            }
        }
    }
}

With our dimensions and attributes safely assigned to lists, we can then match up the indexes of those lists to the code from the DataSet column.

// Break apart dataset into dimensions
string[] dsDimensions = dataSetString.Split(',');


// Find only the attributes that have a "1" bit.
for (int i = 0; i < dsDimensions.Length; i++) { if (cubeDimensions.Count > i)
    {
        string d = dsDimensions[i];
        IEnumerable<int> indexes = Regex.Matches(d, "1").Cast<Match>().Select(m => m.Index);
        foreach (int ix in indexes)
        {
            Console.WriteLine(cubeDimensions[i].attributes.Count);
            if (cubeDimensions[i].attributes dr9sepx.Count >= ix)
            {
                logQueryDetails(connectionString, queryDateString, measureGroupName, cubeDimensions[i].id, cubeDimensions[i].attributes[ix].attributeName, queryDuration);
            }
        }
    }
}

If a match is found where the attribute bit is 1, a call to the method that logs the attributes to the summary table is made:

public static void logQueryDetails(string connectionString, string queryDateString, string measureGroupName, string cubeDimensionID, string cubeDimensionAttributeName, Int64 queryDuraction)
{
            
    string queryString = @"INSERT INTO dbo.OlapQuerySummary
                            (queryDate, measureGroupName, cubeDimensionID, cubeDimensionAttributeName, queryDuration)
                            VALUES
                            ('" + queryDateString + "','"
                                + measureGroupName + "','"
                                + cubeDimensionID + "','"
                                + cubeDimensionAttributeName + "',"
                                + queryDuraction + ");";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

OlapQueryLog Decoded!

So now lets walk through how a query is logged and also how the app can capture the details in a readable format:

To keep it simple, I am going to browse the AdventureWorksDW2012Multidimensional-EE.AdventureWorks cube by dragging over Internet Sales Amount. I then Customer, and finally City.

Here is what is displayed in the cube browser:
InternetSalesAmountByCustomerAndCity

And upon debugging the application, the following is displayed:
InternetSalesDimensionAttributeUsage

The output of the console is nothing too fancy and is really just for debugging. The key information is stored in the summary table. Here is what got inserted into the table:
QuerySummaryOutput

Note that by dragging over Customers first and then City, 2 distinct queries were captured. Since City is a member of the Customer Geography hierarchy, it also logged their usage. This may be something that should be filtered out in future modifications.

As for the recurring Destination Currency, this shows due to the fact that it is used in the internet sales calculation. Again, this may be something that should be filtered out so it won’t pollute the table with attributes that aren’t strictly selected by users.

2 Comments


  1. I’ve got this error in this line
    public List<Attribute> attributes {get; set;}

    Error 15 Using the generic type ‘System.Collections.Generic.List’ requires 1 type arguments C:\Users\vita.rozenberg\AppData\Local\Temp\Vsta\SSIS_ST110\VstaqoAGem_H50ST__P__2rABNtA\VstaOCsY51nO2E__AjPDq__UdPGw\ScriptMain.cs 40 17 ST_44661ca0abc2496c9fb7ca3e5ae67881

    Can you help?

    Reply

  2. Erik, this looks like something that will fit a need I have, but I do not see the final solution on your GitHub. Can you email me and provide? Thanks much.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *