Microsoft SQL 2005 Business Intelligence Bootcamp Training

With the release of SQL Server 2005, Microsoft has completely re-architected their Business Intelligence (BI) platform. Veteran BI professionals who want to upgrade their skills and newcomers alike will learn how to apply key BI concepts using the new 2005 Integration Services (formerly DTS), Analysis Services, and Reporting Services.

Through instructor-led sessions and hands-on exercises, students will develop an understanding of what has changed and what is new in the 2005 BI SQL Server architecture. Students will learn how to develop applications using the new Business Intelligence Development Studio environment, how to use the new Integration Services tasks and transformations, how to build dimensions and cubes for analytical applications, how to take advantage of the new features in Reporting Services, how to use MDX in queries and formulas, and how to work with the new Data Mining models and tools.

Students will be able to immediately apply this knowledge to their real-world databases and BI applications to begin preparing for the transition to SQL Server 2005.

Audience
This course is intended for business analysts, database developers, database administrators, and database architects who are responsible for the design, implementation, and maintenance of Business Intelligence applications.

Students should meet the following prerequisites:

• Have an understanding of Business Intelligence principles and concepts.
• Be familiar with basic relational database design, administration, and implementation concepts

At Course Completion

At the end of this course, the student will be able to:

· Develop BI applications using the new integrated development environment.
· Understand the components of the SQL Sever 2005 BI architecture.

Integration Services
· Develop packages using the new Control Flow and Data Flow tasks and transformations.
· Understand how to develop complex precedence constraints.
· Use configuration files to change package components and settings.
· Learn how to migrate DTS 2000 packages.

Analysis Services
· Build dimensions, measure groups, cubes, and perspectives.
· Design cube storage and aggregations and implement usage-based optimization.
· Create partitions in measure groups.
· Understand how to save, build, deploy, and process an Analysis Services project.

Reporting Services
· Use Report Designer to add new interactive features to standard reports.
· Use Report Builder to provide users with an ad hoc reporting environment.
· Explore the new features in Report Manager for user access.
· Manage report organization, security, and migration from Reporting Services 2000.

MDX
· Describe the key differences between SQL and MDX.
· Understand how to reference tuples and sets.
· Write MDX expressions and queries.

Data Mining
· Describe the different Data Mining models and how to build them.
· Identify opportunities to apply Data Mining functionality.


Prerequisites

Before attending this course, students must have competency in the following areas:

· Familiarity and comfort with basic operating system functions such as file manipulation.
· Understanding of the basics of structured programming, including concepts such as flow control, variables and parameters, and function calls.
· MSDN Training Course 1587-Introduction to Programming with Microsoft Visual Basic 6, may help students gain basic skills in programming techniques.
· At least 3 months experience developing applications in either a graphical or non-graphical environment, or equivalent knowledge.
· Experience with object-oriented programming and concepts is not required.





Course Outline

Day 1: SQL Server 2005 Business Intelligence

Business Intelligence Fundamentals
o Key BI System Considerations
o Relational, OLAP, & Data Mining Compared
o Reporting Alternatives
Working With Integration Services
o Using The SSIS Import And Export Wizard
o Understanding Control Flow And Data Flow
Creating Your First Cube
o Adding A Data Source
o Creating A Data Source View
o Building A Dimension
Building A Report
o Using OLAP As A Data Source
o Using The Report Wizard


Day 2: Integration Services (DTS)

Building Your First Package

• Starting A Project
• Adding A Data Flow Task
• Adding Connection Managers

Adding Transformations

• Using The Conditional Split Task
• Incorporating A Derived Column Task
• Branching Data Flow With The Multicast Task
• Finding Matches With The Fuzzy Lookup Task

Using Advanced Features

• Branching With A Script Task
• Organizing Tasks With Containers
• Working With Variables
• Looping Through Multiple Files
• Debugging With A Data Viewer

Deploying Your Package

• Modifying Settings With A Configuration File
• Deploying And Executing Your Package

Migrating A DTS 2000 Package

• Using The Migration Wizard
• Enhancing The Migrated Package

Day 3: Analysis Services Scalability

Understanding The Benefits Of OLAP
Working With Dimensions
o Building User Hierarchies
o Changing Dimension Properties
o Creating Role-Playing Dimensions
o Creating A Parent-Child Dimension
Working With Measure Groups
o Creating Multiple Measure Groups
o Adding Calculated Members
o Displaying Trends And Results With KPIs
Deploying A Project
Building Partitions
o Improving Scalability Through Partitioning
o Using A Query To Define A Partition Slice
Using The Aggregation Wizard
o Defining Aggregations
o Understanding The Aggregation Wizard


Day 4: Reporting Services

Report Authoring Basics
o Starting A New Report Project
o Working With Datasets
o Working With Data Regions
o Working With Expressions
o Adding Report Parameters
o Adding Interactive Features
Report Management Options
o Organizing Content
o Applying Security
o Migrating From Reporting Services 2000
Report Delivery Options
o Accessing Reports Online
o Reviewing SQL Server 2005 Features
Report Builder
o Building A Report Model
Creating Ad Hoc Reports


Day 5:

MDX

Working With MDX
o Comparing SQL To MDX
o Using MDX In Queries
Applying MDX To Formulas

Data Mining

Learning About Data Mining
o Interpreting Data Mining Models And Tools
Building A Data Mining Model