One of the MDX syntaxes in SSAS

MDX is the abbreviation of Multidimensional expressions, multidimensional expression, is the standard OLAP query language. MDX support is provided in most olapserver, such as Microsoft SQL Server OLAP services,sas,hyperion essbase. Supports multidimensional objects for data definition and operation. Many aspects of MDX are similar to Structured Query Language (SQL) syntax, but he is not an extension of the SQL language; In fact, some of the functionality provided by MDX can also be provided by SQL, albeit not as effective or intuitive.

As with SQL queries, each MDX query requires a data request (SELECT clause), a starting point (from clause), and a filter (a WHERE clause). These keywords and other keywords provide tools to extract specific portions of data from the cube. MDX also provides a reliable set of functions for retrieving data to operate while also having the ability to extend MDX with user-defined functions.

MDX provides expression query syntax for multidimensional databases for cube data, and provides a number of powerful analytic functions to support common OLAP analysis.

Grammar

Dimensions, levels, members, and so on, are generally marked with unique name UniqueName, you can surround name with [], and if name has a space or a number at the beginning, you must use [], otherwise you can ignore it. UniqueName is a method that is represented by a hierarchical structure. That is, recursion shows the ancestor's name.

Dimensions (Dimension): Dimensions are surrounded directly by []. For example, the unique name for product is [Product] or product.

Level: The level of UniqueName is [dimension name]. [level name], such as [year]. [2001], similarly, if there are no spaces, [] can be omitted, as year. [2001].

Members (member): The UniqueName format for members is [dimensions]. (Parent member UniqueName). [member Name], such as: The February 2003 UniqueName on the Time dimension is [2003]. [1]. [2], the middle 1 is 1 quarters, because the structure of the dimension is year, quarter, month.

Metric (Measure): A metric is actually a member of a dimension dimension, i.e. the metric (Measure) is a dimension of any cube. For example, the Unqiuename for unit sales is [Measures]. [Unit Sales]

UniqueName is an internal representation of OLAP elements, and in the MDX query language, you can use UniqueName to represent elements.

MDX also provides a way to represent vague and other equivalent elements:

<1> omit the dimension name representation level, and if a dimension's level name is unique throughout the cube, you can omit the dimension name to query the level. such as product. [Product Family] can be written [Product Family]

<2> omit dimension names to represent members. If you omit the dimension name, you can mark the highest member of the dimension, such as [time]. [2000] can be written as [2000],[measures]. [Unit sales] can be written as [unit sales].

<3> members are hung at the level, that is, members are not necessarily written as [dimensions]. (Parent member UniqueName). [member Name], which can be written as [dimensions]. [Level]. [member Name], as March 2000 can be written as [time]. [Months]. [3]

Fuzzy query notation, based on the assumption that there is no repetition, if there are duplicate elements, take the first lookup element as the result of the query, there may be errors, therefore, it is recommended to use a complete representation

Tuples, collections

A tuple is used to define a multidimensional data slice, which is composed of an ordered set of individual members of one or more dimensions. Tuples do not contain multiple members from the same dimension (which can be interpreted as coordinates), and tuples are surrounded by ().

Such as:

Time. [Second half])

Time. [Second half],[products]. [Mobile phone]. [Nokia])

If a tuple is made up of members of a single dimension, you can surround it without (), that is, the time. [second half]) can be expressed as: time. Late

A collection (set) is an ordered set of 0, one, or more tuples. Collections are most commonly used to define axis and slicer dimensions in an MDX query, and they may only have a single tuple or may be empty in some cases. In MDX syntax, tuples construct collections with curly braces {}

For example, here is a collection of two tuples:

{(Time. [first half], route, non-terrestrial. Aviation), (time. [Second half], route. Non-Terrestrial. Sea)}

A collection can contain more than one occurrence of the same tuple. This is also possible:

Time. [Second half], time. [Second half]}

A set of member groups represented by a tuple, or a value in a cell represented by a tuple in a collection, depending on the context in which the collection is used.

Note: The collection of individual tuples is not equal to tuples. such as {time. [ Second half]} is not equal to time. [Second half].

In MDX syntax, many function semantics contain tuples and collections, either as parameters or as return values.

MDX BASIC Syntax Structure

First look at the MDX basic syntax structure:

SELECT [axis specification] on COLUMNS,

[Axis specification] On ROWS

From

[Cube name]

WHERE

[Silcer specification]

1. [axis specification] can be viewed as a member selection of an axis.

2, [Silcer specification] represents the member on the slice, can be considered as filtering information, [Silcer specification] optional, if not specified, take the system default dimension member as a slice

Axis Dimensions and slicer dimensions

When you design a Multidimensional Expressions (MDX) query, the application generally views the cube and divides the dimension collection into two subsets:

1, Axis dimension, the dimension of retrieving data for multiple members

2, slicer dimension, the dimension that retrieves data for a single member.

Because both the axis dimension and the slicer dimension are constructed from multiple dimensions of the cube that you want to query, use these terms to separate the dimensions that are used by the cube that you are querying from the dimension areas that are created in the cube returned by the MDX query.

For example:

Suppose there is a cube named TestCube with two simple dimensions named route and time. Because the cube's measures are part of the measures dimension, the multidimensional data has a total of three dimensions. Query to provide a matrix in which you can compare packages measures across routes and times

In the following MDX query instance, the route and time dimensions are used as axis dimensions, and measures dimensions are used as slicer dimensions. The Members function indicates the member of the dimension or level that is to be used to construct the collection, without having to display the individual members of a given dimension or level in an MDX query.

SELECT

{Route.nonground.Members} on COLUMNS

{time. [1st half]. Members} on ROWS

From

TestCube

WHERE ([mesures].[ Packages])

Specify the contents of the dimension:

The axis dimension determines the edge of the multidimensional result set. Multidimensional Expressions (MDX) uses the SELECT clause to specify an axis dimension by assigning a collection to a specific axis.

In the following syntax example, each <axis_specification> value defines an axis dimension. The number of axes in the dataset equals the number of <axis_specification> values in the Multidimensional Expressions (MDX) query. An MDX query can support up to 128 specified axes, but almost no MDX query will use more than 5 axes.

<axis_specification> syntax can be broken down into:

<axis_specification>::=<set> on <axis_name>

<axis_name>::=columns| ROWS

An axis dimension can only accept collection <set> If you specify a member collection manually, you must surround it with {}, and if you use an MDX aggregate function, you do not need to surround it with {} because the collection function return value is a collection. An axis dimension can contain several dimensions, such as:

SELECT

{

[Measures]. [Sales_dollars], [Measures]. [Sales_units],

[Measures]. [Sales_units_max]

}

On

Columns

CrossJoin (

{

[State]. [Canada], [state]. [Mexico], [state]. [USA]

},

{

[Product]. [Bread], [Product]. [Dairy], [Product]. [Meat]

}

) on Rows

From

Sales

WHERE

([Time]. [All time], [Employee]. [All Employee])

The columns axis is a manually specified set of member tuples, surrounded by {}, using the aggregate function Crossjoin, which returns the intersection of two sets, with two dimension state and product on the rows axis.

To specify the contents of a slicer dimension

Slicer Dimension Filter multidimensional data, you can limit the data returned by including the Slicer dimension in the WHERE clause of the Multidimensional Expressions (MDX) query.

It is assumed that the dimension assigned to an axis is not shown to be a slicer dimension and is filtered with its default members. The default member is the first member of the highest level.

The slicer dimension can also be specified by the WHERE clause of the MDX syntax. The syntax of the WHERE clause can be decomposed into:

[WHERE [<silcer_specification>]]

A slicer dimension can only accept expressions that are evaluated as a single tuple. As shown in the following example:

WHERE ([time].[ 1st Half],[route]. [Nonground])