50399 Designing a Business Intelligence Solution by Using Microsoft SQL Server 2008 70-452

50399: Designing a Business Intelligence Solution by Using Microsoft SQL Server 2008 70-452 (5 Days)

About this Course: This five-day instructor-led course provides in-depth knowledge on designing a Business Intelligence solution by using Microsoft SQL Server 2008.

Audience Profile The primary audience for this course is developers who have knowledge of Business Intelligence (BI) concepts, with an experience of 4-5 years in BI. The audience has an understanding of how to use SQL Server 2005 BI tools to implement a BI infrastructure and solution. However, the audience needs to develop an understanding of design principles and best practices when designing a BI solution. The target audience also includes BI developers with experience in using SQL Server 2008 BI features and tools, and who want to obtain a professional certification in BI development and design.The secondary audience for this course is information technology (IT) professionals, and Business Analysts who specialize in the area of BI.

At Course Completion After completing this course, students will be able to:

• Design a Business Intelligence (BI) architecture in SQL Server 2008.
• Design a strategy for implementing the extract, transform, load (ETL) processby using SQL Server 2008 Integration Services (SSIS).
• Design a strategy for managing packages by using SSIS.
• Design an online analytical processing (OLAP) solution architecture by using SQL Server 2008 Analysis Services (SSAS).
• Design a physical storage for an OLAP solution by using SSAS.
• Design an administration and maintenance strategy for an SSAS solution.
• Design effective queries for an OLAP solution by using SSAS.
• Design and develop a SQL Server 2008 Reporting Services (SSRS) solution architecture.
• Design a strategy for deploying and managing an SSRS solution architecture.
• Design data mining solutions by using SQL Server 2008.

Before attending this course, students must have:

• Knowledge of data warehousing, data marts, and industry-accepted Business Intelligence (BI) methodologies
• Conceptual understanding of SQL Server 2005 and SQL Server 2008 components, such as online analytical processing (OLAP), extract, transform, load (ETL), and reporting tools and technologies
• Experience in developing and implementing cubes at the physical level
• Experience in working with data transformations
• Experience in creating star and snowflake schemas at both conceptual and logical levels
• Experience in writing MDX for customization and querying
• Fundamental understanding of Microsoft Windows security, such as how groups, delegation of credentials, and impersonation function in a security context
• Fundamental understanding of Web-based architecture
• Experience in using the following tools:
• Microsoft SQL Server BI Development Studio
• Microsoft SQL Server Management Studio
• Report Builder and Report Manager
• Source Code Control (SCC) product

Possible experience in using the following tools:

• Microsoft Office Visio
• System Monitor

Course Outline:

Module 1: Designing a Business Intelligence Architecture in Microsoft SQL Server 2008

This module explains basic BI concepts to the student and provides the framework to start planning and designing for a BI infrastructure. This includes getting to know the various BI methodologies, Microsoft’s BI vision, common terminologies used in a BI environment, and gathering system and organizational requirements. The module also introduces the various Microsoft BI tools available, which can be used both in the front-end and back-end of a BI solution.

Lessons

• Overview of BI Concepts
• Planning BI Solutions
• Planning the Development of a BI Solution
• Determining BI Requirements
• Determining Architectural Requirements of a BI Solution
• Planning the Microsoft BI Environment
• Overview of BI Operations Management
• Lab : Designing a Business Intelligence Architecture in Microsoft SQL Server 2008 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.

Module 2: Designing a Strategy for Implementing ETL by Using Microsoft SQL Server 2008 Integration Services

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.

Lessons

• 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
• Lab : Designing a Strategy for Implementing ETL by Using Microsoft SQL Server 2008 Integration Services 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.

Module 3: Designing a Strategy for Managing Packages by Using Microsoft SQL Server 2008 Integration Services

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.

Lessons

• 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

Lab :

• Designing a Strategy for Managing Packages by Using Microsoft SQL Server 2008 Integration Services 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.

Module 4: Designing an OLAP Solution Architecture by Using Microsoft SQL Server 2008 Analysis Services

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.

Lessons

• 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

Lab :

• Designing an OLAP Solution Architecture by Using Microsoft SQL Server 2008 Analysis Services 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.

Module 5: Designing Physical Storage for an OLAP Solution by Using Microsoft SQL Server 2008 Analysis Services

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.

Lessons

• 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

Lab :

• Designing Physical Storage for an OLAP Solution by Using Microsoft SQL Server 2008 Analysis Services 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.

Module 6: Designing an Administration and Maintenance Strategy for a Microsoft SQL Server 2008 Analysis Services Solution

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.

Lessons

• 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

Lab :

• Designing an Administration and Maintenance Strategy for a Microsoft SQL Server 2008 Analysis Services Solution 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.

Module 7: Designing Effective Queries for an OLAP Solution by Using Microsoft SQL Server 2008 Analysis Services

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.

Lessons

• Designing Business Driven Calculations by Using MDX
• Working with MDX Queries
• Exploring MDX Query Context and Execution
• Optimizing MDX Queries
• Extending Multidimensional Expressions

Lab :

• Designing Effective Queries for an OLAP Solution by Using Microsoft SQL Server 2008 Analysis Services 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.

Module 8: Designing and Developing a Microsoft SQL Server 2008 Reporting Services Solution Architecture

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

Lessons:

• 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

Lab :

• Designing and Developing a Microsoft SQL Server 2008 Reporting Services Solution Architecture 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.

Module 9: Designing a Strategy for Deploying and Managing a Microsoft SQL Server 2008 Reporting Services Solution Architecture

This module covers deployment and management strategies for SSRS.

Lessons

• 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

Lab :

• Designing a Strategy for Deploying and Managing a Microsoft SQL Server 2008 Reporting Services Solution Architecture 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.

Module 10: Designing Data Mining Solutions by Using Microsoft SQL Server 2008

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.

Lessons:

• Fundamentals of Data Mining
• Designing a Mining Model and Structure
• Designing Strategies for Staging Data
• Visualizing Data Mining Results
• Testing Mining Models

Lab :

• Designing Data Mining Solutions by Using Microsoft SQL Server 2008 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.