Implementation and operation of SQL Server data warehouses

During the course, participants will learn how to build, create, populate, configure and manage SQL Server data warehouses using SQL Server Integration Services, SQL Server Data Quality Services and SQL Server Master Data Services. The course also covers the use of Azure SQL Data Warehouse.

The course is recommended for operators, administrators, database administrators, business intelligence professionals, BI designers who will be responsible for implementing, managing and supporting data warehouses, data transformation and data upload services, business intelligence solutions.

• Introduction to data warehouses: purpose and operation of data warehouse; data warehouse architecture and building blocks, basic concepts; overview of data warehouse solutions (data sources, ETL processes).
• Hardware requirements for building data warehouses: challenges, requirements, reference architectures, recommendations.
• Design and implementation of data warehouse: logical and physical design; overview and characteristics of different schemas (star, snowflake); overview and application of time dimension table.
• Use of columnstore indexes: overview, operation; creation and use of columnstore indexes; design considerations for columnstore indexes.
• Implementing a cloud-based data warehouse: overview of Azure SQL Data Warehouse, benefits; implementing and developing an Azure-based SQL data warehouse; migrating data to Azure SQL Data Warehouse platform.
• Design and implement a data warehouse schema: introduction to data transformation and data transfer (ETL) processes; overview of SQL Server 2012 Integration Services (SSIS); mapping data sources; concept of SSIS packages; implementing data flow, data transfer and data transformation.
• Implementing Control Flow in SSIS package: introduction, basic concepts; creating dynamic packages; tasks and precendence; variables and parameters; using containers; managing consistency, using transactions, using checkpoints.
• SSIS packet error detection and repair: overview of typical errors; logging and analysis of SSIS packet events; use of error handlers; handling errors in data flow.
• Application of the ETL incremental process: overview, basic concepts, operation; unpacking modified data, change tracking; loading modified data, inserting dimensional data, updating; use of temporary tables.
• Ensuring data quality: concepts, purpose of data cleansing; overview of SQL 2016 Data Quality Services (DQS); using DQS to clean data, creating a DQS knowledge base, using DQS projects, using DQS in SSIS package; filtering data duplications, creating matching policies, using DQS project to match data.
• Using Master Data Services (MDS): basic concepts, objectives, operation; creating and modifying MDS model, loading data into MDS; enforcing business rules, publishing MDS data, creating Master Data Hub.
• Extending SSIS capabilities: using custom components in SSIS; creating and using scripts.
• Deploying and configuring SSIS packages: overview of deployment; building and deploying SSIS projects, creating SSIS catalogue, preparing appropriate environment for SSIS solution; running SSIS packages using SQL Server Management Studio; scheduling SSIS packages using SQL Server Agent.
• Managing and analysing data in a data warehouse: introduction to data analysis and reporting; overview of Reporting Services and Analysis Services; data analysis capabilities using Azure SQL Data Warehouse.

Windows Server 2012 or 2016 operator basics, relational database management and SQL query and SQL Server operation basics. Completion of or knowledge of course 20461 or 20761 and 10775 or 20762. As the course materials are in English, basic English language skills at document reading level are required. The lectures will be given in Hungarian.