An Introduction to Microsoft’s Business Intelligence Stack

By Angel Abundez

Introduction

Business Intelligence (or BI) is the visual display of key business information to business users that need to do their jobs. Microsoft’s BI offerings have gone through leaps and bounds since their initial inclusion in SQL Server 2000. The reason I like Microsoft’s stack so much is because it’s proven to give companies the flexibility, performance and scaling they need in a BI platform at the most competitive price. Of course, a picture is worth a thousand words. From the looks of Gartner’s 2011 Magic Quadrant for Business Intelligence Platforms, you can tell clearly that Microsoft is leading the heap.

Some reading this may already be familiar with BI vendors such as Tableau or QlikTech. Perhaps the reporting capabilities out of your ERP/financial system reports are good enough. Without attacking any of the tools you use right now, this article is meant to educate you on the Microsoft BI platform from a practical sense. It’s important to understand why customers rated it so high on its “ability to execute” in the magic quadrant above. Gartner also mentions that low licensing cost smake Microsoft’s BI platform very competitive as well.

To show you just how valuable the Microsoft BI platform can be, I thought I would tell you a story.

…In a Galaxy Far, Far Away

Once upon a time, Company X provided professional services and manufacturing to a variety of industries. They had experienced healthy growth in recent years with a few remote offices turning up around the United States. The time came when the company’s employee count increased, the amount of projects also increased, people were busy and the shop was full of work. Life had to be good, right? Well, not exactly.

Deliveries were not being met and Company X was failing to hit their revenue projections. Management scrambled to get the data needed to investigate as to why costs were high. Was utilization of their employees too low? How did management not know this issue was brooding?

Realizing that users needed accurate information faster, the company piloted the use of SQL Server Reporting Services (SSRS). They developed reports from their two core systems: Finance and Project Management. Together, these two systems were to provide the outlook for the entire company, both actual and forecasted.

The investment proved to be useful, as awareness of company issues was become apparent. Business users’ reporting needs grew, the knowledgebase of the source systems was growing and the web based portal they called “Report Manager” was handling most, if not all, of the data distribution. SSRS also includes features such as Data-driven Subscriptions and exports to Excel. One developer even used T-SQL behind an Access database to automate the generation of a Budget spreadsheet complete with Actuals and Current Estimates, also known as EACs.

Life at the company was getting better. But, upper management was stuck because forecasts still weren’t accurate enough. Additionally, management needed a view of the aggregate costs and revenue across all projects, not just the multi-million dollar projects.

Company X’s vice president had developed an awesome model using Excel by creating a mashup of actual and forecast data to analyze cash flow for a few projects. However, the process was cumbersome, as it required pulling data from the source systems and into Excel. Then, employees had to further manipulate the data over six worksheets to come up with a summary tab for one project. With more than 60 projects to forecast, this method would not do.

Luckily, Microsoft answers this need with SQL Server Integration Services (SSIS) to Extract, Transform, and Load the data into a separate table in the database for the end analysis. No more multiple-tab worksheets! More importantly, the 60 projects were ready to be analyzed. 

SSIS’ core purpose is to move data and do something with it. This is probably my favorite tool in the SQL Server BI Stack because it is very visual and you can see where your data is going. Once the ETL is developed, you can also schedule it to run as often as you need updated information. It has many other uses, such as import/export to Excel, emailing attachments, looping through files, performing maintenance on the server, changing the tires on your car. Anything! (Ok, I’ve gone too far).

With a couple SSIS packages and SSRS reports later, upper management had its company-wide forecast table being updated daily! This allowed the business analysts to further refine the project forecasts reports, check for accuracy and ensure they get the data they want. Soon after, more summary reports were being developed.

Life was really good, right?

Yes, but the reports started running slow. Users were using the reports more frequently. Plus, new report requests were now coming in almost daily. Common requests were to slice the data by different entities such as Resources, Departments, Project Types and Offices. There was some confusion on which SQL Server feature to use to tackle this problem.

The company had to determine exactly how many users needed to analyze the data. If the answer was one or two, then PowerPivot for Excel 2010 would have been a nice fit. It answers the business analysts’ need to pull large volumes of data into a spreadsheet to slice and dice, complete with slicers that are graphical filters, which allow dynamic changes to your reports.

However, Company X had multiple users who needed to access big data; therefore, the best option was SQL Server Analysis Services (SSAS) to create OLAP databases, also known as Cubes. These OLAP databases are high performance database structures that aggregate, slice-and-dice and organize your dimensions with hierarchies, allowing you to drill down until you get to the data you want. On top of all of that, it is really fast.

Company X was now well ahead of their business. Because to their BI efforts, behaviors were encouraged, hard-fact discussions were taking place and forecasts were getting more accurate. New project forecasting standards started to arise that were never in place. Surprise spikes or dips into revenue and billings would be detected months before they were expected to occur.

Conclusion

In short, Microsoft’s BI stack extends the capabilities of SQL Server to new heights. If you own a license, don’t be afraid to use these exciting tools.