Date Dimension having Date Type as primary key

Question

Hi Gurus,

From architecture team came a suggestion that they wanted the surrogate key to be of Date Datatype in Time Dimension rather than of number type.

Reason is that they want to partition the fact on monthly basis and its lot easier with the Date Datatype.

Do you guys see any issue arising in obiee because of this Date Datatype primarykey in Time Dimension?

Note:- I know kimball suggests surrogate key to be of number type.

Thanks

Ashish

[Deleted User] · Answer

As it was already stated here: no problem whatsoever functionally. Just to be picky about one thing for your architecture team: a singular data type column is NOT a surrogate key. Logically for a data dimension a DATE is actually a natural key

Gianni Ceresa · Answer

Hi,

OBIEE will not complain at all if you use a date instead of a number. Everything will work without problems, no issues on that side.

SPowell42 · Answer

Kimball suggests using a numeric instead of a date because if you need to have date dimension values of "Unknown", "Not Applicable", etc. and you're using a date field you have to start doing wacky things like assigning bogus dates of 1/1/1900 to mean "Unknown", 1/2/1900 = "Not Applicable" etc. And if you do that, you also have to be careful that any type of date arithmetic that may use the key column doesn't get goofed up when unknowns and not applicables come through.

Having said that, I've done exactly what you've suggested (for exactly the same reason, to make the partitioning easier). If I do need "Unknown" and "Not Applicable" type date values, I've also added two columns to the fact table - one with the surrogate key (numeric), and one with the associated date.

Not sure if that's the right thing to do, but easier than trying to partition based on random numbers, IMO.

Scott