Analyzing Multi-Dimensional Data with Microsoft SQL Server 2000

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 an Analysis Services cube by using Microsoft SQL Server 2000.

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.

Microsoft Certified Professional Exams

There is no MCP exam associated with this course.

Prerequisites

• Successful completion of course 2074, Designing and Implementing OLAP Solutions Using Microsoft SQL Server 2000, or equivalent experience.

Course Outline

Module 1: Multidimensional Review

Skills

After completing this module, students will be able to:

• 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

Skills

After completing this module, students will be able to:

• 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

Skills

After completing this module, students will be able to:

• 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

Skills

After completing this module, students will be able to:

• 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

Skills

After completing this module, students will be able to:

• 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

Skills

After completing this module, students will be able to:

• 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

Skills

After completing this module, students will be able to:

• 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

Skills

After completing this module, students will be able to:

• 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

Skills

After completing this module, students will be able to:

• 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

Skills

After completing this module, students will be able to:

• 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.

For more information or to give feedback, send e-mail to info@OneApex.com or call 949-597-2100