MDX members, sets and tuples

We have already touched upon OLAP query language, to be precise MDX. Today we would like to highlight MDX primary components: members, sets and tuples.

What are OLAP and MDX?

Practice makes perfect, so let’s return to OLAP definition once again. OLAP or Online Analytical Processing a method for multidimensional data analysis through query language. This BI tool was created for effective data analysis, reporting, planning and forecasting. The main benefit is that the data can be analyzed from the different points of view.

OLAP analysis works as following:

1. The data collected from the multiple sources;
2. Then the data is stored in the warehouse;
3. And after, the data is sorted and organized in different data cubes and categorized by dimensions.

There are four core OLAP features:

• Time intelligence;
• Intuitive interface;
• Support for complex calculations;
• Multidimensional data view.

As OLAP is based on query language, we are moving on to the next part – MDX language.

Multidimensional Expressions (MDX) is a query language for OLAP in database management system. MDX is aimed at work with OLAP members, dimensions and hierarchies. It allows querying data from SQL server and get a dataset with axis and cell data.

What are the main components of MDX query? And how does it work? Follow the next part to learn.

MDX structure

Being a query language MDX has its own syntax, stage of query complexity and structure.

Clauses

Talking about MDX structure there are four basic clauses:

Query types

MDX queries can be of two types: simple and complex.

1. Simple queries can be generated easily as they specify data from a single category. For example:

SELECT
FROM
[Adventure Works]

2. Complex queries generation usually requires special technical knowledge and not every ordinary used can write them. They provide more complex request based of several categories. For example:

SELECT
{[Measures].[Internet Sales Amount]} ON 0,
Non empty {[Product].[Product Categories].[Category].Members} ON 1
FROM [Adventure Works]

Such BI tools as Ranet OLAP are very useful if you want to have an option: generate MDX query manually or automatically.

Ranet OLAP by GalaktikaSoft has two query modes suitable for both developers and users without technical knowledge:

1. Design mode: MDX query is generated by the system automatically. Good solution for fast data analysis in companies without IT staff.
2. Custom query mode: MDX query is generated by the user manually. The benefit is the query can be of any complexity and meet all the user requirements.

Data classification

MDX includes several types of OLAP data to work with and return through queries:

• Scalar is either a number or a string.
• Dimension is an element in a cube and it’s initial part to arrange measure and attribute.
• Hierarchy is primarily a dimension hierarchy and is organized inside dimensions, can’t be determined by unique names.
• Level belongs to a dimension hierarchy and in its turn is organized in it.
• Members represents concrete data of OLAP dimension.
• Tuple is a collection of members from various dimensions and can be determined by several members.
• Set in its turn is a collection of tuples from identical dimensions/hierarchies.
• Other: there are also member properties as an alternative to data attributes.

Let’s overview more closely three main components: member, set and tuple.

Members

Member is an element in dimension, which represents at least one data occurrence. A member can be described as one or more records in the database whose value in this column falls under this category. It is also the lowest reference level when describing cell data in a cube.

Members can also be created as part of a MDX query to return data based on evaluated expressions, instead of the data stored in the cube for the query. Such members are called calculated members, and they provide more power and flexibility to the MDX.

WITH keyword is used in an MDX query to define a calculated member. For example, if you want to provide a predictive estimate of all the packages, adding 10% of the existing value of the indicator Packets, you can simply create a calculated element that provides information and use it just like any other element in the cube. For example:

WITH MEMBER [Measures].[PackagesForecast] AS
'[Measures].[Packages] * 1.1'

MDX performs a number of functions for extracting elements from other MDX entities, such as dimensions and levels, so explicit references to the element are not always necessary. For example, the FirstChild function allows you to extract all the elements from a given dimension or level. In order to get the first child of the Time dimension, you can explicitly specify it:

Time.[1st half]

Tuples

Another important component is tuple. A tuple is an ordered composition of members taken from different dimensions. A tuple is specified by commas and parentheses:

(Time.[2nd half], Route.nonground.air)

A tuple can be used either to refer to the cell value that the tuple indicates, or to indicate a combination of elements when used in a function.

A tuple can include elements in several dimensions, as well as several elements from one dimension. The term dimensionality is used to refer to the sizes described by elements in a tuple. Order plays a role in the dimension of the tuple and can affect the use of the tuple in the set.

Sets

Set in its turn is an ordered composition of tuples with the same dimensionality. The set is most often used for sizing axes and slices in an MDX query, and therefore it can have only one tuple or in some occasions, it can be empty.

A set of two tuples can be presented as following:

{ (Time.[1st half], Route.nonground.air), (Time.[2nd half], Route.nonground.sea) }

Explicit input of tuples and putting them into braces is not the only way to get a set. MDX supports a wide range of functions that return sets.

The colon operator allows you to use the natural order of the members for set creation. Other MDX functions that return sets can be used either by themselves or as part of a comma-separated list of members.

As like as tuples, sets also have dimensionality. Since a set consists of tuples, the dimensionality of a set is expressed by the dimensionality of each tuple in it. Because of this, the tuples in the set must have the same dimensionality.

There is also such term as named set. A named set is a set for which an alias was created. The named set is most often used in complex MDX queries to facilitate reading of these queries and to facilitate maintenance.

Summary

We hope this article was useful for you and you’ve discovered something new in MDX topic. Remember, you can always find more related info in our blog, as well as practice with OLAP for free with our demo and 30-day free trial.

Generate the CSS style for border radius, fonts, transforms, backgrounds, box and text shadows with the online CSS code generators.