Microsoft Business Intelligence Differentiators

You will find that many systems cover the basics fairly well but there are differences between them that can be crucial. Our analysis was conducted a few years ago for a seminar presented at the Financial Technology Show. We will endavour to provide an update to our analysis in the near future.

Platform

Microsoft offers a platform to build BI. BI requires ETL tools (Extract, Transform and Load) and Microsoft offers 3 solutions for ETL - Data Transformation Services (DTS), BizTalk and Host Integration Server (HIS). BI usually requires a data warehouse or staging area, which can be Microsoft SQL Server. OLAP is another BI component and Microsoft Analysis Server is included with Microsoft SQL Server. BI also requires reporting and enquiry and Microsoft offering includes Excel, Data Analyzer and Office Web Components.

Microsoft SQL Server

Microsoft SQL Server has become a leader in the enterprise database marketplace. Most ERP systems already use Microsoft SQL Server, which means that they already have many of BI's components.

DTS

With knowledge of the data structures being extracted, DTS is the tool of choice as you don't need to invest in expensive ETL tools. DTS will connect to the data sources using OLE DB, Open Database Connectivity (ODBC), or directly to text-only files. If data scrubbing is required, you will need additional tools.

BizTalk

BizTalk provides enterprise application integration using XML and includes data mapping for many of the leading ERP systems.

Analysis Server

Analysis server provides the OLAP engine. One key point is that Analysis Server is open and there are many third party tools that allow access to Microsoft's OLAP cubes, which will drive down the price of enquiry, which tends to be expensive with proprietary OLAP cubes. Analysis Server also supports real time updates from Microsoft SQL Server as well as write-back functionality. Security can be at the dimension or cell level.

Excel

Excel's PivotTable allows for slicing and dicing across multiple dimensions, drag and drop dimensions across rows or columns, drill down, drill through, and graphing results. It currently lacks tools to customize rows or columns with new calculations.

Data Analyzer

With Data Analyzer, you can graphically filter, sort, drill up or down.

Office Web Components

You can create dashboards or portals that access OLAP over the internet. You could also manipulate data from a browser just as if you were using Excel.