Retrieving information from a Tabular database is easy – just query it with some basic DAX functions! In this article, we will learn how to query a Tabular model with some of the MDX expressions, examine the results from DAX querying and analyze data from the tables.

Requirements

The prerequisites for querying with MDX are SQL Server Management Studio 2014 or higher with an installed SSAS Tabular instance and deployed SSAS Tabular model database.

Environment preparation

Querying a SSAS Tabular model database with MDX takes place in SQL Server Management Studio, in the MDX query window.

Multidimensional Expressions (MDX) overview

MDX is expression language which can query and analyze multidimensional data in OLAP cubes (like SSAS Multidimensional model databases, e.g.). Although it is primarily designed for querying a Multidimensional model database, it can query a Tabular model database, too. In the other hand, DAX functions cannot query Multidimensional model databases, because DAX query expressions are specific to Tabular model databases.

Structurally, MDX incorporates XMLA specified syntax. Since it is similar to T-SQL, some DDL statements are present, which can manage and organize multidimensional data constructions. This applies to SSAS Multidimensional model databases only.

Regarding complexity, the main types of MDX expressions are: constant (numeric, string etc.), scalar and object-oriented. These types combine into complex expressions corresponding with statements and MDX functions.

When speaking of querying a SSAS Tabular model database, expressions in this article are combined scalar/constant type, as simple Cube expressions. These expressions only retrieve data from a Tabular cube, show the results in column(s), order data and prepare it for further analysis.

Querying SSAS Tabular model database with MDX

At the beginning of the most MDX queries is SELECT statement, followed with a FROM statement. It is very similar to T-SQL’s (as opposed to the Evaluate statement in DAX):

This query is the simplest one, and it returns value 1 (“true”). It means that AW Tabular database is present.

Selecting the table Product Name from Product table, SELECT statement in T-SQL looks like this:

The corresponding statement written in MDX, would be, logically:

Notice the braces {}, which are the operators like the parenthesis in DAX. They enclose the statements and make possible to add complexity in query writing. MDX queries are case-insensitive like DAX queries.

This query would retrieve Product Name column and Product table from AW Tabular cube, but instead of showing the single column, it raises an execution error:

The main reason is in type of the database model and syntax: Multidimensional and Tabular model databases both have dimensions, as crucial concept, but MDX in its syntax recognizes columns, rows and other elements (specific to Multidimensional databases) as axes in query – imagine them as the x, y, z axes on a 3D cube model.

When querying a SSAS Tabular model database, MDX must recognize at least two axes in them: columns and rows, to show data properly:

Now, we set the axes in the query, and its result shows:

As can be seen, all values of the Product Name column show on columns in result, and rows show the corresponding years (in Fiscal Year column) and number of items (in this case, bicycle parts). Therefore, instead of three axes in MDX query, when querying a SSAS Tabular model database only two axes are enough to retrieve data.

This example queries the same table within both axes:

The result of the query shows:

The value “1” means “true” in this case, and value “(null)” means there are no related data within Standard Cost column.

1. Viewing particular value (item) from specified column and NON EMPTY function

   With MDX, it is possible to see the single value from column in Tabular model database:

   1 2 3 4 5

   select {{[Product].[Model Name].[Model Name]}.Item(53)} on 0 from [AW Tabular]

   

   In this example, the result shows the 53^rd item (random picked) from Model Name column. Notice the defined axis “on 0” – instead of defining “columns” (we will see in further examples) or “rows”, that single value is placed on initial beginning of the tabular view of query result.

   With inclusion of NON EMPTY function, single calculated value from the particular measure (Internet Total Sales) displays:

   1 2 3 4 5 6

   select non empty {[Measures].[Internet Total Sales]} on columns FROM [AW Tabular]

   

2. Tuple function

   Tuple is specified set of data, a combination of two or more dimension members (in this case two values from Year column).

   1 2 3 4 5

   select {[Date].[Year].[2005] : [Date].[Year].[2016]}  on rows, {[Measures].[Internet Total Margin]} on columns from [AW Tabular]

   

   Notice the specified values from the Year column making the range of years, and corresponding values from Internet Total Margin in the query result.

3. CHILDREN and MEMBER Expression

   This expression shows the set of data which is related to specified columns or measures. Usually, these set of values are presenting as children or members, and it is related to hierarchy. They are ordered hierarchically in the query result.

   1 2 3 4 5

   select [Measures].[Internet Total Freight] ON COLUMNS, [Internet Sales].[Freight].CHILDREN ON ROWS FROM [AW Tabular]

   

   In this case, Internet Total Freight measure is based on calculation with Freight column, and thus, they can combine.

   Notice that braces are not present in the query, but the query is still valid without them.

   For showing only the values from a desired column with the CHILDREN expression, in correspondence with a non-related column, use this query:

   1 2 3 4 5 6

   select {[Date].[Year].[Year]} on 0, {[Geography].[City].children} on 1 FROM [AW Tabular]

   

   Note: In this expression, on 0 stands for on columns, and on 1 stands for on rows axis.
   

   Here is one more example using the MEMBER expression

   1 2 3 4 5

   Select [Measures].[Internet Total Sales] on Columns, [Product].[Category].members on Rows From [AW Tabular]

   
   

4. WHERE clause

   Addition of the WHERE clause is the one way to filter specific data from a table, and it is similar to WHERE clause in T-SQL:

   1 2 3 4 5 6 7

   select {[Geography].[City].[City]} on columns, {[Date].[Month Name].[Month Name]} on rows from [AW Tabular] where {[Geography].[Country Region Name].[France]}

   

   The value from the Country Region Name column, „France“ is related to the column City in Geography table, and the query included in result only cities from France.

   The WHERE statement is actually the slicer axis, which adds an extra dimension both in Multidimensional and Tabular model databases.

5. CASE expression

   This expression is a comparison based on the logical order of elements, which returns a query result.

   In this example, create a new measure called Sales Amount Overview corresponding with a specified values range from Internet Total Sales measure:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   with member [Measures].[Sales Amount Overview] as case [Measures].[Internet Total Sales]    when 69712.49 then 'Great'    when 1045.6 then 'Bad'    else '(normal)' end   select {[Measures].[Internet Total Sales] , [Measures].[Sales Amount Overview] } on columns ,{[Internet Sales].[Order Date].members} on rows from [AW Tabular]

   

I hope you enjoyed this article, and happy querying a SSAS Tabular model database with MDX!

References

• Using Cube and Subcube Expressions
• MDX Language Reference (MDX)
• MDX Query Fundamentals (Analysis Services)

• Author
• Recent Posts

Daniel Tikvicki

Daniel is a librarian who ran into a vortex of IT world, where he is levitating and learning constantly. He likes books, merely all forms of artistic expression (being a musician/poet himself), and he is underground publisher (fanzines and experimental music). Also, the points of interest include theology, mythology and pseudoscience.

Daniel is currently working for ApexSQL LLC as Software sales engineer where he specializes for the BI environment.

Latest posts by Daniel Tikvicki (see all)

• How to monitor the SQL Server tempdb database - July 6, 2017
• How to monitor total SQL Server indexes size - June 12, 2017
• How to set and use encrypted SQL Server connections - May 8, 2017