SQL Server Analysis Services Multidimensional MDX
By: Siddharth Mehta
Multidimensional Expressions (MDX) is one of the primary query languages of SSAS. After the SSAS multidimensional data model has been deployed, the next step is to browse the data. MDX is a very sophisticated and powerful language that contains all the constructs of a query language. SQL Server Management Studio has features to browse a cube and execute MDX queries. In this chapter we will look at how to execute a simple MDX query against the AdventureWorks cube.
1) Open SSMS and connect to your SSAS instance. Right-click on the AdventureWorks database, and select New Query > MDX.
2) Type the below query, and click on the Execute button as shown below.
SELECT Measures.[Internet Sales Amount] on COLUMNS, Product.[Product Categories].[Category] on ROWS FROM [Adventure Works]
The SELECT clause sets the query axes as the Internet Sales Amount member of the Measures dimension and the Category level of Product Categories hierarchy of the Product dimension. MDX queries can have 0, 1, 2 or up to 128 query axes in the SELECT statement. The FROM clause names the source of the data for the MDX query. However, the MDX FROM clause is restricted to a single cube. In the above query, the FROM clause indicates that the data source is the Adventure Works cube.
- MDX is a very elaborate query language. Consider reading in detail about this here.