Business Intelligence

The business intelligence tools available in the SQL Server 2005 product suite are the most comprehensive yet in a SQL Server release and these will be further enhanced in the 2008 version of the product which is due to be released in February 2008. There are four main components that make up the SQL Server 2005 Business Intelligence (BI) suite:

We will look at each of these in more detail in future articles but we will provide a very brief overview here to allow you to get a feel for each component and how it can help you in your BI needs.


Replication is a mature product that is not new to the SQL Server 2005 release of the database engine. Replication has two significant uses uses:
  • Replicate data between multiple locations
  • Create reporting servers thus relieving the OLTP production system of significant load.
In some organisation replication is used for disaster recovery personally I don�t think that replication is the best solution for disaster recovery because replication cannot guarantee that the subscriber (the database that is being replicated too) has not changed when compared to the main primary database.

The fundamental principle behind replication is to allow you to �copy� data from one system to another. You can also specify what data is copied from database to another. For example you could replicate all tables in a database or you can restrict the replication to just one copy. We will discuss replication in much greater detail in a different article but I hope this short summary has provided you with enough information to understand what replication does?

Integration Services

Integration Services is brand new component in SQL Server 2005 it is Microsoft�s Extract Transform and Load (ETL) tool. SQL Server Integration Services (SSIS) replaces Date Transformation Services (DTS) available in version 7 and 2000 of SQL Server The activities you undertake using Integration Services could be as simple as moving data between data sources, for example table by table or as complex as consolidating data from multiple data stores in different formats, applying rules to cleanse the data and make it consistent and then load the results in to a data warehouse.

In following articles we will look at SSIS in more detail including how best to migrate your old DTS packages to the SSIS equivalent in SQL Server 2005.

Analysis Services

SQL Server Analysis services contain two very different but useful capabilities:
  • Online Analytical Processing (OLAP)
  • Data Mining
OLAP engines allow for ad hoc queries by end users, uses can drill, slice, dice and pivot data.

Data mining is generally used behind the scenes by �technical specialists� and experts in a related subject to identify patterns in corporate data. The patterns identified can be used to support corporate decisions for example identify which customers are likely to stop using products or services.

Reporting Services

The Reporting Services component of SQL Server 2005 uses the .net framework to support both a web service and API to provide reporting solutions for any environment. Reporting Services fully supports the full reporting cycle:

1. Report Authoring
2. Report Management
3. Report Delivery

Reporting services can be run out the box or can be completely customised to suite your needs.

I hope this article has provided you with an insight into some of the Business Intelligence tools available in the latest version of SQL Server. Further articles will discuss these components in more detail.

If can assist in configuring your Business Intelligence environment then please contact us.

footer for Business Intelligence page