Module 1: OLAP Review
- 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
- 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
- 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
- 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
- Understanding MDX query statement terminology.
- riting simple MDX query statements.
Module 6: Creating and Manipulating Sets
- 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
- 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
- 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
- Overwriting cell content via subcubes
- CALCULATE, SCOPE, IF and FREEZE
If time permits, these topics can be practised in a workshop-alike style on the
last day of this course:
- Extending MDX with .Net code
- MDX Performance tuning
This course provides students with the knowledge and skills necessary to use multidimensional
expressions (MDX) to add calculated members, named sets and other dynamically calculated
values to a Microsoft SQL Server Analysis Services cube.
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 (inspecting cube definitions in BIDS or SSDT).