OLAP_MDX Learning and Finishing

The MDX (multi-dimensional expressions) multidimensional expression is a syntax that supports the definition and manipulation of multidimensional objects and data, and the standard OLAP query language

Some of the features that MDX provides can also be provided by SQL. However, processing MDX for multidimensional objects is more visual and effective than SQL.

MDX Main Concepts: Dimensions (dimensions), levels (levels), members, and measures (measures)

Units (cells), tuples (tuples), and collections (sets)

Axis dimensions and slice dimensions (axis and SLicer Dimensions)

Dimension: That is, the angle of the object of observation, directly surrounded by "", as I select the time dimension to use [times], and then under the dimension of the selection of its corresponding level.

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

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

Measure (Measure): A metric is actually a member of a dimension dimension, meaning that a measure (Measure) is a dimension of any cube. For example, the unqiuename of unit Sales is [Measures]. [Unit Sales]

UniqueName are representations inside OLAP elements, and in MDX query languages, you can use UniqueName to represent elements.

MDX also provides the representation of fuzzy and other equivalent elements:

<1> omit dimension names represent levels, and if the level name of one dimension is unique throughout the cube, you can omit the dimension name to query the level. such as product. [Product Family] can be written as [product Family]

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

The <3> member hangs at the level, that is, the member is not necessarily written as [dimension]. (Parent Member UniqueName). [Member Name], can be written as [dimension]. [Level]. [Member Name], such as March 2000 can be written as [time]. [Months]. [3]

Fuzzy query notation, based on the assumption that there is no repetition, that if there are duplicate elements that take the first found element as the result of the query, an error may occur, so it is recommended that a complete notation be used

tuples, Collections

A tuple is used to define a slice from a multidimensional data, which consists of an ordered collection of individual members of one or more dimensions. Tuples do not contain multiple members from the same dimension (which can be understood 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, it can be surrounded without (), i.e. (time. [second half]) can be expressed as: time. Second half of

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

For example, here's a collection with two tuples:

{(Time. [first half], route. Non-Terrestrial. Aviation), (time. [Second half], route. Non-Terrestrial. by sea)}

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

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

Collection values are grouped by a group of members expressed in tuples, or values in cells represented by tuples 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 the 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 seen as a member of the axis selection.

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

Axis Dimensions and slicer dimensions

When designing a Multidimensional Expressions (MDX) query, the application generally looks at the cube and divides the dimension collection into two subsets:

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

2, slicer dimension, a 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 are querying, the dimensions that are used by the cubes that will be queried are separated from the dimensions that are created in the cube that is 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, there are three dimensions in the multidimensional data set. Query to provide a matrix within which you can compare packages measures across alignments and times

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

SELECT

{Route.nonground.Members} on COLUMNS

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

From

TestCube

WHERE ([mesures].[ Packages])

Specify the contents of the dimension:

Axis dimensions Determine the edges of a multidimensional result result set. Multidimensional Expressions (MDX) uses a 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 a 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 decomposed into:

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

<axis_name>::=columns| ROWS

The Axis dimension can only accept collection <set> If you specify a member collection manually, you must enclose it with {}, and if you use an MDX aggregate function, you do not need to enclose {} because the aggregate function returns a value of 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 {}, and the rows axis uses the aggregate function Crossjoin, which returns the intersection of two sets, and the rows axis contains two dimensions state and product.

Specifying the contents of a slicer dimension

Slicer Dimension filter multidimensional data, which restricts the data that is returned by including the Slicer dimension in the WHERE clause of a Multidimensional Expressions (MDX) query.

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

Slicer dimensions can also be specified by displaying the WHERE clause of the MDX syntax. The syntax of the WHERE clause can be decomposed into:

[WHERE [<silcer_specification>]]

The 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])

OLAP_MDX Learning and Finishing