Building A Complete Data Solution Using The Microsoft BI Stack

Microsoft has a full-featured platform for handling the data needs of most companies. It starts with a strong relational database backend for transactional processing and data warehousing (SQL Server), followed by an analytical processing and data mining tool (SSAS), a solid integration service to handle various data migration tasks (SSIS) and finally a good product for various reporting needs (SSRS). On top of all that we have a variety of Excel add-ons for business intelligence and data mining and lately the very well received Power BI platform for self-service data visualization and business intelligence.

This is all great. The problem is that often many of these products end up being used by themselves instead of combining their various strengths to truly build a data management solution that will enable the business to use its own data for maximum benefits.

There are all sorts of resources online that will attempt to make one an expert in any of the products mentioned above … but not that much guidance in terms of really explaining how they’re supposed to work together.

Many technologists start with SQL Server on the OLTP side – we process daily transactions for the business and we make sure that create / update / delete operations are as fast as possible or else customers will go to a competitor.

So now we have all this data that’s accumulating in a relational database and somebody wants to run reports against it. No problem – we know how to do that so we slap together some reports in SSRS – problem solved … except that as time goes on (and we get more and more data) those reports seem to get slower and slower. And no … please don’t run that report that gives us a summary for the whole business for the last 3 years … you know which one … the one that joins 30 tables, takes 10 minutes to run and slows down the entire database.

What’s the problem here? To use the famous quote – “If you only have a hammer, you tend to see every problem as a nail.” We know SQL Server very well but the relational database model for transaction processing is not the ideal backend for business intelligence and analysis.

Somebody then mentions the term “data warehouse” .. and OLAP … and slowly changing dimensions. Oh no – more technology to learn. How exactly do we go from point A (our transactional database) to point B (some sort of data store that business analysts can query with their fancy graphical tools to build all the nice charts they’re looking for).

We need to spend more time looking at how we can integrate all these technologies (most of which we get for free once we purchase SQL Server) in order to satisfy the various data needs for the business.

I recently came across a very good series of hands-on articles (source code included) that explores all these products and creates a nice picture of how they all fit together.

Part 1 – Introduction and OLTP Database Analysis

Part 2 – OLAP Database Objects Modeling

Part 3 – Data Mart Load Approach and Coding

Part 4 – Data Mart Load Using SSIS

Part 5 – Defining A SSAS Project And Its Dimensions

Part 6 – Refining SSAS Data Warehouse Dimensions

Part 7 – Cube Creation, Cube Deployment and Data Validation

Part 8 – Creating A Sample SSRS Report

I hope you find all these useful.

Leave a Comment