Using the book The Data Warehouse Toolkit as a reference (thanks Peter), I’ve revised the date dimension. I figure having a better base in our data will enable me and give me more options creating reports. I am only a few steps in this project from the diving into reporting.
Previously, our date dimension looked like this:
While not bad (I think it looks perfect), I admit book does a lot better job of preparing a date dimension with a lot more information. Essentially, data warehousing initiatives tend to do a lot of the work ahead of time in order to save cycles on report creation. Per the book, we could revise our dimension to be better represented as follows:
Although subtle and seemingly unnecessary, it is common practice to store all these (and lots more) derived values in our database in order to easy report construction. Clearly, we could derive all of these recently added bits of information at report run time. The point is saving this work since it does nothing but diminish report performance and complicate report creation. Taking things a bit further, we could replace our date dimension with a dateStamp and be on our way but this would be a chore. The same hold true, I admit, for other date information. Harddrive space is cheap so I am not going to complain.