Implementing Business Logic with MDX in Microsoft SQL Server 2000

Introduction
This course provides students with the knowledgeand skills necessary to use multidimensional expressions (MDX) to add calculatedmembers and other dynamically calculated values to a Microsoft SQL Server 2000Analysis Services cube by using SQL Server 2000.

At Course Completion
At the end of the course, students will be ableto:

Understand and use multidimensional terminology.

Add calculated members to a cube.

Use MDX member functions to navigate within a hierarchy.

Use MDX time functions to compare values over time.

Use MDX set functions in a query statement.

Use functions to manipulate sets of members.

Use functions to aggregate values from a set of members.

Apply MDX to make a cube change dynamically.

Calculate formulas for specific blocks of cells.

Control the order of calculations within a cube.

Apply MDX in common customer and sales applications.

Apply MDX in common financial applications.

Microsoft CertifiedProfessional Exams
This course will help the student prepare for thefollowing Microsoft Certified Professional exam:

There are no exams associated with this course.

Prerequisites
Before attending this course, students must have:

Successfully completed Course 2074, Designing and Implementing OLAP Solutions Using Microsoft SQL Server 2000 , or possess equivalent experience.
The course materials, lectures, and activities arein English. To benefit fully from the instruction, students need anunderstanding of the English language and completion of the prerequisites.

Course Materials
The course materials are yours to keep.
You will be provided with the following softwarefor use in the classroom:

Microsoft SQL Server 2000

Course Outline

Module1: OLAP Review
The following topics are covered in this module:

OLAP Databases

OLAP Definitions

Warehouse Data Flow
At the end of this module, you will be able todemonstrate a working knowledge of OLAP concepts and terminology. Thisincludes:

Understanding the basic elements of online analytical processing (OLAP) databases.

Understanding the basic definitions and structures of an OLAP cube.

Understanding how data flows through an organization's analytical processes.

Module2: MDX Foundations
The following topics are covered in this module:

The Role of MDX

MDX Member Names

Using the Calculated Member Builder

Working with Calculated Members
At the end of this module, you will be able tobuild simple calculated members. This includes:

Understanding the role of MDX in the overall flow of information through a data warehouse and the two specific functions that it performs.

Understand MDX object and member naming conventions.

Using the Calculated Member Builder for building simple calculated members.

Understanding the characteristics of calculated members compared with stored members.

Module3: Using MDX Expressions
The following topics are covered in this module:

Using MDX Expressions

Displaying Member Information

Displaying Family Tree Relatives

Working with Member Properties

Using Conditional Expressions
At the end of this module, you will be able tocreate very simple multidimensional expressions. This includes:

Creating MDX expressions in calculated members and manipulating the expressions by using numeric and string constants.

Displaying information about a member-particulary the name of the member and the name of the level of the member.

Displaying information about the parent or other ancestor of a member.

Displaying the member property associated with a member, and using the value in an arithmetic expression.

Creating and using simple and complex conditional expressions.

Module4: Retrieving Values from a Cube
The following topics are covered in this module:

Understanding Multidimensional Coordinates

Retrieving a Value from a Cube

Percent of Total Calculations

Growth Calculations
At the end of this module, you will be able toapply tuples for retrieving values from a cube and creating practicalcalculations. This includes:

Understanding basic concepts of multidimensional coordinates, including how to retrieve values from a cube by specifying a member from a single dimension.

Defining a tuple and using tuple expressions to retrieve values with two or more dimensions.

Using tuple expressions to retrieve values for calculating percentages of a total.

Using tuple expressions to retrieve values for period-to-period growth percentages.

Module5: Creating Simple MDX Queries
The following topics are covered in this module:

Understanding MDX Query Statements

Creating Simple MDX Query Statements
At the end of this module, you will be able tocreate simple MDX query statements that generate a report. This includes:

Understanding MDX query statement terminology.

Writing simple MDX query statements.

Module6: Creating and Manipulating Sets
The following topics are covered in this module:

Using Set Creation Functions

Using Set Manipulation Functions

Using Subquery Set Functions

Working with Dimension Interactions
At the end of this module, you will be able to usesets of members to create new sets. This includes:

Using functions to create a set.

Using functions to manipulate a set.

Creating a set from subqueries.

Manipulating dimension interactions by using advanced functions.

Module7: Using Aggregation Functions
The following topics are covered in this module:

Understanding Aggregation Functions

Using Time Set Functions

Calculating a Trend
At the end of this module, you will be able tosummarize multiple retrieved values from a set into a single value. Thisincludes:

Creating temporary calculated members.

Understanding and creating calculated members from aggregation functions.

Understanding and using Time Set Functions.

Understanding how to calculate a trend.

Module8: Case Study - Implementing Budget Allocations
The following topics are covered in this module:

Allocating Values from a Grand Total

Allocating Values from a Subtotal

Allocating Values Across Multiple Dimensions
At the end of this module, you will be able toapply multiple MDX concepts and functions in a single, complex project. Thisincludes:

Allocating values from a grand total across a dimension.

Allocating values from an intermediate level across a dimension.

Creating a calculated member that allocates values across three dimensions.

Module9: Using Calculated Cells
The following topics are covered in this module:

Using Calculated Cells
At the end of this module, you will be able to usecalculated cells in a cube. This includes:

Retrieveing revenues from the Sales cube by using the LookupCube function.