Codestock: Dimensional Design 101

Data

Codestock: Dimensional Design 101

by Tim Costello

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!

Dimensional Design 101 slide deck. Download.

AndyLeonard1browniepoints2AndyLeonard3sqlnightowlCGLuttrellSQLStephenHornebrowniepointsAndyLeonard

More About the Author

Tim Costello

Analytics Consultant
3 Reasons Why You Should Want a Data Warehouse I hear a lot of reasons people avoid a data warehouse: It takes too long to build, data warehouse projects are expensive and often end ...
The Tableau Performance Checklist: Filtering – Avoid High-Cardinality Quick Filters The next item in our Filtering checklist is: “Avoid high-cardinality quick filters (multi-select or drop-down lists). ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072