Processing your application. Please be patient while we make sure we receive every piece of it.
MDX for Creating Business Logic
2 Day(s)
Course OverviewThe MDX language is needed to create business logic for multidimensional cubes in Analysis Services. This course teaches students how to create and understand MDX calculations and named sets. Analysis Services in SQL Server 2012 is used in the course, but almost all of the content is also applicable to Analysis Services in SQL Server 2008.ObjectivesUpon completion of this course, the student will be able to: • Use MDX to create business logic inside cubes• Use MDX to create business logic while querying cubes• Understand complex MDX formulas• Learn how to create MDX named setsAudienceThis course is intended for individuals that are developing or supporting Analysis Services multidimensional cubes. PrerequisitesBefore attending this course, it is recommended that students have the following skills: • Familiarity with Microsoft Analysis Services• Familiarity with multidimensional objects such as cubes, dimensions, hierarchies, levels, members, and measures• It is recommended that the students have taken the SolidQ course Accelerated SQL Server 2012 Analysis Services, or have equivalent knowledgeNOTE: This is an introductory MDX course. But it is also intended to be a very challenging course. We encourage students to be well prepared before taking this course. It is very difficult to grasp the basics of MDX without already being thoroughly familiar with the multidimensional concepts used in Analysis Services.
Price: $2,180
To find out more available information
about this course, please contact us:

To find out more available information
about this course, please contact us:

To find out more available information
about this course, please contact us:


MDX for Creating Business Logic


Module 01: Introduction to MDX

  • Why MDX is used
  • The MDX Query
  • Creating an MDX Calculation
  • Comparing MDX to SQL

Module 02: MDX Concepts

  • Dimensions, hierarchies, levels and members
  • Measures
  • Default member and current member
  • Tuples
  • Sets
  • Functions
  • Punctuation

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

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

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

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

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

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