Multidimensional Expressions (MDX) is a language for querying and scripting cube data in SSAS Databases. Cubes provide access to aggregated data through MDX. Just as a table is organized by columns, a cube is organized by hierarchies. This tutorial (part I of our MDX training series) covers some basic MDX queries.
Tip: All of the example queries below are taken from the Integrated Financial Planning database. We recommend you install it to your Kepion instance to follow along.
Before you start
We will be using SQL Server Management Studio (SSMS) for processing the MDX code. Upon opening SSMS, you will be prompted to connect to your SQL Server. Configure as follows:
- Server type as Analysis Services.
- Server name can be ".", "localhost", the machine's name if Kepion is running on your personal machine. The machine's IP address is required if Kepion is running in a different location.
- Authentication as Windows Authentication or assigned login depending on how SSMS was set up.
Once everything is entered correctly, select Connect.
In the Object Explorer pane, select the expand box for Databases.
Right-click the database that you would like to query. Hover over New Query and select MDX. If the database you're looking for doesn't exist, ensure the application has been deployed in Kepion.
This will open up a side window where you can enter text. Select Execute when your query is ready. Your results should appear in the Results tab of your query window.
SELECT
SELECT statements can be used to query data from a cube. SELECT must be followed by at least one axis, beginning with COLUMNS. An axis can be defined by a set of Hierarchies (explained below) and their Members.
To select the Member [Salaries] from the [Account] Hierarchy, which is in the [Workforce-Reporting] cube:
SELECT
{
[Account].[Account].[Salaries]
}
ON COLUMNS
FROM [Workforce-Reporting]
The result above is the aggregation of all salaries in the cube. We can break down this value further by including additional hierarchies and members in the query.
If we want to see the salary data for just the Finance Department, we could include it on the Rows:
SELECT
{
[Account].[Account].[Salaries]
}
ON COLUMNS,
{
[Department].[Department].[Finance]
}
ON ROWS
FROM [Workforce-Reporting]
Tip: The first two axes can also be referred to as 0 and 1 instead of COLUMNS and ROWS, respectively.
WHERE
A WHERE clause can be added to the SELECT statement to further scope the query results. Like the ROWS and COLUMNS, a WHERE clause is also defined by a set of Hierarchies and their Members.
To scope the results to just the Plan Scenario, we can write:
SELECT
{
[Account].[Account].[Salaries]
}
ON COLUMNS,
{
[Department].[Department].[Finance]
}
ON ROWS
FROM [Workforce-Reporting]
WHERE
{[Scenario].[Scenario].[Actual]}
Sets
Sets are groupings of hierarchy members or other sets. Each axis we define must be a set, even if it contains just one member.
In the example below, we actually have four sets—one on COLUMNS, two on ROWS, and one in the WHERE clause. On ROWS, the outer set is redundant; however, it's best practice to include it for improved readability.
A set is defined with a pair of curly braces { }. To include multiple sets or members, separate them with a comma.
SELECT
{
[Account].[Account].[Salaries]
}
ON COLUMNS,
{
{[Department].[Department].[Finance], [Department].[Department].[Marketing]}
}
ON ROWS
FROM [Workforce-Reporting]
WHERE
{[Scenario].[Scenario].[Actual]}
Tip: The empty set, defined by just { }, can also be used on axes.
CROSS JOIN
CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table. We can CROSS JOIN one set with another using the star * notation. Ensure both sets are enclosed within curly braces { }.
SELECT
{
{[Account].[Account].[Salaries]}
*{[Time].[FiscalYear].[FY20]}
}
ON COLUMNS,
{
{[Department].[Department].[Finance], [Department].[Department].[Marketing]}
}
ON ROWS
FROM [Workforce-Reporting]
WHERE
{[Scenario].[Scenario].[Actual]}
Measures
Measures are a collection of values within a cube. Measures are used in a similar way as members in a hierarchy. By default, Kepion cubes come with a single Value Measure. If you don't include a measure on the axes in your query, this measure will be used by default.
You can reference this measure directly with:
[Measures].[Measures].[Value]
If there are multiple measures, you can reference them in a similar way:
[Measures].[Measures].[<Measure Name>]
Here's a query with measures in action:
SELECT
{
{[Account].[Account].[Salaries]}
}
ON COLUMNS,
{
{[Measures].[Measures].[Value]}
}
ON ROWS
FROM [Workforce-Reporting]
WHERE
{[Scenario].[Scenario].[Actual]}
*{[Department].[Department].[Finance]}
Next Steps
Continue learning about MDX in MDX Training - Basics II.