3 Replies Latest reply on Jan 5, 2016 8:28 PM by Christian Berg

    Date Dimension having Date Type as primary key

    Ashish Gupta

      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.




        • 1. Re: Date Dimension having Date Type as primary key

          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.


          • 2. Re: Date Dimension having Date Type as primary key
            Gianni Ceresa


            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.

            1 person found this helpful
            • 3. Re: Date Dimension having Date Type as primary key
              Christian Berg

              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