SQL Server 2000 Data Warehousing

SQL Server 2000 Data Warehousing

Full Description

This course will teach you the ins and outs of in Micorsoft SQL Server 2000 by providing the knowledge and skills necessary to understand the underlying design and principles of a data warehouse. You will learn to plan and build a data warehouse, learn how to populate data and to transform data.

Outcomes:

Upon successful completion students will be able to: Analyzing Business Requirements, Defining the Technical Architecture for a Solution, Developing the Logical Design, Derive Physical Design, Create Data Services and Implement a physical data warehouse and OLAP services

Assessment:

You will be able to measures your ability to design and implement data warehouse solutions by using Microsoft SQL Server 2000 with OLAP Services and Data Transformation Services (DTS) installed, by pre and post course assesments in the form of quizzes and tests.

Course Information (see above or below lessons, outlines, activities, etc.) Instructor: Michael Lee is a Senior Trainer and Consultant with Saqqara Technology, Inc, a Microsoft Certified Partner and has over 10 years technology training experience and has published numerous books about SQL Server and Client/Server development. He is a popular speaker and presenter, and is in demand at client sites and conferences all over the US and Europe.



Course Outline:

Lesson 1: Introduction & Planning Introduction Topic Overview Transactional vs. Analytical Data Systems Data Warehouse Defined Data Warehouses vs. Data Marts/OLAP Dimensional Modeling & Planning Entity-Relationship Modeling vs. Dimensional Modeling Elements of the Dimensional Model The Role of User Requirements Identifying Source Data Implementing the Data Warehouse Bus Planning Facts What is a fact? Measures Planning Dimensions Planning Dimension Tables Planning a Hierarchy Special Dimensions Lesson 2: Data Marts Building a Data Mart Creating the Database Creating the Fact Table Creating Dimension Tables Creating Constraints and Indexes Loading the Data Mart Using Staging Tables Using Transact SQL for Data Migration Loading Data using Data Transformation Services (DTS) Advanced DTS Issues Evaluating a Typical Load Scenario Maintaining the Data Mart Backup and Restoration Considerations Handling Changing Facts Handling Changing Dimensions Securing the Data Mart Lesson 3: Introduction to OLAP, Cube and Dimension Introduction to OLAP OLAP Concepts Analysis Services Architecture Cube Basics Cube Basics & Measures Dimensions & Hierarchies Aggregations Using the Analysis Manager Creating Dimensions Defining Terms Creating Dimensions with the Dimension Wizard Creating Time Dimensions Using the Dimension Editor Basic Dimension Properties Using Member Properties Virtual Dimensions Multiple Hierarchies Creating Cubes Selecting Facts & Measures Choosing Dimensions Creating Calculated Measures Storage Models Processing Dimensions Processing Cubes Using Cube Properties Lesson 4: Cubes and Special Features Managing Cube Partitions Partitions Concepts Creating Cube Partitions Merging Partitions Optimizing Cubes Optimizing Aggregations with Storage Models Usage-Based Optimizations Tuning the Analysis Server Special Features Using Drillthrough Creating and Using Actions Connecting Clients Using OLE DB Analysis Application Architecture Using Excel as an Analysis Server Client Overview of Client Interfaces Introduction to Data Mining Data Mining Basics Creating Data Mining Models Lesson 5: MDX MDX Concepts Overview What is MDX? Role of MDX in Application Architecture Creating Calculated Members Within Cubes Using MDX in Client Applications MDX Examples Creating MDX Expressions Understanding the Hierarchy Using Brackets Identifying Members Qualifying Sets of Members Creating Calculated Members Interacting with Cubes Anatomy of an MDX Query Basic MDX Queries Tuple Expressions Performing Aggregations Calculating Data in an MDX Query Using Aggregate Functions Using Time Set Functions Using MDX with ADOMD/div> Connecting to an Analysis Server Executing an MDX Query Manipulating Results

Contact Hours: 24