SQL Server Analysis Services Multidimensional MDX




By:
Overview

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.

Explanation

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.

Sample SSAS MDX Query
Additional Information
  • MDX is a very elaborate query language. Consider reading in detail about this here.

Last Update: 4/18/2017




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools