MDX for Creating Business Logic (SQMDX)

 

Kursüberblick

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.

Zielgruppe

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

Voraussetzungen

Analysis Services Kenntnisse

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

Kursinhalt

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

Preise & Trainingsmethoden

Online Training

Dauer
2 Tage

Preis
  • 1.190,– €
Classroom Training

Dauer
2 Tage

Preis
  • Österreich: 1.190,– €

Derzeit gibt es keine Trainingstermine für diesen Kurs.