We are happy to advise you!
+43 1 6000 880-0     Contact

MDX for Creating Business Logic (SQMDX)


Course Overview

MDX wird verwendet, um Business Logic für multidimensionale Cubes in SQL Server Analysis Services zu kreieren. In diesem Kurs lernen die Teilnehmer MDX Berechnungen und Named Sets (=Gruppen von Elementen zur wiederholten Verwendung in MDX Abfragen) zu verstehen und zu erzeugen. Dieses Seminar ist relevant für SQL Server 2014, 2012 und 2008.

Der Kurs MDX for Creating Business Logic ist ein Einführungskurs in MDX.

Who should attend

Das Seminar richtet sich an Entwickler und BI-Profis, die multidimensionale Cubes in SQL Server Analysis Services entwickeln und/oder supporten.


Analysis Services Kenntnisse

Grundlagen zu multidimensionalen Objekten wie Cubes, Dimensions, Hierarchies, Levels, Members und Measures

Course Content

Module 01: Introduction to MDX
  • Why MDX is used
  • The MDX Query
  • Creating an MDX Calculation
  • Comparing MDX to SQL
  • LAB 01: Writing Your First MDX
Module 02: MDX Concepts
  • Dimensions, hierarchies, levels and members
  • Measures
  • Default member and current member
  • TuplesSets
  • Functions
  • Punctuation
  • LAB 02: Dimensions, Hierarchies, Levels, Members, Measures, Tuples, and Sets
Module 03: Date Calculations
  • Ways to create a calculation
  • Using a template for developing calculations
  • This month/last month
  • This year/last year
  • Moving calculations to the cube
  • LAB 03: Creating Date Calculations and Moving Them to the Cube
Module 04: Aggregation Functions
  • Aggregation functions
  • Count and DistinctCount
  • Sum
  • Using YTD with Sum
  • QTD, MTD, WTD, PeriodsToDate
  • Avg compared to Sum divided by Count
  • The Aggregate function
  • The other Aggregation functions
  • LAB 04: Creating Calculations with Sum, Avg, and YTD
Module 05: Logical Functions
  • IIF and Case
  • Understanding Null and Empty in MDX
  • Eliminating Divide-By-Zero errors
  • Different logic for different time periods
  • Different logic for different levels
  • LAB 05: Using IIF and CASE to Handle Boundary Conditions
Module 06: Hierarchy Functions
  • Moving back and forth in a Level – PrevMember, NextMember, Lag, and Lead
  • Finding related members in a level – FirstSibling, LastSibling, ParallelPeriod, and Cousin
  • Moving Between Levels - Parent, Children, FirstChild, LastChild, and Descendants
  • Calculating the Count at a Lower Level Ancestor and Ascendants
  • Calculating the Proportion of the Parent or the Proportion of the Whole
  • ClosingPeriod and OpeningPeriod
  • LAB 06: Using Descendants and Ancestor in Calculations
Module 07: Set Creation Functions
  • Named Sets Sets in Query, Session, and Cube Scopes
  • The Head and Tail functions – and NonEmpty
  • Ranges and Rolling Averages
  • Crossjoin – Set Multiplication
  • Using Crossjoin for high performance filtering in a calculation
  • Union – Adding sets
  • Except – Removing unwanted members from sets
  • Intersect – Finding the common members
  • LAB 07: Using Set Addition, Subtraction, and Intersection
Module 08: Set Manipulation Functions
  • Order – Putting the set in a desired order
  • Hierarchize – Returning to the natural order of the hierarchy
  • Filter – Applying complex logic to sets
  • TopCount – Showing the best
  • TopSum, TopPercernt, BottomCount, BottomSum, and BottomPercent
  • Rank – Applying a number to the ordering
  • LAB 08: Using the Order, Filter, Top/Bottom and Rank Functions
Online Training

Duration 2 days

Price (excl. tax)
  • 1,190.- €
Digital courseware

Courseware language: English

Classroom Training

Duration 2 days

Price (excl. tax)
  • Austria: 1,190.- €
incl. catering
Catering includes:

  • Coffee, Tea, Juice, Water, Soft drinks
  • Pastry and Sweets
  • Bread
  • Fresh fruits
  • Lunch in a nearby restaurant

* Catering information only valid for courses delivered by iTLS.

Digital courseware

Courseware language: English

Currently there are no training dates scheduled for this course.  Enquire a date