SQL Server 2005 MDX In-depth

MDX is the fundamental language of Analysis Services – it’s the SQL of the multi-dimensional
environment.  From the expressions that calculate the most valuable business metrics to KPIs,
dynamic security, and report queries your ability to effectively use MDX will dictate how much of
Analysis Service’s power you are able to unleash.    

Though the Multidimensional Expressions (MDX) language can at first appear to be mysterious and
complex, there are a few fundamental concepts that form the basis for understanding how MDX
works and how it should be used in your solution.   In this course we’ll use demonstrations and
hands-on labs to teach and use these fundamental concepts to develop MDX expressions and
queries, implement dynamic security, and define KPIs.

Prerequisites:
Required: BI2791 Implementing and Maintaining SQL Server 2005 Analysis Services or equivalent
knowledge.

This is a fast-paced course encompassing MDX from fundamentals to advanced concepts. 
Experience with Microsoft SQL Server Analysis Services is recommended for this challenging course
but prior experience with MDX is not required.  

Audience:
Business Intelligence Designers, Developers, Analysts and Consultants currently working with or
considering Microsoft SQL Server Analysis Services

At Course Completion: 
 
  After completing this course, students will be able to: 

•   Describe the fundamental concepts of MDX
•   Describe how and where MDX is used in an Analysis Services solution
•   Create Calculated Member and KPI’s in an Analysis Services cube
•   Write MDX queries for reporting and validation
•   Implement dynamic security using MDX expressions 

  
Course Outline:   
 
Module 1: Introduction
 
Module 2: MDX Usage Overview 

•   Overview of how and where MDX is used in an Analysis Services solution.

Module 3: MDX Language Fundamentals 

•   Overview of the Analysis Services model and how MDX accesses cube data
•   MDX Member, Tuples and Sets
•   MDX familial relationships and hw these are used to navigate the hierarchies

Module 4: Creating MDX Queries 

•   Starting with simple queries and building up to more advanced analysis
•   Using set functions in an MDX query
•   Filtering with non-empty and attribute relationships

Module 5: Creating Calculated Members 

•   Starting with simple expressions and building up to more advanced expressions.
•   Understanding the Current Member
•   Creating dynamic queries
•   Using conditional logic

Module 6: Creating Key Performance Indicators 

•   Understanding KPI functions in MDX
•   Developing Status and Trend expressions

Module 7: MDX with Role-Playing Dimensions 

•   Using the SCOPE function to define sub-cubes
•   Implementing time-based analysis

Module 8: Implementing Dynamic Security 

•   Understanding the User Dimension and Security Measure Group
•   Developing Dynamic Security expressions 

 
Module 9: Creating Analysis Services Actions 

•   Integrating MDX into Actions to create seamless functionality

Module 10: Optimizing MDX Queries and Expressions 

•   Techniques for improving query and expression performance