Microsoft MDX On-Site Training
Program access to Microsoft SQL Analysis Services (SSAS) databases cannot be implemented using Standard SQL. To solve this, Microsoft has created MDX as a SQL equivalent for multidimensional databases.
This training course focuses on both the basics and the advanced aspects of MDX. The emphasis here is on participants applying the knowledge they gain to complete practical exercises.
Participants must bring their own Windows laptops with them to the course.
The following product will be installed: Microsoft SQL Server 2008 R2 Developer Edition (English), 64-bit, Minimum requirements: Processor: 64-bit, 2.0 GHz, Operating system: x64 Windows 7 or newer, RAM: 4 GB hard drive space: 4 GB
- History and Goals (SQL for OLAP? Industry Standard, Author)
- Relational Databases vs Multidimensional Databases
- Fundamental Components of MDX (Cubes, Measures, Dimensions / Members, Hierarchies, Levels, Sets / Stored Procedures and UDFs)
- OLAP Cube ? Basic Operations (Slicing, Dicing / Pivoting / Rotation / Drill-Down, Roll-Up / Drill-Up, Drill-Across / Drill-Through / Drill-In / Drill-to-Detail)
- MDX Syntax and Naming Conventions, Three Ways of Referencing Objects.
- Default Elements and WHERE Expressions, SQL WHERE versus MDX WHERE
- Sets and Main Set Functions (Union, Intersect, Except / CrossJoin / Extract )
- Functions for Navigation (<hierarchy>.CurrentMember / <member> .Children, .Parent, .Siblings / <level>.Members , <hierarchy>.Members / Descendants, Ascendants, Ancestors)
- EXISTS Function
- FILTER and ORDER Functions, HAVIGLeer (EMPTY) Elements and Cells
- Calculated Measures & Custom Members (CMs) in Query, Client (Session) and Server Scope
- IIF Function, CASE Expression
- IS Operator
- Aggregate Functions: COUNT, SUM, MIN, MAX, AVG, DISTINCTCOUNT
- ParallelPeriod, PeriodsToDate
- Sub Queries, Sub Cubes, Sub Queries with NON VISUAL
- StrToMember(?), StrToSet(?)
- Known Sets
- Lag, Lead<Set>.item(?)
- Generate, Hierarchise
Agenda (Bonus Topics):
- MDX Queries Outside the Cube (MDX queries relating to relational database objects / MDX queries within T-SQL queries)
- Custom Security MDX Expressions
Do you have any questions?
Head of inovex Academy