The basic Multidimensional Expressions (MDX) query is the SELECT statement-the most frequently used query in MDX. By understanding how an MDX SELECT statement must specify a result set, what the syntax of the SELECT statement is, and how to create a simple query using the SELECT statement, you will have a solid understanding of how to use MDX to query multidimensional data.

Specifying a Result Set

In MDX, the SELECT statement specifies a result set that contains a subset of multidimensional data that has been returned from a cube. To specify a result set, an MDX query must contain the following information:

  • The number of axes that you want the result set to contain. You can specify up to 128 axes in an MDX query.

  • The set of members or tuples to include on each axis of the MDX query.

  • The name of the cube that sets the context of the MDX query.

  • The set of members or tuples to include on the slicer axis.

To identify the query axes, the cube that will be queried, and the slicer axis, the MDX SELECT statement uses the following clauses:

  • A SELECT clause that determines the query axes of an MDX SELECT statement. For more information about the construction of query axes in a SELECT clause
  • A FROM clause that determines which cube will be queried. For more information about the FROM clause
  • An optional WHERE clause that determines which members or tuples to use on the slicer axis to restrict the data returned.

SELECT Statement Example

The following example shows a basic MDX query that uses the SELECT statement.

SELECT
NON EMPTY { 
	[MEASURE_1],
	[MEASURE_2] 
} ON COLUMNS,
NON EMPTY { 
	{ { [TABLE].[COLUMN_NAME].&[FILTER1],
		[TABLE].[COLUMN_NAME].&[FILTER2] } } *
	{ HIERARCHIZE ( [TABLE].[COLUMN_NAME].[COLUMN_NAME].AllMembers ) } *
	{ HIERARCHIZE ( FILTER ( [TABLE].[COLUMN_NAME].[COLUMN_NAME] , [TABLE].[COLUMN_NAME].CurrentMember.MemberValue >= DATEADD("m",-6,NOW()) ) ) } *
	{ HIERARCHIZE ( FILTER ( [TABLE].[COLUMN_NAME].[COLUMN_NAME] , LEFT([TABLE].[COLUMN_NAME].CurrentMember.MemberValue,1) = 'Partial String' ) ) }
} ON ROWS
FROM (
	SELECT {
		[TABLE].[COLUMN_NAME].[COLUMN_NAME].[1st Option],
	 	[TABLE].[COLUMN_NAME].[COLUMN_NAME].[2nd Option]
	} ON COLUMNS
	FROM [CUBE]
)

Source

MDX Query - The Basic Query

  • No labels