Saturday, August 15, 2009

Microsoft BI - The Good, the Bad, and the Ugly

I was reading Derek Comingore's post "Addressing the MSFT BI Naysayers" and it pushed me over the tipping point to actually write about the Microsoft BI stack. It's been bugging me for awhile. I'm a professional business intelligence consultant and have used the Microsoft stack to develop solutions for both very large organizations as well as numerous SMBs.

It's trendy to criticize the Microsoft's BI effort. Rather than try to argue against generalizations such as "Microsoft BI sucks" (you can't really win an argument here) I wanted to put something out there for the technologist and/or decision maker who is not sure whether or not to use Microsoft for their BI needs. It's as unbiased as I can make it given my background. I hope my opinions are somewhat helpful.

There are many Microsoft-specific tools and factors that can affect the success of your BI project. Here are some of them in no particular order.

SQL Server (the relational database) - Very good.
I don't think anyone would argue that the core SQL Server relational database engine is solid and scalable. There's absolutely no reason why SQL Server cannot be the RDBMS engine for a multi-terabyte data warehouse (as long as you have the right people implementing it.)

Analysis Services (SSAS) - Very good.
For the price, Analysis Services is the best multi-dimensional database on the market. It's been around, in one form or another, for over a decade. It matured significantly in 2005 and can service most analytical needs. Sure, there are ongoing issues but the product continues to evolve.

Reporting Services (SSRS) - Good.
I have a love/hate relationship with reporting services. It's matured significantly in the last 6 years and is completely satisfactory for relational reporting. However the SSRS and SSAS teams really need to get together and hug it out. I've developed many complex SSRS reports using MDX and it's not fun. There are 3rd party tools that can make life easier but, really, SSAS should not be a second-class citizen here.

SQL Service Integration Services (SSIS) - Good.
SSIS 2005 was a huge improvement over DTS in terms of capabilities and SSIS 2008 was a huge improvement over SSIS 2005 in terms of performance. This is a solid ETL tool that is just getting better. I still have a lot of things I'd love to see change but it does its job like a good soldier.

PerformancePoint - Bad and Ugly.
What can I say that hasn't been already said about PerformancePoint? It was a complete disaster. What has survived from PerformancePoint is, quite frankly, a pitiful excuse for a BI product. The scorecard abilities are clunky and simplistic and the visualization capabilities are downright embarrassing. I'm not the kind of person that wishes harm upon anyone but I really hope someone got fired for this. Solution partners that built practices/businesses around the planning component got screwed with their pants on. This set the Microsoft BI movement back years and may have permanent consequences.

ProClarity - Ugly.
I love ProClarity. I've been using it for years and it has such a strong story both from the client and server perspectives. One of the biggest tragedies in the Microsoft BI world is that Microsoft purchased ProClarity and has done absolutely nothing with it; it's just sitting there collecting dust. To this day, I have no idea why they purchased it and I don't know what will replace it. All I know is that Microsoft partners I talk to say not to invest too much time/effort into implementing it going forward. What are we supposed to use instead for the centralized management of custom views with all of those advanced and interactive visualizations? Imagine where we would be today if ProClarity would have remained independent and innovative.

Excel 2007 - Good.
Excel is the 3rd rail of Microsoft BI. Stephen Few's article "Excel 2010: Another Opportunity Missed" is interesting and certainly argues strongly that I should hate Excel and Microsoft for moving so slowly. But I don't. And, you know what? Most customers I talk to don't either. They feel like they should because everyone tells them that Excel "sucks". But, they like it. Even while writing this I feel ashamed to admit that I use Excel for many of the analytical tasks I do. I find things like conditional formatting and the data mining add-ins awesome. There are a lot of BI-related things I can't do in Excel but I wouldn't *want* to do them in Excel. There needs to be other products for things like scorecard management and dashboard design. Excel is one of my many data analysis tools and it's good. There, I said it. Stop picking on us.

Metadata management and general knowledge management - Non-existent.
Why would anyone need to document their system, capture requirements in a consistent way, and centralize the management of issues/defects? Nobody needs to know how a data warehouse solution was built, right? People never leave organizations with their knowledge in tow. And it's not like there are a lot of moving parts that are involved in bringing data from a multitude of external systems into a central warehouse? Oh wait, there is. One day Microsoft will realize that the tools that facilitate the process of building and maintaining these systems are at least as important in influencing project success as the technology itself. Yes, there is Team Foundation Server and SharePoint but they fall woefully short in far too many areas. Effective knowledge management is everything.

3rd Party Tools - Very good.
There is a cornucopia of third party tools out there. This is, in my opinion, the #1 thing that most armchair critics don't understand. Microsoft is not in it alone. Yes, Microsoft visualization tools are awful but others are not. Unfortunately most people think that Microsoft has to provide an end-to-end solution for it to compete. In fact, with all of the recent acquisitions in the BI space, it seems that all vendors think this is true. It's wrong. It's very wrong. Microsoft had to first concentrate on delivering a solid platform for data warehousing (SQL Server, SSAS, SSIS) because this is the foundation for everything else. Choice is good and I want competition at the analytics level. That's where the real innovation is going to happen and Microsoft can't do it all by themselves. The unfortunate reality is that procurement officers and decision makers often don't want to look outside of Microsoft. They would rather implement a flawed Microsoft front-end rather than a proven 3rd party suite. The old saying is true: "No one ever got fired for choosing Microsoft."

The Price - Extremely good.
One of the strongest arguments for choosing Microsoft BI is the relative cost of the core data warehouse suite (not including its data presentation components.) It's a steal. Additionally, although I have no quantitative proof to back this up, I believe from experience that consulting companies that implement Microsoft BI solutions are far less expensive than comparable consulting companies for the competitors.

The Support - Very Good.
The user communities are abundant and Microsoft support is top notch. There is no reason why you can't contact the product teams in cases of emergency. I'm continually impressed by the quality of newsgroups, blogs, and books from people in this community. Thank you.

Microsoft itself - So very, very, very bad and ugly.
If you are considering implementing your first Microsoft BI solution then pay particular attention here. Most of the development teams in the Microsoft organization are utterly amazing however...

Microsoft is their own worst enemy when it comes to BI. They don't know what they are doing. I'm not qualifying this with "in my humble opinion" because I feel so very strongly about this. They have no clue. They've gone off the reservation. There is no concerted vision for Microsoft Business Intelligence or even an indication of profound understanding in the space. They just want to sell licenses in any way possible.

Their sales and marketing force is embarrassing. They will come into your organization and show you canned demos to illustrate the power of Microsoft BI. They will gloss over the complexities of data warehousing and effective data visualization. They will recommend completely inappropriate products to you without understanding your needs. They will help their solution partners make a sale and then disappear. They will effectively cripple your project before it has even started. They will not be good advisors. They will lie. It is a crime.

Furthermore this attitude is so deeply ingrained in many of their solution partners that many Microsoft Gold Certified Partners that you'll run into will be from the planet Mars. If you, in any way, question the comprehensiveness or capabilities of the Microsoft BI stack you will be met with furrowed brows. It will not compute for them. In their hearts they feel that they are part of the Microsoft world and Microsoft can do no wrong. Yes, there are good consulting companies that will guide you to success, but there's also a chance that black-23 will come up on the roulette wheel when you put your money down. How lucky do you feel? If you do find a good Microsoft BI partner, don't let them go.


I'm going to diverge a little bit and propose a solution to this particular problem. It's quite easy. Leadership.

There are two types of business leaders: Those that do because they want to innovate and grow and those that do because they don't want to get fired. Far too often business executives do the easy thing to make a good showing. Purchasing companies and launching slick marketing campaigns is easy. Putting a positive spin on failure is easy. Let me tell you what's difficult.

Scott Guthrie is a professional hero for a lot of people (including me.) He leads many of the web initiatives for Microsoft. The man is not human. He simply inspires awe.

Traditional ASP.NET is flawed and many people for many years have known it. It was designed using a bad abstraction layer and it's been steadily deteriorating. After 7 years of web development using ASP.NET I had grown to loathe it.

Here's what Microsoft achieved under Scott Guthrie's leadership in under two years:
  • Proposed a new web development framework (ASP.NET MVC) even though a fully mature web development framework had existed in the .NET space for 7 years. How do you even justify this to a steering committee or board of directors?
  • Hired strong and opinionated team members including Scott Hanselman, Phil Haack, and Rob Connery (sorry to see you go Rob) who came from a variety of private companies and open source projects to develop and promote MVC.
  • Released code previews and updates on a continual basis to the community in order to garner feedback and ensure quality before the initial release candidate. They listened… hard.
  • Changed the way Microsoft and the web development community interacted. The "let's make mistakes together and figure out what to do" approach was taken. Rob Connery got absolutely slapped around at times on MVC Storefront but it was a great experiment.
  • Incorporated jQuery (an open-source JavaScript framework) into ASP.NET including full technical support from Microsoft. They had their own JavaScript framework but said "jQuery is better." This is huge.
  • Made ASP.NET MVC open to the public (more or less.)
  • Many more things that I'm sure I don't even have a clue about.
Without openly saying so, Scott has tried to rectify a mistake that Microsoft made in the web space. I don't know how much Microsoft pays Scott but I guarantee you that it's not enough. Please acquire a Guthrie for the BI division. It will save Microsoft Business Intelligence. I promise.


Finally, in my experience, most of the BI projects that fail have nothing to do with the particular technology stack. It all has to do with "human" elements. Teams often:
  • Model the data warehouse in a way that is completely inappropriate - Good luck in recovering from this if you're close to the delivery date because it's likely all over. Read a good book on the fundamentals of data warehouse design before embarking on your next job.
  • Do not understand the art of visual presentation - Read a couple of Stephen Few books and subscribe to his blog. Presentation of data is everything in a BI project and I mean everything. The existence of a data warehouse alone provides no value.
  • Do not have the "right" data in the system - Too many times technologists just put whatever "stuff" they can in a data warehouse and hope for the best. Everything has to start with the users. You need to understand what dials and knobs they have to control their business and how you can help them figure out when to turn them.
  • Are lazy, have insufficient experience, and/or have bad leadership.
  • Have no sponsorship or they have stakeholders that ask for absurd things.
  • Over promise and under deliver.
I've never hit a large technological barrier with the Microsoft BI stack that forced us to pack our bags and call it a day. It's all about people. You will be successful with Microsoft, Cognos, MicroStrategy, Oracle or any other technology stack in the proper environment with the right team. Please remember that.

Microsoft has an incredibly strong BI story in terms of technology. You can do wonders with it. Visionaries will win here. Just navigate carefully.


  1. Hello,
    We facilitate the provision of independent analysis to support expert testimony, regulatory or legislative engagements. Frequently, this work includes economic, financial and statistical studies of varying data analysis, technical and

  2. Hi there! This is a good read. I will be looking forward to visit your page again and for your other posts as well. Thank you for sharing your thoughts about business intelligence consulting. I am glad to stop by your site and know more about business intelligence consulting. Keep it up!
    Often BI applications use data gathered from a data warehouse or a data mart. However, not all data warehouses are used for business intelligence, nor do all business intelligence applications require a data warehouse.
    Business Intelligence consulting is about identifying, harnessing, cleansing, standardizing and consolidating key enterprise data and presenting that in an appropriate medium that can be accessed and analyzed by business users in a secure environment to provide insight into business performance and empower superior decision-making.

  3. Resources like the one you mentioned here will be very useful to me! I will post a link to this page on my blog. I am sure my visitors will find that very useful.

  4. Learn Ms Excel and enjoy making your work easier as you need to do work manually to make sheets you can do on Excel that things. Excel training NY