Data Warehouse Foundation
（更多资料和具体参加方法）
1. 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 Oracle and SQLServer, Erwin, DataStage and complete Microsoft BI solution including SSIS, SSRS and SSAS.
You Will Learn How To:
Design and build comprehensive Oracle Data Warehousing 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
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.
2. Course Objectives
Upon completion of this course, you should be able to:
Thorough understanding with Data Warehouse philosophy, purpose and technology.
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 Design
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
Extracting, transforming and loading (ETL) data to Data Warehouse using ETL tool DataStage
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.
3. Course Content
Module 1: Introduction-- The role of a data warehouse
· What is Data Warehouse
· 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
· Information access and analysis tools
· The vision of Data Warehousing
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 With Erwin
· Data Modeling Concepts
· Understanding Data Modeling concepts
· Understanding Entities
· Understanding Attributes
· Understanding Relationships
· Introducing 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 ORACLE RDBMS
Module 5: Data Warehouse Data Population
· Defining the ETL process
· Extracting source data
· Transferring data to target D/W Server
· Loading data into staging area using SQL*Loader and database links
· 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: Indexing the Data Warehouse
· Index approaches
· Index types
· Single column or composite indexes
· Indexing dimension tables
· Indexing fact tables
· Optimizing the index creation process
· Index creation and maintenance guidelines
Module 7: Advanced SQL
· SET operators
· Oracle DATETIME functions
· ROLLUP, CUBE operators and GROUPING SETS
· Hierarchical queries
· Correlated subqueries
· Multitable inserts
· External Tables
· Module 8: ETL Tool DataStage
· Understanding the role of ETL Tool, such as DataStage
· DataStage Architecture
· DataStage Components
· Using Data Stage Client
o DataStage Administrator
o DataStage Manager
o DataStage Desighner
o DataStage Director
· Developing ETL Project using DataStage
· Running and Testing DataStage Jobs
Module 9: Microsoft ETL SSIS project
· The introduction of SSIS Tool.
· SSIS Tasks
· Containers and Data Flow
· Creating an End to End Package
· Advanced tasks and Transformations
· Advanced Tasks and Transformations
· Accessing heterogeneous Data
· Error And event Handling
· Design ETL Solution for data migration project
Module 10: Business Reporting by using SSRS
· Reporting Service Architecture
· Business Intelligence Defined
· Reporting with Relation Data (OLTP)
· Reporting with Data Warehouses
· Reporting with Multidimensional Sata
· The Reporting Lifecycle
· Designing Reports
· Designing Data Access
· Parameter Concepts
· Report Parameters
· Basing a Parameter on a Query
Module 11: Advanced Report Design
· Grouping Data
· Drill-Down Reports
· Drill-Through Reports
· Recursive Relationships
· Sub-reports
· Designing Matrix Reports
Module 12: OLAP with SSAS
· Introduction of Multidimensional Database
· online Analytical processing
· Creating a Multidimensional database
· Processing a Multidimensional database
· Queering a Multidimensional database
· Administering a Multidimensional database
· processing Cubes
· Backup and Restore
· Summary
（更多资料和具体参加方法）
