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
) ON [PRIMARY]
GO

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…