SQL Server

THIS COURSE HAS NOW RETIRED. PLEASE SEE THE SUPPLEMENTARY INFORMATION FOR DETAILS OF ALTERNATIVE COURSES.
This course is intended for people who build and use an effective data warehousing application with SQL 7.0.

1. Introduction to Data Warehousing
2. Designing a Data Warehousing System
3. Populating a Data Warehouse
4. Creating Cubes
5. Analysing Cube Data Using Clients
6. Multi-Dimensional Expressions (MDX)
7. Building OLAP Clients
8. Building Applications by Using Microsoft English Query
9. Maintaining a SQL Server Data Warehouse
10. Managing a SQL Server Data Warehouse

---

Supplementary Information This course has now retired and is no longer available. We suggest you look at Courses 2780: Maintaining a Microsoft SQL Server 2005 Database and 6231: Maintaining a Microsoft SQL Server 2008 Database.

---

Module 1: Introduction to Data Warehousing

• What is data warehousing?
• What is a data mart?
• Why build a data warehouse?
• What are some business scenarios that would require a data warehouse?

Skills

• 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.
  

top

---

Module 2: Designing a Data Warehousing System

• Business analysis process
• Data warehousing system
• Modelling a data warehouse
• Choosing the grain
• Establishing dimensions
• Establishing a fact table
• Implementing a star schema
• Lab: Designing a star schema
• Lab: Implementing a star schema

Skills

• 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.
  

top

---

Module 3: Populating a Data Warehouse

• Process overview
• Methods of populating a data warehouse
• Tools for populating a data warehouse
• Populating a data warehouse by using DTS
• Lab: Populating a data warehouse

Skills

• 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).
  

top

---

Module 4: Creating Cubes

• 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

• 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.
  

top

---

Module 5: Analysing Cube Data Using Clients

• Concepts of data analysis
• Analysing local cubes
• Analysing data using the Web
• Tools for analysing data
• Lab: Browsing cube data using Office 2000

Skills

• Describe basic data analysis concepts.
  
• Describe how to analyse cube data when disconnected from the network.
  
• Use OLAP Manager and Microsoft Excel to analyse data from a cube.
  

top

---

Module 6: Multi-Dimensional Expressions (MDX)

• What Is MDX?
• Parts of an MDX statement
• Writing an MDX query
• Lab: Writing MDX statements

Skills

• 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.
  

top

---

Module 7: Building OLAP Clients

• 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

• 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.
  

top

---

Module 8: Building Applications by Using Microsoft English Query

• 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

• 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.
  

top

---

Module 9: Maintaining a SQL Server Data Warehouse

• 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

• 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.
  

top

---

Module 10: Managing a SQL Server Data Warehouse

• 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

• 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.
  

top

---

Before attending this course, students must have the following pre-requisites:

• Familiarity with Microsoft SQL Server 7.0.
• Course 832, System Administration for Microsoft SQL Server 7.0, and course [[{http://www.F1Comp.Co.UK/sql/833.html}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 or Microsoft JScript development software).
• Understanding of basic database design, administration, and implementation concepts.

This course provides students with the technical skills required to build an effective data warehouse application with the Microsoft SQL 7.0 Server relational database management system.

This course has now retired and is no longer available. We suggest you look at Courses 2780: Maintaining a Microsoft SQL Server 2005 Database and 6231: Maintaining a Microsoft SQL Server 2008 Database.