Querying Multidimensional Data with MDX
Applies To: SQL Server 2016
Multidimensional Expressions (MDX) is the query language that you use to work with and retrieve multidimensional data in Microsoft Analysis Services. MDX is based on the XML for Analysis (XMLA) specification, with specific extensions for SQL Server Analysis Services. MDX utilizes expressions composed of identifiers, values, statements, functions, and operators that Analysis Services can evaluate to retrieve an object (for example a set or a member), or a scalar value (for example, a string or a number).
MDX queries and expressions in SQL Server Analysis Services are used to do the following:
Return data to a client application from a SQL Server Analysis Services cube.
Format query results.
Perform cube design tasks, including the definition of calculated members, named sets, scoped assignments, and key performance indicators (KPIs).
Perform administrative tasks, including dimension and cell security.
MDX is superficially similar in many ways to the SQL syntax that is typically used with relational databases. However, MDX is not an extension of the SQL language and is different from SQL in many ways. In order to create MDX expressions used to design or secure cubes, or to create MDX queries to return and format multidimensional data, you need to understand basic concepts in MDX and dimensional modeling, MDX syntax elements, MDX operators, MDX statements, and MDX functions.
|Key Concepts in MDX (Analysis Services)||You can use Multidimensional Expressions (MDX) to query multidimensional data or to create MDX expressions for use within a cube, but first you should understand SQL Server Analysis Services dimension concepts and terminology.|
|MDX Query Fundamentals (Analysis Services)||Multidimensional Expressions (MDX) enables you to query multidimensional objects, such as cubes, and return multidimensional cellsets that contain the cube's data. This topic and its subtopics provide an overview of MDX queries.|
|MDX Scripting Fundamentals (Analysis Services)||In SQL Server Analysis Services, a Multidimensional Expressions (MDX) script is made up of one or more MDX expressions or statements that populate a cube with calculations.|
An MDX script defines the calculation process for a cube. An MDX script is also considered part of the cube itself. Therefore, changing an MDX script associated with a cube immediately changes the calculation process for the cube.
To create MDX scripts, you can use Cube Designer in the SQL Server Data Tools (SSDT).