Simple MDX case and description (1)

You can use SQL to query, manage, and operate data in a relational database. In SQL Server, MDX statements are used to operate and manage multi-dimensional databases. MDX can be used not only in SSAS for query management of multi-dimensional databases, you can also use SSIS and SSRS to assist in the ETL process and report development process. Because MDX statements are not standardized, different BI solutions provide different multidimensional data query technologies. Therefore, the MDX language in SQL Server can only be used in SQL Server, if you want to query other databases, such as ORACLE and Hyperion, you need to know the corresponding MDX, which may be different from MDX in SQL Server in syntax or usage. This article focuses on MDX in SQL Server.

This article starts with a simple MDX query and parses each part of the query separately to learn the MDX syntax. But before that, we should first introduce the use case of multi-dimensional data sets.

 

I. Multi-dimensional dataset Used in the case

Dimension:

 

 

Figure 1: dimension information

Metric value:

 

Figure 2: measurement information

 

The two measurements Max Units Ordered and Total Units Ordered are the exported measurement, that is, the measurement obtained through name calculation.

Dimension Structure:

 

Figure 3:ProductDimension Structure

 

 

Figure 4:StoreDimension Structure

 

 

Figure 5:Time By DayDimension structure, that is, time dimension

 

 

Figure 6: Overall structure of a multi-dimensional dataset