Olap Time Dimension Pains (will continue)

During our last project we ran into two big problems with time dimension in Analysis Services 2005. Today I will describe problem with a key attribute of datetime database type.

What is interesting with following members on time dimension?



Correct! The December 2000 must not be a member of 2001 calendar year.
How is it possible? Well it must have be a bug in Analysis Services but is quite easy to eliminate. Still not satisfied? Here are some simple steps to establish problem in Adventure Works:

1. Run 2 sql inserts into relational Adventure Works (script 1 in the bottom)
   
2. Run script 2 in the bottom to create new time sister dimension. Note: sister dimension [Date test] must be added into cube manually via BI studio. (Alternate step is to change key column of the Date attribute of the Date dimension - but you will run into lot of problems with measure groups)
3. Full process this new sister time dimension.
4. Process cube Adventure Works (I had to kick out all data mining things to make full processing working.)
5. Run script 3 to experience the strange behavior - also visible on next picture.
6. Change the MemberNamesUnique property and retry previous step. And there you go (like in Greece Wedding) : setting MemberNamesUnique to True/False is the feature when using natural datetime key for the key attribute.

Script 1:

--1.1
INSERT INTO dbo.[DimTime] (
[FullDateAlternateKey],
[DayNumberOfWeek],
[EnglishDayNameOfWeek],
[SpanishDayNameOfWeek],
[FrenchDayNameOfWeek],
[DayNumberOfMonth],
[DayNumberOfYear],
[WeekNumberOfYear],
[EnglishMonthName],
[SpanishMonthName],
[FrenchMonthName],
[MonthNumberOfYear],
[CalendarQuarter],
[CalendarYear],
[CalendarSemester],
[FiscalQuarter],
[FiscalYear],
[FiscalSemester]
) VALUES (
/* FullDateAlternateKey - datetime */ '20010101',
/* DayNumberOfWeek - tinyint */ 6,
/* EnglishDayNameOfWeek - nvarchar(10) */ N'Friday',
/* SpanishDayNameOfWeek - nvarchar(10) */ N'Viernes',
/* FrenchDayNameOfWeek - nvarchar(10) */ N'Vendredi',
/* DayNumberOfMonth - tinyint */ 1,
/* DayNumberOfYear - smallint */ 1,
/* WeekNumberOfYear - tinyint */ 1,
/* EnglishMonthName - nvarchar(10) */ N'January',
/* SpanishMonthName - nvarchar(10) */ N'Enero',
/* FrenchMonthName - nvarchar(10) */ N'Janvier',
/* MonthNumberOfYear - tinyint */ 1,
/* CalendarQuarter - tinyint */ 1,
/* CalendarYear - char(4) */ '2001',
/* CalendarSemester - tinyint */ 1,
/* FiscalQuarter - tinyint */ 3,
/* FiscalYear - char(4) */ '2000',
/* FiscalSemester - tinyint */ 2 )
--31.12
INSERT INTO dbo.[DimTime] (
[FullDateAlternateKey],
[DayNumberOfWeek],
[EnglishDayNameOfWeek],
[SpanishDayNameOfWeek],
[FrenchDayNameOfWeek],
[DayNumberOfMonth],
[DayNumberOfYear],
[WeekNumberOfYear],
[EnglishMonthName],
[SpanishMonthName],
[FrenchMonthName],
[MonthNumberOfYear],
[CalendarQuarter],
[CalendarYear],
[CalendarSemester],
[FiscalQuarter],
[FiscalYear],
[FiscalSemester]
) VALUES (
/* FullDateAlternateKey - datetime */ '20001231',
/* DayNumberOfWeek - tinyint */ 6,
/* EnglishDayNameOfWeek - nvarchar(10) */ N'Thursday',
/* SpanishDayNameOfWeek - nvarchar(10) */ N'Jueves',
/* FrenchDayNameOfWeek - nvarchar(10) */ N'Jeudi',
/* DayNumberOfMonth - tinyint */ 31,
/* DayNumberOfYear - smallint */ 366,
/* WeekNumberOfYear - tinyint */ 52,
/* EnglishMonthName - nvarchar(10) */ N'December',
/* SpanishMonthName - nvarchar(10) */ N'Diciembre',
/* FrenchMonthName - nvarchar(10) */ N'Décembre',
/* MonthNumberOfYear - tinyint */ 12,
/* CalendarQuarter - tinyint */ 4,
/* CalendarYear - char(4) */ '2000',
/* CalendarSemester - tinyint */ 2,
/* FiscalQuarter - tinyint */ 2,
/* FiscalYear - char(4) */ '2000',
/* FiscalSemester - tinyint */ 1 )

Sript 2 (is too long so here is attachment):

Script 3:

with member Measures.KeyName
as
[Date Test].Calendar.CurrentMember.UNIQUE_NAME
member KeyCaption
as
[Date Test].Calendar.MEMBER_CAPTION
select {KeyName, KeyCaption} on columns,
{[Date Test].Calendar.[Date].&[2000-12-31T00:00:00]:[Date Test].Calendar.[Date].&[2001-01-01T00:00:00]} on rows
from [Adventure Works];

Checksum pains

Are you using TSQL Checksum function in MSSQL2005? If yes be carefull with decimal (numeric) types. Try this example:

SELECT CHECKSUM(1000.0)
,
CHECKSUM(100.0)


Should not be there different values? Of course. Retype above SQL into following cast:

SELECT CHECKSUM(CAST(1000.0 AS VARCHAR(50)))
,
CHECKSUM(CAST(100.0 AS VARCHAR(50)))


or you can use other examples. The positive is that casting takes very small performance points:

DECLARE @i DECIMAL(20,5), @c INT
SET @i=0;

WHILE (@i<500000)
BEGIN
SET @i=@i+1; --7s

SET @c=CHECKSUM(CAST(@i AS VARCHAR(50))); --10 s
--SET @c=CHECKSUM(CAST(@i AS FLOAT)); --10 s
--SET @c=CHECKSUM(CAST(@i AS FLOAT)); --10 s
--SET @c=CHECKSUM(@i + CAST(0 AS FLOAT)); --10 s
--SET @c=CHECKSUM(@i); --10 s

END;