SQL BI

Multiple DISTINCT COUNT measures in SSAS 2005

Analysis Services has a limitation of one distinct count measure for each measure group; in the past, it was a limitation for the single cube. Each distinct count measure cause an ORDER BY clause in the SELECT sent to the relational data source during processing. I'd like a different approach, but probably this is already a good compromise between all the needs.

Now in SSAS2005 it seems to be a little inconsistency in the user interface of BI Studio. If you try to create a measure group manaully, editor don't allow you to create more than one measure group from the same fact table. If you use the "New Measure..." context menu (and related wizard) it creates a new measure group for each distinct count measure.

What I'd like to know is if the presence of a distinct count measure in the same measure group containing several other measures cause a performance loss for all the measures similarly to AS2000: in that version I create each distinct count measure in a separate cube, joining all the measures in a single virtual cube; in that way I had no performance loss (caused by potentially less aggregations) when queries contain only "standard" measures. The "New Measure" dialog box creates a new measure group for each distinct count measure. It seems that not very much has changed from the previous version.

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
) 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...

Transform Data Task (DTS2000) and SSIS

My first period using SSIS in a real-world application convinced me that there is a lack of support in migration of SQL2000 DTS packages, expecially in a star schema transformation scenario.

Most of my actual DTS packages are combinations of SQL Execute Task and Transform Data Task.
90% of Transform Data Task are so composed:
- SELECT from Data Source
- mapping from source to destination with "copy column" (source and destination columns have the same name)
- SQL Fast Load with a defined batch size (1000 or 2000 tipically) and Table Lock
- Log to text file of source and destination rows that fail transformation (tipically when I try to put NULL in a NOT NULLable column)

In a SSIS package I could have a data flow task corresponding to the Transform Data Task. It would be very easy to handle my tipical use of Transform Data Task. While I understand that there are many other uses that could be not so easy to translate, a wizard that try to convert a transform data task into a data flow before to convert it into a Execute legacy Package task would be very useful.
I completely understand that a rewrite of my DTS could give me great advantages, but from a practical point of view I could facilitate the adoption of SQL 2005 if DTS packages would run AND would be editable in the new native environment, allowing a progressive optimization and a gradual adoption of the new features.

If this scenario is not to be supported, it would be good at least to have a chance to integrate an external "migration component" into the Migration Wizard. Is there a way to do that? Or if I want to support a similar scenario I have to rewrite the whole migration wizard?

(this post is cross-posted into the yukon.dts newsgroup)

SSIS2005 feature request (small things)

Drillthrough in SQL2000 SP4

I just discovered that drillthrough works in SQL Server 2000 SP4 even when used on calculated measures. I'm pretty sure it doesn't work with SP3. I haven't read about this in the kb888800.

I'd like to know if this is really a bug fixed or if I always misused drillthrough... Anyway, may be it's a very interesting news for many olap developers!

UPDATE: starting from a comment from Mosha I further investigated it and it was a miss on the Drillthrough macro code in Excel, that doesn't include the measure in the generated drillthrough query. So definitely don't consider this post as a real issue.