Duration 5 days – 35 hrs
Overview
This intensive 5-day course is designed for professionals seeking advanced-level skills in Microsoft SQL Server’s BI stack: SSRS (SQL Server Reporting Services), SSIS (SQL Server Integration Services), and SSAS (SQL Server Analysis Services). Through hands-on labs and real-world scenarios, participants will master the techniques needed to build, deploy, and manage enterprise-grade data integration pipelines, analytical cubes, and interactive reports. The training emphasizes performance optimization, security, best practices, and enterprise deployment models.
Objectives
- Design and optimize complex ETL packages using SSIS with error handling and data flow transformations.
- Implement dynamic configurations, custom scripts, and incremental data loads in SSIS.
- Create advanced SSRS reports using parameters, expressions, custom code, sub-reports, and drill-throughs.
- Secure SSRS reports and integrate them into web portals or enterprise systems.
- Design and deploy advanced multidimensional and tabular data models in SSAS.
- Write efficient MDX and DAX queries for business analysis.
- Monitor and tune performance across the BI stack.
- Implement BI solutions that support decision-making at scale.
Audience
- Data Engineers
- Business Intelligence (BI) Developers
- SQL Developers and DBAs
- Platform Support Specialists
- Report Developers and Data Analysts
- IT Professionals responsible for enterprise reporting and analytics
Pre- requisites
- Working knowledge of SQL and Microsoft SQL Server
- Basic experience with SSRS, SSIS, and SSAS
- Familiarity with data warehousing concepts is recommended
Course Content
Day 1–2: Advanced SSIS (Integration Services)
Module 1: Advanced ETL Architecture
- ETL strategies and patterns
- Incremental loads and CDC
- Data flow tuning and buffering
Module 2: Control Flow Enhancements
- ForEach loops, Sequence containers, and Event handling
- Dynamic package configuration with variables and parameters
- Package logging and checkpoints
Module 3: Advanced Data Flow Techniques
- Lookup, Merge, Conditional Split, Fuzzy Lookup
- Custom components and script tasks
- Handling data anomalies and cleansing
Module 4: SSIS Package Deployment and Security
- Project deployment model vs. package deployment model
- Environments, parameters, and SSIS Catalog
- Logging, auditing, and package protection levels
Hands-on Labs
- Create incremental load pipelines
- Use script components for custom transformations
- Deploy and monitor SSIS packages using SSISDB
Day 3: Advanced SSRS (Reporting Services)
Module 1: Complex Report Design
- Drill-down, drill-through, sub-reports, linked reports
- Dynamic sorting, grouping, and matrix reports
- Expressions and custom code blocks
Module 2: Parameters and User Interactivity
- Cascading parameters
- Multi-value and dynamic parameters
- Interactive filters and actions
Module 3: Report Publishing and Delivery
- Report Manager and Report Builder
- Report subscriptions (email, file share)
- Exporting reports to Excel, PDF, CSV, and more
Module 4: SSRS Security and Administration
- Roles and permission management
- Integrating reports into web apps or SharePoint
- Performance tuning for large datasets
Hands-on Labs
- Create a sales dashboard with drill-through reports
- Configure report subscriptions
- Secure report folders and items
Day 4–5: Advanced SSAS (Analysis Services)
Module 1: Designing Multidimensional Models (Cubes)
- Star vs. Snowflake schemas
- Measures, dimensions, hierarchies
- Aggregations and partitions
Module 2: Introduction to Tabular Models
- In-memory analytics using VertiPaq
- Relationships, calculated columns, KPIs
- Data modeling best practices
Module 3: Advanced MDX and DAX Queries
- Calculated members and named sets in MDX
- Time intelligence, filters, and ranking in DAX
- Writing KPIs and business metrics
Module 4: Deployment, Processing, and Security
- Cube processing strategies
- Role-based security and perspectives
- Performance optimization and caching
Hands-on Labs
- Build a sales cube and deploy to SSAS
- Create tabular model KPIs with DAX
- Query cubes using Excel PivotTables and Power BI
