So I was building this OLAP cube for a new application and looking at how to populate the time dimension, which is on a date level. I was sure I wanted to have an integer key on the table to make joining to the fact table as painless as possible. But the problem is I also wanted all of my dates in key order. If I used an Identity for my key field, I'd never be guaranteed that my dates were in key order. I could insert 12/31/2007 today and it would take key 1, and then I could insert 12/30/2007 tomorrow and it would take key 2. How would I ever sleep at night?
What I realized I could do, though, is cast my datetime field to an int when I inserted.
SQL Server 2005 will give you the number of days between 1/1/1900 and the date you specify in your cast. Now I can ensure that each row represents a different date and still not be tied to joining to another table while running ETL jobs or digging in the data.
It's the little things.
No comments:
Post a Comment