SQL Server 2008 Business Intelligence Fundamentals

Expand all Collapse all

• Developing SSIS Packages

   This seminar explains how to create SSIS packages and defines the basic objects

  contained in the control flow and data flow. Later seminars describe the advanced features,

  deployment, and implementation details of SSIS.

  Seminar objectives:

  Implement control flow.

  Implement data flow.

  Implement package logic by using variables.

  Extend SSIS packages by using .NET code.

  Identify and resolve issues related to SSIS solution deployment.

  Install and maintain SSIS components.

   

  Step 1: Creating SSIS Packages and Data Sources

  Creating SSIS Packages

  Developing Project Data Sources and Package Connections

  Practice: Creating New Packages, Data Sources, and

  Connections

  Step 2: Creating and Editing Control Flow Objects

  Creating Control Flow Tasks

  Using Control Flow Containers

  Working with Package Variables

  Using the Script Task and Data Profiling Task

  Testing Package Execution in BIDS

  Practice: Creating and Editing a Control Flow Task

  Step 3: Using Data Flow Adapters and Transformations

  Defining Data Flow Source Adapters

  Creating Data Flow Destinations

  Working with Data Flow Transformations

  Practice: Creating Simple and Complex Data Flows

  Case Scenario: Creating an ETL Solution

   

   

  This seminars series based on Microsoft Certification Training Kit for

  Exam 70-448: Microsoft SQL Server 2008 - Business Intelligence Development and

  Maintenance

  MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

• Debugging and Error Handling in SSIS

   

  In this seminar, we will look at the various capabilities in SSIS for debugging packages during

  development and for dealing with errors during production execution, including execution

  logging. You begin by looking at how to configure package transactions and checkpoints

  and then see how to identify package status, handle task errors, and log your package

  execution. Last, you learn about data flow error handling and debugging.

  Seminar objectives:

  Implement auditing, logging, and event handling.

  Install and maintain SSIS components.

  Identify and resolve issues related to SSIS solution deployment.

   

  Step 1: Configuring Package Transactions and Checkpoints

  Defining Package and Task Transaction Settings

  Implementing Restorability Checkpoints

  Practice: Implementing Package and Task Transactions

  Step 2: Identifying Package Status, Enabling Logging, and

  Handling Task Errors

  Viewing Package Status

  Configuring Execution Logging

  Connecting Control Flow Objects with Precedence

  Practice: Creating and Configuring Precedence Constraints

  Step 3: Handling Data Flow Errors and Debugging

  Using Error Paths to Handle Data Flow Errors

  Using 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

   

  This seminars series based on Microsoft Certification Training Kit for

  Exam 70-448: Microsoft SQL Server 2008 - Business Intelligence Development and

  Maintenance

  MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

• Deploying and Configuring SSIS Packages

   

  This seminar discusses how to configure your package to

  eliminate the need to make changes as you move from one environment to the next and then

  covers how to use the Business Intelligence Development Studio (BIDS) to deploy a package.

  Seminar objectives :

  Implement dynamic package behavior by using property expressions.

  Implement package configurations.

  Deploy an SSIS solution.

  Identify and resolve issues related to SSIS solution deployment

   

  Step 1: Using Package Configurations and Expressions

  Understanding Package Configurations

  Using SSIS Expressions and Property Expressions

  Practice: Using Configurations and Expressions to Make

  Package Properties Dynamic

  Step 2: Deploying SSIS Packages

  Understanding Package Deployment

  Creating an Installer Kit by Using the

  Package Deployment Utility

  Deploying Packages

  Using the SSIS DTUtil Command-Line Utility

  Practice: Deploying SSIS Packages

  Case Scenario: Deploying SSIS Packages

    

  This seminars series based on Microsoft Certification Training Kit for

  Exam 70-448: Microsoft SQL Server 2008 - Business Intelligence Development and

  Maintenance

  MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

• Administering, Securing, and Executing SSIS Packages

  This seminar covers package security and execution as well as several  management and administration

  aspects.

  Seminar objectives:

  Install and maintain SSIS components.

  Manage SSIS package execution.

  Configure SSIS security settings.

   

  Step 1: Managing the SSIS Service and Configuring

  Package Security

  Managing the SSIS Service

  Configuring the SSIS Service in a Windows Cluster

  Environment

  Adding SSIS Package Security

  Practice: Encrypting a Package and Assigning Package Roles

  Step 2: Executing and Scheduling Packages

  Using DTExecUI to Configure Package Execution

  Using DTExec for Package Execution

  Executing Packages in SSMS with the SSIS Service

  Creating SQL Server Agent Jobs to Execute SSIS Packages

  Practice: Executing Packages by Using DTExecUI,

  DTExec, and SQL Server Agent

  Case Scenario: Securing and Scheduling SSIS Packages

   

  This seminars series based on Microsoft Certification Training Kit for

  Exam 70-448: Microsoft SQL Server 2008 - Business Intelligence Development and

  Maintenance

  MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

• Developing SSAS Cubes

   

  This seminar focuses on

  the OLAP functionality of SSAS, exploring the process of developing basic SSAS cubes and

  dimensions.

  Seminar objectives:

  Implement dimensions in a cube.

  Implement measures in a cube.

  Implement a data source view.

   

  Step 1: Creating Data Sources and Data Source Views

  Defining a New Data Source

  Selecting Objects for a DSV

  Creating DSV Keys and Table Relationships

  Defining DSV Named Calculations and Named Queries

  Practice: Creating an SSAS Project, a Data Source, and a DSV

  Step 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 Dimension

  Modifying Dimension Attribute Properties

  Assigning Dimensions to Cubes

  Practice: Working with SSAS Dimensions

  Step 4: Creating Measure Groups and Measures

  Creating a Measure Group

  Adding and Configuring Measures

  Practice: Adding Measure Groups and Measures

  Case Scenario: Building an SSAS Solution as a Prototype

   

   

  This seminars series based on Microsoft Certification Training Kit for

  Exam 70-448: Microsoft SQL Server 2008 - Business Intelligence Development and

  Maintenance

  MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

• Extending SSAS Cubes

   

  This seminar starts by discussing attribute and dimension relationships. You

  will learn how to extend the Unified Dimensional Model (UDM) with KPIs, actions, translations,

  and perspectives. And after describing the MDX query fundamentals, the we will

  explore MDX calculated members and named sets.

  Seminar objectives :

  Implement dimensions in a cube.

  Configure dimension usage in a cube.

  Implement custom logic in a cube by using MDX

   

  Step 1: Defining User Hierarchies and Dimension Relationships

  Defining Attribute Relationships

  Creating and Modifying User Dimension Hierarchies

  Associating Dimensions to Measure Groups

  Selecting Relationship Types

  Practice: Creating and Modifying Dimension Hierarchies

  Step 2: Creating KPIs, Actions, Translations, and Perspectives

  Understanding KPI Value, Goal, Status, and Trend Properties

  Additional KPI Properties

  Creating KPIs

  Viewing KPIs

  Implementing Actions

  Localizing Cubes Through Translations

  Implementing Cube Perspectives

  Practice: Creating KPIs, Actions, Translations, and Perspectives

  Step 3: Creating Calculations and Queries by Using MDX

  Understanding MDX Syntax

  Applying MDX Functions

  Creating Calculated Members

  Defining Named Sets

  Practice: Extending Cubes by Using MDX Expressions

  Case Scenario: Extending SSAS Cubes

   

   

  This seminars series based on Microsoft Certification Training Kit for

  Exam 70-448: Microsoft SQL Server 2008 - Business Intelligence Development and

  Maintenance

  MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

• Managing SSAS Storage, Processing, and Deployment

   

  This seminar provides comprehensive coverage of SSAS storage options and optimization

  techniques. It also explores ways to partition measure groups to improve processing

  and query performance and describes ways to design and optimize cube aggregations.

  In a typical SSAS enterprise environment, three groups of people are involved in the

  different phases of the SSAS cube’s life cycle. Database developers focus on designing and

  programming the cube. Administrators are concerned with managing the cube. And end

  users query the cube and run reports.

  As an SSAS administrator, you will perform various management tasks in different phases

  of the cube life cycle. Two ongoing activities that you need to master are deploying and

  processing SSAS objects.

  Seminar objectives:

  Implement storage design in a cube.

  Deploy SSAS databases and objects.

  Implement processing options.

   

  Step 1: Defining Measure Group Partitions and Aggregations

  Understanding Partitions

  Creating Measure Group Partitions

  Contents xiii

  Selecting Partition Storage Modes

  Understanding Proactive Caching

  Understanding Aggregations

  Defining Aggregations with the Aggregation Design Wizard

  Practice: Defining Measure Group Partitions and Storage

  Step 2: 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

  Step 3: Processing SSAS Objects

  Understanding SSAS Processing Options

  Processing SSAS Objects in BIDS

  Processing SSAS Objects in SSMS

  Setting Advanced Processing Options with Proactive Caching

  Using the Analysis Services Tasks in SSIS

  Practice: Processing SSAS Objects

  Case Scenario: Implementing Low-Latency OLAP and

  Deployment Strategies

   

  This seminars series based on Microsoft Certification Training Kit for

  Exam 70-448: Microsoft SQL Server 2008 - Business Intelligence Development and

  Maintenance

  MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

• Securing and Administering SSAS

   

  In this seminar, you will learn how to use SSAS security features to protect data in the

  cube. In addition, you will learn how to back up and restore databases and monitor the

  health and performance of your SSAS installations.

  Seminar objectives :

  Configure permissions and roles in SSAS.

  Install and maintain an SSAS instance.

  Diagnose and resolve performance issues.

   

  Step 1: Setting Up SSAS Server Security

  Understanding SSAS 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

  Step 2: Managing SSAS High Availability, Backups, and

  Object Scripting

  Backing Up an SSAS Database in SSMS

  Scheduling SSAS Backups in SQL Server Agent

  Scripting SSAS Objects in SSMS

  Clustering SSAS

  Practice: Creating and Scheduling SSAS Backups with SSMS

  Step 3: Managing SSAS Tuning and Logging

  Editing SSAS Server Properties

  Defining Aggregations with the Usage-Based

  Optimization Wizard

  Practice: Setting Up SSAS Query Logging

  Step 4: Tracing and Monitoring SSAS Instances

  Working with SQL Server Profiler for SSAS Tracing

  Using Performance Monitor to Analyze SSAS Performance

  Understanding SSAS Performance Counters

  Configuring Performance Monitor

  Using Dynamic Management Views

  Practice: Tracing and Monitoring SSAS Instances

  Case Scenario: Administering and Securing SSAS Cubes

   

  This seminars series based on Microsoft Certification Training Kit for

  Exam 70-448: Microsoft SQL Server 2008 - Business Intelligence Development and

  Maintenance

  MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

• Working with SSAS Data Mining

   

  In this seminar, you will learn how you can use SSAS data mining .
  First you will learn how to develop several SSAS data mining models and select from those the best models for

  deployment into production. You will learn how to assess the accuracy and reliability of

  the models and compare the performance of the models so that you can choose the best

  model for your business needs. Then you will see how to secure the models and how to create

  prediction queries. In addition, you will learn how to deploy a mining model by using it

  in an SSRS report.

  Seminar objectives:

  Implement data mining.

  Deploy a mining model

   

  Step 1: Preparing and Creating Data Mining Structures

  Understanding the Data Mining Project Life Cycle

  Preparing Data for Data Mining

  Creating Data Mining Models

  Selecting Data Mining Algorithms

  Understanding the Data Mining Tools

  Practice: Preparing Data for Data Mining and

  Creating Predictive Models

  Step 2: Creating Models and Applying Algorithms

  Mapping Mining Structure Attributes to Source Columns

  Using Case Table Definitions and Nested Tables

  Using Cube Sources

  Configuring Algorithm Parameters

  Practice: Creating Mining Models and Setting

  Algorithm Parameters

  Step 3: Validating Models, Using DMX Queries, and Using

  Prediction Queries in Reports

  Validating Predictive Models

  Measuring the Accuracy of Other Models

  Creating Data Mining Queries and Reports

  Creating Prediction Queries in BIDS and SSMS

  Understanding the DMX Language

  Using Prediction Queries in Reports

  Practice: Testing Model Accuracy, Creating a DMX Report,

  and Using DMX Queries

  Step 4: Securing and Processing Data Mining Models

  Configuring SSAS Properties

  Configuring SSAS Roles, Permissions, and Data Sources

  Processing Data Mining Objects

  Processing Mining Structures and Models

  Practice: Securing and Processing Data Mining Models

  Case Scenario: Working with SSAS Data Mining

    

  This seminars series based on Microsoft Certification Training Kit for

  Exam 70-448: Microsoft SQL Server 2008 - Business Intelligence Development and

  Maintenance

  MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

• Developing SSRS Reports

   

  In this seminar, you will first learn how to create SSRS projects

  and reports in Business Intelligence Development Studio (BIDS). You will also see how to

  develop report objects by using the Report Designer and how to work with advanced report

  object properties. You will then see how to use a SQL Server Analysis Services (SSAS)

  database as the source for your report. And last, after looking at how to create a dataset

  from a data source, you will learn how to apply dataset filters and groups.

  Seminar objectives:

  Implement report data sources and datasets.

  Implement a report layout.

  Create an SSRS report by using an SSAS data source.

  Implement interactivity in a report.

  Implement report items.

   

   

  Step 1: Creating SSRS Projects and Reports in BIDS

  Understanding the SSRS Report Templates

  Using the Report Wizard

  Modifying Project Properties

  Modifying Report-Level Properties

  Developing Report Objects with the Report Designer

  Adding Report Objects to a Report

  Practice: Creating and Modifying a Report

  Step 2: Creating a Dataset from a Data Source

  Creating a New Report Dataset

  Working with an SSAS-Based Dataset

  Practice: Creating Report Datasets

  Step 3: Working with Advanced Report Object Properties

  Toggling Object Visibility

  Defining Report Actions

  Adding Bookmarks

  Practice: Modifying Advanced Report Object Properties

  Step 4: 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 Advanced Data Regions

  Case Scenario: Building Reports for the AdventureWorks Intranet

   

  This seminars series based on Microsoft Certification Training Kit for

  Exam 70-448: Microsoft SQL Server 2008 - Business Intelligence Development and

  Maintenance

  MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

• Extending and Deploying SSRS Reports

   

  In this seminar, you will learn some techniques for using parameters to let users interact with

  reports and how to use these parameters to filter data. You will also learn how to enhance

  reports by using conditional formatting and how to extend SSRS features with custom code,

  either embedded in a report or made available to SSRS from a custom assembly. Last, you

  will see how to use SSRS reports in a client application.

  Seminar objectives:

  Implement a report layout.

  Extend an SSRS solution by using code.

  Implement report parameters.

  Implement interactivity in a report.

  Implement report items.

  Embed SSRS reports in custom applications.

  Deploy an SSRS solution.

   

  Step 1: Assigning Parameters Within Reports

  Creating Parameters in Report Datasets

  Exposing Parameters to Users

  Binding Datasets to Parameters

  Using Multivalued Parameters

  Working with Parameter Defaults

  Working with Parameters in URLs

  Practice: Creating and Applying Report Parameters

  Step 2: Using Expressions to Perform

  Advanced Report Item Formatting

  Extending Report Properties by Using Expressions

  Using the <Code> Element in a Report

  Extending SSRS with Custom Assemblies

  Creating a Custom Assembly

  Practice: Setting Properties and Making Them Dynamic

  Step 3: Deploying New Reports and Changes

  Configuring Report Deployment Properties in BIDS

  Deploying and Redeploying Reports in BIDS

  Uploading a Report File in Report Manager

  Deploying Report Builder Models and Reports

  Practice: Deploying Reports

  Step 4: Using Reports in Your Code

  Using the SSRS Web Service

  Using the Windows Forms Report Viewer Control and

  the Web Forms Report Viewer Control

  Practice: Using the Windows Application

  Case Scenario: Creating a Reporting Services Infrastructure

    

  This seminars series based on Microsoft Certification Training Kit for

  Exam 70-448: Microsoft SQL Server 2008 - Business Intelligence Development and

  Maintenance

  MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

• Scheduling and Securing Deployed Reports and Data Sources

   

  In this seminar, you will learn about Reporting Services site management and security,

  report and data source security, simple and advanced data-driven subscriptions, and report

  history and caching.

  Seminar objectives :

  Implement report data sources and datasets.

  Configure report execution and delivery.

  Configure authentication and authorization for a reporting solution.

   

  Step 1: Administering SSRS Item-Level Permissions and

  Site Security Roles

  Understanding SSRS Item-Level Roles

  Assigning Item Permissions and Site Security Access

  Managing Data Sources and Credentials

  Practice: Creating Roles in Report Manager and

  Managing Data Sources

  Step 2: Creating Report Schedules and Subscriptions

  Creating Shared Schedules

  Defining a Report-Specific Schedule

  Applying a Subscription to a Report

  Defining Data-Driven Subscriptions and Rendering Formats

  Creating Data-Driven Subscriptions

  Specifying the Subscription Delivery Format and Location

  Practice: Creating Report Schedules and Subscriptions

  Step 3: Managing Report Caching and Execution Properties in

  Report Manager

  Understanding Report Execution Behavior

  Using Report Caching

  Using Report Snapshots for Report Execution

  Setting a Time-Out and Restricting Linked Reports

  Practice: Using Report Manager to Modify Report Properties

  Case Scenario: Managing the Report Environment for

  Adventure Works

    

  This seminars series based on Microsoft Certification Training Kit for

  Exam 70-448: Microsoft SQL Server 2008 - Business Intelligence Development and

  Maintenance

  MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

• Configuring and Administering the SSRS Server

   

  This seminar covers the configuration and administration of SQL Server Reporting

  Services (SSRS) instances, including configuring advanced settings and developing a

  scale-out architecture for SSRS. Configuring SSRS includes working with the Reporting Services

  Configuration Manager tool, the command-line utility named RSConfig.exe, and the

  command-line utility named RSKeyMgmt.exe to set up the SSRS database repository, configure

  the Web management and report virtual Web directories, and manage the encryption

  keys. In addition, to work with SSRS, you might need to change the rendering output

  settings or to control Secure Sockets Layer (SSL) connections.

  Seminar objectives :

  Install and configure SSRS instances.

  Configure SSRS availability.

   

  Step 1: Installing and Configuring Initial Instances of SSRS

  Reviewing the Reporting Services Components

  Installing Reporting Services

  Naming SSRS Instances

  Using the Reporting Services Configuration Manager Tool for

  Server Setup and Management

  Managing Report Server Encryption Keys

  Practice: Using Reporting Services Configuration Manager

  Step 2: Configuring Advanced SSRS Settings and

  Scale-Out Deployment

  Using the Reporting Services Command-Line Utilities to

  Manage SSRS

  Configuring SSRS for Scale-Out Deployments and

  High Availability

  Changing Report Server Properties in SSMS

  Understanding Configuration Files in

  Reporting Services 2008

  Practice: Managing SSRS Encryption Keys

  Case Scenario: Scaling Out Your SSRS Servers

    

  This seminars series based on Microsoft Certification Training Kit for

  Exam 70-448: Microsoft SQL Server 2008 - Business Intelligence Development and

  Maintenance

  MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)