I had a great time sharing my ‘Dimensional Design 101’ presentation with the devs at Codestock 2011. This is one of my favorite presentations and Codestock has become one of my favorite events. […]

My presentation focused on the Kimball style data mart. We talked about the Bus Matrix and how it should drive the design of your data mart. Next I went over fact tables and how to design them as a product of the Bus Matrix. We talked about the three types of fact table (Transactional, Accumulating Snapshot and Periodic Snapshot). With the bus and fact tables behind us we moved on to talk about dimensions. I described dimensions as fields that decorate the measures in our fact tables. Funny, Jessica Moss (Blog | Twitter) and Andy Leonard (Blog | Twitter) have both commented on this way of describing dimensions. I wish I could say this was my idea, but it isn’t. I got the idea reading the Kimball Data Warehouse Toolkit books.

Things got a little complicated at one point when I stumbled a bit trying to find the best way to describe conformed dimensions. Eventually I got around to explaining that tables with consistent schema and data can be considered conformed. Imagine a dimension table called dimPeople. There could be a dimPeople table on Server A and another dimPeople table on Server B. The two tables would be conformed if a given PersonID described the exact same person in both tables. The tricky part is that the tables don’t need to be identical to be conformed. The data in dimPeople on Server A might be in English, the data in dimPeople on Server B might be in French. The dimPeople on Sever A might even have a few additional fields that are not included in the dimPeople table on Server B. As long as a PersonID exists in both tables and it describes the same person in both tables the two tables are conformed. Ya … like I said, it got a bit complicated but we got through it.

After covering conformed dimensions we discussed role playing dimensions with an example of two views based on a date dimension that acted as role playing dimensions to support a periodic snapshot table.

It was a really fun presentation with lots of great questions and participation from the group. About 10 minutes into the session Andy Leonard joined us and I almost had a heart attack. I’m a HUGE Andy Leonard fan. His SQL 2005 SSIS book was my first introduction to SSIS. Without a strong background in ETL using SSIS I wouldn’t be designing data marts. I have no doubt that without that book I wouldn’t be living the life I enjoy today. Thanks Andy!

Thanks also to Chris Luttrell (Twitter), Stephen Horne (Blog | Twitter) and Mike Brown (Blog | Twitter) for attending and participating in my session and Paul Hunter (Twitter) for tweeting encouragement!