Big game hunting

The last DW post talked about dates and times within a data warehouse. The time that a sale or some other transaction occurs can usually be recorded by an Oracle DATE value in the data warehouse; some DW may need to use timestamps, but to-the-second times would normally be adequate.

Transactional fact data tends to be sparse. We are most unlikely to sell every single item in our stock inventory to every one of our customer in all of our stores on each and every day. But other date related snippets of information are likely to be dense. A simple ‘work day calendar’ could just contain a single time dimension to show if we are open for trade on any date, add a geography dimension and we can then do it by country or store. Similarly we could build a weather table against date and geography to help determine if we sell more umbrellas when it rains, or how about a adding a ‘big game’ table to see if a major football match close to a store affects takings.