What's the Data Modeling Standard for Business Intelligence Systems?

RSS

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: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!

-Kevin
-Follow me on Twitter!
-Google Author

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.

Discuss this Blog Entry 4

on Jul 11, 2013

Perhaps The Data Model Resource Book by Len Silverston and Paul Agnew is a useful reference for design patterns and notations. Also Data Model Patterns by David C. Hay might be a resource. Above all practice pragmatism, rather than stringent adherence to a set of rules. Aligning to a common approach is a great thing, but not "the thing".

on Aug 16, 2013

Please keep on posting such quality articles as this is a rare thing to find these days. I am always searching online for articles that can help me. Looking forward to another great blog. Good luck to the author! all the best!

on Aug 30, 2013

I would also recommend another book by David C. Hay, UML & Data Modeling: A Reconciliation. It's a great book for both Data Modelers and UML experts. I have had a lot of great discussions with my peers over this book.

on Nov 7, 2013

If you are considering Microsoft PDW consider also the article Implementing a Microsoft SQL Server Parallel Data Warehouse Using the Kimball Approach (http://msdn.microsoft.com/en-us/library/hh290126.aspx)

Please or Register to post comments.

What's Tool Time Blog?

SQL Server tools and tips from Kevin Kline.

Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×