MSBI COURSE OUTLINE

Duration : 30 days | 1hr theory/day 1hr Practical

Introduction to MSBI

>> SSIS

1 Introduction to Integration Services
Defining SQL Server Integration Services
Exploring the need for migrating diverse data
The role of business intelligence (BI)

2 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

3 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

4 Transforming with the Data Flow Task
Performing transforms on columns
Converting and calculating columns
Transforming with Character Map
Combining and spliting 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

5 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

6 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

>> SSAS

7 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

8 Extending the Cube with Hierarchies
Creating hierarchies
Building natural hierarchies
Many-to-many hierarchies
Creating atiribute relationships
Distinguishing between ragged, balanced and unbalanced hierarchies
Discretizing atiribute values with the Clusters and Equal Areas algorithms
Parent-child relationships
Defining parent and key atiributes
Generating level captions with the Naming Template feature
Removing repeated entries with the MembersWithData property

9 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

10 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

11 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

12 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 algorithm
Deploying models
Predicting new cases with algorithms
Utilizing DMX to perform batch and singleton predictions
Exploring results with data mining viewers

>> SSRS

13> Introduction to SQL Server 2008 Reporting Services
Identifying deployment: native, integrated or single server
Managing Web farm deployment with/without SharePoint

14 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 formatied text
Composing expressions
Computing custom fields
Managing built-in collections
Linking expressions to properties
Employing conditional formating
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

15 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
Transmiting 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

16 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
Formating 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

17 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