Codestock: Dimensional Design 101
I had a great time sharing my ‘Dimensional Design 101’ presentation with the devs at Codestock 2011 today. 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. 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.
Lego Denormalization
‘Normalize till it hurts, Denormalize till it works’. I’ve lived my life as a SQL developer in the Business Intelligence world by this rule. Before I knew the first thing about dimensional design I knew this rule. It’s served me well.
This will be the first in a series of posts on dimensional design. ‘Dimensional design’ is basically consultant speak for ‘reporting database’. To create a really fast, easy to maintain reporting database that anyone can look at and understand you will want to design your reporting system as a data mart. There are a few philosophies out there about how you should design your data mart. I choose to follow the Kimball Method of dimensional design.
In this series of blog posts I’ll walk you through the dimensional design process. Before we can really begin I think it’s a good idea to understand what it means to normalize a database, how a denormalized database is different and why you should care.
To start, let’s keep it simple. The simplest description of normalization I’ve heard comes from Mike Hotek. He taught me to ‘put stuff where it belongs’. To expand on that idea just a bit, normalizing a database is the act of removing redundant data and ensuring data dependencies make sense. A highly normalized database is a series of tables where data has been broken down to it’s most atomic level. At this level, data is not repeated in multiple fields or multiple records. Each type of data lives in it’s own table (eg: a table of Address info would be separate from a table of Person info).
Denormilization is the process of recombining things that were split up in the normilization process to improve query performance and make it easier for someone unfamiliar with a database to find data points that would commonly go together (eg: put address info in the same record as People info).
Imagine I’m creating a completely awesome lego music video. I have assembled my band and I’m ready to film. As I review my work I realize that to take this music video to an even awesomer level I should switch out the bass player with my favorite Star Wars bounty hunter, Boba Fett.
If my lego collection is normalized I would have to find the bins (think tables) that hold the minifig pieces. Id then have to combine the separate pieces (think query) together from any of a dozen different bins (think joining tables). The more bins I have to look in to get all the pieces I need to assemble my Boba Fett minifig, the longer it’s going to take before I can start filming.
This scenario is fairly descriptive of reporting against a normalized database. It takes some hunting to find the tables I need. When I write the query there will be a lot of joins.
If my minifig collection were denormalized I could simple reach into my Star Wars Characters bin (table) and pull out (query) an assembled Boba Fet. Much better. Now I can get busy filming the awesomest Bohemian Rhapsody cover ever!
Late to bed, early to rise …
makes Tim lose his mind. No kidding.
I had this idea a month or so ago that I could be more productive if I got up earlier. I still think this idea has merit. The problem is that I don’t live my life in a vacuum. My wife likes to stay up late. My kids like to stay up late. I like to stay up late. I can try to force habit change on myself, forcing my wife and kids to go to bed early … not so much.
So I tried staying up late (often working late), and getting up early. How early? 4:30am. Damn, that’s early. I cringe just thinking about it. Soon I was ‘surviving’ on 6 hours of sleep a night. Then 5 hours a night. The last few nights of this experiment I barely hit 4 hours a night before my alarm woke me from my nap.
All of this takes a toll. I’m not as young as I used to be. I might not be as old as I feel some times, but to quote Danny Glover … ‘I’m too old for this shit.’. Amen brother.
After about a month of this insanity my body just gave up. I woke up one morning with a splitting headache. I have high blood pressure. Every time I get a particular kind of headache I can’t stop thinking about all the stroke victims I worked with in my life as a paramedic. None of them planned on having a stroke. None of them wanted a stroke. I would rather not go down that road, thank you very much.
For now, my alarm clock has been reset to a more civilized 6:45am. I force myself to bed by 10:00pm no matter what. I’ve set a firm ‘no work on work stuff after 9:00pm’ rule. I’m trying the pomodoro technique of time management to see if that helps me recover some of the productivity I lost in the shift from 16 hour work days to a more sane 10’ish hour work days.
Eventually I’d like to shift into being an early riser. I think it’s a healthy choice. The day does feel different when you start before the sun. I never thought I’d be an early riser, but this experience has shown me that it can be good.
If you live the kind of life I described I ask you to step back and think about it. Is it worth it? For me the answer was a definite NO.
SQL Saturday 63
Wow. I’ve been to a few SQL Saturday’s now, but there’s something about having the event at home that just makes it that much better!
I got off to a late start Saturday morning, but managed to get to the event site before the end of the first session. I was up as a fill in speaker for session 3, so I sat out the second session to go through my demo one last time.
My first session was a fun one. I presented the history of SQL Saturday with data visualizations. It’s not till you dig into the details that you realize what an amazing thing SQL Saturday really is. More than 1,800 sessions submitted by 560 speakers over 4 years. I was surprised to find that my friend Kevin Boles (Twitter) is the most prolific speaker in the history of SQL Saturday with 34 sessions delivered in two years!
I had lunch with my mentor (and friend) Trevor Barkhouse (Blog | Twtitter). It’s been a while, so we caught up and talked about our projects and goals. I can always count on Trevor to inspire me to push a little harder. It’s good to have friends that challenge you.
After lunch I sat in on Kevin Boles session Sans and SQL Server. You know Kevin's an amazing presenter if he can even make a DataViz guy like me feel like he understands how SQL and Sans work (or don’t) work together.
I sat out the next session to work on my slides for my next session. I presented the basics of dimensional modeling and data marts to a crowd of about 30 people. It was an amazing experience. I felt like there was a great give and take with the crowd in this one. At the end I got some great feedback. I was riding that buzz all night. It reminded me why I love speaking at SQL Saturday.
The after party was a smash hit. My friend Latish (Blog | Twitter) stopped by for my dimensional modeling session and joined us for the after party. He killed me in 3 games of pool.
To top it all off I got the chance to talk with some of the people I admire the most. Steve Jones (Blog | Twitter) and Andy Warren (Blog | Twitter) both gave me great feedback. Coolest of all ? I had a chance to talk with Jessica Moss (Blog | Twitter) ! I’m a huge fan of her work. SQL Server 2008 Problem Design Solutions is my all time favorite technical book. If you haven’t read it yet, do yourself a favor and buy it now. It’s awesome.
Ryan Adams, Sri Sridharan, Tim Mitchell, The Midnight DBA’s (Sean and Jen), David Stein, Andy Eggers and all the rest of the SQL Saturday #63 team did a great job!
I’m a Community Super Hero
And for $25.00 you can be one too! Participating in SQL Saturday events as both a speaker and an attendee has had a profound impact on my career. I’ve had the opportunity to learn from some of the best SQL people in the business and it’s never cost me more than a Saturday and maybe a few bucks for a box lunch. SQL Saturday #65 is coming up in a few weeks in Vancouver and they need some help. At the moment they’re projecting an $1800 deficit in the budget. This is Canada’s first SQL Saturday and I want it to be a huge success. I rarely break out the wallet, but this is important. I’m stepping up with $25.00. If you can do the same, go here. It will be put to good use. I promise.
Oh … by the way, I’m also speaking at this event. I’ll be talking about rapid fire bi with Tableau. If you’re planning on being there drop me a note in the comments!
A vote for me is a vote for … me!
I’ll let you in on a little secret. Public speaking is FUN! About a year ago I gave my first presentation at a SQL developers group. Since then I’ve given about a dozen presentations for SQL developer groups and SQL Saturday events. It turns out giving presentations on technical topics to your peers is a great way to stay on top of your game.
This year I decided to submit a session for the SQL Rally analytics track. SQL Rally is a new yearly event offered by the PASS (Professional Association for SQL Server) committee to give people a smaller alternative to the big yearly PASS summit. It’s two days of SQL training with an optional one day pre-con the day before the event begins.
You can register to vote here . After you register you’ll receive an email with a link to the voting survey.
I’d really appreciate it if you would register and vote for my session.
Title:
Building a Visual Vocabulary
Speaker:
Tim Costello
Category:
Analytics
Level:
100
Abstract:
Data visualization is a hot topic. In this session we will cover two of the most common data visualization patterns: time series and Part to Whole analysis. We will discuss how to identify them, common problems, best practices and what visualizations work best for each.
Session Goals
- At the end of this session the attendee will feel comfortable identifying these common data patterns and understand which data visualizations most effectively tell the story of the data in each scenario.
Speaker Bio:
Tim is a business intelligence developer with InterWorks inc. in Dallas, Texas. Tim is passionate about data visualization and helping people realize new insights from their data through data visualization.
Moving on.
I’m pleased to announce that I’ve been offered a chance to work with the team at InterWorks. I’ve given notice to my current employer (the Beryl Companies) and will start as a full time BI consultant specializing in business intelligence development on February 1, 2011.
It’s been a difficult choice, but in the end I couldn’t resist the chance to work with InterWorks. They are the best of the best in BI consulting. I’m looking forward to working with @behfar, @DGM885, @brianbickell and the rest of the InterWorks team!
SQL Server Integration Services (SSIS) Resources
SQL Server Integration Services has been a passion of mine for a long time now. I’ve loved working with SSIS from the beginning, what I didn’t love so much was learning about SSIS. There are some good resources out there, then there are some really bad resources. These are my favorites …
Books:
If you’re new to SSIS, “Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services” is the place to start. Short, well written chapters cover all the fundamentals. The best part of this book is that there is a video tutorial associated with each chapter, and each chapter has labs. Read the book, watch the video, do the labs and you’ll have a good grasp on SSIS.
“Professional Microsoft SQL Server 2008 Integration Services (Wrox Programmer to Programmer” almost didn’t make my list. I thought the first version of this book was like reading a dictionary. I enjoyed this version (the 2008 version) a LOT more. Maybe I just wasn’t ready for this book when I read the 2005 version. By the time I finished the 2008 version of this book I had a whole new appreciation for SSIS. This one stays close at hand when I’m working in SSIS.
“Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution (Wrox Programmer to Programmer)” is one of my favorite tech books of all time. I LOVE this book! My only warning is that this one’s not for beginners. There is very little hand holding in this book. Each chapter is solid gold, no fluff at all. For example, in the first step by step section of the book you’ll be told to open a connection to a data source. You don’t get the same 10 steps in every walk through helping you establish a connection to data. The authors assume you know the basics by this point. If you’re not strong on the basics before you read this one, make another pass through the 24-hour trainer.
Online resources:
The Midnight DBA’s have some EXCELLENT SSIS video training on their site. They are both SQL Server MVP’s. I’ve learned a lot from the Midnight DBA over the years. This video on commenting tribal knowledge in ssis pages is one of my favorite SSIS videos .
Patrick LeBlanc hosts SQL Lunch, a monthly webcast about SQL Server. This webcast has been going strong for a long time, there is a ton of great (free) SSIS training available here.
SQL Share is an amazing resource for free SQL video training. There are hundreds of SQL videos available here, many of them are on SSIS topics. The cool thing about this site is all the videos (hundreds) are very short 5 – 15 minute takes on really important topics. This is a great place to brush up on t-sql or get a nice 10 minute overview on how that transform you never noticed might make your next SSIS package the talk of the office. HIGHLY recommended.
The BI Developer Network is one of favorite BI (business intelligence) focused web sites. There are hundreds of white papers, blog posts and forums to discover and explore here. If you want to be a part of the SQL BI community, this should be your first stop.
My Tweets
-
Howard Dresner: #FF @doug_laney @Greendrv @Xenos_Group @millenium2ko @seamuswalsh @TimCost @PanoramaSW @kiranjoza @ekolsky @MarkLack @BradyMagic @wjdataguy
-
Danny Lloyd: @TimCost @chriskoenig I'll have to try it the next time I'm in Dallas
-
Tableau Software: @TimCost @pouls29 have you checked through the forum and with the LinkedIn group?
-
TimCost: been there, #Hard8 better. RT @dannylloyd: @ChrisKoenig @timcost Those are some bold statements! What about The Salt Lick in Austin?
-
FlyingBinary: TY Richard RT @ManBagBlog: #FF @howarddresner, @FlyingBinary, @bikespoke, @millenium2ko, @HiddenProfit, @EvansBI, @timcost, @genesissap
-
Danny Lloyd: @ChrisKoenig @timcost Those are some bold statements! What about The Salt Lick in Austin?
-
Richard Jarvis: #FF Suggestions: @howarddresner, @FlyingBinary, @bikespoke, @millenium2ko, @HiddenProfit, @EvansBI, @timcost, @genesissap
-
Chris Koenig: No lie >> RT @TimCost: @ChrisKoenig I'll RT you're #Hard8 tweets for a share of the coupons! Best bbq in Texas.
-
TimCost: #Interworks does this. RT @pouls29: Has anyone used #tableau to navigate a debug log? #omniture
-
-
TimCost: @ChrisKoenig I'll RT you're #Hard8 tweets for a share of the coupons! Best bbq in Texas.
-
-
Matt McQuillan: So very true! RT: @TimCost: @mmcquillan There can be only one. #CouldntResist.
-
TimCost: @mmcquillan There can be only one. #CouldntResist.
-
TimCost: @nebbed grab it while you can. I would clarify 'it', but what the hell, it's friday . #whateverworks
-
Chad Walter: @TimCost - Thanks Tim - enjoyed what you brought to the table! #BIWISDOM
-
FlyingBinary: RT @howarddresner: @TimCost Thanks for joining! Have a great weekend Tim! #BIWisdom
-
Howard Dresner: @TimCost Thanks for joining! Have a great weekend Tim! #BIWisdom
-
-
TimCost: well said: RT @FlyingBinary: #cloud security is a red herring but scaremongering on this profits those with most to lose #BIWisdom