Introduction To MDX (Online)

During this 4-half day MDX virtual training course, you will learn how to use MDX for use with SSAS in your enterprise. This class starts by explaining the basic concepts related to multidimensional expressions and how they are used to enhance cubes with calculations as well as to query cubes to retrieve data for reports. Then the class continues by explaining how to write MDX queries to retrieve data from an Analysis Services cube. This class also covers the following topics: common functions for creating sets and performing set-based operations, development of calculations that can be used as measures or as dimension member, addition of calculations and named sets to a cube for re-usability, script assignments for complex calculations, MDX and cube security, and optimization techniques for queries.

IMPORTANT: When you purchase a seat in this class you also receive 2 hours of our BI Virtual Mentor service for assistance with YOUR project work. This remote service lets you work ONE on ONE with our BI mentors to accelerate YOUR project and further improve YOUR skills. Your Virtual Mentor hours can be used during business hours anytime during the 12 months following your class. To set up a session, download this simple questionaire, fill it out, and send it as an attachment to mentor@pragmaticworks.com. This unique Pragmatic Works offering insures that your training will translate into real world success for YOUR projects.

All virtual training students are provided a previously recorded version of the class that are available for 7 days after the end of the class.

Outline

Day 1 – Introduction to MDX

  1. Getting Started with MDX
    • Thinking about Multidimensional Space
    • Exploring Options for Using MDX
  2. Building MDX Queries
    • Understanding the MDX Query Editor
    • Building Basic MDX Queries
    • Adding Sets to a Query
    • Comparing MDX to T-SQL
    • Hands-on Lab: Creating Simple Queries

Day 2 – Sets and Calculations

  1. Defining Sets
    • Creating Sets with Functions
    • Using Functions for Set Operations
    • Working with Hierarchical Sets
    • Hands-on Lab: Developing Queries based on Set Evaluation
  2. Adding Calculations to a Query
    • Building Calculated Members
    • Exploring MDX Functions
    • Using Time Functions
    • Hands-on Lab: Developing Queries with Calculations

Day 3 – Cube Enhancements

  1. Creating Calculations in a Cube
    • Creating Calculated Measures and Custom Members
    • Defining Named Sets
    • Creating KPIs
    • Hands-on Lab: Adding Calculations to a Cube
  2. MDX Scripting
    • Introducing MDX Script Functions
    • Adding Calculations to the MDX Script
    • Debugging an MDX Scripts
    • Hands-on Lab: Creating a Weighted Allocation Calculation

Day 4 – Advanced MDX

  1. Optimizing MDX Queries
    • Using SQL Server Profiler to Analyze Queries
    • Optimizing Queries for the Formula Engine
    • Optimizing Queries for the Storage Engine
    • Hands-on Lab: Tuning a Query

Prerequisites

The target of this class is developers, architects or IT professionals who have some experience with Analysis Services.

SystemRequirements

  • Microsoft Windows XP Professional, Microsoft Windows Server 2003, Microsoft Vista, Windows 7 or Windows 8
  • 1 GHz or faster CPU
  • 1 GB of available RAM (recommended 2 GB)
  • 20 GB of free hard drive space
  • SQL Server Developer, Standard or Enterprise Edition 2008/2008R2/2012/2014
    • SQL Server Database Engine
    • SQL Server Analysis Services Multidimensional
    • SQL Server Data Tools (SQL 2012/2014) or Business Intelligence Development Studio (SQL 2008/2008R2)
    • SQL Server Management Studio
    • Follow this installation guide to ensure all items are installed correctly
  • AdventureWorksDW2008R2 or AdventureWorksDW2012 database installed on the instance.
  • AdventureWorks Cube restored to the Analysis Services instance.

Some of our clients