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