Solving Business Problems in MDX
Printer friendly

Course Overview
  This three-day, instructor-led course provides students with the knowledge and skills to write MDX expressions, calculations and queries for Microsoft SQL Server 2005 Analysis Services in order to implement the most demanding requirements for a Business Intelligence project.

This course was written by Chris Webb, a mentor with Solid Quality Learning. He is a specialist in Analysis Services and MDX, is a co-author of the second edition of "MDX Solutions with Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase", a recipient of the Microsoft MVP award and maintains a Microsoft BI-related blog at http://cwebbbi.spaces.live.com/

Note: This course may be taught by other qualified Solid Quality Learning mentors, and is not restricted solely to the author.

Objectives
  Upon completion of this course, the student will be able to:
  • Understand the basic concepts of MDX such as members, tuples and sets
  • Be able to write SELECT statements to query data
  • Understand what a calculated member is and where to create it
  • Understand how and when to use all major MDX functions
  • Break down a calculation from a business requirement using set-based logic and then express it in MDX - be able to 'think in MDX'
  • Understand the behavior of advanced concepts and features such as solve order, subselects and auto-exists
  • Write MDX queries for use in SQL Server Reporting Services
  • Write MDX expressions for security roles, KPIs and Actions
  • Write scoped assignments in a cube's MDX Script and understand how these assignments can affect the rest of the cube
  • Write efficient MDX

Audience
  This course is intended for Business Intelligence professionals seeking to develop solutions using the Microsoft BI platform, including consultants, software developers and business analysts.

Prerequisites
  Before attending this course, it is recommended that students have the following skills:
  • Experience working with Microsoft SQL Server Analysis Services 2005, BI Development Studio/Visual Studio and client tools such as Excel to query an Analysis Services cube. No prior experience of MDX is necessary.
Course Number
  NHSQLMDX
Course Length
  3 Days
Course Price
  $2,350.00
Category
  4
Topics
  Module 01: Introduction



Module 02: MDX Overview

This module describes the MDX language, when and why it is used (including a comparison of its strengths and weaknesses versus SQL) and what tools are used to run MDX queries.

Module 03: MDX Overview

This module explains the building blocks of the MDX language: cubes, measure groups, dimensions, hierarchies, members, tuples and sets.

Module 04: Writing Simple Queries

This module describes how to query a cube using simple SELECT statements.

Module 05: Writing Simple Calculated Members

This module explains what a calculated member is, the different places they can be defined, and shows how MDX expressions can be used in them to return constants and reference values from other cells in the cube. This module also introduces the concept of named sets.

Module 06: Set Functions, Part 1

This module describes MDX set functions such as Crossjoin(), and also introduces hierarchical functions such as .Parent and .Children, Descendants() and others.

Module 07: Numeric and Member Functions

This module describes various MDX numeric functions such as Count(), Sum() and Aggregate(). It also introduces functions that return members such as the .CurrentMember function.

Module 08: Solving Business Problems in MDX, Part 1

This module takes everything that has been learned so far and shows how to use MDX to implement common business calculations such as previous period growths and percentage shares. It also introduces the concept of time utility dimensions.

Module 09: Set Functions, Part 2

This module describes more MDX set functions such as YTD(), Order(), Generate(), NonEmpty() and TopCount().

Module 10: Advanced Concepts

This module describes advanced features of MDX in Analysis Services 2005 such as auto-exists, subselects and solve order.

Module 11: MDX for Reporting Services, KPIs, Security and Actions

This module gives examples of how to use MDX in KPIs, security role definitions and actions; and also describes some of the limitations that Reporting Services 2005 imposes on MDX queries and how to work around them.

Module 12: MDX Scripts

This module explains what an MDX Script is and where to find it, describes how to write advanced MDX expressions using scoped assignments and illustrates how making an assignment can affect values throughout the cube as a result of aggregation. It also discusses the 'Last Pass Wins' and 'Closest Pass Wins' rules in detail, as well as showing other MDX Script statements such as Freeze(), Format_String() and Language().

Module 13: Writing Efficient MDX

This module gives some tips and tricks on how to write efficient MDX expressions for calculations and queries, as well as showing how to benchmark query performance and understand the information provided by a Profiler trace.

Module 14: Solving Business Problems in MDX, Part 2

This module consists of practical exercises where students have to write more complex queries and calculations in MDX so that they can practice what they have just learned.