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.
Objective
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 and
validation
·
Understand how MDX is used to implement
dynamic security
Course Outline
Introduction
Introduction of the instructor and students as well as an
overview of the course agenda, class hours and other
housekeeping items.
Module 1: Solution Overview
Sets the conceptual framework for understanding MDX as an
intersection-based language and where MDX is used within a
BI Solution.
Also uses a SQL-MDX comparison to help students understand
differences and similarities between the two query
languages.
Module Agenda:
·
Understanding the Excel Paradigm
·
Dimensional Intersections (Tuples)
·
SQL – MDX Comparison
·
MDX Usage Overview
Module 2: Using the BI Toolset
This module is an introduction to BIDS and Management Studio
with an emphasis on how and by whom these tools are used in
a BI solution.
Emphasis is placed on functionality specific to BI
including: running MDX queries in Mgmt Studio and importing
SSAS databases to create project files.
Module Agenda:
·
Introducing Adventure Works
·
Introducing BIDS and The Management Studio
Module 3: MDX Language Fundamentals
Using primarily group activities this module introduces the
fundamentals of creating MDX queries.
In addition to introducing basic syntax a key
emphasis of this module is to show students how queries can
be an efficient and effective way to develop and validate
Calculated Members.
Module Agenda:
·
MDX Overview
·
Attribute and User Hierarchies
·
Creating Simple MDX Queries
·
Complete and Partial Tuples
·
Using Familial Relationships
·
Handling Exceptions
Module 4: Advanced MDX Queries
Building on the previous module, this module further
introduces advanced Set-based functions, using the WHERE
clause to slice data, and strategies for Time Based
analysis.
Module Agenda:
·
Explicit and Dynamic Sets
·
Slicing Data using the WHERE Clause
·
Using Set Functions
·
Using Time Based Functions
Module 5: Creating Calculated Members and Named Sets
This module guides students through the process of creating
Calculated Members and Names Sets in BIDS.
Three techniques for creating Member and Sets will be
demonstrated: using the BIDS Form interface, using the BIDS
Script interface, and using the BIDS interface to
cut-and-paste from an MDX query.
Module Agenda:
·
Calculated Member Overview
·
Using the Forms Interface
·
Using the Script Interface
·
Creating Calculated Members
·
Creating Named Sets
Module 6: Creating Key Performance Indicators
This module uses MDX functionality presented in previous
modules as well as the KPI specific functions KPIValue,
KPIGoal, KPIStatus, and KPITrend to understand and create
Key Performance Indicators in BIDS.
Module Agenda:
·
KPI Overview
·
Creating KPIs
·
Using the KPI Viewer
Module 7: Implementing Dynamic Security
This module introduces the concept of implementing data
access controls via dynamic Security.
This technique requires a specific relational schema,
an update to the SSAS project, and the creation of a Role
which uses MDX functionality to grant or deny users access
to specified dimension members.
Module Agenda:
·
Overview Role Based Security
·
Implementing Dynamic Security
Module 8: Using Analysis Services Actions
Actions can significantly improve usability in a BI solution
but are often overlooked.
The module introduces the most commonly used actions
types and how they are implemented and used.
Module Agenda:
·
Overview of SSAS Action Usage
·
Introducing Action Types
Module 9: MDX with Role-Playing Dimensions
Role-playing dimensions are common and helpful in SSAS
solutions, particularly with Time (Date) dimensions,
but they do introduce some special considerations
with Calculated and Default Member definitions.
Module Agenda:
·
Overview of Role Playing Dimensions
·
Defining a Default Member
·
Creating Calculated Members
Module 10: Optimizing MDX Queries and Expressions
This final module is a discussion of best practices for
optimizing and benchmarking MDX queries.
Module Agenda:
·
Benchmarking Performance
·
Best Practices for Optimizing Performance
