- Microsoft SQL Server 2014 Business Intelligence Development Beginner’s Guide
- Reza Rad
- 335字
- 2021-08-13 17:55:24
Time for action – writing an MDX query
In this example, you will learn more about the MDX structure and how to write queries in the MDX language.
- Open SQL Server Management Studio, connect to the Analysis Services instance, and click on Chapter 02 SSAS Multi Dimensional, and then click on New Query.
- In the New Query window, write the following expression:
select [Order Date].[Calendar Year].members on 0 from [Internet Sales]
You will see a list of the years, the sales amount for each year, and the grand total.
- Now, run the following statement:
select [Sales Territory].[Sales Territory Country].members on 0, [Order Date].[Calendar].[Calendar Year].members on 1 from [Internet Sales]
- Check the result and you will see the years listed in each row, the countries listed in each column, and the Sales Amount measure shown in each cell, which represents the sales amount of that country in that year.
What just happened?
MDX is a query language that returns the results in a multidimensional format. In this example, you saw how we define an axis in the output result set. In the first query (step 2), there is only one axis used, which is the column axis. In the second query (step 5), two axes are used, the column axis shows the countries and the row axis shows the years.
An MDX query is not limited to only two axes; you can write an MDX query with 128 axes , for example. However, the client tools are limited to show more than two axes for example, in SSMS, you cannot write a query with more than two axes because it cannot be shown as a result.
The MDX select query structure consists of the SELECT clause on multiple axes, FROM clause, and the WHERE clause.
SELECT [<axis_specification>
[, <axis_specification>...]]
FROM [<cube_specification>]
[WHERE [<slicer_specification>]]
There are many MDX functions to work with time, hierarchies, and other common applications that are helpful when writing MDX queries. You will work with a few of them in the following examples.