This course describes how to implement a data warehouse platform to support a BI solution. Students will learn how to create a data warehouse with microsoft SQL Server 2014, ETL with SQL Server Integration Services, implement and validate data cleansing and quality services data from SQL Server and SQL Server Master Data Services.
This course is aimed at professionals in the database they need to create and support a data storage solution. Main responsibilities include:
Implementation of a data warehouse.
The development of SSIS packages for data extraction, transformation and loading.
The data integrity by using Master Data Services.
Data cleaning services using data quality.
Module 1: Introduction to Data Warehousing
This module provides an introduction to the key components of a solution of data storage and high-level considerations to keep in mind when embarking on a project for data storage.Lessons
• Summary of Data Warehousing
• Considerations for a data warehouse solution
Module 2: Planning Data Warehouse infrastructure
This module discusses considerations for selecting hardware and distribute SQL Server services through servers.
• Considerations for Data Warehouse infrastructure
• Hardware Planning Datastore
Module 3: Design and implementation of a Data Warehouse
This module describes the key considerations for the logical design of a data warehouse and then discusses best practices for its physical implementation.
• Abstract design of data warehouse
• Design dimension tables
• Design fact tables
• Physical design of a Data Warehouse
Module 4: Creating an ETL solution with SQL
This module discusses the considerations for the implementation of an ETL process and then focuses on microsoft SQL Server Integration Services (SSIS) as a platform to create ETL solutions.
• Introduction to ETL with SQL
• Browse data sources
• Application dataflow
Module 5: Implementing Control Flow in an SSIS package
This module describes how to implement ETL solutions that combine multiple tasks and workflow logic.
• Introduction to Flow Control
• Creating dynamic packages
• Consistency Management
Module 6: Debugging and troubleshooting SSIS packages
This module describes how to debug packages to find the cause of errors that occur during execution.logging functionality built in SSIS you can use to log events to solve is then analyzed. Finally, the module describes common approaches to management errors in the control flow and data flow.
• Debugging an SSIS package
• Registration SSIS package events
• Error handling in an SSIS package
Module 7: Implementing a data mining solution
This module describes the techniques you can use to deploy an update process of incremental data warehouse.
• Planning data extraction
• Change Data Extraction
Module 8: Loading data into a data warehouse
This module describes the techniques you can use to implement the process of loading data store.
• Planning data loads
• Using SSIS for incremental loads
• Using techniques charging Transact-SQL
Module 9: Implementing data quality
This module introduces microsoft SQL Server Data Quality Services (DQS) and describes how you can use for cleaning and Deduplicate data.
• Introduction to data quality
• With data quality services to clean data
• With data quality services to clean data
Módulo 10: Master Data Services
Master Data Services provides a way for organizations to standardize and improve data quality, consistency and reliability of data that guides the key business decisions. This module provides services Master data and explains the benefits of its use.
• Introduction to Master Data Services
• Implement a model of Master Data Services
• Master data management
• Creating a primary datacenter
Módulo 11: Extiende SQL Server Integration Services
This module describes the techniques you can use to extend SSIS. The module is not designed to be a comprehensive guide to developing custom solutions SSIS, but to provide an understanding of the basic steps required to use scripts and custom components an ETL process based on SSIS.
• Usar Scripts en SSIS
• Using custom components in SSIS
Module 12: Deploying and configuring SSIS packages
In this module, students will learn how to deploy packages and their dependencies to a server and how to manage and supervise the execution of deployed packages.
• SSIS Deployment Summary
• Deploying SSIS projects
• SSIS package execution planning
Module 13: Consumption data in a data warehouse
This module introduces solutions of business intelligence (BI) and describes how you can use a data warehouse as the foundation of the company and BI self.
• Introduction to Business Intelligence
• Enterprise Business Intelligence
• Self-service BI and Big Data