THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

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

I had the good fortune to overhead some of my good friends and fellow MVPs discussing the process of data modeling for business intelligence systems.  So what are the industry standard approaches for modeling dimensional data modelling?
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.

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

http://www.amazon.com/The-Data-Warehouse-Toolkit-Dimensional/dp/0471200247/ref=sr_1_1?ie=UTF8&qid=1342453858&sr=8-1&keywords=data+warehouse+toolkit

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:   http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/microsoft-data-warehouse-dw-toolkit/.

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 here.  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 | Blog), 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.

Published Tuesday, July 02, 2013 11:21 AM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

RichB said:

I rather like Lawrence Corrs 'BEAM' - Business Event Analysis & Modelling.  

Seems to make it all much more accessible than the usual approaches. Based on Kimball as I understand it.

July 3, 2013 6:18 AM
 

Steve P said:

Yep. We've adopted Lawrence Corrs BEAM too. Works well with a more Agile approach to the dev and is just less scary than any other approach.

There's way too much involved in getting Enterprise Data Models right before you start. Its often a futile exercise as the systems and requirements are too much of a moving target.

A fast way to get something working is often more useful to our clients. Right enough rather than absolutely right.

http://www.amazon.co.uk/Agile-Data-Warehouse-Design-Collaborative/dp/0956817203/ref=sr_1_1?ie=UTF8&qid=1372858941&sr=8-1&keywords=lawrence+corr

July 3, 2013 9:47 AM
 

Koen Verbeeck said:

I usually go with the Kimball approach. It works really well with SSAS Multidimensional and it's quite intuitive for end users as well.

It also serves well for multiple iterations: you make one fact table with its corresponding dimensions and in the next iteration you add another fact table and so on.

July 4, 2013 3:10 PM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement