Introduction
This course provides students with the knowledge and skills necessary to use
multidimensional expressions (MDX) to add calculated members and other
dynamically calculated values to a Microsoft SQL Server 2008 Analysis Services
cube.
At Course Completion
At the end of the course, students will be able to:
| • |
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. |
Prerequisites
Before attending this course, students must have a good understanding of the
fundamental concepts in Online Analytical Processing (dimensions, measures,
hierarchies, members,...), and basic knowledge of Analysis Services on SQL
Server 2005 or 2008.
The course materials, lectures, and activities are in English. To benefit
fully from the instruction, students need an understanding of the English
language and completion of the prerequisites.
Course Materials
The course materials are yours to keep.
Course Outline
Module 1: 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 to demonstrate a working
knowledge of OLAP concepts and terminology. This includes:
| • |
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. |
Module 2: 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 to build 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. |
Module 3: 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 to create 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. |
Module 4: 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 to apply tuples for retrieving
values from a cube and creating practical calculations. 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. |
Module 5: 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 to create simple MDX query
statements that generate a report. This includes:
| • |
Understanding MDX query statement terminology. |
| • |
Writing simple MDX query
statements. |
Module 6: 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 use sets 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. |
Module 7: 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 to summarize multiple retrieved
values from a set into a single value. This includes:
| • |
Creating temporary calculated members. |
| • |
Understanding and creating calculated members from
aggregation functions. |
| • |
Understanding and using Time Set Functions. |
| • |
Understanding how to calculate a
trend. |
Module 8: 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 to apply multiple MDX concepts
and functions in a single, complex project. This includes:
| • |
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. |
Module 9: Using Subcubes
The following topics are covered in this module:
| • |
Overwriting cell content |
| • |
Working with subcubes |
|