colin Robinson (4/20/2008)
i would also probably drop the table rather than truncate in this instance. You would then get the same Identity value for each date each time you ran the code.I have a question though, isnt there a performance impact of creating dim_time in this way with say a five year end date, as drop downs etc in front ends would be larger than required, and each date would create a null value in the fact table for every measure as the cube processes
Im thinking we should use code like the example in a slowly changing dimension with code in an if block
ie if Getdate() >= (select max(date) from DimTime) then
exec createDates 30(x num) days more data
an advantage to this is we would never have to revisit the time dimension unless there was a fundamental change like the Fiscal year dates changed and even this would just be a change to the sproc for future dates.
Just as a suggestion...
It seems to me that a Calendar table of this nature (time dimension, whatever), should be made to go out to 01/01/2100 and that the GUI should have criteria in it to return the correct number of dates.
Heh... as a side bar, I have to ask why anyone would use a loop to creat such a simple table... then I saw the date this was published and that pretty well explains it. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.