SQL BI Dataware house

Course Overview

A data warehouse is used for the purpose of providing and analyzing information from a number of sources including inside as well as outside of the organization. The course introduces students to the terms and architecture of data warehousing, dimensional modeling, data sources, Extract-Transform-Load (ETL) processes and subsystems, and schemas. Moreover, Online Analytical Processing (OLAP) and Online Transactional Processing (OLTP) will also be discussed. A few case studies will be used to help students understand the importance as well the implementation of data warehouses in a corporate environment.

The course has been divided into two distinct areas. The first part, or basics, is to provide the participants with theoretical knowledge on what a data warehouse is and how does it work.

Course Objectives

  • Introduction of data warehouses, and differences and limitation of Operational Databases, differences between OLAP and OLTP and schema types of Data Warehouses
  • Understanding the four components of a data warehouse environment. A case study will be done to help understand fact table and dimensional modeling
  • Steps in dimensional design process. Introduction of Star schema, Fact-less fact table, degenerate dimensions, snow flaking and surrogate keys.
  • Implications of value chain, periodic snapshots, semi-additive facts, Bus matrix, Role playing, Junk dimensions, outrigger and mini-dimensions and grain of fact tables.
  • Understanding the concepts of Role playing, Junk dimensions' outrigger and mini-dimensions. The grain of fact tables will also be discussed in-depth because of its importance with the final assignment.
  • 10 requirements for ETL, process steps and sub-systems 1-3 Extracting process, and 4-8 Cleaning and conforming process
  • Sub-systems 9-21 the cleaning and conforming process, sub-system 21-34 supporting the management
  • Purpose of OLAP, relationship between OLAP and data warehousing, representing multi-dimensional data, categories and comparisons of OLAP tools.
  • Main categories of OLAP tools, OLAP extensions in SQL,
  • Design and implement fact tables and dimensions
  • Managing dataflow and extracting data
  • Transforming data into acceptable formats for processing
  • Configure and deploy SSIS
  • Manage Enterprise data

Course Content

SECTION 1

  • Introduction, general information and key terms of the subject.
  • Dimensional modeling and architecture and case study
  • Dimensional modeling and case study
  • Value chains, fact tables, and dimensions.
  • Role playing, Junk dimensions, outrigger and mini-dimensions and grain of fact tables
  • ETL requirements, process steps, and subsystems.
  • ETL sub-systems
  • OLAP
  • OLAP with extensions in SQL

SECTION 2

  • Schema Deployment
  • Data Extraction
  • Data Transformation
  • Deployment
  • Management

Course Methodology

The training is going to be highly interactive combination of lectures, group discussions, questionnaires, individual reflections, role plays, simulations and videos.

Target Audience

This course has been designed for IT/Business professionals who have an understanding of a Database Management System and are looking for further analytical models.

Duration

5 Days (08:30–14:30) with appropriate breaks for tea/refreshments and lunch.

Related Courses

View All Courses