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.

• Familiarity with Microsoft SQL Server 7.0.
• Course 832, System Administration for Microsoft SQL Server 7.0, and Course 833, Implementing a Database on Microsoft SQL Server 7.0.
• Knowledge of Transact-SQL usage in the development of online transaction processing (OLTP) systems.
• Basic understanding of programming principles (especially experience with a scripting language like Microsoft Visual Basic^® Scripting Edition).
• Understanding of basic database design, administration, and implementation concepts.

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.