Advanced MDX – Performance, Optimization, and the MDX Script

BI-MDXAD12-301-EN

Enhance your knowledge of MDX functions and sets and learn effective use of the MDX Script. Explore strategies to monitor and optimize MDX.

Download outline

}

Duration: 16 hours



Level: 300



Objectives

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

”Very knowledgeable and good energy. Kept the class moving and engaged”

WHEN and WHERE is this course running?



What does this course cover?

Developed in-house by SolidQ, 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 focused on effectively using the MDX Script. The second day is devoted completely to examining strategies for monitoring and optimizing MDX. The Advanced MDX course uses Analysis Services in SQL Server 2012, but almost all of the content is also applicable to Analysis Services in SQL Server 2008.

Who is this course designed for?

Individuals that are developing or supporting Analysis Services multidimensional cubes.

Pre-requisites: What do you need to know?

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.

All our courses can be offered as a private delivery and tailored for your team's specific needs

Course outline

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

WHEN and WHERE is this course running next?

This course may be scheduled in more than one region. Please check availability in your country.

Check dates