THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

QueryLog in Analysis Services

More than 2 years ago I posted on the microsoft olap newsgroup a bug on the QueryLog table that Analysis Services uses to log user queries (very important to make usage based optimization). A KB was supposed to be pending, but after two years and a service pack, nothing happened.

The table has insufficient space in two fields. For a future reference this is the correct SQL query to create the table (better to move it into a SQL Server database):

CREATE TABLE [dbo].[QueryLog] (
 [MSOLAP_Database] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [MSOLAP_Cube] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [MSOLAP_User] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Dataset] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Slice] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [StartTime] [smalldatetime] NULL ,
 [Duration] [int] NULL ,
 [MOLAPPartitions] [smallint] NULL ,
 [ROLAPPartitions] [smallint] NULL ,
 [SamplingRate] [int] NULL

I emphasized the two sizes I expanded to allow the log of cubes with more than 64 dimensions.This night I was curious to see what's changed with SSAS 2005. First of all - no documentation (if there is something, it's not indexed). In Server Properties I discovered that by default there is no query log activated. You can activate it by defining a connection (Log / QueryLog/ QueryLogConnectionString) and you can auto-create the necessary OlapQueryLog table (Log / QueryLog / CreateQueryLogTable); you can change this table name with another option (Log / QueryLog / QueryLogTableName). It seems that you could use a text file also (Log / QueryLog / QueryLogFileName and QueryLogFileSize). I'm a little bit disappointed that the default for QueryLogSampling is 10 - I prefer one and in my opinion this should be the default, especially now that log query is disabled by default.

The auto-created table has a DataSet field long 4000 unicode characters. I don't understand why NVARCHAR is used here, but may be that I need to better understand what is the actual representation here, different from the previous version of Analysis Services.

When I searched for the Usage Based Optimization Wizard... surprise! It's gone. The actual documentation says "The Usage-Based Optimization Wizard is not implemented in Microsoft SQL Server 2005. Information to come later.". I'd like to know more about this.

I'd like an "Inside Analysis Services 2005" book under my hands at soon as possible. Is there someone already working on it? Let me know...

Published Thursday, June 23, 2005 3:32 PM by Marco Russo (SQLBI)


No Comments
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement