The MicrosoftВ Data Warehouse Toolkit: With SQL ServerВ 2005 and the MicrosoftВ Business Intelligence Toolset

The Microsoft DW/BI Toolset

The core set of DW/BI tools that Microsoft Corporation sells is Microsoft SQL Server 2005. SQL Server includes several major components of primary interest for DW/BI projects:

• The relational engine (RDBMS) to manage and store the dimensional data warehouse database.

• Integration Services to build the extract, transformation, and load (ETL) system.

• An OLAP database in Analysis Services to support users queries, particularly ad hoc use.

• Analysis Services data mining to develop statistical data mining models, and also to include those models in advanced analytic applications.

• Reporting Services to build predefined reports . Most of the Reporting Services features are most appropriate for the DW/BI team, but you may provide some ad hoc query and report building functionality with Report Builder .

• Development and management tools, especially SQL Server BI Development Studio and SQL Server Management Studio, to build and manage your DW/BI system.

The SQL Server product contains the software necessary to build, deploy, populate, manage, and access your DW/BI system. A second significant set of Microsoft tools are designed for the business user . These include Microsoft Office, notably Excel, Office Web Components, Data Analyzer, and SharePoint Services.

Office and SharePoint provide tools that you can use to build end-user applications to access the data warehouse databases. Many DW/BI systems supplement Microsoft end-user tools with third-party software.

An increasingly important set of software developed by Microsoft is packaged analytic applications. Examples of these packaged analytic applications are:

• The analytic functionality that Microsoft is increasingly adding to transaction systems like Commerce Server and Microsoft Business Systems Great Plains

• Standalone analytic applications, which will be released after this book goes to print

Microsoft Visual Studio is a fundamental tool for the DW/BI development team. The SQL Server DW/BI development tools are hosted in Visual Studio. The necessary Visual Studio components are installed for you, and you may not even realize that you are using the standard Microsoft development environment.

You can use Visual Studio to build a custom application, such as an analytic application that connects your DW/BI system back to transaction systems. The heavy lifting of such an application may occur within your Analysis Services data mining model, but youd still need to develop a bit of plumbing to connect the two systems.