In today’s blog I decided to cover what Microsoft has to offer when you want to build a business intelligence platform and I will explain when (I personally) would use certain product or technology.
But why I do bother writing about it? Gartner (gartner.com) research reports BI Market Revenue Hit $12.2 Billion in 2011 and increase of massive 16.3% comparing to 2010 so it is not surprising that you have many serious players with lots of different products which can create a lot of confusion. This gets interesting when you hear about this fantastic product that is quick like a lightning but costs millions of $ (or £) and someone goes into details and finds out that almost every single vendor offers the same product in a similar price! (they just call it differently).
One of the questions I want to answer is “does Microsoft offers it as well”? The quick answer is yes! Let me give you a quick overview of Microsoft history in BI.
Feel free to skip the next paragraph! After history I put high level overview of Microsoft products with usage in "Matrix" format.
Microsoft invested a lot of resources to become serious player in BI but that was not always the case so the Microsoft you know before 2005 actually was extremely BAD in BI tools (I would never recommend them!) but they listened to customers (who are willing to pay) and they released first proper BI tools in 2005 which mainly was SQL Server 2005 (which is also when I decided to go with Microsoft) and that is point when it all started….. they become more and more popular (also because they are “cheap” comparing to others offering similar features). 2008 was another big year for Microsoft with a few interesting features….but they haven’t managed to put everything in 2008 so in 2010 then released SQL Server 2008 R2 which contained “missing” features/products. The latest SQL was actually released this month (April 2012) and contains plenty of new features that many customers were impatiently waiting for! Main ones being in-memory BI solutions (Vertipaq/DAX in SSAS that are used with PowerPivot), Master Data Management version 2 (v1 are never brilliant) and Data Quality Services (data cleansing tool which is a must for a successful implementation of BI Platform!)… and of course PowerView finally a proper self-service BI tool!... but I hate Microsoft decision for forcing customers to buy SharePoint in order to use it!!! Microsoft when will you learn to listen properly??!!
So let’s give you a quick overview of all products and quick pros and cons (related to BI Platform). Let’s assume we have “average” database with no more than 500 million rows in the biggest fact table:
Let's begin with matrix from my point of view for average solution.
The options are: 100% (I would use it in 100% of cases if I can), 50% (some specific cases), 0% never (if I have choice). N/A (Not applicable)
Parallel Data Warehousing (Software + expensive hardware). I haven't added it to my matrix because it is not used for average solution, unless you want to spend millions with very low return on investment (ROI).
NOTE: I go with multiple Microsoft products because that is most effective. Each of them is particularly good at something (but not so much with other stuff) all together they cover almost everything every business needs.
Below are more details (for average solution):
Technology: Relational Database (SQL)
Overview: Great for storing large volume of data (data warehouse).
PROS: SQL is simple to learn, plenty of options to improve performance (including new SQL Server 2012 column store index).
CONS: SQL is not brilliant for decision questions or analysis (trends, YTD, Previous Year). Can be slow if you haven’t spent a lot of money on hardware or you don’t have full time Database Administrators (DBAs).
Usage: I would use it for storing data (data warehouse) but not for reporting unless I don’t have choice.
Let’s move to another tool.
Technology: Analysis Services (Cubes)
Overview: Pre-aggregated data stored in multi-dimensions format (similar concept to “points on a map”)
PROS: Performance, simplicity of MDX queries for analytical questions (trends, YTD, previous “member”).
CONS: You need someone who knows how to use it. Many people are “scared” as they are not very familiarised with this technology but for average solution it really is very simple!
Let’s move to another tool.
Technology: Business Intelligence Semantic Model (BISM – Tabular model)
Overview: This is new to me as well but basically it is similar to cubes but in-memory = extremely fast! Uses Vertipaq engine (in summary massive compression + quick access to data)
PROS: Extremely fast! Able to answer some very complex scenarios (which are very difficult with SQL and sometimes also with MDX)
CONS: You need a lot of memory! (Not that much for average solution). This is still new Microsoft technology so it will take time to improve it. Uses DAX to query data which is another thing to learn (for me at least!).
Let’s move to another tool.
Technology: Parallel data warehouse
Overview: I will use Microsoft words…. “Organizations need actionable and timely business insights from rapidly growing data. Take advantage of Microsoft SQL Server Parallel Data Warehouse and its massively parallel processing (MPP) architecture to gain scalable performance, flexibility, and hardware choices with the most comprehensive data warehouse solution available.”
PROS: You get a lot of power!
CONS: Costs a lot of money (you won’t get high return on investment with average solution)
Usage: Data storage, processing and reporting.
I’ll finish here for today…. When I find a minute I will add:
Self-service BI (PowerView)
Self-service BI (PowerPivot)
Self-service BI (Report Builder)
Master Data Management (Master Data Services)
Data Quality (Data Quality Services)
Cloud (SQL Azure)
Before I finish let me ask you a question. Do you think it is possible to create one "technology" that would meet all customers needs? So we don't have to have relational databases, multi-dimensional databases, in-memory "databases", parallel data warehouses and so on!
Feel free to add a comment.