fbpxl

Private Onsite | 2 Days Classroom

Advanced MDX – Performance, Optimization, and the MDX Script

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

During this course you will learn how to analyze and optimize the performance of MDX calculations, you will understand the situations where MDX can be modified to improve performance and those situations where other strategies must be used, you will learn how to re-write MDX calculations so that they will work with specific client applications, how to use all the MDX functions, the MDX Script to create complex business logic and how to create complex Date Calculation Hierarchies.

Main Benefits

Analyze and optimize the performance of MDX calculations.

j

Re-write MDX calculations so that they will work with specific client applications.

Use the MDX Script to create complex business logic.

Understand the situations where MDX can be modified to improve performance.
e

Use all the MDX functions.

Use the MDX Script to create complex Date Calculation Hierarchies.

Upcoming Dates

There are no scheduled classes for this course at the moment. Please, send us an information request to find out more.

Course Delivery Options

Private Onsite.

The course will take place in your company’s facilities. We limit attendance to no more than 20 students in order to maintain a good level of interactivity.
Contact us

2 Days Classroom.

The course will take place in a classroom with no more than 20 students in order to maintain a good level of interactivity.

View outline

Pre-requisites

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

    • After this Course

    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.
    ”Very knowledgeable and good energy. Kept the class moving and engaged”

    Course Outline

    Module 1: 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 2: 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 3: 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
  • 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 4: 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 5: 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 6: 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

    Lab 05 B: Actions and Perspectives

    Lab 05 C: Translations

    • Module 7: 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 8: Using Subselects and Subcubes
  • 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 8: Storage Modes, Aggregation Designs, and Proactive Caching
  • Storage modes
  • Storage modes for the multidimensional model
  • Storage modes for the tabular model
  • Partitions
  • Aggregation designs
  • Proactive caching

    Lab 08 A: Aggregations and Partitions

    Lab 08 B: Partitions in the Tabular Model

    • Module 9: Security
  • SSAS security architecture
  • Defining administrative access roles
  • Defining user access roles
  • Sections specific to the multidimensional model
  • ASPE
  • Custom security

    Lab 09 A: Security in Multidimensional Models

    Lab 09 B: Security in Tabular Models

    • Module 10: Scalability and Performance
  • Optimizing many-to-many dimensions
  • Server properties
  • Tools for monitoring performance
  • Formula engine (FE) vs storage engine (SE)
  • Reading queries in SQL Server Profiler

    Lab 10: Query Troubleshooting

    • Module 11: Basic Maintenance and administration
  • Processing
  • SQL Server Management Studio
  • Backup and restore
  • Synchronization
  • Deployment
  • Lab 11 A: Backing Up, Restoring, and Deploying SSAS Databases (Multidimensional Model)

    Lab 11 B: Deploying SSAS Databases (Tabular Model)

    • Contact us

    Do you want more info about these courses?

    • By submitting this information, you acknowledge that you have read the Privacy Policy and that you consent to our data processing in accordance with this Statement.