Mastering MDX in IBM Planning Analytics Workspace

Part 1: Essential Functions Every TM1 Developer Should Kno

MultiDimensional Expressions (MDX) is one of the most powerful features available in IBM Planning Analytics Workspace (PAW). While many TM1 developers rely on static subsets, mastering MDX opens the door to dynamic reports, intelligent dashboards, and significantly reduced maintenance

In this first article of the series, we'll explore three fundamental MDX functions that every TM1 developer should understand:

• TopCount()

• Filter()

• Descendants()

These functions form the building blocks of many real-world PAW applications.

Why Learn MDX?

Static subsets require manual maintenance and often become outdated. MDX allows subsets to adapt automatically to changing data and hierarchies.

With MDX, you can:

• Create dynamic reports.

• Build interactive dashboards.

• Automatically rank and filter data.

• Simplify maintenance.

• Deliver more flexible analytics to business users.

Let's look at three commonly used functions.

1. TopCount() – Find Your Top Performers

The TopCount() function returns the top members from a set based on a specified measure.

Syntax

Example: Top 10 Products by Revenue

What This Does

• Evaluates all products.

• Sorts them based on Revenue.

• Returns the top ten products.

Typical Use Cases

• Top 10 customers by sales.

• Highest-cost departments.

• Top-performing regions.

• Best-selling products.

Dashboard Example

A PAW dashboard displaying:

Top 10 Products by Revenue

Since the subset is dynamic, rankings update automatically whenever the data changes.

2. Filter() – Show Only What Matters

The Filter() function allows you to return members that satisfy a specific condition.

Syntax

Example: Customers with Revenue Above $1 Million

What This Does

• Evaluates every customer.

• Keeps only customers whose revenue exceeds $1 million.

• Excludes all others

Common Business Applications

Budget Variance Reporting

Show only cost centers exceeding budget:

Active Employees

Display only employees with a headcount value greater than zero:

Product Profitability

Show products with positive margins:

Why Use Filter?

Instead of maintaining manual subsets every month, your reports automatically update based on business rules.

3. Descendants() – Navigate Hierarchies Dynamically

TM1 dimensions are hierarchical by nature. The Descendants() function retrieves all child members below a specified parent.

Syntax

Example

Suppose the Region hierarchy looks like this:

Using:

returns:

Common Use Cases

Regional Reporting

Display all locations under North America.

Cost Centre Rollups

Retrieve all departments under Finance.

Organizational Structures

Expand reporting relationships automatically.

Product Hierarchies

Display all SKUs belonging to a product family.

Benefits

When new members are added to the hierarchy, they are automatically included without requiring changes to the subset.

Combining Functions

MDX functions become especially powerful when combined.

Example: Top 10 Profitable Products

What Happens?

Step 1:

Filter removes products with negative profit.

Step 2:

TopCount selects the ten most profitable products.

This type of expression is commonly used in executive dashboards.

Performance Considerations

Prefer Dynamic subsets

Dynamic subsets reduce manual maintenance and improve flexibility.

Use Filter Carefully

Filtering very large dimensions can impact performance. Consider limiting the initial set whenever possible.

TopCount Is More Efficient Than Order + Head

Instead of:

Use:

TopCount is cleaner and generally performs better.

Test Expressions in the PAW Set Editor

The Set Editor provides a quick way to validate and troubleshoot MDX expressions before deploying them into books and dashboards.

Conclusion

MDX is one of the most valuable skills a TM1 developer can master. Functions such as TopCount(), Filter(), and Descendants() enable dynamic reporting and significantly reduce the effort required to maintain subsets and dashboards.

By incorporating these functions into your Planning Analytics applications, you can deliver smarter, more responsive solutions to your users.

In Part 2, we'll explore time intelligence functions, including:

ParallelPeriod()

PeriodsToDate()

YTD calculations

Rolling periods

Year-over-Year analysis

These functions are essential for financial reporting and variance analysis.

Are you using MDX extensively in your Planning Analytics applications? Which function do you find most useful? Share your thoughts in the comments.