Course 1502—Five days—Instructor-led
Introduction
This course provides students with the technical skills required to plan, implement, and
maintain a data warehouse using Microsoft SQL Server™ client/server database
management system version 7.0.
At Course Completion
At the end of the course, students will be able to:
- Describe basic data warehousing concepts.
- Design a data warehousing system, and implement a database designed with a star schema in SQL Server.
- Gather data from primary data sources, transform it, and place it in a SQL Server staging database.
- Create a cube using OLAP Services.
- Analyze cube data using existing client applications.
- Query cubes using MDX.
- Build custom OLAP clients using ADO MD, OLE DB for OLAP, and DSO.
- Query warehouse data using Microsoft English Query.
- Maintain a SQL Server data warehouse.
- Manage a SQL Server data warehouse.
Microsoft Certified Professional Exams
This course will help the student prepare for the following Microsoft Certified Professional exam:
Prerequisites
The course materials, lectures, and lab exercises are in English. To benefit fully
from our instruction, students need an understanding of the English language and completion
of the prerequisites.
Course Materials
The course workbook and lab book are yours to keep.
You will be provided with the following software for use in the classroom:
- Microsoft Windows NT® Server 4.0, Enterprise Edition
- SQL Server 7.0, Enterprise Edition
- Microsoft Office 2000
Course Outline
Day 1
Module 1: Introduction to Data Warehousing
Topics:
What is data warehousing?
Data marts and data warehouses
The data warehousing process
Data in a data warehouse
Skills:
Students will be able
to:
- Describe data warehousing and the reasons for implementing a data warehousing solution.
- Explain the relationship of data marts to a data warehouse.
- Describe the data warehousing process, including its basic elements and the tools that manage it.
- Describe how data is structured in a data warehouse.
Module 2: Installing Windows 98 in a Network Environment
Topics:
Business analysis process
Data warehousing system
Modeling a data warehouse
Choosing the grain
Establishing dimensions
Establishing a fact table
Implementing a star schema
Labs:
Designing a star schema
Implementing a star schema
Skills:
Students will be able
to:
- Identify specific steps involved in analyzing and implementing a data warehousing system.
- Use star and snowflake schema to model a data warehouse or data mart database.
- Design a data warehouse or data mart database.
- Choose an appropriate grain for the fact table.
- Define dimensions and facts.
- Create a data warehouse or data mart database.
Day 2
Module 3: Populating a Data Warehouse
Topics:
Process overview
Methods of populating a data warehouse
Tools for populating a data warehouse
Populating a data warehouse by using DTS
Labs:
Populating a data warehouse
Skills:
Students will be able
to:
- Describe the process of populating a data warehouse.
- Describe several methods of populating a data warehouse.
- Describe the Microsoft SQL Server version 7.0 tools available for populating a data warehouse.
- Populate a data warehouse by using Data Transformation Services (DTS).
Module 4: Creating Cubes
Topics:
Introduction to cubes
Defining cubes
Managing access to cubes
Demonstration: using OLAP manager
Storing cubes
Processing cubes
Customizing cubes
Lab:
Creating and processing cubes
Skills:
Students will be able
to:
- Define a cube by identifying fact tables, defining dimensions, and creating aggregations.
- Establish security protocol for accessing data in cubes.
- Choose an appropriate storage mechanism for a cube, such as relational online analytical processing (ROLAP), multidimensional OLAP (MOLAP), or hybrid OLAP (HOLAP).
- Create cubes with calculated members and create virtual cubes.
Day 3
Module 5: Analyzing Cube Data Using Clients
Topics:
Concepts of data analysis
Analyzing local cubes
Analyzing data using the Web
Tools for analyzing data
Lab:
Browsing cube data using Office 2000
Skills:
Students will be able
to:
- Describe basic data analysis concepts.
- Describe how to analyze cube data when disconnected from the network.
- Use OLAP Manager and Microsoft Excel to analyze data from a cube.
Module 6: Title
Topics:
What Is MDX?
Parts of an MDX statement
Writing an MDX query
Lab:
Writing MDX statements
Skills:
Students will be able
to:
- Describe the function and use of MDX syntax.
- Describe the parts of an MDX (multidimensional expressions) statement.
- Write an MDX statement to query a cube.
Day 4
Module 7: Building OLAP Clients
Topics:
Introducing the OLAP Services architecture
Analyzing existing cube metadata
Creating and populating a cellset
Retrieving data
Creating local cubes
Lab:
Accessing data using ADO MD
Skills:
Students will be able
to:
- Describe Microsoft SQL Server OLAP Services client/server architecture.
- Analyze existing cube metadata by connecting to multidimensional data sources and accessing cube definitions.
- Create and populate a cellset.
- Retrieve data from cellsets and individual cells.
- Create local cubes.
Module 8: Building Applications by Using Microsoft English Query
Topics:
Introduction to English Query
Database normalization requirements
Creating an English Query application
Designing an English Query application for multidimensional databases
Deploying an English Query application
Lab:
Building applications by using English Query
Skills:
Students will be able
to:
- Design a Microsoft English Query application.
- Create a Microsoft English Query application.
- Test an English Query application.
- Deploy an English Query application in a Web page or in Microsoft Visual Basic or Microsoft Visual C++® applications.
Day 5
Module 9: Maintaining a SQL Server Data Warehouse
Topics:
Developing a maintenance plan
Synchronizing data
Maintaining SQL Server data
Maintaining OLAP Services data
Backing up and restoring databases
Automating administrative tasks
Archiving enterprise data
Lab:
Maintaining a SQL Server data warehouse
Skills:
Students will be able
to:
- Develop a maintenance plan.
- Synchronize Microsoft SQL Server and Microsoft SQL Server OLAP Services data.
- Maintain SQL Server data.
- Maintain data in an online analytical processing (OLAP) environment.
- Back up and restore specific elements in a data warehouse.
- Automate administrative tasks.
- Archive and store data and metadata in Microsoft Repository.
Module 10: Managing a SQL Server Data Warehouse
Topics:
Managing slowly changing dimensions
Optimizing your configuration
Optimizing your server configuration
Optimizing data warehouse performance
Optimizing cube design
Creating partitions
Optimizing based on usage
Lab:
Creating a cube with partitions
Skills:
Students will be able
to:
- Manage changing dimensions.
- Describe optimization strategies for configuring a server.
- Describe optimization strategies for a data warehouse.
- Describe optimization strategies for cubes.
- Create cube partitions.
- Optimize performance by determining appropriate levels of aggregations, indexing, and storage methods.
|
