Hi, I have migrated a DTS package from SQL 2000 to SQL 2005. When I view the
time dimension within SQL 2005 I get the following:

http://tinyurl.com/hba6p

Why do I get (for example):

year = 2006, month = July 1987, Year = 1 July 2006 ?

I can't quite see why I am getting the year 1987 in the month column. The
month column has incorrect years, it should be:

year = 2006, month = July 2006, Year = 1 July 2006 ?

Any help most appreciated.

Peter

Hi Peter,

Did you try an "Explore Data" browse of the Time dimension table, and
see if the column which provides the name of the month has correct
values for the day rows? It's hard to say what the issue is without
knowing more about the dimension data.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

how have you defined your key columns for the month and the day attributes?
(which column(s) are used)
do you use unique keys?
I mean:
200605 = May 2006
20060505 = 5 may 2006

if you only use the month number the result could be like your issue.(the
month number only is not unique inthe hierarchy, may is under every year)

"peter walker" <p.walker (AT) nospam (DOT) com> wrote

Thanks Deepak and Jeje.

the key to my solution was:

I recreated the time dimension table to have the following structure

<column name>: <sample data>
Year: 1980
Month: January 1980
Day: 1 January 1980
date: 1980-01-01 00:00:00Z
indexDay: 19800101
indexMonth: 198001

'indexDay' is the index for 'Day'. Likewise 'indexMonth' is the index for
'Month'. 'Year' is indexed against itself.

It works nicely now!

I have noticed that Year and Month are using an index of datatype integer.
However Day is using an index of datatype WChar. Shouldnt all indexes be of
type integer?

Cheers,

Peter

try to change from WChar to integer or bigint

in my case I'm using the date himself for the day key, not an integer.

"peter walker" <p.walker (AT) nospam (DOT) com> wrote