MDX Query

MDX Query ..

Workshop - MDX Query

While Pentaho Analyzer provides an intuitive drag-and-drop interface for business users to explore OLAP cubes, schema developers and power users often need to query cubes directly using MDX (Multidimensional Expressions)—the SQL-equivalent language for OLAP databases. Understanding MDX enables you to test your schema designs, validate calculated members, troubleshoot query performance, and create sophisticated analytical queries that go beyond the capabilities of standard reporting interfaces. Schema Workbench's built-in MDX Query tool provides a direct testing environment for executing and debugging MDX statements against your published schemas.

In this hands-on workshop, you'll learn to use Schema Workbench's MDX Query mode to write and execute multidimensional queries against the SteelWheelsSales cube. You'll start with simple queries that retrieve all measures for a specific year, then progress to more complex cross-tabular queries that display multiple measures across different time periods filtered by geographic regions. This practical experience with MDX syntax—including SELECT, FROM, WHERE clauses, axis specifications, and member references—provides essential skills for schema validation, query optimization, and advanced analytical development.

What You'll Accomplish:

• Launch MDX Query mode in Schema Workbench and connect to existing Mondrian schemas
• Write basic MDX queries using SELECT, FROM, and WHERE clause syntax
• Use the AllMembers function to retrieve all members from a dimension
• Execute queries and interpret multidimensional result sets
• Construct cross-tabular queries with measures on columns and time periods on rows
• Apply WHERE clause filters to restrict analysis to specific dimensional contexts (territories/markets)
• Reference dimension members using bracket notation and ampersand key syntax
• Understand the difference between axes (COLUMNS, ROWS) and slicer dimensions (WHERE)
• Test calculated members and validate schema behavior through direct MDX queries

By the end of this workshop, you'll understand how to leverage MDX Query mode as a powerful testing and development tool for your Mondrian schemas. Rather than waiting for users to discover issues in production reports, you'll proactively validate your schema designs, test calculated members, and verify query performance using direct MDX queries. This capability transforms you from a passive schema builder into an active analyst who can interrogate cubes, troubleshoot unexpected results, and understand exactly how your dimensional model responds to different query patterns—essential skills for maintaining robust, high-performance OLAP environments.

Prerequisites: Completion of SteelWheels or similar schema workshops; Schema Workbench and Pentaho Server installed and configured with published schemas; Basic understanding of SQL syntax and multidimensional concepts

Estimated Time: 25 minutes

MDX Query

---

1. Start Schema Workbench:

Windows (PowerShell):

cd \
cd Pentaho/design-tools/schema-workbench/
./workbench.bat

Linux:

cd
cd Pentaho/design-tools/schema-workbench/
./workbench.sh

1. Ensure Pentaho Server is running:

Ensure that the Pentaho Server is up and running (automatically started in Pentaho Lab):

cd
cd /opt/pentaho/server/pentaho-server
sudo ./start-pentaho.sh

1. To access MDX Query mode, from the menu select File > New > MDX Query.

MDX Query

1. Connect to the steelwheelssales.xml schema.
2. To dismiss the Mondrian connection Successful dialog, click OK.
3. Enter the following Query into the top pane:

SELECT Measures.allmembers on columns 
FROM SteelWheelsSales
WHERE ([Years].[2004])

MDX Query - Quantity, Sales, Count for FY2004

1. Execute the Query.

---

1. Here's another ..

SELECT
{ [Measures].[Sales], [Measures].[Quantity] } ON COLUMNS,
{ [Years].&[2004],
[Years].&[2005]} ON ROWS
FROM [SteelWheelsSales]
WHERE ( [Markets.markets].[APAC] )

MDX Query - Sales, Quantity for FY2004 & 2005 APAC Markets

---