Till the time, we have used MDX multiple times in our blog, but what’s it exactly? MDX is a query language that is used to retrieve data from multidimensional databases. It is used to query multidimensional data from analysis services and supports two distinct modes. SSAS isn’t a proprietary language but a standards-based query language that is used to retrieve data from OLAP databases. It is a part of the OLAP specification sponsored by Microsoft. Although MDX has a similar syntax to SQL, still it is significantly different.

MDX Queries

A multidimensional database is typically referred to as a cube and is the foundation of the multidimensional database, and each cube contains one or more dimensions. The subsequent important term in MDX is set. A set may contain zero, one, or more tuples. A set with zero tuples is named as an empty set. An empty can be written as:

{customer.Country.Australia, Customer.Country.Canada, Customer.Country.Australia}

Further, the basic syntax of an MDX query can be given as:




[WITH <formula_expression> [, <formula_expression> ....]]

SELECT [<axis_expression>,[<axis_expression> ....]]

FROm [<cube_expression>]

[WHERE [Slicer_expression]]

Keywords like “with, select, where, from” that can be used with the expression are clauses. In MDX query, if anything is given within square brackets, it means it is optional and can be skipped if not required. Within the above expression, we can see that “with” and “WHERE” clauses are options while SELECT and FROM clauses are mandatory. The “WITH” clause is generally used for custom calculations and operations.

Select statement and axis expression in MDX Query

The SELECT statement within the MDX query is used to retrieve a subset of multidimensional data within a cube. In SQL, the SELECT statement can specify which column to be included and which can be skipped. This is the reason, select statement analyzes two-dimensional data within the SQL and MDX query analysis of multidimensional data.

When it is two-dimensional data, it means there are two axes, X and Y. you can retrieve data either from the X-axis or Y-axis. At the equivalent time, the MDX query gives you the capability of retrieving data from one, two, or more axis. The basic syntax of SELECT statement in MDX query can be given as:

SELECT [<axis_expression>,[axis_expression> ...]]

The axis expression specifies the dimension you are interested in viewing or retrieving. These dimensions are named as axis dimensions because data from these dimensions is projected on to the corresponding axis. The basic syntax of AXIS expression in MDX query can be given as:

<axis_expression>:=<set>ON(axis | AXIS (axis number) | axis number)

Here, you can retrieve multi-dimensional result sets. A set is a collection of tuples that is used to form an axis dimension. It gives you the capability to define up to 128 axes within the SELECT statement. The first five axes are aliases and named as ROW, COLUMN, SECTION, PAGE, CHAPTER, etc. Axes can also be given as numbers that allow you to specify more than five dimensions within the SELECT statement. Let us understand the concept with the help of an example below.

SELECT Measures.[Internet Sales Amount] ON COLUMNS,

[Customer].[Country].MEMBER On ROWS,

[Product].[Product Line].MEMBERS On PAGES

FROM [Adventure Works]




SELECT Measures.[Internet Sales Amount] ON 0,

[Customer].[Country].MEMBER On 1,

[Product].[Product Line].MEMBERS On 2

FROM [Adventure Works]

AXIS Dimensions

When we are defining a SELECT statement, we build axis dimensions. A SELECT statement specifies a set of attributes for each dimension like ROW, COLUMN, and additional axes.

FROM clause and CUBE specifications

The FROM clause in an MDX query specifies the cube from which you are going to retrieve the data. It is similar to the FROM clause in SQL that is used within a table. The FROM clause is necessary to define for each MDX query. The basic syntax of FROM clause for an MDX query can be given as:

FROM <cube_expression>

Within the cube expression, you have to give the name of the cube that you want to use for retrieving data. In SQL, we can define multiple tables using the FROM clause. At the equivalent time, an MDX query allows using a single cube only.

WHERE clause and SLICER specifications

In a relational database system, we issue a query that returns only a specific portion of data available within a table, a set of joined table, or joined databases, etc. It is accomplished through SQL statements that specify which data should be returned and which to avoid as the result of running query. Take the example of a product table that contains sales information about a product.

Product ID	Product Line	Color	Weight	Sales
1	Accessories	Silver	5.00	200.00
2	Mountain	Grey	40.35	250.00
3	Road	Silver	50.23	2500
4	Touring	Red	45.11	2000.00

Here is a simple query to return just two columns from the table:

SELECT ProductLine, Color

FROM Product

This query will return the Product Line, and Color column from the product table. The output will look like this:

Product Line	Color
Accessories	Silver
Mountain	Grey
Road	Silver
Touring	Red

To display the whole table, you can use the syntax given below.

SELECT*

FROM Product

To limit the result set, you can add a WHERE clause and retrieve the selected data. Now, let us see how to write an MDX query for the equivalent. In this example, the given table can be used as a Fact table and Dimension Table both. An MDX query against the cube that produces the equivalent results as that of the SQL query is:

SELECT Measures.[Sales] On COLUMNS,

[Product].[Product Line]. MEMBERS on ROWS

FROM [ProductCubbe]

WHERE ([Product].[Color].[Silver])

The final output for the query will be:

ProdcutLine	Sales
Accessories	200
Road	2500.00

The subsequent important term is SLICER dimension that you build when working on a WHERE statement. It can be taken as a filter to remove unwanted members or dimensions.

WITH clause and calculated members

Often business needs involve calculations that must be formulated withwithin the scope of a specific query. The MDX “WITH” clause provides you with the ability to compute such calculations withwithin the scope of a query. Additionally, we can retrieve data outside the cube using the Lookup method. The WITH clause allows you to perform calculations like sets, calculated members, cell computations, etc. The basic syntax of MDX WITH clause can be given as:

[WITH <formula_expression> [,<formula_expression>…]]

the formula_expression will vary depending upon the type of calculations. calculations are separated by commas.

MDX Expressions

An MDX expression is a partial MDX statement that can be used to evaluate value. They are typically used in calculations and defining values for objects or default measures. It can be used to define security expression to allow or deny access. MDX expressions typically take a member, set, tuple as input and return a value. The basic syntax of an MDX expression can be given as:

Customer.[Customer Geography]. DEFAULTMEMBER

it will return the default member specified for the customer geography hierarchy of the customer dimension

The subsequent important term is MDX function that can be used with MDX queries or functions as per the requirement. It can be called in multiple ways so you should learn it practically to use with MDX queries.

So, this brings us to the end of blog. This Tecklearn ‘Detailed Introduction to MDX’ blog helps you with commonly asked questions if you are looking out for a job in Microsoft BI. If you wish to learn Microsoft BI and build a career in Business Intelligence domain, then check out our interactive, Microsoft SSAS Course Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/microsoft-ssas-course-content/

Microsoft SSAS Course Training

About the Course

Tecklearn’s Microsoft SQL Server Analysis Services (SSAS) training SQL Server Analysis Services (SSAS) is a part of Microsoft Business Intelligence (MSBI), used for developing Online Analytical Processing (OLAP) solutions. This course gives a brief knowledge of developing Analysis, services, databases from the dimension modelling process to the development of cubes and dimensions. You’ll also learn how to deploy and process the Analysis Services database.

Why Should you take Microsoft SSAS Training?

The Average salary for a SQL SSAS Developer is $103,987 per annum – ZipRecruiter.com
Wells Fargo, JPMorgan Chase, Northrop Grumman, DaVita and many other MNC’s are using Microsoft SSAS.
Microsoft BI is a Leader in 2018 Gartner Magic Quadrant for Business Intelligence & Analytics Platforms (9th Consecutive Year).

What you will Learn in this Course?

Introduction to MSBI and Data Warehousing

Fundamentals of Data Warehousing
Concepts of Dimensions, Measures, Metadata and Schemas
Data Marts and Design approaches
Normalization and Denormalization and Schema types
Concepts of Online Analytical Processing and Transactional Processing
OLAP Cube
Explain ETL process and various tasks involved in it
Slowly Changing Dimensions Types
Business Intelligence Concepts
Working of BI with data-warehouse

Introduction to SSIS

Understanding of the MSBI Architecture
Import and Export wizard
Understand SSIS Architecture
Control Flow and its Components (Tasks, Containers and Precedence Constraints)
Data Flow and its Components (Source and Destination Connections and types of Transformations)
System Variables and User-defined variables
Scenarios by combining Control Flow and Data Flow components
Hands On

Transformations and Use-Cases

Data Conversion transformation
Multicast transformation
Union all transformation
Conditional Split Transformation
Merge and Merge Join Transformation
Lookup transformation
Cached Lookup transformation
Foreach loop and use-cases
Bulk-insert task
Archival process using dynamic variables and FST
Advancing Execute SQL Task with Object return type
Types of Outputs usage
Hands On

Slowly Changing Dimensions

Understanding data that slowly changes over time
Learning the process of how new data is written over old data
Detail explanation of three types of SCDs –Type1, Type2 and Type3, and their differences
Hands On

Overview of Fuzzy Look-up Transformation and Lookup and Term Extraction

Concept of Fuzzy matching
How Fuzzy Lookup Transformation varies from Lookup Transformation
Hands On

Concepts of Logging & Configuration

Learning about error rows configuration
Package logging
Defining package configuration
Understanding constraints and event handlers
Hands On

573