MDX Training at AmeriTeach - Denver, CO 80111

MDX for Analysis and Reporting Services

Course 2800: Three 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 generate 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 the first two days of this course we’ll use demonstrations and hands-on labs to first understand then use these fundamental concepts to develop MDX expressions, implement security, and define KPIs.

In the third day of this course we’ll use demonstrations and hands-on labs to first understand then develop optimized MDX queries for Reporting Services.

Target Audience

Business Intelligence Designers, Developers, Analysts and Consultants currently working with or considering Microsoft SQL Server Analysis and Reporting 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.  Experience with Reporting Services is recommended but not required for those attending the full three day course.

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 security
• ·         Understand how to generate and optimize MDX queries in Reporting Services

Course Outline (Day 1 – 2)

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
• ·         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
• ·         MDX Query Context and Execution
• ·         Dimensional Intersections (Tuples)
• ·         Attribute and User Hierarchies
• ·         Creating Simple MDX Queries
• ·         Complete and Partial Tuples
• ·         Using Familial Relationships
• ·         Hierarchy Navigation
• ·         Handling Exceptions
• ·         Null Handling
• ·         Set Functions
• ·         IIF / CASE expressions
• ·         WITH clause to define Calculations and Sets

Module 4: Solving Business Problems with MDX

Building on the previous module, this module focuses on using MDX queries to solve standard business requirements that apply to many analytical applications.

Module Agenda:

• ·         Ratios
• ·         Sums and Differences
• ·         Allocations
• ·         Averages
• ·         Time based Calculations
• ·         Defining a sub-cube using the SCOPE statement

Module 5: 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 6: Implementing Basic Security

This module introduces the concept of implementing data access controls using MDX expressions.

Module Agenda:

• ·         Overview Role Based Security
• ·         Defining Dimension Data Security
• ·         Controlling access to Cells based on Dimension members

Module 7: 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

Course Outline (Day 3)

Module 8: Using the Analysis Services Query Designer

The Analysis Services Query Designer gives developers the ability to write reports based on an Analysis Services data source without having to understand MDX. This module explores the report development using the Design Mode of the Analysis Services Query Designer.

Module Agenda:

• ·         Analysis Services Query Designer Overview
• ·         Building a Basic Query
• ·         Adding Filters and Parameters

Module 9: Using MDX in the Analysis Services Query Designer

In this module, students apply the learning from the previous module by using MDX in both the Design Mode and the Query Mode of the Analysis Services Query Designer. This module also introduces additional features of the Analysis Services Query Designer facilitating query development.

Module Agenda:

• ·         Using the Calculated Members Pane in Design Mode
• ·         Understanding Query Structure Requirements for Reporting Services
• ·         Using Metadata, Function Templates, and Calculation Templates in Query Mode
• ·         Adding Filters to a Query

Module 10: Using Analysis Services Features in Report Design

After learning how to retrieve data Analysis Services by using an MDX query, students are ready to interact with Analysis Services features in the report design process. This module shows students how to work with detail and aggregated data in the result set, how to use server-side formatting properties to control the report appearance, and how to display key performance indicators defined in a cube.

Module Agenda:

• ·         Working with Aggregations
• ·         Using Extended Field Properties
• ·         Working with Key Performance Indicators

Module 11: Working with Parameters

This module teaches several methods for implementing parameters for more flexible reports.

Module Agenda:

• ·         Modifying the Auto-Generated Parameter Query
• ·         Adding Parameters to a Query
• ·         Creating Cascading Parameters
• ·        

Find out more about SQL Server Training at AmeriTeach

View the SQL Training Schedule

Visit the AmeriTeach website