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


Designing dimensional models for business intelligence is no simple trick. The first part of this chapter concentrated on defining and describing the basic concepts of dimensional modeling: facts, dimensions, the bus matrix, and conformed dimensions. The next section expanded the description of dimensional modeling with key concepts such as surrogate keys and tracking changes with Slowly Changing Dimensions. We described several techniques to model a broad range of common (and uncommon) business processes and relationships like many-to-many relationships, hierarchies, heterogeneous products, and junk dimensions.

The third part of this chapter covered the process of dimensional modeling. Begin with a preparation step to identify the team, set up the modeling environment, and determine naming conventions. Begin the modeling process by using our four-step approach to create a high-level business dimensional model, along with attributes and issues lists. The next step is to develop the detailed model, table by table and column by column, filling in all the needed information and addressing all the issues. The last step in the process of creating the dimensional model involved reviewing the proposed model with several interested parties, including other IT people and core business users.

The last part of the chapter applied the dimensional modeling concepts and process to the Adventure Works Cycles case study, resulting in a dimensional model for the Orders business process. This dimensional model will be the target for the physical database creation and the ETL system described over the next several chapters.

Chapter 3: The Toolset

When you have a hammer , everything looks like a nail.


In this chapter, we describe the architecture and product selection for the Microsoft data warehouse/business intelligence (DW/BI) system. It may seem pointless to talk about architecture alternatives and product selection for a Microsoft system, but Microsoft offers enough software components that there actually is an element of product selection.

Figure 3.1 repeats the familiar Business Dimensional Lifecycle diagram, highlighting the Architecture and Product Selection boxes that are the focus of this chapter. In this version of the diagram, weve included a mapping between the Lifecycle boxes and the Microsoft products and components you may use during your development and management processes.

image from book
Figure 3.1: Business Dimensional Lifecycle and Microsoft technologies

This chapter continues with a description of the two main tools that youll use to develop and operate your DW/BI system. Youll use a single integrated environment called the Business Intelligence Development Studio to develop most of your DW/BI system, and a second environment called the SQL Server Management Studio to manage it. We introduce the two tools here and provide an overview of the elements that are the same no matter what part of the DW/BI project youre working on.

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.