SAP Multidimensional Expressions (MDX) allows you to navigate, query, filter, and perform calculations against multidimensional structures, including dimensions (characteristics), hierarchies, sets, measures (key figures), and members. People often refer to this kind of multidimensional analysis as Online Analytical Processing (OLAP).

MDX is comprised of nearly 200 expressions that cover mathematical, statistical, logical (Boolean), conditional, member, set, time series, and navigation functions, such as up and down dimension hierarchies and levels. You can combine MDX expressions to create advanced calculations and filters, such as identifying at-risk customers that did business with you on a regular basis in the last 26 weeks but have not in the most recent four weeks.

I’ll show you how MDX works, and then explain how you can apply it to SAP NetWeaver BI. I’ll provide examples of MDX expressions using the MDXTEST transaction and MDX Testeditor. I’ll also briefly introduce how you can use third-party tools to simplify MDX queries. I’ll end with a discussion of the pros and cons of using MDX. For some background about the relevance of MDX in SAP NetWeaver BI, see the sidebar, “Why MDX Is Important for SAP NetWeaver BI.”

How MDX Works

MDX emphasizes hierarchies and drill down, which give MDX the basis for its effectiveness as a query and calculation language. Although theoretically you could query an OLAP cube (referred to as an InfoCube by SAP) using SQL, SQL has no inherent knowledge of dimension hierarchies and cannot easily navigate to the parent or children of a particular dimension member.

MDX, however, recognizes all kinds of dimensional relationships, from ancestors to descendants, from members to their siblings (in a hierarchy level), and from the dimension root (i.e., the All level) to the leaf level (i.e., the most granular data). If you know a particular dimension member, then MDX lets you determine all these relationships with very little coding. For example, if you select a particular region or state, the MDX Descendants function can list all the cities belonging to that region or state, as shown in Figure 1.

Figure 1

MDX can traverse multiple levels in a hierarchy

MDX knows that the Southwest region has children at the state level and descendants at the city level. It also knows that Albuquerque’s parent is New Mexico and its ancestor is the Southwest region. It’s this navigation ability that makes MDX so useful. It lets you generalize the MDX code to work at any level of a dimension hierarchy without hard-coding specific dimension members.

MDX’s ability to understand time relationships yields a variety of functions for time-period comparisons, such as ParallelPeriod for viewing results from a current period to the same period a year ago. You use PeriodsToDate to analyze data from a particular period in the past to the most current period. LastPeriods lets a user select a set of periods from a current period to a period in the past (for example, the last three, six, or 12 months). Other MDX time functions include year-to-date (YTD), quarter-to-date (QTD), and month-to-date (MTD), which are shorthand for the PeriodsToDate function.

The equivalent queries in SQL would take dozens or hundreds of single-spaced lines of code and only an SQL expert could write them. In contrast, MDX has nearly 200 functions to navigate, query, filter, and perform calculations in OLAP InfoCubes.

That’s not to say that MDX is always simpler than SQL. While basic queries that are straightforward in SQL are equally straightforward in MDX, some calculations spanning two or more dimensions can be relatively complex. MDX, however, can reference multidimensional cube space. SQL cannot do this.

Use the MDX Testeditor to Learn MDX

Let’s look more closely into the structure of MDX queries using the MDX transaction MDXTEST in SAP NetWeaver BI. For this example, I am using the SAP NetWeaver BI demo InfoCube 0D_DX_M01, which is the SAP Demo: DalSegno Company Reporting InfoCube.

First, use transaction MDXTEST to open the MDX Testeditor (Figure 2). Select the catalog and InfoCube (or query cube) you want to analyze. In my example, I used the InfoProvider catalog and demo InfoCube mentioned above. Then use one of the pre-built SELECT templates provided in the Available Objects section. In my example, I chose the WHERE template.

Figure 2

Access the MDX Testeditor through transaction MDXTEST

MDX Testeditor lets you choose the catalog (InfoProvider) and cube (InfoCube or query cube). You can drag and drop characteristics and key figures into the query design pane. The MDX Testeditor provides templates for basic MDX queries and some MDX functions. You have to specify the InfoCube or query cube in the FROM clause. Then specify a characteristic member in the WHERE clause (replacing the <tuple> placeholder).

Finally, click on the execute query icon , circled in the Query task bar in Figure 2, to execute the query. You can save and reopen your MDX queries in MDX Testeditor by clicking on the save and reopen icons circled in the query display pane in Figure 2.

A Closer Look at MDX Queries

Now that I’ve shown how to create a basic query, let’s take a look at the components of the query. The basic MDX query has the form shown in Figure 2, with SELECT, FROM, and WHERE statements.

Although this looks similar to a basic SQL query, this is where the similarity ends. In SQL, the SELECT command selects columns from one or more tables, which are specified in the FROM clause and linked together using JOIN conditions. Multi-table joins can be quite complex. However, MDX requires no joins because dimensions and measures are linked through an InfoCube’s metadata.

To further show how the MDX query works, I’ll use the example query shown in Figure 3 to test a basic MDX statement. Figure 4 shows the results of this query. Note that the quarter (Q1 2005) specified in the WHERE clause is shown in the result set. The FROM clause in the MDX query specifies a particular OLAP cube (an InfoCube or BEx query cube).

Figure 3

Example of a basic MDX query

Figure 4

Example of a basic MDX result set

In MDX, the SELECT keyword places sets of dimension members on one or more axes. Axis 0 contains the values for the columns and axis 1 contains the values for the rows. These terms are just for convenience — MDX query functions work just fine using either the terms “0” or “columns” and “1” or “rows.” Technically, an MDX query could have three or more axes, but most query tools can represent only a two-dimensional crosstab or grid.

Both MDX and SQL have optional WHERE clauses. In SQL, you use the WHERE clause to restrict (filter) the rows. In MDX, the WHERE clause points to a specific slice of an OLAP cube using tuples, which consist of unique members from one or more dimensions.

MDX and SQL also share similar functions for counting and calculations, such as AVG, COUNT, MIN, MAX, SUM, STDEV, and VAR. However, MDX goes one step further and includes the ability to create reusable calculated key figures and named sets.

Figure 5 shows an MDX query with a calculated measure [Unit Price], which I specified after the WITH clause. This clause appears before the SELECT statement. You then use the calculated unit price in the SELECT statement. You can see the new measure in the result set in the bottom left of the screen.

Figure 5

The WITH statement at the start of an MDX query allows you to define calculated key figures and named sets

In the example shown in Figure 6, the expression following the SET clause (SET [Top 10 Customers] AS) uses the TopCount function to determine the top 10 customers by net sales. MDX contains a variety of SET functions, including TopCount, BottomCount, TopPercent, and BottomPercent.

Figure 6

Named sets simplify the MDX code

Note
MDX is not a full report-formatting language. You need an MDX-based third-party tool for formatting options.

MDX and Third-Party Tools

Many organizations use third-party BI tools so that users never need to write full MDX queries. Instead, these tools generate the MDX queries in the background. You can use simple MDX in these BI tools to create calculated members, sets, filters, and exceptions, as shown in Figure 7.

Figure 7

Example of an MDX-based third-party tool

In this screen, you can drag and drop MDX functions, InfoCube characteristics, and key figures into the query design pane to create MDX expressions. The MDX-based third-party BI tool generates the full MDX SELECT statements automatically. All the user must do is enter the MDX expression for the calculated key figure. The complete expression is:

[Measures].[OD_NETSALES]
[Measures].[OD_INV_QTY]

The result of this query is identical to the full MDX statement in the MDX Testeditor shown in Figure 7.

Benefits and Drawbacks of Using MDX

MDX permits the design of complex OLAP cubes while hiding much of the complexity from users. The capability of MDX to navigate up and down dimension hierarchies with a single line of code greatly facilitates self-service reporting and analytics. Of particular importance is the capability of MDX to look back across time using functions such as ParallelPeriod, PeriodsToDate (and the YTD and MTD shortcuts), LastPeriods, PrevMember, and CurrentMember. SET functions (such as TopCount, BottomCount, TopPercent, BottomPercent, Ancestors, Descendants, and Filter) allow users to find specific groups of products, customers, and geographic areas, such as those that affect sales results.

For me, the primary reason to use MDX-based SAP NetWeaver BI tools is for self-service reporting and analysis. I view the BEx suite more as a high-end solution for business analysts, and less as a BI system for senior executives and mid- level managers. I’ve also found it easier to design complex queries using MDX-based SAP NetWeaver BI tools compared to the same queries in BEx.

I want to stress, however, that I am not recommending replacing the BEx suite with a third-party tool. Ease of use is a relative term and a BEx query designer may find working in BEx easier than learning MDX. There is also a cost issue if your company already owns the BEx toolset. Nonetheless, a third-party tool using MDX can add a lot of firepower to an SAP NetWeaver BI system.

Furthermore, MDX is not a particularly intuitive or easy-to-learn language. Knowing when to use a function that has a parentheses (), or the .dot operator is a matter of rote memorization. Basic queries are quite easy, but they get progressively more complex when you start adding new calculations. The biggest challenge with MDX is in understanding the multi-dimensional space in an OLAP cube and then referencing the appropriate characteristics and key figures to create sets or calculations.

MDX is most effective when working against multi-level hierarchies. Many SAP NetWeaver BI dimension (characteristic) hierarchies are flat, with only one level. To get the full benefit of MDX, you often must modify InfoCubes to incorporate multi-level characteristics. For example, the Product characteristic in the demo InfoCube 0D_DX_M01 is separate from the Product Group characteristic, making it difficult to navigate from a particular product to its corresponding product group.

To make the InfoCube more user- and MDX-friendly, you should create an external hierarchy with the Product Group level for the Product characteristic in the underlying InfoObject. You can carry out this task by using the SAP NetWeaver BI Administrator Workbench.