Course Outline
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 first
learn then use these fundamental concepts to develop MDX
expressions and queries, implement dynamic security, and
define KPIs.
Target Audience
Business Intelligence Designers,
Developers, Analysts and Consultants currently working with
or considering Microsoft SQL Server Analysis Services
Prerequisites
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.
Objectives
Attendees completing this course will:
·
Understand the
fundamental concepts of MDX
-
Understand how and where MDX is used in an Analysis
Services solution
-
Be able to create Calculated Members and KPIs in an
Analysis Services cube
-
Be able to write MDX queries for reporting or validation
-
Understand how MDX is used to implement dynamic security
Agenda
Course Modules
1.
Introduction
2.
MDX Usage Overview
o
Overview of how
and where MDX is used in an Analysis Services solution
3.
MDX Language
Fundamentals
o
Overview of the
Analysis Services model and how MDX access cube data
o
MDX Members,
Tuples, and Sets
o
MDX familial
relationships and how these are used to navigate hierarchies
4.
Creating MDX
Queries
o
Starting with
simple queries and building up to more advanced analysis
o
Using set
functions in an MDX query
o
Filtering with
non-empty and attribute relationships
5.
Creating
Calculated Members
o
Starting with
simple queries and building up to more advanced expressions
o
Understanding the
Current Member
o
Creating dynamic
queries
o
Using conditional
logic
6.
Creating Key
Performance Indicators
o
Understanding KPI
functions in MDX
o
Developing Status
and Trend expressions
7.
MDX with
Role-Playing Dimensions
o
Using the SCOPE
function to define sub-cubes
o
Implementing
time-based analysis
8.
Implementing
Dynamic Security
o
Understanding the
User Dimension and Security Measure Group
o
Developing Dynamic
Security expressions
9.
Creating Analysis
Services Actions
o
Integrating MDX
into Actions to create seamless functionality
10.
Optimizing MDX
Queries and Expressions
o
Techniques for
improving query and expression performance
