Course Content
Introduction to Integration Services
- Defining SQL Server Integration Services
- Exploring the need for migrating diverse data
- The role of business intelligence (BI)
SSIS Architecture and Tools
Managing heterogeneous data
- Leveraging the Extract, Transform and Load (ETL) capabilities of SSIS
- Running wizards for basic migrations
- Creating packages for complex tasks
Illustrating SSIS architecture
- Distinguishing between data flow pipeline and package runtime
- Executing packages on the client side or hosted in the SSIS service
Upgrading legacy DTS
- Executing existing DTS packages in the SSIS environment
- Converting DTS packages to SSIS with the migration wizard
- Logging migration results
Implementing Tasks and Containers
Utilizing basic SSIS objects
- Configuring connection managers
- Adding data flow tasks to packages
- Reviewing progress with data viewers
- Assembling tasks to perform complex data migrations
Operating system level tasks
- Copying, moving and deleting files
- Transferring files with the FTP task
- Reading system information with WMI query language (WQL)
Communicating with external sources
- Sending messages through mail
- Detecting system events with WMI
Processing XML
- Iterating XML nodes
- Writing XML files from databases
Extending Capabilities with Scripting
Writing expressions
- Making properties dynamic with variables
- Building expressions in Expression Builder Script Task
- Extending functionality with the Script Task
- Debugging, breakpoints, watches
Transforming with the Data Flow Task
Performing transforms on columns
- Converting and calculating columns
- Transforming with Character MapCombining and splitting data
- Profiling data
- Merge, Union, Conditional Split
- Multicasting and converting data
Manipulating row sets and BLOB data
- Aggregate, sort, audit and look up data
- Importing and exporting BLOB data
- Redirecting error rows
Performing database operations
- Implementing Change Data Capture (CDC)
- Executing a SQL task
- Bulk inserting data from text files
Error Handling, Logging and Transactions
Organizing package work flow
- Defining success, failure, completion and expression precedence constraints
- Handling events and event bubbling
Designing robust packages
- Choosing log providers
- Adapting solutions with package configurations
- Auditing package execution results
Administering Business Intelligence
Managing and securing packages
- Storing packages in Package Store and msdb
- Encrypting packages with passwords and user keys
Integrating with other BI components
- Displaying data in Reporting Services
- Accessing package data with ADO.NET
Building and Modifying an OLAP Cube
Designing a Unified Dimension Model (UDM)
- Identifying measures and their suitable granularities
- Adding new measure groups and creating custom measures Creating dimensions
- Implementing a Star and Snowflake Schema
- Managing Slow Changing Dimensions (SCD)
- Identifying role-play dimensions
Extending the Cube with Hierarchies
Creating hierarchies
- Building natural hierarchies
- Many-to-many hierarchies
- Creating attribute relationships
- Distinguishing between ragged, balanced and unbalanced hierarchies
- Discretizing attribute values with the Clusters and Equal Areas algorithms
- Parent-child relationships
- Defining parent and key attributes
- Generating level captions with the Naming Template feature
- Removing repeated entries with the MembersWithData property
Exploiting Advanced Dimension Relationships
Storing dimension data in fact tables
- Building a degenerate dimension
- Configuring fact relationships
Saving space with referenced dimension relationships
- Identifying candidates for referenced relationships
- Utilizing the Dimension Usage tab to configure referenced relationships
Including dimensions with many-to-many relationships
- Implementing intermediate measure groups and dimensions
- Reporting on many-to-many dimensions without double counting
Designing Optimal Cubes
Assembling cube components
- Selecting the appropriate fact tables
- Adding cube dimensions
- Distinguishing between additive, semiadditive and nonadditive measures
Designing storage and aggregations
- Choosing between ROLAP, MOLAP and HOLAP
- Partitioning cubes for improved performance
- Designing aggregations with the Aggregation Design Wizard
- Leveraging the Usage-Based Optimization Wizard
Automating processing
- Exploiting XMLA scripts and SSIS
- Refreshing cubes with Proactive Caching
Performing Advanced Analysis with MDX
Retrieving data with MDX
- Defining tuples, sets and calculated members
- Querying cubes with MDX
- Navigating hierarchies with MDX and utilizing set functions
Monitoring business performance with KPIs
- Building goal, status and trend expressions
- Using PARALLELPERIOD to compare with past time periods
Creating calculations with MDX
- Adding runtime calculations to the cube
- Comparing MDX calculations with DSV calculated columns
Securing Cube Data
Securing data and simplifying the user interface
- Distinguishing between perspective feature and security
- Creating roles for administrative privileges
- Securing dimension data
- Implementing cell-level security
Gaining Business Advantage with Data Mining
Determining the correct model
- Identifying business tasks for data mining
- Training and testing data mining algorithms
- Comparing algorithms with the accuracy chart and classification matrix
- Optimizing returns with the Profit Chart
Performing real-world predictions
- Classifying with the Decision Trees, Neural Network and Naive Bayes algorithms
- Predicting with the Time Series algorithmDeploying models
- Predicting new cases with algorithms
- Utilizing DMX to perform batch and singleton predictions
- Exploring results with data mining viewers
Introduction to SQL Server 2008 Reporting Services
- Identifying deployment: native, integrated or single server
- Managing Web farm deployment with/without SharePoint
Developing Reports
Designing fundamental reports
- Connecting to relational and multidimensional data sources
- Generating a Tablix reporting structure
- Building an interactive chart
- Creating reports with richly formatted text
Composing expressions
- Computing custom fields
- Managing built-in collections
- Linking expressions to properties
- Employing conditional formatting
Arranging and sorting data
- Multiple-level grouping and categorizing the results
- Applying aggregate functions
Producing various outputs from a Tablix
- Creating parallel dynamic group report formats
- Combining dynamic and static columns
Integrating Parameters and Filters
Incorporating parameters into reports
- Yielding subsets of data with query parameters
- Aligning report parameters to query parameters
- Constructing cascading report parameters
- Transmitting parameters to stored procedures
- Handling multivalued parameters
Applying filters to report data
- Augmenting performance with filters
- Determining filters vs. query parameters
Implementing Interactive Features
Combining multiple data regions in one report
- Applying sequential and nested regions
- Creating master/detail reports and linking subreports
Executing advanced data visualizations
- Applying the radial and linear gauge
- Customizing charts with scale breaks
- Charting KPIs from Analysis Services
Showing robust data with relevant detail
- Drilling through report detail and drilling down report data
- Navigating reports with document maps
- Reporting with hierarchical rows and dynamic headers
Deploying and Delivering Reports
Deploying reports to the server
- Publishing reports and configuring project properties
- Verifying results with Report Manager
Identifying delivery options
- Electing on demand or via subscription
- Enhancing performance with cached instances and snapshots
- Configuring snapshot history
- Formatting different outputs
Dispatching subscription reports
- Publishing reports via e-mail and fileshare
- Seamlessly delivering reports by data-driven subscriptions
Integrating reports with SharePoint
- Deploying reports to SharePoint
- Viewing results in Report Center and Data Connections libraries
Safeguarding Reporting Services
Structuring content security
- Leveraging existing Windows authentication
- Establishing permission levels on report items
Ensuring the RS system
- Instituting varying levels of administrative roles
- Allowing and withdrawing system-level permissions
Empowering Users with Ad Hoc Reporting
Designing models for users
- Building personalized reports with Model Designer
- Defining data source views
- Creating and refining models
Designing reports with Report Builder
- Launching Report Builder to users across the enterprise
- Dragging and dropping entities onto charts and tabular reports
- Exploiting Analysis Services cubes as report models
Delivering Reports to Users
- Accessing and controlling reports using URLs
- Leveraging RS Web services
- Embedding reports in applications with the Report Viewer
- Delivering reports through SharePoint
