MSBI course content

  •  SSAS
  •  SSIS
  •  SSRS

SQL Server Analysis Services Course Content


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
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
Intermediate SSAS
  •  KPIs
  •  Perspectives
  • Translations – cube metadata and currency localization

Actions – regular, drill-through and reporting

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

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

  •  Basic syntax
  •  Using the MDX query editor in SQL Server Management Studio
  •  Adding calculated members
  •  Adding scripts
  •  Adding named sets
SSAS Administration
  • Best practices – health monitoring
  • XMLA scripting (SQL Mgmt Studio)
  • Security – roles and permissions
  • Disaster Recovery – backup / restore
Introduction to Reporting Clients
  •  Excel 2007/  Power pivot  
  •  SQL RS & Report Builder
  •  Sharepoint Performance Point Services 2010


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

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

Business Intelligence Development Studio
  • Launching BIDS 
  • Project templates 
  • The package designer 
  • The Toolbox 
  • Solution Explorer 
  • The Properties window 
  • The Variables window · The SSIS menu

Introduction to Control Flow
  •  Control Flow Overview 
  •  Precedence Constraints 
  •  The Execute SQL Task 
  •  The Bulk Insert Task 
  •  The File System Task 
  •  The FTP Task 
  •  The Send Mail Task
Advanced Control Flow
  •  Containers – grouping and looping 
  •  The Web Service Task 
  •  The WMI tasks 
  •  The Analysis Services tasks 
  •  The Execute Process Task · The Execute Package Task
  •  Introduction to Data Flow
  •  Data Flow Overview 
SQL Server Integration Services Course Content
  • 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
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
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
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
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
SSIS Package Deployment
  •  Configurations and deployment 
  •  The deployment utility 
  •  Deployment options 
  •  Deployment security 
  •  Executing packages – DTExec and DTExecUI
SSIS Package Management
  •  The SSIS Service 
  • Managing packages with DTUtil 
  • Managing packages with SQL Server Management Studio
  • Scheduling packages with SQL Server Agent
SSIS 2012 new features
  •  CDC (Change data capture) control Flow & Dataflow task 
  •  SSIS Catalog Reports (Dashboard)
  •  Package upgrades from legacy to SSIS 2012


Introducing SQL Server Reporting Services
  •  Tour of Features 
  •  Reporting Services Architecture 
  •  Reporting Services Terminology
  •  Reporting Services Editions

Creating Reports
  •  Deconstructing Reporting Services 
  •  Shared Data Sources
  •  Creating Reports from Scratch
Calculations and Formatting
  •  Creating Expressions 
  •  Using the Global Collections 
  •  Formatting Items ·  Conditional Formatting
Grouping and Sorting
  •  Creating Groups 
  •  Calculating Totals and Percentages 
  •  Interactive Sorting
  • Creating Drill-Down Reports
Report Parameters
  •  Creating Report Parameters 
  •  Creating Drop-Down Parameters 
  •  Multi-Valued Parameters
  •  Debugging Parameter Issues
Creating Matrix Reports and Charts
  •  Creating a Basic Matrix Report 
  •  Matrix Subtotals 
  •  Creating a Basic Chart 
  •  Exploring the Charting Possibilities
Managing Reporting Services
  •  Deploying Reports and Data Sources 
  •  Exporting Reports and Printing
  •  Using the Web-Based Report Manager
Reporting Services Security
  •  Two Parts to Reporting Services Security 
  •  Securing Access to Reports 
  •  Data Source Security
SQL Server Reporting Services Course Content

Programming Reporting Services
  •  The Many Reporting Services APIs 
  •  Integrating Reporting Services into Applications using URL Access 
  •  Using the Report Viewer Controls

Advanced Reporting Services Programming
  •  Using the Reporting Services Web Service 
  •  Working with Custom Assemblies 

Snapshots and Subscriptions
  •  Caching and Snapshots 
  •  Creating Standard Subscriptions 
  •  Creating Data-Driven Subscriptions
  •  Managing Subscriptions

Ad-Hoc Reporting with Report Builder
  •  The Report Builder Architecture 
  •  Creating Report Model Projects 
  •  Running Report Builder