Processing your application. Please be patient while we make sure we receive every piece of it.
Advanced MDX - Performance, Optimization, and the MDX Script
2 Days
This is an advanced MDX course. Half of the first day is devoted to advanced MDX functions and sets. The second half of the day is focussed on effectively using the MDX Script. The second day is devoted completely to examining strategies for monitoring and optimizing MDX. Analysis Services in SQL Server 2012 is used in the course, but almost all of the content is also applicable to Analysis Services in SQL Server 2008.
Upon completion of this course, the student will be able to:
  • Analyze and optimize the performance of MDX calculations
  • Understand the situations where MDX can be modified to improve performance and those situations where other strategies must be used
  • Re-write MDX calculations so that they will work with specific client applications
  • Use all the MDX functions
  • Use the MDX Script to create complex business logic
  • Use the MDX Script to create complex Date Calculation Hierarchies
This course is intended for individuals that are developing or supporting Analysis Services multidimensional cubes.
Before attending this course, it is recommended that students have the following skills:
  • Ability to create and understand MDX calculations
  • Thorough understanding of sets and tuples
  • Ability to use all the basic MDX functions
  • It is recommended that the students have taken the SolidQ course MDX for Business Logic
NOTE: This is an Advanced MDX course. If a student is unfamiliar with MDX before they take this course, they are unlikely to receive much benefit from it.
Module 01: Advanced Functions
  • Using VBA and Excel Functions in MDX
  • Using the String Functions
    • StrToMember, StrToSet, StrToTuple, MemberToStr, SetToStr, TupleToStr
  • Using the Statistical Functions
    • StdDev, StdDevP, Var, VarP, Covariance, CovarianceN
    • LinRegIntercept, LinRegPoint, LinRegSlope, LinRegR2, LinRegVariance
  • Using the Drilldown (UI) Functions
    • DrilldownLevel, DrilldownMember, DrilldownLevelBottom, etc.
  • Using the KPI Functions
    • KPIGoal, KPIStatus, KPITrend, KPIValue, etc.
  • Using the LinkMember Function
  • Using the Generate Function to Create a Set
  • Using the Generate Function to Create a String
  • Using Recursion in MDX
LAB: Using LinkMember and recursion

Module 02: Advanced Set Topics
  • The Autoexists Functionality
  • The Existing Keyword
  • The Exists Function
  • Dynamic and Static Sets
  • Referencing the Sets on the Axes
LAB: Referencing the Set on the Rows

Module 03: The MDX Scripting
  • The MDX Script
  • The Calculate Command
  • Setting the Default Member
  • Creating a Calculation Hierarchy
  • Scoping and Assignments
  • Debugging the MDX Script
  • The Freeze Command
  • Assigning Formatting in the MDX Script
  • Using IF in MDX Scripting
  • Adding Values Not Directly in Source Data
  • Removing Inaccurate Values
  • Replacing IIF with MDX Scripting
  • Replacing Unary Operators with MDX Script Assignments
LAB: Writing MDX Script Code

Module 04: Building a Time Calculation Hierarchy
  • Understanding Time Calculation Hierarchies
  • The Business Intelligence (BI) Wizard
  • Customizing Time Calculation Hierarchies
  • Current Period Calculations
  • Relative Date Period Calculations
  • Period To Date and Rolling Average Calculations
  • Comparison, Ratio, and Forecasting Calculations
  • Assigning Formatting
  • Creating a Second Hierarchy to Choose the Type of Date
LAB: Adding New Time Calculations

Module 05: MDX Performance Analysis
  • Optimization Strategy
  • SSAS Cache, Windows Cache, and MDX Script Cache
  • Using SQL Server Profiler with SSAS
  • Determining Storage Engine Time and Formula Engine Time
  • Cache Warming Strategies
LAB: Examining Cache Behavior

Module 06: Enabling Block Computation
  • Understanding Block Computation
  • Situations that Prevent Block Computation
  • Monitoring for Block Computation
  • Rewriting Calculations to Use Block Computation
    • Removing Named Sets from Aggregation Functions
    • Replacing Filter with Crossjoin or Exists
    • Replacing Count(Filter) with Sum(IIF)
    • Removing Late Binding
    • Replacing User-Defined Functions
    • Replacing LinkMember
  • When Block Computation is Slower
LAB: Fixing Calculations to Use Block Computation

Module 07: Other Calculation Performance Strategies
  • Simplifying Complex Calculations
  • Making Sets as Small as Possible
  • Ordering in Set Multiplication
  • Using Query-Created Cache
  • Fixing Non-Varying Expressions
  • Other Best Practices
  • IIF Function Hints
  • Adding Attributes for Specialized Grouping
  • Adding Attributes to Avoid Calculations
  • Consolidating Attributes into Same Dimension
  • Adding Measures to Avoid Querying the Leaf Level
  • Creating Dummy Objects in the Cube
  • Replacing Calculations with Cube Features
  • Removing the Non_Empty_Behavior Property
  • Aggregation and Partition Strategy
LAB: Using MDX Calculation Performance Enhancement Strategies

Module 08: Using Subselects and Subcubes
  • Understanding Subselect and Subcubes
  • Subselect and Slicing
  • Cache and Performance
  • Subselect and Subcube Syntax
  • Referencing Members Excluded by Subselect
  • Client Applications and Multiple Member Slicing
  • Writing Calculations for Subselect
LAB: Modifying a Calculation for Subselect