Table of Contents
Introduction
1 Installing SQL Server 2005 Business Intelligence Tools
Before You Begin
Lesson 1: Installing SQL Server 2005 Components
Selecting Installation Components
Choosing Installation Details
Practice: Installing SQL Server 2005 Components
Lesson 2: Performing Updates and Advanced Installations
Keeping Current with Service Packs
Clustering Analysis Services and Managing Instances
Installing Multiple Instances of Reporting Services
Practice: Installing a Service Pack
Case Scenario: Installing SQL Server 2005 Components for a BI Project
Chapter Summary
2 Configuring SQL Server 2005 Business Intelligence Components
Before You Begin
Lesson 1: Configuring the SSRS Architecture and Instances
Using the Report Server Configuration Manager Tool for
Server Setup and Management
Using the Reporting Services Command-Line Tools to Manage SSRS
Configuring the Report Server for SSL Communication
and Internet Deployment
Practice: Managing SSRS Encryption Keys
Lesson 2: Setting SSAS Server Security and Server Properties
Setting SSAS Administrative Roles and Permissions
Editing SSAS Server Properties
Practice: Setting Up SSAS Query Logging
Case Scenario: Setting up your SSAS and SSRS Servers
Chapter Summary
3 Migrating to SQL Server 2005 Business Intelligence Components
Before You Begin
Lesson 1: Upgrading SSAS
Architecture Changes
Migration Overview
Using the Analysis Services Migration Wizard
Post-Migration Considerations
Practice: Using the Analysis Services Migration Wizard
Lesson 2: Migrating to SSIS Packages
DTS and SSIS Differences
Executing and Managing DTS in SQL Server 2005
Running the Package Migration Wizard
Post-Migration Steps
Taking Advantage of SSIS Features
Practice: Migrating a DTS Package to SSIS
Case Scenario: Migrating to SQL Server 2005 Business
Intelligence Components
Chapter Summary
4 Developing SSIS Packages
Before You Begin
Lesson 1: Creating SSIS Packages
Using the Import and Export Wizard
Creating an SSIS Project in BIDS
Practice: Creating a New Project
Lesson 2: Defining Project Data Sources and Package Connections
Creating a Data Source
Creating Package Connections
Adding Connections in the Connection Managers Pane
Practice: Creating Connections for a Package
Lesson 3: Creating and Editing Control Flow Objects
Control Flow Tasks
Using Control Flow Containers
Testing Package Execution in BIDS
Practice: Creating and Editing a Control Flow Task
Lesson 4: Using Data Flow Adapters and Transformations
Defining Data Flow Source Adapters
Creating Data Flow Destinations
Data Flow Transformations
Practice: Creating a Simple Data Flow
Case Scenario: Creating an ETL Solution
Chapter Summary
5 Debugging and Error Handling with SSIS Packages
Before You Begin
Lesson 1: Configuring Package Transactions and Checkpoints
Defining Package and Task Transaction Settings
Implementing Restartability Checkpoints
Practice: Implementing Package and Task Transactions
Lesson 2: Identifying Package Status and Handling Task Errors
Viewing Package Status
Connecting Control Flow Objects with Precedence
Practice: Creating and Configuring Precedence Constraints
Lesson 3: Handling Data Flow Errors and Debugging
Using Error Paths to Handle Data Flow Errors
Leveraging Data Viewers to Identify Data Flow Issues
Handling Package Errors with Event Handlers
Debugging the Control Flow with Breakpoints
Practice: Identifying Data Flow Errors
Case Scenario: Troubleshooting and Handling Errors in SSIS Packages
Chapter Summary
6 Developing SSAS Cubes
Before You Begin
Lesson 1: Creating a Data Source and Data Source View
Defining a New Data Source
Selecting Objects for a Data Source View
Creating DSV Keys and Table Relationships
Defining DSV Named Calculations and Named Queries
Practice: Creating an SSAS Project, Data Source, and DSV
Lesson 2: Creating and Modifying SSAS Cubes
Using the Cube Wizard
Modifying a Cube with the Cube Designer
Practice: Creating and Modifying a Cube
Lesson 3: Creating and Modifying Dimensions
Creating a New Dimension
Modifying Dimension Attribute Properties
Assigning Dimensions to Cubes
Practice: Working with SSAS Dimensions
Lesson 4: Defining User Hierarchies and Dimension Relationships
Creating and Modifying User Dimension Hierarchies
Associating Dimensions to Measure Groups
Selecting Relationship Types
Practice: Creating and Modifying Dimension Hierarchies
Case Scenario: Building an SSAS Solution as a Prototype
Chapter Summary
7 Extending SSAS Cubes
Before You Begin
Lesson 1: Creating Measure Groups and Measures
Creating a New Measure Group
Adding and Configuring Measures
Practice: Adding Measure Groups and Measures
Lesson 2: Creating Key Performance Indicators
Understanding KPI Goal, Value, Status, and Trend Properties
Additional KPI Properties
Viewing KPIs
Practice: Creating Key Performance Indicators
Lesson 3: Defining Actions, Translations, and Perspectives
Implementing Actions
Localizing Cubes with Translations
Implementing Cube Perspectives
Practice: Implementing Actions, Translations, and Perspectives
Lesson 4: Creating Calculations and Queries with MDX
Understanding MDX Syntax
Applying MDX Functions
Creating Calculated Members
Defining Named Sets
Practice: Extending Cubes with MDX Expressions
Case Scenario: Extending SSAS Cubes
Chapter Summary
8 Defining SSAS Storage, Partitions, and Aggregations
Before You Begin
Lesson 1: Defining Measure Group Partitions and Storage
Understanding Partitions
Creating Measure Group Partitions
Selecting Partition Storage Modes
Understanding Proactive Caching
Practice: Defining Measure Group Partitions and Storage
Lesson 2: Designing Partition Aggregations
Understanding Aggregations
Defining Aggregations with the Aggregation Design Wizard
Defining Aggregations with the Usage-Based Optimization Wizard
Practice: Designing Partition Aggregations
Case Scenario: Implementing Low-Latency OLAP
Chapter Summary
9 Developing SSAS Data Mining Models
Before You Begin
Lesson 1: Preparing for Data Mining
Data Mining Project Lifecycle
Preparing Data for Data Mining
Practice: Preparing Data for Data Mining
Lesson 2: Creating a Data Mining Structure and Data Mining Models
The Data Mining Algorithms
Practice: Creating Predictive Models
Lesson 3: Creating Association, Sequence, and Forecasting Models
Mapping Mining Structure Attributes to Source Columns
Case Definitions and Nested Tables
Practice: Creating Association, Sequence, and Forecasting Models
Lesson 4: Using Cube Sources and Configuring Data Mining Algorithms
Using Cube Sources
Configuring Algorithm Parameters
Practice: Creating a Model from a Cube and Refining
Model Parameters
Case Scenario: Developing SSAS Data Mining Models
Chapter Summary
10 Developing SSRS Reports
Before You Begin
Lesson 1: Creating an SSRS Project and Report in BIDS
Using the Report Project Wizard
Modifying Project Properties
Modifying Report-Level Properties
Practice: Creating and Modifying a Report
Lesson 2: Developing Report Objects with the Report Designer
Modifying Report Item Properties
Adding Report Objects to a Report
Practice: Modifying Report Items
Lesson 3: Working with Advanced Report Object Properties
Toggling Object Visibility
Defining Report Actions
Practice: Modify Advanced Report Object Properties
Lesson 4: Creating a Dataset from a Data Source
Creating a New Report Dataset
Working with an SSAS-Based Dataset
Practice: Creating Report Datasets
Lesson 5: Applying Dataset Filters and Groups
Assigning Datasets to Data Regions
Applying Filters, Groups, and Sorts to Data Regions
Applying Aggregates to Data Regions
Practice: Creating a Table Data Region with Running Totals
Case Scenario: Building Reports for the AdventureWorks Intranet
Chapter Summary
11 Formatting and Extending SSRS Reports
Before You Begin
Lesson 1: Assigning Parameters Within Reports
Creating Parameters in Report Datasets
Exposing Parameters to Users
Binding Datasets to Parameters
Working with Parameter Defaults
Working with Parameters in URLs
Practice: Creating and Applying Parameters to Reports
Lesson 2: Extending Reports with Expressions
Extending Report Properties with Expressions
Leveraging the Element in a Report
Invoking Embedded Functions with Expressions
Practice: Setting Properties and Making Them Dynamic
Lesson 3: Creating Report Builder Models
Creating the Project and Data Source and a DSV
for a Report Model
Using the Report Model Wizard for Relational Databases
Working with Report Model Projects
Creating a Report Model on an SSAS Cube
Practice: Creating a Report Builder Model
Case Scenario: Creating a Reporting Services Infrastructure
Chapter Summary
12 Deploying and Configuring SSIS Packages
Before You Begin
Lesson 1: Enabling and Creating Package Configurations
Understanding Package Configurations
Enabling SSIS Package Configurations
Creating a Configuration
Leveraging Two-Pass Package Configurations
Practice: Using Configurations to Make Package Properties Dynamic
Lesson 2: Deploying SSIS Packages
Understanding Package Deployment
Creating an Installer Kit by Using the Package Deployment Utility
Deploying Packages
Using the SSIS Command-Line Management Utility, DTUtil
Practice: Deploying SSIS Packages
Case Scenario: Deploying SSIS Packages
Chapter Summary
13 Deploying and Processing SSAS Objects
Before You Begin
Lesson 1: Deploying SSAS Objects
Deploying SSAS Projects with BIDS
Using the Deployment Wizard
Running XMLA Scripts for Deployment
Using the Synchronize Database Wizard
Practice: Deploying SSAS Objects
Lesson 2: Processing SSAS Objects
Understanding SSAS Processing Options
Processing SSAS Objects in BIDS
Processing SSAS Objects in SSMS
Setting Advanced Processing Options with Proactive Caching
Practice: Processing SSAS Objects
Lesson 3: Processing Data Mining Objects
Understanding Data Mining Processing
Processing Mining Structures and Models
Practice: Processing Data Mining Objects
Case Scenario: Deploying and Processing SSAS Objects
Chapter Summary
14 Deploying and Scheduling SSRS Reports
Before You Begin
Lesson 1: Deploying New Reports and Changes
Report Deployment Properties in BIDS
Deploying and Redeploying Reports in BIDS
Uploading a Report File in SSMS and Report Manager
Deploying Report Builder Models and Reports
Practice: Deploying Reports
Lesson 2: Creating Report Schedules and Subscriptions
Creating Shared Schedules
Defining a Report-Specific Schedule
Applying a Subscription to a Report
Practice: Creating Report Schedules and Subscriptions
Lesson 3: Defining Data-Driven Subscriptions and Rendering Formats
Creating a Data-Driven Subscription
Specifying the Subscription Delivery Format and Location
Practice: Creating Data-Driven Subscriptions
Case Scenario: Deploying and Scheduling SSRS Reports
Chapter Summary
15 Securing and Executing SSIS Packages
Before You Begin
Lesson 1: Setting Package Roles, Protection Levels, and the Password
Understanding and Setting the Package Protection Level
Assigning a Package Password
Assigning Roles and Securing Packages Stored in msdb
Managing Package Security with DTUtil
Practice: Encrypting a Package and Assigning Package Roles
Lesson 2: Creating Execution Command Lines
Configuring Package Execution by Using DTExecUI
Using DTExec for Package Execution
Practice: Executing Packages with DTExecUI and DTExec
Lesson 3: Managing, Scheduling, and Executing Packages in SSMS
Executing Packages in SSMS with the SSIS Service
Creating SQL Server Agent Jobs to Execute SSIS Packages
Practice: Scheduling a Package in SQL Server Agent
Case Scenario: Securing and Scheduling SSIS Packages
Chapter Summary
16 Administering and Securing SSAS Cubes
Before You Begin
Lesson 1: Implementing User Security on SSAS Cubes
Understanding User Security
Creating Roles and Applying User Security to Cubes
Defining Advanced SSAS Cell Security
Setting Drillthrough Security
Testing Database Role Security
Practice: Implementing User Security on SSAS Cubes
Lesson 2: Creating and Scheduling SSAS Backups with SSMS
Backing Up an SSAS Database in SSMS
Scheduling SSAS Backups in SQL Server Agent
Scripting SSAS Objects in SSMS
Practice: Creating and Scheduling SSAS Backups with SSMS
Lesson 3: Tracing and Monitoring SSAS Instances
Working with SQL Server Profiler for SSAS Tracing
Using Performance Monitor to Understand SSAS Performance
Understanding SSAS Performance Counters
Configuring Performance Monitor
Practice: Tracing and Monitoring SSAS Instances
Case Scenario: Administering and Securing SSAS Cubes
Chapter Summary
17 Securing and Querying SSAS Data Mining Models
Before You Begin
Lesson 1: Testing the Accuracy of Models
Accuracy of Predictive Models
Accuracy of Other Models
Practice: Testing Model Accuracy
Lesson 2: Creating Data Mining Queries and Reports
Prediction Queries in BIDS and SSMS
The DMX Language
Prediction Queries in Reports
Practice: Creating a Report Based on DMX Query and DMX Language
Lesson 3: Securing Data Mining Models
SSAS Properties
SSAS Roles, Permissions, and Data Sources
Practice: Securing Data Mining Models
Case Scenario: Securing and Querying SSAS Data Mining Models
Chapter Summary
18 Managing and Securing SSRS Reports
Before You Begin
Lesson 1: Creating SSRS Roles and Item Access in Report Manager
Understanding SSRS Item-Level Roles
Creating a New SSRS Role in Report Manager
Understanding Object Permissions and Assignment to Roles
Assigning Object Permissions to Roles
Practice: Creating New Roles in Report Manager
Lesson 2: Assigning Item Permissions and Site Security Access
Understanding SSRS System-Level Roles
Creating a New SSRS System-Level Role in Report Manager
Understanding Server Permissions and Assignment to
System-Level Roles
Assigning Server Permissions to Roles
Understanding the Way My Reports Functions within
Reporting Services
Understanding Linked Reports within SSRS
Understanding the Correlation between Linked Reports and
My Reports Functionality
Practice: Applying System-Level Security to Roles
Lesson 3: Managing Report Execution Properties and Linked Reports
Behavior of Report Execution
Understanding Report Cache and Snapshots
Report Time-Out
Creating New Linked Reports with Content Manager
Practice: Using Report Manager to Modify Report Properties
Lesson 4: Managing Data Source Credentials
Understanding Shared Data Sources
Using Expressions to Create Dynamic Data Source Credentials
Practice: Managing Data Sources in SSRS
Case Scenario: Managing the Report Environment for Adventure Works
Chapter Summary
Case Scenario Answers
Index
© Microsoft. All Rights Reserved.