Data Warehouse实战班


Course Introduction

This hands-on course provides the knowledge and skills needed to design and build a data warehouse using North America leading-edge products. The most important point is that you will gain practical Data warehousing and BI project experience working with SQLServer, and complete Microsoft BI solution including SSIS, SSRS and SSAS.

Your will learn How to:

    • Design and build comprehensive Data Warehousing, big data warehouse and data lake solutions
    • Implement special data modeling techniques and define metadata
    • Analyze business requirements
    • Develop a data model & generating a multidimensional database using Erwin.
    • Generate and maintain database objects such as Tables, Primary Key, Foreign Key, Unique Key, Indexes,

    Views, and Triggers etc. using Erwin.

    • Define Extraction, transformation and loading (ETL) process and use ETL tool – DataStage to load data into

    data warehouse

    • Develop comprehensive ETL project by using Microsoft SSIS  Develop Business Intelligence (BI)

    solutions with SSRS and SSAS.

    • Extend data mining by using Microsoft enterprise miner
    • Microsoft PowerBI data analysis

Who should attend:

This course is valuable for Data Warehouse Administrators / Designers / Developers / Managers, System Analyst, System Engineers, Consultants, ETL Developers, and others involved in building data warehouse. Basic knowledge of Oracle RDBMS is helpful.

Upon completion of this course, you should be able to:

  • Thorough understanding with Data Warehouse philosophy, purpose and technology.
  • Understanding Big Data warehouse and data lake architecture
  • Understanding with D/W development life cycle, including define business requirements, system analysis,

system design, construction and deployment.

  • Data Warehousing experience in Data Architecture, Data Modeling and logical and physical Database


  • Developing a multidimensional data model and generating Oracle database structures using ERwin
  • Build indexes and partitioning tables to improve the performance
  • Defining, managing and central control enterprise reference data, and metadata
  • Design ETL project by using Microsoft SQLServer Integration Service (SSIS)
  • Design ETL meta data management database
  • Optimizing query performance
  • Develop various kinds of business reports by using Microsoft Reports Server (SSRS) and to do online

analytical process by using Microsoft Analysis Service (SSAS.)

  • Defining dimensions, hierarchies, measures, and populating data cubes using SSAS.
  • Develop PowerBI model and comprehensive BI dashboard

2. Course Content

Module 1: Introduction-- The role of a data warehouse

  • What is DW (Data Warehouse) and BDW (big data warehouse)
  • Key elements of BDW
  • BDW environments
  • What data can be stored in Data Warehouse
  • Uses and objectives of a data warehouse
  • Glossary of data warehousing terms
  • Data warehousing conceptual architecture
  • Central Reference Database
  • Data Warehouse project approaches
  • Information access and analysis tools

Module 2: Data Warehouse development lifecycle

  • Planning A Data Warehouse
  • Business Requirements
  • Analysis – Functional Specifications
  • Design – System Design Specifications
  • Construction – Coding / Testing
  • Deployment – Implementation
  • Post Review & Enhancement

Module 3: Data Modeling fundamental

  • Data Modeling Concepts
    • Understanding Data Modeling concepts
    • Understanding Entities
    • Understanding Attributes
    • Understanding Relationships

    Introducing data modeling tool ERwin

    • Getting started with Erwin
    • Introducing Erwin Menus and Tools
    • Reverse Engineering and Report Generation in Erwin
    • Gathering information requirements, Metadata, and Business Rules

    Creating the Logical Model

    • Developing the Logical Data Model
    • Building Logical Relationships
    • Organizing and Enhancing the logical data model
    • Reviewing the logical data model
    • Delivering the logical data model
    • Advanced features for the logical model

    Creating the Physical Model

    • Developing the physical model
    • Building the physical model in Erwin
    • Building Physical Relationships
    • Reviewing the Physical data Model
    • Delivering the physical data model
    • Advanced features for the physical model

Module 4: Data Warehouse Design

  • Data Warehouse E-R modeling techniques
  • Dimensional modeling
  • Star schemas benefit
  • Star schema
  • Snowflake schema
  • Constellation schema design
  • Implementing database partitioning
  • Dimensional vs. traditional approaches
  • Data Mart Vs. Data Warehouse
  • Meta data introduction
  • Define the metadata and metadata model sample
  • Case study – dimensional modeling
  • Design a Data Warehouse Logical model sample
  • Design a Data Warehouse Physical model sample
  • Create a Data Warehouse Database in SQL-Server RDBMS

Module 5: Data Warehouse Data ETL

  • Defining the ETL process
  • Extracting source data
  • Transferring data to target D/W Server
  • Implementing data validation rules
  • Mapping data sources to targets
  • Maintaining referential integrity
  • Executing the ETL processes
  • Parallel operations for load processing
  • Central Reference Database Implementation
  • Central Reference Data Maintenance and interface
  • Central Reference and Data Warehouse data synchronization
  • Data Warehouse DB data Load and data operation

Module 6: Microsoft ETL SSIS project

  • Design and Develop data warehouse ETL packages
  • Design and Develop incremental ETL process packages
  • Develop dimensional model ETL paakges

Module 7: Business Reporting by using SSRS

  • Grouping Data
  • Drill-Down Reports
  • Drill-Through Reports
  • Recursive Relationships
  • Sub reports
  • Designing Matrix Reports

Module 8: OLAP with SSAS

  • Design and develop SSAS dimension
  • Design and develop SSAS cube
  • Design and develop KPI
  • Design and develop many to many relationship dimension

Module 9: Create dashboard based on DW, BDW and PowerBI

  • Design and develop PowerBI model
  • Retrieve data from multiple of data source including local SQL-Server, web and Cloud platform
  • Design comprehensive PowerBI dashboards

Module 10: Introduction to Data Lakes

  • Creating a Successful Data Lake
  • Roadmap to Data Lake Success
  • Data Lake Architectures
  • From Data Ponds/Big Data Warehouses to Data Lakes
  • Industry-Specific Perspectives
Join Now

负责人:Kevin Wang
联系方式:416-665-1888 Ext:1
微信号:mariasunvic12 mariasunvic12