Implementing Business Logic with MDX in Microsoft SQL Server 2000

Target Audience
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 an Analysis Services cube by using Microsoft SQL Server 200

Pre-Requisites
Before attending this course, students must have the following pre-requisites:Successful completion ofcourse 2074, Designing and Implementing OLAP Solutions Using Microsoft SQL Server 2000, or equivalent experience.

Purpose
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 outline

1. Multidimensional Review
2. Multidimensional Expressions
3. MDX Member Calculations
4. MDX Time Calculations
5. MDX Statements and Sets
6. MDX Set Manipulation
7. MDX Aggregation Functions
8. Using MDX in Cube Definitions
9. Calculating Cell Values by using MDX
10. Controlling Calculations in a Cube

Module 1:Multidimensional Review

• Understand and use multidimensional terminology.
• Define multidimensional terms: Database, Cube, Dimension, Hierarchy, Level, Member.
• Explain the use of Parent Child dimension and Unary Operators.
• Understand the sample database used in the examples and exercises.
• Explain the difference betweeen a "derived measure" (a SQL expression) and a "calculated member" (an MDX).

Module 2:Multidimensional Expressions

• Add calculated members to a cube.
• Contrast multidimentional expressions and statements.
• Explain the derivation and meaning of the word tuple.
• Compare MDX with spreadsheet formula.
• Include multiple explicit dimensions in a tuple.
• Create percent of total calculations

Module 3:MDX Member Calculations

• Use MDX member functions to navigate within a hierarchy.
• Use the Ancestor function.
• Use the IIF and IsEmpty functions and logical operators.
• Use the Name function.
• Include member properties in expression.
• Create percent of parent calculations.

Module 4:MDX Time Calculations

• Use MDX time functions to compare values over time.
• Use PivotTable List control as a browser.
• Use PrevMember and ParallelPeriod functions.
• Find and apply other time-related member functions.
• Calculate various types of growth.

Module 5:MDX Statements and Sets

• Use MDX set functions in a query statement.
• Compare MDX statements with expressions.
• Define basic MDX Statement Syntax.
• Use MDX Sample application.
• Explain concept and usage of a Set.
• Use basic Set functions: Members, Children, Descendants

Module 6:MDX Set Manipulation

• Use functions to manipulate sets of members.
• Modify sets by using Head and Tail functions.
• Create tuples in a set by using the CrossJoin function.
• Use the Union and Hierarchize functions.
• Use the Order and Filter functions.

Module 7:MDX Aggregation Functions

• Use functions to aggregate values from a set of members.
• Use Sum and Aggregate functions (RollupChildren()).
• Use Time-related Set functions: LastPeriods and PeriodsToDate.
• Create calculated members by using Aggregation functions.
• Follow strategy for creating calculated members.

Module 8:Using MDX in Cube Definitions

• Apply MDX to make a cube change dynamically.
• Create calculated Default Members.
• Create calculated Actions (and captions).
• Create calculated Cell-level Security.

Module 9:Calculating Cell Values by using MDX

• Calculate formulas for specific blocks of cells.
• Create Custom Rollup formulas.
• Create Custom Member formulas.
• Create Custom Cell formulas.

Module 10:Controlling Calculations in a Cube

• Control the order of calculations within a cube.
• Use the Solve Order property to control calculation sequence.
• Use Multi-Pass calculations to control calculation sequence.
• Correctly understand and apply the Interaction of Aggregation function, Unary Operators, Calculated Members, Calculated Cells, Custom Members, Custom Rollups, Multi-Pass, and Solve Order.