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.
1 person found this helpful
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.
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