I had the good fortune to overhead some of my good friends and fellow SQL Server MVPs discussing the process of data modeling for business intelligence systems. So what are the industry standard approaches for modeling dimensional data modeling?
The short answer is that Kimball's dimensional modeling is the most widely adopted standard for any sort of data warehouse. Inmon is also well respected.
Recommended Dimensional Modeling Book
But the consensus is to stick with Kimball, especially if you're looking for something an enterprise standards team is going to recognize as "standard." The book on my shelf and which I most frequently recommend is The Data Warehouse Toolkit: Complete Guide to Dimensional Modeling.
There's a version of this book specifically for the Microsoft platform, focusing on SQL Server Analysis Services (SSAS). But I recommend the generic one because the platform-specific version compresses some of the general concept sections to make room for the Microsoft-specific content. The Kimball book does a good job describing how important it is to get the conformed dimensions and the precise measures most needed for the BI application. Otoh, the platform-specific version does point out some nice tools available for download from Microsoft's website. Fortunately, you can just go the "Tools & Utilities" tab from this page.
Notations & Design Patterns
Of course, if you're using a data modeling tool, the specific notation and design patterns might vary a little bit because the tool offers only a given set of workflows or symbols. Also, some industries have already mapped out specific pattern data models along with six or seven industry standard figure data models, many of which are free if you know where to look.
[Note: I don't know where to look. So if you do, please post a comment with this important insight!]
You might, for example, apply the set of common patterns made popular in financial BI apps, in which you have a staging/ETL area, data marts, and a data warehouse and then carefully measure how quickly and reliably data reaches the user. After all, BI is much more than just the cubes, reporting, dashboards, and event subscriptions of an SSAS/SSRS/SSIS implementation. It must be useful for and usable by the end-users.
So, I'm curious—
- What standards patterns and notations are you using?
- What data modeling tools are influencing your design?
- Have you taken advantage of the various free industry patterns out there?
Inquiring minds want to know! Post your comment below. And, as always, thanks!
P.S. A special thanks to Bob Duffy (Blog), Davide Mauri (Blog), Robert Pearl (Twitter | Blog), Audrey Hammonds (Twitter | Blog), Karen Lopez (Twitter), Thomas Ivarsson (Twitter | Blog), Chistian Cote (Twitter | Blog), and Dr. Greg Low (Twitter | Blog) for letting me eavesdrop on their very informative conversation! Be sure to read their blogs and follow them on Twitter.