Date Attribute in Dimension

My dimension table has a date field that foreign keys into my time dimension table. The relational tables look something like this:

ProductRelease table

ReleaseId int (pk)

ReleaseDate datetime (fk)

TimeDay table

DayDate datetime (pk)

YearId int

In the dimension designer, my Product Release dimension has an attribute relationship between ReleaseDate and YearId. The problem is when I process the dimension I get an error like this:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_ProductRelease, Column: ReleaseDate, Value: 1/15/2007. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Product Release of Dimension: Product Release from Database: MyWarehouse, Record: 2.

But the TimeDay table does have a record whose primary key is 1/15/2007 (actually, 2007-01-15 00:00:00.000), so I'm not sure why I'm getting the error.

What's interesting is that if I use another Time dimension table whose primary key is an integer instead of a date field, and change my ProductRelease table to fk into that using an int field, everything works fine.

I may end up using the integer pk Time table anyway (although it is nice to see the date field in the foreign key table -- more convenient when browsing the data), but why would I get this error using the date data type? Does the OLAP engine have trouble with using date foreign keys?

Thanks for any help with this.

-Larry

It can be as simple as that the other table have a different time(but the same date) as the TimeDay table.

This Date(2007-01-15 00:00:00.000 ) is not the same as 2007-01-15 23:02:02.002.

HTH

Thomas Ivarsson

|||

I'm fairly certain all the values are pure dates w/o a time, but I'll double-check to make sure.

Thanks.