MDX for Creating Business Logic

BI-MDXBL12-301-EN

Learn to use MDX to create business logic inside cubes and while querying them. Understand complex MDX formulas and learn to create MDX named sets.

}

Duration: 16 hours

Level: 300

Objectives

  • 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 sets

”Clearly knows his stuff. Good presentation skills. Good jokes.”

WHEN and WHERE is this course running?

What does this course cover?

The MDX language is needed to create business logic for multidimensional cubes in Analysis Services. Developed in-house by SolidQ, the MDX for Creating Business Logic course teaches you 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.

Who is this course designed for?

Individuals that are developing or supporting Analysis Services multidimensional cubes.

Pre-requisites: What do you need to know?

  • 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 knowledge

NOTE: The MDX for Creating Business Logic course is an introductory 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.

All our courses can be offered as a private delivery and tailored for your team's specific needs

Course outline

Module 01: Introduction to MDX
  • Why MDX is used
  • The MDX Query
  • Creating an MDX Calculation
  • Comparing MDX to SQL

LAB: Writing Your First MDX

Module 02: MDX Concepts
  • Dimensions, hierarchies, levels and members
  • Measures
  • Default member and current member
  • Tuples
  • Sets
  • Functions
  • Punctuation

LAB: 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: 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: 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: 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: 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: 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: Using the Order, Filter, Top/Bottom and Rank Functions

WHEN and WHERE is this course running next?

This course may be scheduled in more than one region. Please check availability in your country.