Solving Business Problems with MDX in SQL Server 2008
 

3-Day (BI-MDX2008-301-EN)

Description
This hands-on, instructor led course will guide you step-by-step through the Analysis Service 2008 Multi Dimensional eXpression (MDX) OLAP query language. You will learn a practical approach to solve real-world problems using MDX expressions, calculations and queries, which will allow users to extract valuable information.
Target Audience
This course is intended for:
  • Developers creating Microsoft Analysis Services solutions
  • Database Administrators who are optimizing Microsoft Analysis Services query and browsing speed
  • Analysis Services Power Users who want to convert business rules to MDX calculations and queries
  • Those who are using MDX with Reporting Services
  • Those who are using MDX with PerformancePoint Services for SharePoint Services
Prerequisites
Before attending this course, it is recommended that students have the following skills:
  • Basic understanding of Analysis Services
  • An understanding of multidimensional concepts – cubes, dimensions, attributes, and hierarchies
Course Objectives
Upon completion of this course, the student will be able to:
  • Convert business requirements into MDX calculations and queries
  • Query data from a cube
  • Navigate hierarchies
  • Create calculated members
  • Create named sets
  • Create time-based calculations
  • Use all the MDX functions
  • Write code to assign values in a specific portion of the cube using the MDX Script
  • Debug, analyze, and optimize MDX queries
  • Use MDX to create KPIs
  • Use MDX in setting Analysis Services security
  • Use MDX in Reporting Services
  • Use MDX in PerformancePoint Services for SharePoint Services
Course Summary Outline
Day 1 - Introduction and Basics
Module 01:  MDX Concepts
  • What is MDX?
  • MDX Design and Query Tools
  • Basic Concepts
    • Naming (Identifiers)
    • Cubes/Dimensions/Measures
    • Attributes/Hierarchies/Levels
    • Members/Tuples/Sets
    • Queries
Module 02:  Basic Calculations
  • How to define calculations
  • Basic math in calculations
  • Allocations and tuples
  • LAB 02A: Basic Calculations
    • Use BI Development Studio online and offline
    • Practice basic math with measures
    • Percent calculations
  • LAB 02B: Basic Allocation Calculations
Module 03:  Navigation in Hierarchies
  • Why navigate hierarchies?
  • How to create a basic set from a hierarchy.
  • How to navigate between members.
  • LAB 03A: Simple Navigation in a Hierarchy
    • Members
    • Children
    • PrevMember / NextMember
    • Using IIF and format_string
  • LAB 03B: Usage of Parent and Descendants
    • Parent
    • Descendants
DAY 2
Module 04:  Sets
  • Multiply , Add, and Subtract Sets
  • Aggregating Sets
  • Filtering Sets
  • Ranking
  • LAB 04A: Manipulating Sets
    • Multiplying Sets
    • Adding and Subtracting Sets
  • LAB 04B: Aggregating Sets
  • LAB 04C: Advanced Set Operations
    • Topcount
    • Rank
    • Order
    • Filter
Module 05:  Common Calculations
  • Distributions
  • Averages
  • Time Based References
  • Choose Value Based on Another Dimension
  • Handle Two Exclusive Time Dimensions
  • LAB 05A: Distributions
  • LAB 05B: Averages and Time Calculations
Module 06:  The MDX Script
  • Overview of MDX Script
  • Calculate Command
  • Modifying Objects
  • Creating Calculated Members
  • Creating Named Sets
  • Using Assignments and Scopes
  • Creating a Date Calculation Hierarchy
  • LAB 06A: Enhancing your cube with the MDX Script
DAY 3
Module 07:  Advanced MDX Topics
  • NonEmpty Function
  • AutoExists and Exists
  • SubSelect and Subcubes
  • Visual Totals in Subcubes/SubSelect
  • Cell Calculations
  • Slicing on Multiple Members
  • Recursion
  • LAB 07A: Using Advanced MDX Concepts
Module 08:  Optimizing and Debugging MDX
  • MDX Query Optimization Strategy
  • Debugging Tools
  • Fixing Bottlenecks in the Storage Engine and the Formula Engine
  • Taking Advantage of Performance Enhancements in AS2008
  • Checklist for Optimizing MDX
  • LAB 08A: Optimizing and Debugging MDX
Module 09:  Creating and Querying KPIs
  • What is an Analysis Services KPI?
  • Querying a KPI
  • Creating a KPI in Analysis Services
  • Creating a Session KPI
  • LAB 09A: Creating a KPI
Module 10:  Using MDX in Setting Analysis Services Security
  • Using MDX in Security
  • Security and Performance
  • Role-based and Dynamic Security
  • The UserName Function
  • Dynamic Security in Regular Hierarchies
  • Dynamic Security in Parent-Child Hierarchies
Module 11:  MDX for Reporting Services
  • Limitations of MDX in Reporting Services
  • MDX Generator
  • Manual MDX
  • Using Parameters
  • LAB 11A: Creating an MDX Based Report
  • LAB 11B: Setting Up a Parameter
Module 12:  Using MDX in Special Situations
  • Actions
  • Drillthrough
  • Creating Local Cubes for Off-Line Browsing
  • Writing Back to a Cube
  • LAB 12A: Actions, Drillthrough, Local Cubes, Write-Back
Module 13:  Using MDX in PerformancePoint Services for Microsoft Office SharePoint Services
  • Three Strategies Regarding the Use of MDX in PerformancePoint Services
  • Places Where MDX is Used in PPS
  • Debugging MDX in PPS
  • Comparing the Use of MDX and STPS (Simple Time Period Specification)
​