Monday, 4 March 2013

Data warehouse starter for 10

I've been working on a little project in my spare time, as scarce as that is lately, and as part of that I was putting together some dimensions for a star schema.  This isn't the first time I've been around this block so I dug out some old code.  I also thought "I can't be the first to do, need or want this".

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:


Currently you'll find it has dimensions for:
  • Date
  • Time
  • Country
  • Continent
  • Currency
  • HTTP and FTP status codes - thanks to David Curlewis (b | t) for the inspiration on these
  • Languages
  • Top Level Domains
If you're in New Zealand, you'll find a couple of specific dimensions:
  • ANZSIC codes
  • NZ Regions
Its far from complete.  So if you find something that needs fixing or like it but want it expanded feel free to log an issue/request in BitBucket and I'll see what I can do for you.  For example; I want to add currency symbols to the currency dimension (but haven't yet found a good source).


Along 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.

Holiday Dates

Now a Date dimension, like any dimension or even database, is only as good as the data in it.  You can customise the dates by adding your current regional dates into the dimHolidayDate table.

1 comment:

  1. I can’t say enough good things about this storage facility. We enjoy their service very much. I feel very comfortable storing my items at this storage place. I can recommend this Storage facility without a doubt
    Self storage software companies