MSBI course content


  • SSAS
  • SSIS
  • SSRS

SQL Server Analysis Services Course Content

SSAS:

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

MDX
  • 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

SSIS:

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

SSRS:

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