Implementing a Data Warehouse with Microsoft SQL Server

Course Description

This 8 hours instructor-led course provides participants the knowledge and skills to implement a data warehouse using Microsoft Business Intelligence Product Suite. Participants will learn how to do the requirement gathering for DW/BI, build a relational data warehouse, implement an ETL solution and consume data using models.

Audience

The primary audience for this course is individuals who need to develop and implement data warehouses using Microsoft SQL Server Product Suite. They will need to focus on hands-on work making logical data warehouse, physical data warehouse, loading the data warehouse and making models for analysis.

The secondary audience of this course is individuals who develop applications that integrate with databases and keen to data warehousing and business intelligence.

Participant Prerequisites

This course requires that you meet the following prerequisites:

• Working knowledge of Microsoft development platform

• Working knowledge of Microsoft SQL Server database solutions

• Working knowledge of SQL

• Basic knowledge on Business Intelligence solutions

• Basic knowledge on Microsoft SQL Server Integration Services

Course Objectives

After completing this course, student will be able to:

• Describe what is Data Warehousing and Business Intelligence

• Describe the key elements of a data warehousing solution

• Identify BI requirements and prepare Bus-Matrix

• Implement a logical design for a data warehouse

• Implement a physical design for a data warehouse

• Optimize the data warehousing using Columnstore indexes

• Describe the considerations for implement an ETL solution

• Implement an ETL Solution for loading the data warehouse

• Describe the considerations for implement a data model

• Implement a tabular model

• Consume data using Power BI

Course Outline

The following items describe the outline of the course:

• Introduction to Data Warehousing and Business Intelligence

• Introduction to Kimbal’s concept

• Hands-On – Preparing a Bus-Matrix for given requirements

• Relational Data Warehouse – Considerations and Best Practices

• Introduction to Columnstore indexes

• Hands-On – Creating a data warehouse

• Introduction to Integration Services

• ETL Solutions – Considerations and Best Practices

• Hands-On – Creating an ETL Solution

• Introduction to Analysis Services

• Building dimensions and cube

• Hands-On – Creating a Multidimensional model

• Introduction to BI Client tools

• Hands-On – Consuming data using Power BI

Course Materials

The following materials are included as part of the course.

• A sample database as a source database

• A sample Excel file as a source file

• Printed slides related to the course

Classroom Setup

Each participant must attend the course with following;

• A laptop with Microsoft SQL Server 2016 full installation;

  • Database Engine

  • Integration Services

  • Analysis Services – Tabular mode

  • Power BI Desktop

Speaker : Dinesh Priyankara, MVP - Data Platform, Founder - dinesQL (Pvt) Ltd

Speaker Profile:

Dinesh is an experienced professional and database enthusiast with skills in database management systems and business intelligence. Possessing over 17 years of experience on data related technologies, he does training, consulting, and is a top contributor to the local SQL Server community. In recognition of his contributions and expertise he has demonstrated, he was awarded the Microsoft Most Valuable Professional (MVP) award for SQL Server for 12 consecutive years since 2006.
Dinesh is the Founder/Principal Architect of dinesQL (Pvt) Ltd (http://dinesql.com). In his spare time he runs a blog at http://dinesql.blogspot.com and maintains his speaker profile at http://rateevent/dineshpriyankara