Analysis Services 2005 Usage Based Optimization and OlapQueryLog

The Usage-based Optimization feature is alive and well within SQL Server 2005 Analysis Services (AS2k5).  But by default, the QueryLog feature (which samples and records the queries sent to AS2k5 is turned off.  To turn it on, you will need to:
1) Create a SQL database (e.g. QueryLog) on the Analysis Services server (Actually, you can put it on another SQL server if you like, but the preferred option is to place it on the SQL server on the box).
2) Through SQL Management Studio, connect to your Analysis Services, right click, and click on "Properties".
3) Within the Analysis Services Properties dialog, scroll down the properties, and first look for:
Log \ QueryLog \ QueryLogConnectionString
Click on the Value column and it will provide you a dialog to point the connection string to the SQL database.  Point to the SQL dB (e.g. QueryLog) that you had just created.
4) Then scroll to the property
Log \ QueryLog \ CreateQueryLogTable
Flip the Value column from false to true.
5) Click on OK and then you will have created the OlapQueryLog table within the QueryLog (if that’s what you called it) database.  The name of the table is actually the parameter:
Log \ QueryLog \ QueryLogTableName
Now that you have created your query log table, you can send queries to your OLAP cubes and then when its time to perform usage-based optimization:
1) Connect to your Olap cube via the SQL Management Studio
2) Go down the hierarchy of Measure Group > Partition, right-click the partition in question and then you will be provided the "Usage-based Optimization Wizard".
From there, you can use that wizard to optimize your Olap cubes.  The "SQL Server Books Online" provides good reference material for the wizard and can view some of the screenshots of it right now at Mosha’s blog at:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s