Business Intelligence with SQL Server 2005 (M2791-2792-2793)

Students have to know how to use:

• Microsoft SQL Server Business Intelligence Development Studio
• Microsoft SQL Server Management Studio
• Performance Monitor
• Microsoft SQL Server Profile

Analysis Services

Here students will be taught to design, create and manage cubes, dimensions, KPI, data mining models. Students will also learn how to use MDX, how to extend the functionality of a cube with MDXscripts and actions. We will also teach how to partition cubes, how to process cubes and dimensions and how to create an aggreagation design for partitions. Performance optimization and maintenance of a multidimensional database is also part of the curriculum.

SQL Server Integration Services

Here students will be taught to design an ETL (Extract Transform and Load) solution that supports their BI Solution. Students will also learn how to create a dataflow task and work with sources, transformations and destinations. In addition the use of scripting to extend the functionality of SSIS will be taught. Finally optimizing a SSIS solution and deploying it is also taught.

SQL Server Reporting Services

Students will learn how to design a reporting services solution that supports their BI solution. Students will go through the entire process of authoring their reports, deploying and managing of their reports and finally rendering of the report. Students will be taught how to optimize the execution of reports and how to secure reports. Finally students will also learn how to use reports models to allow end users to do ad hoc reporting.

Module 1: Planning an SSIS Solution.
In this module, students will learn how to plan an SSIS-based ETL solution. Specifically, they will learn how to plan SSIS packages, how to plan an effective package development process, and how to design the control flow within the packages.
• Planning Packages
• Planning Package Development
• Designing Package Control Flow

Module 2: Designing Data Flow.
In this module, students will learn how to design data flows that extract, transform, and load data. They will also learn about factors to consider when working with slowly changing dimensions (SCDs) and when implementing custom SCD transformations.
• Understanding Data Flow
• Designing Data Flow Operations
• Handling Data Changes

Module 3: Logging, Error Handling, and Reliability.
In this module, students will learn how to incorporate logging, error handling, and reliability into your SSIS package designs. The module covers standard logging, custom logging, and log reporting. The module also teaches students how to implement error handling and how to handle bad data. Finally, the module shows students how to implement a transaction strategy, use checkpoints, and handle restarts and rollbacks.
• Logging ETL Operations
• Handling Errors in SSIS
• Implementing Reliable ETL Processes with SSIS

Module 4: Optimizing an SSIS Solution.
In this module students will learn how to monitor and optimize SSIS performance. They will also learn how to scale-out SSIS packages.
• Monitoring SSIS Performance
• Optimizing SSIS Packages
• Scaling Out SSIS Packages

Module 5: Deploying and Operating an SSIS Solution.
This module describes considerations for deploying an SSIS solution into a production environment. It also discusses some of the issues related to operations and management that you must consider when planning an SSIS solution.
• Deploying SSIS Packages
• Operating an SSIS Solution

Module 6: Designing and Implementing a Logical OLAP Solution Architecture.
This module describes considerations and guidelines for designing an OLAP solution, including a relational data warehouse and an Analysis Services cube.
• Planning an OLAP Solution
• Designing and Implementing Fact and Dimension Tables
• Designing and Implementing Cubes

Module 7: Designing Physical Storage for a Multidimensional Solution.
In this module, students will learn how to design an effective physical storage solution for a multidimensional application.
• Designing Physical Storage
• Partitioning Relational Data
• Partitioning Multidimensional Data
Module 8: Creating Calculations.
In this module, students will learn how to create Multidimensional Expression (MDX) calculations. The module describes how to create calculated members, named sets, and scoped assignments.
• Implementing Calculated Members
• Implementing Named Sets
• Implementing Scoped MDX Scripts

Module 9: Extending Cube Functionality.
In this module, students will learn about the benefits of KPIs, actions, and stored procedures. They will also learn how to implement KPIs, actions, and stored procedures in an Analysis Services cube.
• Key Performance Indicators
• Actions
• Stored Procedures
Module 10: Designing an Analysis Services Infrastructure.
In this module, students will learn how to design an appropriate infrastructure for an OLAP application.
• Considerations for Analysis Services Resource Requirements
• Considerations for Analysis Services Scalability
• Considerations for Analysis Services Availability

Module 11: Deploying a Multidimensional Solution into Production.
In this module, students will learn about and compare the different deployment methods available in SQL Server 2005 Analysis Services. They will also learn about how security in Analysis Services functions and how to protect their company's critical business information.
• Deploying an Analysis Services Database
• Managing Analysis Services Security

Module 12: Optimizing an OLAP Solution.
In this module, students will learn how to monitor Analysis Services and how to optimize performance of their Analysis Services solutions.
• Monitoring Analysis Services
• Optimizing Performance

Module 13: Implementing Data Mining.
In this module, students will learn what a data mining solution is and how to design and implement data mining functionality with SQL Server Analysis Services.
• Introduction to Data Mining
• Implementing a Data Mining Solution
• Using Data Mining in a BI Solution

Module 14: Developing a Reporting Solution.
In this module, students will first identify how best to develop reports using the powerful Business Intelligence Development Studio Report Designer tool. This will include a review of best practice for report layout, and how this is affected by the various rendering formats available. Secondly, they will identify the considerations for effective development of a report model to enable more experienced users to create their own reports.
• Developing Reports
• Developing Report Models

Module 15: Integrating Reporting Services with Applications.
In this module, students will review the options for integrating Reporting Services reports into a custom application. They will look at using direct URL access, the report viewer controls provided by Microsoft Visual Studio 2005, the Reporting Services SharePoint Web Parts, and the Reporting Services Web services. Lastly, they will discuss which approach, or combination of approaches, might suit different BI applications and their different kinds of users.
• Integrating with Custom Applications
• Controlling Reporting Services by Using Web Services

Module 16: Deploying and Managing a Reporting Solution.
In this module, students will learn how to deploy a Reporting Services solution into a production environment, and how to manage report distribution.
• Deploying a Reporting Services Solution
• Managing Report Distribution

Module 17: Designing Security for a Reporting Solution.
In this module, students will learn how to secure a Reporting Services solution.
• Designing an Authentication Strategy
• Designing an Authorization Strategy
• Designing a Secure Communication Strategy

Module 18: Optimizing Report Performance.
In this module, students will learn how to optimize a Reporting Services solution.
• Monitoring Report Execution
• Designing a Report Execution Strategy
• Scaling Reporting Services