DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to learn SQL Server 2008 Business Intelligence from video?

The following is the sqlusa.com screencast video course syllabus for SQL Server 2008 SSAS, SSIS & SSRS. Lesson video can be paused at any time so that you can practice along on your own copy of SQL Server. Read accompanying book for in-depth understanding of the subject.

Note: Training videos are distributed on 8GB/16GB USB memory sticks which are more reliable than CD-s.

SSAS

Audience: BI-DW architects/OLAP cube designers/experts

CD 1 - BUSINESS INTELLIGENCE (2 hours 53 minutes)
1. Enter the Golden Age of Business Intelligence
2. Comprehensive & Scalable Persuasive BI Platform
3. BI Initiative. How to Justify, Sell & Architect
4. Installing AdventureWorks2008 DW & SSAS Project
5. Tour of Business Intelligence Development Studio
6. Data Warehousing & Data Mining Review
7. Multi-Level Dimension & Multi-Dimensional Cube
8. OLAP Cube: Millions of Pre-Calculated Answers
9. Easy Drilldown OLAP Reports with Report Builder
10. The Power of BI with Dashboard Reporting
11. Creating Interactive Analytical Matrix Reports
12. Exploring Online Data Analysis with OLAP Cubes
13. Review of Integration and Reporting Services
14. Performance Management with Balanced Scorecard
15. SQL Server 2008 - Integrated BI Delivery Platform
16. Business Intelligence to Machine Intelligence

CD 2 - DW/BI BASICS (3 hours 8 minutes)
1. Overview of Data Warehousing & OLAP Technology
2. Modern Data Warehouse & Data Mart Structure
3. Tour of SQL Server 2008 Analysis Services
4. SSAS Administration - DB Backup & Restore
5. AdventureWorksDW2008 Sample DW Database
6. OLTP Transactional Systems - AdventureWorks2008
7. Pre-BI Approach - GROUP BY & Aggregate Queries
8. OLAP: Interactive Online Analytical Processing
9. Browsing OLAP Cubes & Mining Models in SSMS
10. Slicing & Dicing SSAS Cubes in Excel 2007
11. Selecting Data from SSAS Cube in Report Wizard
12. Multi-Dimensional Model Architecture & Design
13. Exploring Enterprise Requirements Gathering
14. Data Source Analysis and SSIS Data Profiling
15. Convincing Management & Executive Perspective
16. Composition & Dynamics of DW/BI Project Team

CD 3 - DATA WAREHOUSE DESIGN (2 hours 58 minutes)
1. Enterprise Data Warehouse Architecture
2. Dimensional Modelling Star Schema Diagram
3. Understanding Fact Table vs. Dimension Table
4. Fact Table Measures & Calculated Measures
5. Defining Dimension Attributes & Hierarchies
6. Populating & Refreshing the Data Warehouse
7. Extract, Transform and Load (ETL) Processes
8. T-SQL Commands & Utilities for Importing Data
9. Data Transfer with SSIS Import/Export Wizard
10. Exploring Advanced SQL Server 2008 SSIS
11. Complex Data Warehouse Population with SSIS
12. Data Warehouse DB Maintenance & Optimization
13. Slowly Changing Dimensions and SCD Wizard
14. Business Intelligence Wizard & Time Dimension
15. Pros & Cons of Normalized Snowflake Schema
16. Reviewing Data Warehouse Security Concepts

CD 4 - OLAP CUBES (3 hours 1 minute)
1. Creating an Analysis Services Database
2. Adding Simple OLAP Cubes to an SSAS Database
3. Migrating from SSAS 2000 to SSAS 2008
4. Migrating SSAS 2005 Database to SSAS 2008
5. Analysis Services 2008 Architecture
6. OLAP Concepts: Measures & Dimensions
7. Organizing Measures, Dimensions & Hierarchies
8. Aggregations Concepts - GROUP BY Queries
9. The AdventureWorksDW2008 Data Warehouse
10. Managing AS 2008 from Management Studio
11. Designing with the Dimension Wizard
12. Learning the Attribute Relationship Designer
13. Exploring the Aggregation Design Wizard
14. Working with the Cube Wizard
15. Exploring the New Streamlined Cube Designer
16. Processing Dimensions and Cubes

CD 5 - COMPLEX CUBES (3 hours 2 minutes)
1. Adding Measures & Measure Groups to a Cube
2. Calculated Member, Named Set & Script Command
3. Editing Dimensions and Cubes in the Designers
4. Slicing & Dicing, Drilling a Cube in the Browser
5. Understanding the Schema Generation Wizard
6. MOLAP, ROLAP, HOLAP & Other Storage Models
7. Aggregation & Usage-Based Cube Optimization
8. Deployment Wizard & Synchronize DB Wizard
9. Automating Cube Processing with SSIS
10. Drillthroughs, Actions and Perspectives
11. Natural, Navigation & Parent-Child Hierarchies
12. Calculation Tools - Metadata, Functions & Templates
13. Linked Object Wizard, Translations & Currency
14. Referenced & Many-to-Many Dimension Relationships
15. OLAP Cube, Dimension & Cell Level Security
16. Partition Management, DB Processing & Writeback

CD 6 - DELIVERY & PM (3 hours 8 minutes)
1. Delivery to BI Consumers & Project Management
2. Exploring MDX Concepts - Overview and Examples
3. MDX Syntax Elements - Expressions & Queries
4. Working with SSMS MDX Template Explorer
5. Discovering SSRS Visual MDX Query Builder
6. Capturing MDX Queries with SQL Server Profiler
7. OLAP Client - Excel 2007 Pivot Tables & Charts
8. Excel 2007 - Saving & Loading .cub Local Cubes
9. Interactive Drilldown SSRS OLAP Matrix Reports
10. Key Performance Indicator (KPI) Reports
11. The XMLA Language - Capture with SQL Profiler
12. Reviewing DW/BI Project Deliverables
13. Recognizing Moribund DW/BI Projects
14. Understanding Successful DW/BI Projects
15. Choosing the Right BI Presentation Layer
16. Success Secrets to DW/BI Project Management

CD 7 - DATA MINING (2 hours 50 minutes)
1. Reviewing the Data Mining Business Process
2. Extracting Gold from the Mine of Customer Data
3. The DMX Language - Capture with SQL Profiler
4. DM Algorithms – Predicting, Clustering or Both
5. Decision Trees - Classification & Regression
6. Naive Bayes – Classification for Prediction
7. Time Series - Regression of Continuous Values
8. Clustering - Segmentation for Grouping
9. Sequence Clustering for Paths Grouping
10. Association for Recommendation Engines
11. Neural Networks to Calculate Possibilities
12. Linear and Logistic Regression for Prediction
13. Data Mining Wizard to Create Mining Structures
14. Exploring the Data Mining Ribbon in Excel 2007
15. Discovering Data Analysis for DM in Excel 2007
16. Mined Customers Cube - Combining DM with OLAP

SSIS

Audience: ETL developers/DW developers/experts

CD 1 - INTEGRATION SERVICES (2 hours 46 minutes)
1. Welcome to SSIS - Visualized Data Migration
2. New Features in 2008 Integration Services
3. Visit Business Intelligence Development Studio
4. Design & Management Tools - Expression Designer
5. Configuring, Deploying & Managing SSIS Packages
6. Installing and Setting up Integration Services
7. Exploring the SSIS Import and Export Wizard
8. Learning to Export Data OUT from the Database
9. Learning to Import Data IN to the Database
10. SSIS Package Architecture for ETL Solutions
11. Designing Your First SSIS 2008 Package
12. Executing a Package in Management Studio
13. Package Migration Wizard for DTS & SSIS 2005
14. SSIS Sample Packages & Metadata Management
15. Loading & Refreshing Data Warehouse Using SSIS
16. Discover Programmatic Control of SSIS Packages

CD 2 - BIDS (2 hours 49 minutes)
1. Entering SSIS Designer, Zoom & Four-Way Locator
2. Discovering Standard Project Templates & Tasks
3. SSIS Menu, Debugging & Diagnostics Windows
4. Learning Solution Explorer & Properties Window
5. SSIS Design Surfaces - Control Flow & Data Flow
6. Investigating Toolbox & Options Dialog Box
7. Introduction to Connection Manager
8. Studying Variables Window & Package Explorer
9. Training in Find and Replace in Files
10. Exploring Control Flow Basics
11. Execute DTS 2000 Task for DTS Packages
12. Execute Process Task for Win32 Programs
13. Execute SQL Task for Query & Sproc Execution
14. Bulk Insert Task for File to Database Copy
15. File System Task for Copying & File Operations
16. Send Mail Task for E-mail Message Sending

CD 3 - CONTAINERS (2 hours 47 minutes)
1. Introduction to Grouping and Looping
2. For Loop Container - Exec Control Flow in Loop
3. For Each Container - Exec Enumeration Iteration
4. Sequence Container - Group Control Flow Tasks
5. Precedence Constraints - Sequencing Workflow
6. Learning The WMI & Web Service Tasks
7. The FTP Task - Send & Receive Files
8. Script & ActiveX Script Tasks - Execute Scripts
9. The XML Task - Operate on XML Data
10. Execute Package Task - Execute SSIS Package
11. Exploring Transfer SQL Server Objects Task
12. Reviewing the Database Maintenance Plan Tasks
13. Execute SSA Job & Maintenance Plan Tasks
14. Discovering The Execute T-SQL Statement Task
15. The Backup Database & Notify Operator Tasks
16. The Rebuild Index & Update Statistics Tasks

CD 4 - DATA FLOW (2 hours 54 minutes)
1. Overview of SSIS Designer for Data Flow
2. Discovering Data Flow Sources
3. Exploring Data Flow Transformations
4. Investigating Data Flow Destinations
5. Grid, Histogram, Plot & Chart Data Viewers
6. The Copy Column Transformation
7. The Derived Column Transformation
8. Export Column & Import Column Transformations
9. Configuring the Cache Transform Transformation
10. The Data Conversion Transformation
11. The Conditional Split Transformation
12. The OLE DB Command Transformation
13. The Aggregate Transformation for Group By
14. The Sort & Character Map Transformations
15. The PIVOT & UNPIVOT Transformations
16. The Row Count & Row Sampling Transformations

CD 5 - VARIABLES (2 hours 56 minutes)
1. Getting Started with User-Defined Variables
2. Investigating Variable Type & Scope
3. Reviewing & Using SSIS System Variables
4. Mapping Stored Procedure Parms in OLE DB Source
5. Setting up Nested Iteration in Control Flow
6. Nested Variables in SQL & Script Tasks
7. Mapping Parameters & Result Sets to Variables
8. System & User Variables in Property Expressions
9. Configuration Overview & Options
10. The Package Configuration Wizard
11. Debugging, Error Handling and Logging
12. Debug Breakpoints & BIDS Debugging Windows
13. Control Flow: The OnError Event Handler
14. Data Flow: Error Data Flow
15. Configuring Package Logging
16. Built-in Log Providers & Log Events

CD 6 - ADVANCED SSIS (2 hours 40 minutes)
1. Exploring Lookup Transformation with Caching
2. Learning Term Lookup & Term Extraction
3. Discovering Fuzzy Lookup & Fuzzy Grouping
4. The Multicast & Union All Transformations
5. The Merge and Merge Join Transformations
6. The Percentage Sampling & Audit Transformations
7. Investigating the Data Mining Query Task
8. The Slowly Changing Dimension Transformation
9. Extending SSIS through Custom Code
10. The SSIS Object Model & Script Editor
11. Navigating Server Explorer & Object Browser
12. Analysis Services Processing Task - OLAP Cube
13. Advanced Data Flow Sources & Destinations
14. Advanced Control Flow Features
15. Data Profiling Task & Data Profile Viewer
16. SSIS Performance Tuning Techniques

CD 7 - DEPLOYMENT (3 hours 3 minutes)
1. SSIS 2008 Package Configuration & Deployment
2. Investigating the XML Configuration File
3. Introduction to Configuring Connection Managers
4. The MULTIFLATFILE Connection Manager
5. The Deployment Utility & Deployment Options
6. Deployment Security & The Manifest File
7. Learning the Package Installation Wizard
8. Execute Package Utilities: DTExec & DTExecUI
9. Package Management & Import from Other Servers
10. Complex SSIS Import & Export Wizard in BIDS
11. Exploring SSIS Service & SSIS Administration
12. Managing Packages & Automation with DTUtil
13. SQL Server 2008 Management Studio
14. Managing Packages with Management Studio
15. Scheduling Packages with SQL Server Agent
16. TOP 10 SSIS 2008 Success Secrets

Bonus CD
CD 1 SSIS Packages
CD 2 SSIS Packages
CD 3 SSIS Packages
CD 4 SSIS Packages
CD 5 SSIS Packages
CD 6 SSIS Packages
CD 7 SSIS Packages

SSRS

Audience: BI developers/SQL report programmers/experts

CD 1 - BI DELIVERY (2 hours 52 minutes)
1. Welcome to SQL Server 2008 Reporting Services
2. Enabling Enterprises to Deliver BI to All Staff
3. Reporting Services 2008 Architecture Grand Tour
4. Managing Report Server Components & Operation
5. Creating Richly Formatted Interactive Reports
6. SSRS Role in Microsoft Business Intelligence
7. Flexible Report Layout for Data Visualization
8. New & Enhanced Rendering in Word, Excel and CSV
9. Report Builder 2.0 for Business User Authoring
10. Navigating Reporting Services Design Studio
11. Creating Table Report Using the Report Wizard
12. Data Sources: OLTP/DW DB, OLAP, ODBC & OLE DB
13. Executive BI: Performance Management Metrics
14. Designing Matrix Reports using AS OLAP Cubes
15. Business Intelligence Development Studio Tour
16. Exploring Reporting Services Report Manager

CD 2 -REPORTING BASICS (3 hours 8 minutes)
1. Introduction to the BIDS Report Designer Tool
2. Developing Enterprise Reporting Solutions
3. Evaluating Critical Business Requirements
4. Reporting Services Design Concepts
5. Identifying Users & Preparing Specifications
6. Managing Shared & Private Data Sources
7. Designing Basic Reports from Scratch
8. Building Table Reports with Report Wizard
9. Changing Font, Size and Color Options
10. Number, Currency, Date & Time Formats
11. Adding Table & Page Headers and Footers
12. Subtotals, Totals, Grand Totals & Formulas
13. Architecting Chart and Gauge Reports
14. Discovering List - Free Form Data Presentation
15. Improving Usefulness with Paging & Sorting
16. Reviewing the Structure of Tablix Data Region

CD 3 - FORMATTING REPORTS (3 hours 1 minute)
1. Exploring Report Items and Graphical Elements
2. Learning Basic Expressions & Global Collections
3. Including Custom Visual Basic Code in Reports
4. Formatting Fields and Conditional Formatting
5. Data Grouping, Static & Interactive Sorting
6. The Magic of Interactive Drill-Down Reports
7. Calculating Percentages ,Totals & Aggregates
8. BI Dashboards for Data Visualization
9. Corporate Performance Management Scorecards
10. Advanced Report Features, Web Farm Deployment
11. Drill-Through Reports & Report Metadata
12. Linked Reports & Complex Expressions
13. Subreports & Report Definition Language
14. Configuring Document Maps & Bookmarks
15. Organizational Chart - Recursive Reports
16. TOP 10 Report Layout & Formatting Tips

CD 4 - REPORT PARAMETERS (3 hours 9 minutes)
1. Creating Simple Reports with Parameters
2. Binding Report Parameters to Dataset Parms
3. Setting Up Defaults & Drop-Down Parameters
4. Multivalued Parameter with Checkmark Drop-Down
5. Architecting Reports with Multi-Select Parameters
6. Designing Reports with Cascading Parameters
7. Parameters Passing in Drillthrough Reports
8. Exploring the Report Parameters Dialog Box
9. Configuring Stored Procedures with Parameters
10. Configuring Functions (UDF) with Parameters
11. Debugging Reports, Troubleshooting & Error Logs
12. Enhancing Performance with Dataset Filters
13. Learning Multiple Criterion Report Filtering
14. Comparing Filters vs. Query Parameters
15. Parameters as Interactive BI Report Features
16. Top 10 SSRS Report Parameter Tips

CD 5 - MATRIX REPORTS (3 hours 4 minutes)
1. Delivering BI with Matrix (Crosstab) Reports
2. Classifying Data into Dimensions & Measures
3. Matrix Page, Columns, Rows & Details Setup
4. Matrix Subtotals, Totals & Grand Totals
5. Applying Aggregate Functions to Measures
6. Designing Interactive Drill-Down Matrix Reports
7. Exploring Matrix Charts & Parameterized Charts
8. Additive, Semi-Additive & Non-Additive Measures
9. Matrix Reports with Multi-Measures & Parameters
10. Time, Date & Other Multi-Level Dimensions
11. Matrix Reports with Mixed Dimensions
12. Matrix Reports with Multiple Aggregates
13. Adding Static & Dynamic Columns and Rows
14. Architecting Drillthrough Matrix Reports
15. Matrix Reports for OLAP (AS) Cube Reporting
16. OLAP Performance Management Matrix Reports

CD 6 - PUBLISHING & MANAGING (2 hours 42 minutes)
1. SSMS Reporting Services Management
2. Deploying Reports and Data Sources
3. Reporting Services Configuration Manager
4. Scheduled E-Mail Distribution of Reports
5. Scheduled Windows File Share Distribution
6. Configuring Data-Driven Subscriptions
7. Managing & Administering with Report Manager
8. Exception Handling - Catching Errors
9. Snapshots of Reports & Reports Histories
10. Report Caching & Optimized Stored Procedures
11. Automating BI Reports Execution & Delivery
12. Rendering in Excel, CSV, Word, XML, HTML & PDF
13. Reports, Projects, Solutions & Data Sources
14. ReportServer & ReportServerTempDB Databases
15. Importing Reports from Microsoft Access
16. TOP 10 Reporting Services Success Secrets

CD 7 - REPORT BUILDER 2.0 (3 hours 2 minutes)
1. Intuitive Report Authoring - Report Builder 2.0
2. DW Concepts - Dimension, Measure & Hierarchy
3. Exploring Aggregate Functions on Measures
4. The Architecture of a Matrix (Crosstab) Report
5. Designing Table Reports from OLTP Database
6. Designing Matrix Reports from Data Warehouse DB
7. Report Design from Multidimensional OLAP Cubes
8. Designing Reports with Parameters
9. Drop-Down Singlevalued & Multivalued Parameters
10. Adding Multiple Regions to a Report
11. Designing Reports with Charts
12. Designing Hierarchical Drill-Down Reports
13. Designing Master-Detail Drillthrough Reports
14. Key Performance Indicators (KPIs) Reports
15. Designing Reports from Stored Procedures
16. Publishing & Administering Reports


Bonus CD
CD 1 Reports
CD 2 Reports
CD 3 Reports
CD 4 Reports
CD 5 Reports
CD 6 Reports
CD 7 Reports

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE