Designing a Business Intelligence Solution by Using Microsoft SQL Server 2008

Overview of BI Operations Management

Determining Business Requirements for a BI Solution

Designing the Architecture for a BI Solution

Creating a Test Environment for a BI Solution

After completing this module, students will be able to:

Describe BI concepts.

Plan a BI solution.

Plan the development of a BI solution.

Determine BI requirements.

Determine architectural requirements of a BI solution.

Plan the Microsoft BI environment.

Describe the overview of BI operations management.

This module introduces you to designing extract, transform, and load (ETL) packages by using SQL Server 2008 Integration Services (SSIS). This module covers information on how to create an ETL package to extract source data from multiple heterogeneous systems, apply transformations to the ETL package, and load it in data marts or data warehouses.

Planning Data Sources and Destinations

Determining Staging Requirements

Planning SSIS Packages

Planning the Development of SSIS Packages

Designing Data Flow

Planning Data Flow Operations

Extending SSIS Packages with Scripts

Designing Data Flow Logic

Implementing Control and Data Flow

Modifying the Package to Work with Incremental Loads

Determining a Strategy for Scrubbing Data

After completing this module, students will be able to:

Plan data sources and destinations.

Determine staging requirements.

Plan SSIS packages.

Plan the development of SSIS packages.

Design data flow.

Plan data flow operations.

Extend SSIS packages with scripts.

This module describes how to design a management and deployment strategy for packages. Once a data warehouse has been created by using an ETL package, the next step is to build an OLAP database and OLAP cubes to aggregate the data.

Designing a Strategy for Logging ETL Operations

Designing a Strategy for Managing Errors

Designing and Implementing Reliable ETL Processes

Designing a Strategy for Deploying and Maintaining SSIS Packages

Designing a Strategy for Optimizing an SSIS Solution

Designing a Package Management Strategy

Implementing Package Configurations

Implementing an Error-Handling Strategy

Implementing a Logging Strategy

Implementing Reliability by Using Transactions

After completing this module, students will be able to:

Design a strategy for logging ETL operations.

Design a strategy for managing errors.

Design and implementing reliable ETL processes.

Design a strategy for deploying and maintaining SSIS packages.

Design a strategy for optimizing an SSIS solution.

This module explains what an OLAP solution is and helps to gather requirements for the solution. This module also explains dimensions and dimension relationships. In addition, it also includes considerations for implementing cubes, KPIs, and actions, finally helping the student to design an OLAP solution architecture.

Overview of an OLAP Solution

Gathering Requirements for an OLAP Solution

Designing a Logical OLAP Solution Architecture

Designing Dimensions

Designing Dimension Relationships

Considerations for Implementing Cubes, KPIs, and Actions

Global Considerations for an SSAS Solution

Designing an OLAP Solution

Implementing the Data Source View

Implementing Dimensions

Creating a Cube

After completing this module, students will be able to:

Describe an OLAP solution.

Gather requirements for an OLAP solution.

Design a logical OLAP solution architecture.

Design dimensions.

Design dimension relationships.

Describe the considerations for implementing cubes, KPIs, and actions.

Describe the global considerations for an SSAS solution.

This module covers the various aspects of designing and optimizing the physical storage of data in an OLAP solution and provides information on how to partition and design a storage strategy.

Designing and Implementing Physical Storage for Dimensions

Designing and Implementing a Partitioning Strategy for Relational Data

Designing a Partitioning Strategy for Multidimensional Data

Designing Aggregations

Designing a Partitioning Strategy

Implementing Partitions

Implementing Proactive Caching

Improving Query Performance by Creating Aggregations

After completing this module, students will be able to:

Design and implementing physical storage for dimensions.

Design and implementing a partitioning strategy for relational data.

Design a partitioning strategy for multidimensional data.

Design aggregations.

This module covers how to administer and maintain an OLAP solution. The module also covers the considerations for implementing scalability, availability, security, and monitoring for an OLAP solution.

Determining SSAS Resource Requirements

Considerations for Providing SSAS Scalability

Considerations for Providing SSAS Availability

Planning and Implementing a Deployment Strategy for an SSAS Solution

Designing a Strategy for Monitoring an SSAS Solution

Securing an SSAS Solution

Designing a Strategy for Optimizing the Performance of SSAS

Designing a Monitoring Strategy for SSAS

Implementing a Monitoring Strategy for SSAS

Designing a Security Strategy for an SSAS Database

Implementing a Security Strategy for an SSAS Database

After completing this module, students will be able to:

Determine SSAS resource requirements.

Describe the considerations for providing SSAS scalability.

Describe the considerations for providing SSAS availability.

Plan and implement a deployment strategy for an SSAS solution.

Design a strategy formonitoring an SSAS solution.

Secure an SSAS solution.

Design a strategy for optimizing the performance of SSAS.

This module covers how to design effective MDX queries for an OLAP solution. The module also covers identifying and resolving bottlenecks in MDX queries. The end result of most BI applications is to give insights to the end users about what is happening in business through reports.

Designing Business Driven Calculations by Using MDX

Working with MDX Queries

Exploring MDX Query Context and Execution

Optimizing MDX Queries

Extending Multidimensional Expressions

Designing an OLAP Solution

Defining Scoped Assignments Using MDX Scripts

Monitoring and Optimizing queries

After completing this module, students will be able to:

Design business driven calculations by using MDX.

Work with MDX queries.

Describe MDX query context and execution.

Optimize MDX queries.

Extend multidimensional expressions.

This module provides information to design a reporting solution by using SQL Server 2008 Reporting Services (SSRS).

Analyzing the Business Requirements for a Reporting Solution

Planning the Architecture for an SSRS Solution

Designing a Data Acquisition Strategy

Planning a Reporting Solution

Designing Report Models

Extending Reporting with Custom Code

Designing Report Layout

Creating Report Layout

Making Reports Interactive

Extending Reports Using Custom Components

After completing this module, students will be able to:

Analyze the business requirements for a reporting solution.

Plan the architecture for an SSRS solution.

Design a data acquisition strategy.

Plan a reporting solution.

Design report models.

Extend reporting with custom code.

This module covers deployment and management strategies for SSRS.

Planning the Integration of Reports with Applications

Planning Security for a Reporting Solution

Planning Reports Distribution

Designing a Strategy for Managing Report Execution

Designing a Strategy for Optimizing Report Performance

Planning the Administration of SSRS

Designing a Report Management Strategy

Deploying Reports

Monitoring Reports

After completing this module, students will be able to:

Plan the integration of reports with applications.

Plan security for a reporting solution.

Plan reports distribution.

Design a strategy for managing report execution.

Design a strategy for optimizing report performance.

Plan the administration of SSRS.

This module includes creating and testing mining models and using them to run prediction queries. While reporting allows users to see what has already happened, data mining allows end users to predict what is likely to happen in the future.

Fundamentals of Data Mining

Designing a Mining Model and Structure

Designing Strategies for Staging Data

Visualizing Data Mining Results

Testing Mining Models

Designing a Mining Model

Creating a Mining Model

Exploring and Testing Mining Models

Running Prediction Queries

After completing this module, students will be able to:

Describe the fundamentals of data mining.

Design a mining model and structure.

Design strategies for staging data.

Visualize data mining results.

Test mining models.