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.
Describes basic MDX concepts and provides a comparison between SQL syntax and MDX syntax.
Gives a basic overview of the construction of a simple MDX query.
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.