This five-day instructor-led course provides in-depth knowledge on designing a Business Intelligence solution by using Microsoft SQL Server 2008.
Target Audience:
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 specialise in the area of BI.
Pre-requisites:
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 customisation 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 Microsoft SQL Server BI Development Studio, Microsoft SQL Server Management Studio, Report Builder and Report Manager, and Source Code Control (SCC) product
Possible experience in using Microsoft Office Visio and System Monitor
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.
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 organisational 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 Optimising 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 optimising 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 optimising 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 Optimising 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 for monitoring an SSAS solution.
Secure an SSAS solution.
Design a strategy for optimising 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
Optimising 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 Optimising 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.
Optimise 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
Analysing 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:
Analyse 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 Optimising 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 optimising 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
Visualising 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:
