Business Intelligence Stack

50.1 SSIS (SQL Server Integration Services)

ETL tool built around the Control Flow (tasks, containers, precedence constraints) and the Data Flow (sources → transformations → destinations).

Transformations: Lookup, Derived Column, Conditional Split, Merge Join, Aggregate, Sort, SCD, Data Conversion, Multicast, Union All.

Variables, parameters, expressions, event handlers, logging, checkpoints.

Deployment: SSIS Catalog (SSISDB), environments, project deployment model.

Cloud: Azure Data Factory (with the SSIS Integration Runtime) and Fabric Data Factory.

50.2 SSRS (SQL Server Reporting Services)

Paginated reports: data sources, datasets, parameters, tablix, charts, subreports, drill-down/drill-through.

Report Builder, Report Server web portal, subscriptions (email/file), data-driven subscriptions.

Power BI Report Server is a superset of SSRS. In SQL Server 2025, SSRS is consolidated into Power BI Report Server.

50.3 SSAS (SQL Server Analysis Services)

Multidimensional (cubes, MDX) vs Tabular (VertiPaq in-memory, DAX).

Concepts: measures, dimensions, hierarchies, KPIs, partitions, processing, roles.

50.4 Power BI with SQL Server

Import vs DirectQuery vs composite models. Use the on-premises data gateway for on-prem sources.

Star schema modeling, query folding, performance considerations.

50.5 Data Warehousing in SQL Server

Columnstore fact tables, partition switching, staging patterns, incremental loads (watermarks, CDC), surrogate key pipelines, SCD Type 2 with MERGE.