SQL Server Integration Services (SSIS)
1. Introduction to SQL Server Integration Services
 Product History
 SSIS Package Architecture Overview
 Development and Management Tools
 Deploying and Managing SSIS Packages
 Source Control for SSIS Packages
2. SSIS for DBAs: Using SQL Server Management Studio
 The Import and Export Wizard
 Importing and Exporting Data
 Working with Packages
 Database Maintenance Plans
 Creating Database Maintenance Plans using SSMS
 Scheduling and Executing Plans. Examining Database Maintenance Plan
Packages
3. Business Intelligence Development Studio
 Control Flow Overview
 Precedence Constraints
 The Execute SQL Task
 The Bulk Insert Task
 The File System Task
 The FTP Task
 The Send Mail Task
4. Advanced Control Flow
 Building pivot tables on top of Power Pivot.
 Containers – grouping and looping
 The Web Service Task
 The WMI tasks
 The Analysis Services tasks
 The Execute Process Task · The Execute Package Task
5. Introduction to Data Flow
 Data Flow Overview
 Data Sources
 Data Destinations
 Data Transformations
 The Copy Column Transformation
 The Derived Column Transformation
 The Data Conversion Transformation
 The Conditional Split Transformation
 The Aggregate Transformation
 The Sort Transformation
 Data Viewers
6. Variables and Configurations
 Variables Overview
 Variable scope
 SSIS system variables
 Using variables in control flow
 Using variables in data flow
 Using variables to pass information between packages
 Property expressions
 Configuration Overview
 Configuration options
7. Debugging, Error Handling and Logging
 SSIS debugging overview
 Breakpoints in SSIS
 SSIS debugging windows
 Control Flow: The on Error event handler
 Data Flow: Error data flow
 Configuring Package Logging · Built-in log providers
8. Advanced Data Flow
 Revisiting data sources and destinations
 The Lookup Transformation
 Getting Fuzzy: The Fuzzy Lookup and Fuzzy Grouping Transformations
 The Multicast Transformation
 The Merge and Merge Join Transformations
 The Data Mining Query Transformation
 The Data Mining Model Training Destination
 The Slowly Changing Dimension Transformation
9. Extending SSIS through Custom Code
 Introduction to SSIS scripting
 The SSIS script editor
 The SSIS object model
 Script in Control flow: The Script Task
 Script in Data flow: The Script Component
 Introduction to SSIS component development
10. SIS Package Deployment
 Configurations and deployment
 The deployment utility
 Deployment options
 Deployment security
 Executing packages – DTExec and DTExecUI
11. SSIS Package Management
 The SSIS Service
 Managing packages with DTUtil
 Managing packages with SQL Server Management Studio
 Scheduling packages with SQL Server Agent
12. SSIS 2012 new features
 CDC (Change data capture) control Flow & Dataflow task
 SSIS Catalog Reports (Dashboard)
 Package upgrades from legacy to SSIS 2012
13. SQL Server Reporting Services
14. Introducing SQL Server Reporting Services
 Tour of Features
 Reporting Services Architecture
 Reporting Services Terminology
 Reporting Services Editions
15. Creating Reports
 Deconstructing Reporting Services
 Shared Data Sources
 Creating Reports from Scratch
16. Calculations and Formatting
 Creating Expressions
 Using the Global Collections
 Formatting Items · Conditional Formatting
17. Grouping and Sorting
 Creating Groups
 Calculating Totals and Percentages
 Interactive Sorting
 Creating Drill-Down Reports
18. Report Parameters
 Creating Report Parameters
 Creating Drop-Down Parameters
 Multi-Valued Parameters
 Debugging Parameter Issues
19. Creating Matrix Reports and Charts
 Creating a Basic Matrix Report
 Matrix Subtotals
 Creating a Basic Chart
 Exploring the Charting Possibilities
20. Managing Reporting Services
 Deploying Reports and Data Sources
 Exporting Reports and Printing
 Using the Web-Based Report Manager
21. Reporting Services Security
 Two Parts to Reporting Services Security
 Securing Access to Reports
 Data Source Security
22. Programming Reporting Services
 The Many Reporting Services APIs
 Integrating Reporting Services into Applications using URL Access
 Using the Report Viewer Controls
23. Advanced Reporting Services Programming
 Using the Reporting Services Web Service
 Working with Custom Assemblies
24. Snapshots and Subscriptions
 Caching and Snapshots
 Creating Standard Subscriptions
 Creating Data-Driven Subscriptions
 Managing Subscriptions
25. Ad-Hoc Reporting with Report Builder
 The Report Builder Architecture
 Creating Report Model Projects
 Running Report Builder
26. SQL Server Analysis Services
27. What Is Microsoft BI?
 Core concept – BI is the cube or UDM
 Example cube as seen using Excel pivot table
 MSBI is comprehensive – more than Analysis Services on SQL Server
 Demonstration of SQL Reporting Services with cube as data source
28. Using SSAS in BIDS
 Understanding the development environment
 Creating Data Sources and Data Source Views
 Creating cubes – using the UDM and the Cube Build Wizard
 Refining Dimensions and Measures in BIDS
29. Intermediate SSAS
 KPIs
 Perspectives
 Translations – cube metadata and currency localization
 Actions – regular, drill-through and reporting
30. Advanced SSAS
 Using multiple fact tables
 Modeling intermediate fact tables
 Modeling M:M dimensions, Fact (degenerate) dimensions, Role-playing
dimensions, writeback dimensions
 Modeling changing dimensions – Dimension Intelligence w/ Wizard
 Using the Add Business Intelligence Wizards – write-back, semi-additive
measures, time intelligence, account intelligence
31. Cube Storage and Aggregation
 Storage topics – basic aggregations, MOLAP
 Advanced Storage Design – MOLAP, ROLAP, HOLAP
 Partitions – relational and Analysis Services partitions
 Customizing Aggregation Design – Processing Design
 Rapidly changing dimensions / ROLAP dimensions
 Welcome to the Real Time – Proactive Caching
 Cube processing options
32. Beginning MDX
 Basic syntax
 Using the MDX query editor in SQL Server Management Studio
 Most-used Functions & Common tasks
 New MDX functions
33. Intermediate MDX
 Adding calculated members
 Adding scripts
 Adding named sets
 .NET Assemblies