AmeriTeac

 

Taking the Mystery out of MDXTM

 Course 2799: Two days; Instructor-Led

 

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

 

 

 
 

For more information call 720.346.1710 or visit: www.ameriteach.com

7800 E. Dorado Place, Greenwood Village, CO 80111