So I took some of the common dimensions and code to populate them and started a SSDT project. I've added a few more dimensions since that and have since loaded it up to BitBucket.
I've opened up the repository to allow anyone who is interested or would benefit from the beginnings of a data warehouse model. You can find it here: https://bitbucket.org/kchenery/warehouse-model
SchemaCurrently you'll find it has dimensions for:
- HTTP and FTP status codes - thanks to David Curlewis (b | t) for the inspiration on these
- Top Level Domains
- ANZSIC codes
- NZ Regions
DataAlong with the schema to create the basic dimensions, I've included post deploy scripts in the SSDT project to pre-populate the data. Most of these are pretty simple scripts that merge a long SELECT...UNION ALL script into the target dimension table.
The Date dimension has a procedure that is called to create a range of dates. This can be called to customise the range loaded or even extend that range.
You'll also notice each dimension table has a "special" record to record the "unknown" dimension value. The unknown record, and any other special records, will always have a negative value for the dimension key. E.g. -1 is always the unknown record.