Mdx (Analysis Services (sql server)) mdx

MDX (Analysis Services (SQL Server))

MDX

MDX 1

MDX Overview 3

Introduction to MDX 3

Key Concepts in MDX 3

Dimensions, Levels, Members, and Measures 3

Cells, Tuples, and Sets 4

Axis and Slicer Dimensions 5

Calculated Members 5

User-Defined Functions 5

PivotTable Service 5

Comparison of SQL and MDX 5

Basic MDX 6

The Basic MDX Query 6

Basic MDX Syntax - SELECT Statement 7

Basic MDX Query Example 7

Members, Tuples, and Sets 8

Members 8

Tuples 9

Sets 11

Axis and Slicer Dimensions 12

Specifying the Contents of an Axis Dimension 12

Specifying the Contents of a Slicer Dimension 13

Establishing Cube Context 13

Advanced MDX 14

Creating and Using Property Values 14

Using Member Properties 14

Using Cell Properties 18

Building Named Sets in MDX 22

Using WITH to Create Named Sets 24

Building Calculated Members in MDX 25

Using WITH to Create Calculated Members 25

Using Functions in Calculated Members 26

Conditional Expressions 29

Building Caches in MDX 30

Using WITH to Create Caches 30

Building Calculated Cells in MDX 31

Using WITH to Create Calculated Cells 31

Creating and Using User-defined Functions in MDX 33

Using a User-Defined Function in MDX 33

USE LIBRARY Statement 33

DROP LIBRARY Statement 34

Creating User-Defined Functions 34

Using Writebacks 35

Lowest-Level Member Writebacks 35

Aggregate-Level Member Writebacks 35

Using DRILLTHROUGH to Retrieve Source Data 36

Understanding Pass Order and Solve Order 36

Pass Order 36

Effective MDX 42

Comments in MDX 42

Working with Empty Cells 43

Empty Cell Evaluation 43

NON EMPTY Keyword 44

CoalesceEmpty Function 45

Other Functions 46

Creating a Cell Within the Context of a Cube 46

Working with the RollupChildren Function 47

Custom Member Properties 47

IIf Function 48

WHERE Clause Overrides 48

MDX Functions in Analysis Services 48

MDX Function Reference 49

MDX Syntax Conventions 49

MDX Function List 49

Registered Function Libraries 54

Visual Basic for Applications Functions 54

Excel Functions 55

User-Defined Functions with MDX Syntax 56

Calling a User-Defined Function within MDX 56

Function Precedence and Qualification 57

The Multidimensional Expressions (MDX) language is used to manipulate multidimensional information in Microsoft® SQL Server™ 2000 Analysis Services. MDX is defined in the OLAP extensions in OLE DB.

Similar to SQL in many respects, MDX provides a rich and powerful syntax for the retrieval and manipulation of multidimensional data, such as the data stored in cubes on the Analysis server. Analysis Services supports MDX functions in the definitions of calculated members, as well as a full language implementation for building local cubes and querying cube data using PivotTable® Service with OLE DB and Microsoft ActiveX® Data Objects (ADO).

Additionally, MDX supports the creation and registration of user-defined functions. You can create user-defined functions to operate on multidimensional data and accept arguments and return values in the MDX syntax.

The following topics provide more information about MDX.

Topic	Description
MDX Overview	Describes basic MDX concepts and provides a comparison between SQL syntax and MDX syntax.
Basic MDX	Gives a basic overview of the construction of a simple MDX query.
Advanced MDX	Details more advanced information, such as named sets and calculated members, for complex MDX queries.
Effective MDX	Provides a list of tips, workarounds, and feature discussions regarding MDX.
MDX Functions in Analysis Services	Details the statements and functions supported by MDX.

MDX Overview

This section introduces Multidimensional Expressions (MDX) and explains some of the concepts behind its structure and syntax. It contains the following topics.

Introduction to MDX

MDX, an acronym for Multidimensional Expressions, is a syntax that supports the definition and manipulation of multidimensional objects and data. MDX is similar in many ways to the Structured Query Language (SQL) syntax, but is not an extension of the SQL language; in fact, some of the functionality that is supplied by MDX can be supplied, although not as efficiently or intuitively, by SQL.

As with an SQL query, each MDX query requires a data request (the SELECT clause), a starting point (the FROM clause), and a filter (the WHERE clause). These and other keywords provide the tools used to extract specific portions of data from a cube for analysis. MDX also supplies a robust set of functions for the manipulation of retrieved data, as well as the ability to extend MDX with user-defined functions.

MDX, like SQL, provides data definition language (DDL) syntax for managing data structures. There are MDX commands for creating (and deleting) cubes, dimensions, measures, and their subordinate objects.