Couple of weeks before, we had a serious discussion at the office regarding a client requirement on a very comprehensive Business Intelligence Module for their requested software. I was’nt very clear about the overall gravity (scope) of this requirement and also was not sure How to incoporate BI into a Microsoft Application.

So I did some RAD over the weekend on available/proposed Microsoft support for BI, and Here’s what I found.

First, BI is the ability to INTEGRATE, ANALYSE and REPORT on our existing data.
For the above purposes Microsoft has SQL Server Integration Services, SQL Server Analysis Services and SQL Server Reporting Services. These are allready there in SQL Server 2000 and improved/enhanced in 2005.

1. SQL Server Integration Services (SSIS)
This will collate data from different data sources. In SQL Server 2000, this was essentially Data Transformation Services (DTS). In SQL Server 2005 this is much improved. SSIS will prepare and Integrate data much easily than DTS which is then sent to a merge engine. This inserts data into the Data Warehouse.

2. SQL Server Analysis Services (SSAS)
SSAS will manipulate the information that has been brought together in the Data Warehouse from various data sources. SSAS is the next version of SQL Serevr 2000 Analysis Services and has revolutionized the data cube concept.Microsoft has also added XML for Analysis (XML/A) to improve accessability for clients and applications. Introduction of XML will standardize the interface to allow any application to import data for analysis.

Also a new plug-in interface is available for companies to develope their own Data Mining Algorithms and put them in place with the existing ones. The use of these algorithms are also simplified. The Logic is accessed through SQL querys and results are returned as DataSets.

3. SQL Server Reporting Services (SSRS)
There’s a new report builder tool with new support for XML and Web Services. Within a report, now you can link to web sites and external data. Microsofthas also opened up the interface for charting to allow third party plug -ins for clients.
Also it is noteable that Office 12 which is due to be shipped next year also has extensive integration to BI tools.

Suggested/Further Reading:
1. SQL User Group White Papers: www.sswug.org/whitepapers

2. Delivering Business Intelligence with SQL Server 2005. Publisher- Osborne McGraw Hill

3. SQL MSDN www.msdn.microsoft.com/sql

4. Pro SQL Server 2005 Reporting Services : Publisher- APress

5. SQL Team: www.SQLteam.com