THE SQL Server Blog Spot on the Web

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

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

How do you learn BI as a DBA?

As a DBA, I control a lot of data in my environment.  Most of this data is stored in relational Online Transaction Processing (OLTP) databases that support the daily operations of the applications in our environment.  While it is possible to do basic reporting off of this data in its current form, there are trade offs associated with trying to report on data stored in OLTP systems.  Taking a business from basic reporting to the next level, and creating a true Business Intelligence solution around the data contained in relational systems, can be a daunting task for a OLTP DBA to do.

For just over a year now, I have been learning this other side of data management, and i have to admit, that what seems like it should be a simple task has proven to be much more difficult in practical application.  My last employer began a path to BI not long after I started with them, and did a lot of research into the popular BI tools available on the market in 2008.  They looked at almost every solution in the top right quadrant of the Gartner 2008 BI Magic Quadrant:

Gartner BI Magic Quadrant 2008 Diagram

Ultimately they decided on Oracle BI EE as the platform of choice for BI, and I have to admit given that the majority of the business data was stored in Oracle, this selection made sense.  On to of that, the OBIEE platform is really nice.  I attended a good bit of training on OBIEE Administration to fill the roll as a backup to our Oracle DBA, and I was pretty much lost when it came to designing and implementing dimensional based data models to support BI reporting.  It was really hard for me to think about data the way that the trainers were teaching it.  Denormalization, star schema’s, fact tables, attributes, dimensions, and a myriad of other items were all foreign to me.  I trudged through things and built a basic BI model in OBIEE on top of a SQL OLTP system during one of the training sessions, but I continued to have problems with how to properly model the data, and consistently had to dump my current design and essentially start over to correct flaws in the model that didn’t allow it to answer the questions being asked of the data.

One of the best tools that I was provided by a trainer was a copy of The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition) by Ralph Kimball.  This book was really helpful to gaining an understanding of the what, why, and how of designing a Data Warehouse following proven patterns and practices working with common types of data.  This book coupled with the training I had with OBIEE made it possible to build a BI model that worked for building drag and drop reporting from one of the SQL OLTP systems that I managed.  However, I later tried to reproduce this same model using the Microsoft BI Stack and immediately ran into the same problems I had originally working with OBIEE, coupled with the fact that I had no idea how to work with the BI side of SQL Server.

I’d like to be able to say that I pressed on and figured out how to provide a BI solution with SQL Server, but I had other priorities in life at the time and gave up on playing with this.  Less than a year later, I found myself faced with the opportunity to once again dive deeper into the Microsoft BI Stack and build a solution to a business problem associated with data maintained in SQL Server.  However, instead of going the BI route, I leveraged my knowledge of TSQL and SSRS and created a basic reporting solution on top of the existing OLTP. 

Since I created this initial solution, I have been doing a lot of research and reading on the Microsoft BI Stack using SQL Server 2008 R2 and Sharepoint 2010.  It turns out that I had restricted myself a bit more than I should have.  Last night when I got home from work I checked the mail and found the latest copy of MSDN Magazine in my box.  It had a number of articles on Sharepoint 2010, and since we are rolling out a Sharepoint 2010 farm at work right now I decided to throw it in my laptop case to take to the office for the Sharepoint Developer to look at.  Today it peaked his interest so I decided to dig through my stack of older MSDN and TechNet Magazines in my office and for whatever reason, the cover of the August 2009 issue of TechNet caught my eye.  In it are the first two of three articles that provide an excellent primer for getting started with the Microsoft BI Stack.

Planning Your First Microsoft BI Solution
Building a Data Foundation for a BI Solution

After reading these two articles I jumped online to find the third article that was mentioned in the August issue and found it immediately.

Building Your First Cube

I’d highly recommend all three of these to anyone that is a DBA and looking to learn more about the Microsoft BI Stack.  The first article provides some really good background on what BI is, and the problems that it attempts to address.  The second article covers the basics of how to build the underlying Data Warehouse structures.  The third article covers how to build a SSAS cube on top of the Data Warehouse.

Published Monday, July 12, 2010 7:09 AM by Jonathan Kehayias



jamiet said:

Really enjoyed reading this Jonathan, thanks very much. Its nice to see someone coming from the OLTP (hate that term) side to the BI side and reading about their experiences.

One thing I'd like to mention. Kimball's dimensional modelling ethos is no silver bullet. I remember being spoon-fed that stuff when I first started out and it led me down the track of thinking that denormalisation and such were GOOD things when in fact its more accurate to say it can be useful when employed in the correct manner and in the correct time & place. Lest we forget SQL Server has FKs for very good reasons - we shouldn't ignore them.

Hopefully people ask themself whether the kimball method is really necassary. After all, if you're using a cube atop your DW why bother denormalising? You're only speeding up your cube processing which may not be all that important and moreover you're doing it to the detriment of good data management principles.

I'll get off my soapbox now, its dangerous saying this stuff in public :)


July 12, 2010 6:47 AM

Eric Wisdahl said:

Blasphemy! Death to Jamie! ... Just kidding.

I too enjoy hearing about DBAs coming to B.I. as opposed to my own experience of application developer going to B.I. (trying to learn the basics of going to DBA).  

Kimball's Data Warehouse Toolkit is a great way to get your head around the ideas for dimensional data modeling.  But, as always, think for yourself when trying to come to terms with what will work in your environment.  

July 12, 2010 9:11 AM

Adam Machanic said:

You mean top right, not top left -- right?

July 12, 2010 11:09 AM

Jonathan Kehayias said:

Thanks Adam, I've corrected it and republished.  I need to go back to elementary school apparently.

July 12, 2010 11:39 AM

Keith Mescha said:

Nice post, I took the opposite journey actually went from BI person into the DBA team on the OLTP side. Maybe I should write about that some day. I still struggle with things as I want to apply BI techniques to every problem.

July 12, 2010 1:11 PM

rich said:

Cheers Jonathan, thats the push I need.

The pile of books is daunting, those articles are a more feasible starter!

July 13, 2010 10:22 AM

Nitin said:

This blog is a nice read along with the mentioned book

August 3, 2010 2:38 PM
Anonymous comments are disabled

This Blog


Privacy Statement