Two days—Instructor-led
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 2000/2005 Analysis Services cube by using SQL Server 2000/2005.
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. |
| • |
Apply MDX in common financial applications. |
Course Materials
The course materials are yours to keep.
You will be provided with the following software for use in the classroom:
| • |
Microsoft SQL Server 2000/2005 |
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 Calculated Cells
The following topics are covered in this module:
At the end of this module, you will be able to use calculated cells in a cube. This includes:
| • |
Retrieveing revenues from the Sales cube by using the LookupCube function. |