Implementing a Data Warehouse with Microsoft SQL Server (M20463)

Install data warehouse in an organisation using SQL Server

For supporting Business Intelligence solutions learn to implement data warehouse
Maintain quality and integrity of data using SQL Server Data Quality Services
Understand about SSIS packages and how to load, extract and transform information from it
Course is developed by well qualified, and Microsoft certified trainers
Gain the certification and accomplish your career goals
Pentagon Training offers this course at an affordable price

As the size of databases is growing day by day, we need to retrieve data as fast as possible and write it back at same speed. For this, companies need to make sure that databases are working smoothly. For managing different types of applications and databases, nowadays companies make use of SQL Server. Companies chose to use SQL Server over other popular database systems like Oracle or DB2 due to its excellent features. SQL Server provide features like high availability, better transactional throughput and excellent performance. Proper implementation of SQL Server is required for excellent performance.

In this Pentagon Training’s Implementing a Data Warehouse with Microsoft SQL Server (M20463) course delegates will learn how to create data warehouse using Microsoft SQL Server and implementing it with SQL Server Integration services. Get the complete technical skills required to install data warehouse using SQL Server 2014.

Who should take this course

The intended audience for this course is database professionals who want to become Business Intelligence developers.

Database developers are required to focus implementing a data warehouse, ETL and cleansing data.

Primary responsibilities of database administrator include:

Implementing data warehouse
Develop SSIS packages for data loading, transformation and extraction.
Enforce data integrity using Master Data Services.
Cleanse data using Data Quality Services.

Prerequisites

For attending this course, delegates must have at least three years of experience of working with relational database management system.

Knowledge of designing normalised databases, creating tables and their relationships, querying with Transact-SQL and basic programming constructs like branching and looping is required for attending this course.

Understand concepts and architecture of data warehouse.
For Data Warehouse, learn to select suitable hardware platform.
Effectively design and implement a data warehouse.
Understand to implement data flow and control flow in SSIS package.
Learn to debug and troubleshoot SSIS packages.
Implement SSIS solution that supports incremental data warehouse loads and data extraction.
Using Microsoft Data quality services cleanse the data.
Enforce data integrity using Master Data Services (MDS).
Learn to extend SSIS with custom components and scripts.
Understand to configure and deploy SSIS packages.
Learn how Business Intelligence solutions consume data in the data warehouse.

Tutor Support

Key Learning Points

Exam(s) included

Certificate

Course Overview

In this Pentagon Training’s Implementing a Data Warehouse with Microsoft SQL Server (M20463), course get the knowledge and skills required to set up and implement data warehouse using SQL Server in an organisation. Also, learn basic principles of data warehousing, data extraction, using master data services and creating ETL solution. Find out how to cleanse and validate data using SQL Server master data and Quality Services.

Course Content

Introduction to data warehousing

In this module, understand key components of data warehousing and other high-level considerations one must take into account when undertaking data warehousing project.

Introduction to data warehousing
Considerations for data warehousing solution

Planning data warehouse infrastructure

Learn what to consider for selecting hardware and distributing SQL Server facilities across servers.

Considerations for Data Warehousing Infrastructure
Planning Data Warehouse Hardware

Design and implement data warehouse

In this topic, understand about key factors for the logical design of data warehouse and then discuss best practices for physical implementation.

Overview of Data Warehouse design
Design dimension tables
Design fact tables
Physical design for data warehouse

Create an ETL solution with SSIS

In this topic, understand about considerations for implementing ETL process and then focus on Microsoft SQL Server Integration Services (SSIS) for building ETL solutions.

Introduction to ETL with SSIS
Explore data sources
Implement data flow

Implementing Control Flow in an SSIS package

In this module learn how to implement ETL solutions that merge multiple tasks and workflow logic.

Introduction to control flow
Create dynamic packages
Use containers
Manage consistency

Debugging and troubleshooting SSIS packages

In this module understand how you can debug packages to find the reason of errors occurrence during execution. Understand the logging functionality built into SSIS that can be used to log events for troubleshooting purpose. It also describes approaches for handling errors in control flow and data flow.

Debug an SSIS package
Logging SSIS package events
Handle errors in SSIS package

Implementing a Data Extraction Solution

In this module, understand the techniques that can be used to perform an incremental data warehouse refresh.

Plan data extraction
Extract modified data

Loading data into Data Warehouse

In this module understand the techniques used to implement data warehouse load process.

Plan data loads
Use SSIS for incremental loads
Use Transact-SQL loading techniques

Enforce Data Quality

Understand about Microsoft SQL Server Data Quality Services (DQS) and describe how it can be used to cleanse and de-duplicate data.

Overview of Microsoft SQL Server Data Quality Services (DQS)
Cleanse and validate data

Master data Services

In this topic, Master Data Services provide a way for organisations to standardise data and improve its quality, consistency and reliability that helps in key business decisions. It also introduces Master Data Services and its benefits of using it.

Introduction to Master Data Services
Implement Master Data Services model
Manage Master data
Create Master data hub

Extending SQL Server Integration services

In this module understand the techniques to extend SSIS and get awareness of major steps required to use scripts in an ETL process and custom components based on SSIS.

Use scripts in SSIS
Use custom components in SSIS

Deploy and configure SSIS packages

In this module learn how to implement packages and their dependencies to the server. Learn how to manage and monitor the execution of deployed packages effectively.

Overview of SSIS deployment
Deploy SSIS projects
Plan SSIS package execution

Consuming Data in Data Warehouse

This module introduces Business Intelligence solutions that data warehouse can use as a basis for enterprise and self-service BI lessons.

Introduction to business intelligence
Enterprise Business Intelligence
Big data and Self Service BI

About Warwick

Warwick is the region urban of Warwickshire, England. The town dishonesties upon the River Avon, 11 miles (18 km) south of Coventry and fair west of Lamington Spa and Whitnash with which it is touched. At the 2011 United Kingdom review, it had a populace of 31,345.

There was human action at Warwick as early as the Neolithic period, and continuous occupancy since the 6th century. It was a Saxon burh in the 9th century, Warwick Castle was recognised in 1068 as part of the Norman conquest of England. Warwick School rights to be the eldest boys' school in the country. The earldom of Warwick was formed in 1088, and the early restrained the town in the feudal period and constructed city walls, of which Eastgate and Westgate endure. The castle industrial into a stone stronghold and then a country house and is today a general tourist attraction.

Governance:

Population growth has led to Warwick flattering combined to its larger neighbouring town Lamington Spa with which its procedures a small city. The two cities are now along with Kenilworth and Whitnash. These are managed as part of Warwick District. Warwickshire County Council leftovers based in Warwick itself.

Warwick is signified in Parliament as part of the Warwick and Leamington electorate. It has been detained by the Conservative Party since the 2010 general election; since then, Chris White has been the constituency's Member of Assembly. From the 1945 general election until 1997 the electorate elected a Traditional MP. In 1997 a Labour MP was chosen and retained the seat until 2010 when White was chosen.

Geography:

Antiquarian William Dugdale wrote in the 17th century that Warwick was stand up upon a rocky climb from every side, and in a dry and fertile soil, consuming rich and pleasant fields on the south part and woodland on the north. Two issues have pretentious Warwick's constructed environment: the Great Fire of 1694 and the lack of industrialisation. The fire demolished much of the town, and the next transformation was mainly in one style. In the 19th century, when other cities were fast rising during the Industrial Rebellion, Warwick did not know the same development. Part of the reason Warwick did not grow as a centre of the industry was that the town did not lie on significant roads and the River Avon was not manoeuvrable as far as Warwick.

Culture:

Warwick crowds annual centenaries reaching from the Spoken Word to Classical and Modern Music to a Folk Festival and a Victorian Evening, detained in late November or early December. St. Mary's Church crowds a series of Early Music performances, and the Bridge House Theatre hosts the Music-in-Round performances. Warwick Chamber of Trade assistances to endorse the town for companies, inhabitants and traders. The city is also well-known for Warwick Castle, the building of which instigated in 1068. The city centre is also recognised for its significant building and covers a combination of Tudor and 17th-century buildings. In topical years several high-profile national and international businesses have set up large office developments in and around Warwick, notably National Grid plc. And IBM.