Multidimensional Expression (MDX) is the industry standard language used to query multidimensional data sources and generate reports in BW. It is typically used by third-party reporting tools.

Those of you who are interested in a non-SAP reporting solution to augment Business Explorer (BEx) need to understand the impact of MDX. Each tool uses it differently, and MDX affects your BW resources.

Over the years, third-party reporting tools have employed different levels of MDX. The level of MDX usage can impact your system and the capabilities you can realistically expect from them. I will explain the implications of deep versus shallow MDX support. I will also detail how MDX can support a self-service, Web-based reporting environment (ad hoc, standard reports, complex reports, pixel-level formatting for reports, easy interface, etc.), and how it can affect a broad base of users with limited BW knowledge and little to no technical skills.

MDX is not user-friendly like other query languages such as SQL, and it is not a language that you are likely to use yourself. It is run in the background by various applications, however, including third-party BW reporting tools. Created by Microsoft, the MDX syntax offers many benefits. Perhaps its most attractive feature is that MDX is very flexible so it can be used on a range of platforms.

For BW reporting, third-party reporting tools generate MDX, which supports up to 15 dimensions, and passes queries through a BAPI interface behind the scenes to BW for execution. BW then hands the results back to the reporting tools (Figure 1). BEx uses its own API, which is distinct from the published BAPI for third-party tools, and relies on two dimensions for results.

Figure 1

The MDX syntax passes queries via a BAPI interface to BW for execution, and hands the results back to the reporting tools in the same fashion

Deep MDX

In the past, third-party reporting tools used MDX at a simple level. This often limited the BW feature set that these tools supported, leaving gaps in the functionality that had to be managed manually by the IT group or power users to maintain report accuracy and integrity.

With simple or “shallow” MDX, older third-party tools generated a very basic request to the BW system, and the query results were brought back for local processing. The approach is similar to writing an SQL query, “select * from table1” and all data elements (columns) and all transactions (rows) are returned from the source. It is up to the query tool, then, to do any additional activities locally such as filtering, calculating, etc.

Processing data locally required duplicating hardware and infrastructure to effectively mirror the BW system’s query processing capability. It also meant that when the data was downloaded, the link back to the BW server was broken and the ability to leverage special aggregations (or semi-aggregations) implemented on the server was lost.

Relying on a simple level of MDX hampered third-party reporting solutions and caused problems for end users. Shallow MDX flattened hierarchies and caused other headaches such as ignoring free characteristics and limiting data to only the main cross-tab portion of a query, which often produced results inconsistent with those generated by BEx.

A growing number of third-party reporting solutions avoid many of the problems associated with their predecessors by generating more complex MDX and featuring an SAP-certified BAPI interface. While some will undoubtedly stick with an ODBO interface and may explore XMLA, I expect to see more generations of third-party tools based on the SAP BAPI interface and implementing deeper MDX. (For more details on these three interfaces, see “SAP BW Open Access Interfaces and Certified Front-end Tools: What IT and Query Designers Need to Know.”)

With complex, or “deep,” MDX, more is incorporated into the statement sent to the BW system, so the entire query is executed on the BW server including filters, calculations, groupings, and more. By generating deep MDX, newer generations of third-party reporting tools are able to use the BW system more efficiently by reducing result sets, using precalculated aggregations, and optimizing the OLAP processor.

Deep MDX helps ensure that all query processing runs on the BW server and uses more of BW’s native capabilities such as:

• Employing variables regardless of their association with free characteristic values
• Recognizing hierarchies and maintaining parent-child relationships
• Accessing aggregates and semi-aggregates maintained on the server
• Representing accurate data values regardless of formatting such as rounding
• Interpreting and rendering BW visual cues for currency codes and invalid results from bad calculations

With the latest generation of third-party reporting tools, the BW architecture and capabilities are being used more effectively and IT teams no longer have to maintain and duplicate processing power. The BW server is more fully leveraged by deeper MDX to do all the query processing. The results are successfully passed back to the reporting tool for formatting as well as for any additional value-add features such as scheduling, report bursting, and distribution.

Automatic Exposure of All BW Variables

Deep MDX allows third-party reporting tools to use variables including those associated with free characteristics. Variables are part of the business rules applied to queries, characteristics, and hierarchies in the BEx Query Designer. They improve the relevance of data for users and reduce the number of records returned. This improves system efficiency, lowering the load placed on the BW server and infrastructure.

Older reporting solutions that relied on simple MDX could not effectively accommodate free characteristics. Any variables associated with free characteristics were ignored. IT teams or power users were routinely forced to create new queries if their reports were to be generated with an older reporting solution so as to duplicate all free characteristics.

Frequently third-party reports based on queries with free characteristics would not match the results of a BEx report. This further compounded the workload because, to prevent erroneous data from being disseminated, reports had to be intercepted and reviewed for errors by BW teams and power users.

With deeper MDX, reporting tools can recognize free characteristics and render any associated variables as user prompts. The properties of these variables are automatically exposed. Reporting tools from some vendors accept BW default variable properties, and they may allow customization to control how these variables are exposed.

Parent-Child Relationships — Hierarchy Awareness

Reporting tools that rely on simple MDX generation often flatten hierarchies, breaking parent-child relationships. As a result, when performing calculations on values that exist for those relationships, problems arise because some values are prone to duplication and any calculations based on these values are inaccurate.

For example, Figure 2 provides the results of a query researching sales quantities for products by their order method. Two levels from the product hierarchy are included — Product Line and Product Type. You can see the parent-child relationship exposed in the groupings: Camping Equipment is the parent and the children are Cooking Gear, Tents, and Sleeping Bags. The Result entries are the sum of the children for each parent totaled for each group break point.

Figure 2

BEx query showing parent-child relationships and hierarchies

If a report is generated using simple MDX, the hierarchies could be flattened. Table 1 shows what the results might look like in such a situation. Note that the individual values for each of the Product Type entries are erroneously combined with the totals for each Product Line. The end result doubles the actual total quantity sold for the products.

Faced with this report, users must identify and choose the appropriate hierarchical levels and the corresponding values. If that task fell to a less-experienced end user, or if a more complicated report with more hierarchies was required, inaccurate results are likely. IT personnel or power users could intervene and use programming to hide or remove the duplicates. However, such a solution is manually intensive, not always accurate, and could be forgotten in the reporting process — not to mention costly to implement and maintain.

Deep MDX enables reporting tools to maintain hierarchy awareness and accurately reflect parent-child relationships. As a result, users can build accurate reports without manual intervention.

Visual Cue Support

Another problem with simple MDX is that it often fails to recognize BW indicators for visual cues. BW inserts special formatting and other visual cues in reports so users can recognize instances such as when multiple currencies are being displayed or if numbers have been rounded. Without these formats and indicators, it is easy for users to make mistakes such as totaling unlike currencies or units of measure, thinking the results are valid numbers, or mistakenly view rounded values as actual accurate numbers.

For example, Figure 3 represents revenue numbers reported with BEx that have been rounded up to the nearest $1,000. The visual cue is the indicator at the top of the Revenue column — *1,000 $. Without this cue indicating that the values should be multiplied by $1,000, the values in Table 2 might be reported, which are obviously wrong. It would be up to the recipient of the report to determine what these numbers represent.

Figure 3

A BEx report showing Revenue rounded to the nearest thousand

Visual cues are critical for discerning among values when multiple currencies and multiple units of measure appear in a report. Default formatting in BEx uses symbols to differentiate between currencies and measurements to eliminate the possibility of confusion.

Currency and units of measure symbols present a problem for third-party reporting tools not using complex MDX. If such cues are not embedded in the same cell as the values or do not appear as part of the result set from the query, the potential for misinterpretation is high. Doing other calculations that cross multiple currencies or units of measure further compound the errors.

Deep MDX in the latest generations of reporting tools is able to recognize BW indicators for invalid numbers and automatically apply the default formatting. These tools also offer cell-level formatting to embed the symbols for currency or unit of measure in the appropriate field and accurately reflect the correct formats for different currencies.

Other Benefits of Deep MDX

Reporting tools that fully employ MDX can leverage BW global business rules, including multilingual metadata as well as currency conversion capabilities. All fields can have multilingual descriptions, titles, and content. MDX support allows reporting solutions to recognize that there are multiple languages available from BW. At run time, the report can pick up user language preferences and the same report can be displayed using the different languages available changing the report or rebuilding the report multiple times.

In addition, deeper MDX is able to leverage aggregates and semi-aggregates in the BW system. Simple MDX often breaks the ability to use aggregates and may produce different results than what should have come from applying the BW aggregates.

Congnos ReportNet: Deep MDX Support

Cognos ReportNet generates complex MDX through a certified BAPI interface. I’ve highlighted how complex MDX and the SAP-certified BAPI interface support BW in some key areas so as to ensure the accuracy of query results and reduce the impact on your system.

Automatic Variable Exposure

The MDX in Cognos ReportNet recognizes all variables associated with BW queries, including free characteristics, regardless of whether the characteristic (or hierarchy) associated with the variables is included in the report.

At report execution time, the query is sent to BW, which provides values for the active variables. ReportNet automatically reflects the variables’ properties, including if they are mandatory or optional; single-select, multi-select, or interval; etc. My firm’s reporting tool also supports administrator-defined augmentation to the way variables are exposed to the user, including things like prompt type to represent the variable and inserting a default value.

In Figure 1, the query includes a mandatory, multi-select characteristic variable and filters the results based on Order Method. The selections are passed via MDX back to BW to complete the query, ensuring all mandatory variables are addressed. Because MDX exposes all the variables associated with a query, all variable types — an optional, multi-select, characteristic variable, for example — can be applied to a presentation hierarchy (Figure 2).

Figure 1

Mandatory, multi-select prompt driven by a char-acteristic variable from SAP BW on Order Method using Cognos ReportNet

Figure 2

Optional, multi-select hierarchy prompt driven by a hierarchical node variable in SAP BW focused on Sales Region

Cognos ReportNet generates the MDX to leverage BW’s currency-conversion business rules and applies exchange rates to various local currencies so they can be displayed as a single, selected currency. This approach can be applied to sales numbers, expenses, and more.

 

Hierarchy Awareness

 

Cognos ReportNet understands and maintains relationships within BW hierarchies. Reports ensure calculations across multiple levels of the same hierarchy are accurate. The tabular results of a query crossing Product Line and Product Type in the Product hierarchy is presented in Figure 3. While these results are the same as those found in a BEx report, it is only a part of a more complex report.

Figure 3

Tabular results of a query crossing Product Line and Product Type in the Product hierarchy

Rounding and Visual Cues

 

The MDX generated by Cognos ReportNet identifies when BW has applied special formatting. When rounding, for example, the actual number is displayed in the report. Currency and unit of measure are rendered so that symbols are automatically inserted in the same cell as the values. An asterisk also is inserted in the cell of a value if it is an invalid number.

 

Currency symbols and formatting are automatically passed from BW directly into Cognos ReportNet reports. They remain in the same field as the values to ensure that there is no confusion if multiple currencies are displayed.

 

Multilingual Reports

 

As Cognos ReportNet submits the MDX to BW, it indicates the user’s geographic location by sensing the browser settings or ReportNet preferences. The resulting report, which is run by different users from different geographies, displays the information in the appropriate local language. ReportNet also applies formatting for currencies and dates to reflect local conventions such as presenting the date order in the U.S. as month, day, year versus day, month, year for Europe and other regions (Figure 4).

Figure 4

User locale or preferences determine the language displayed at run time such as the English and a German version of the same report

Scott Lawrence

Scott Lawrence is director of product marketing at Cognos and has over nine years of business intelligence experience at the firm. He spent the past five years working on Cognos’s packaged analytic applications for SAP and other ERPs. Scott is currently focused on Cognos’s SAP solution with ReportNet. He also has experience working with global companies such as Andersen Consulting (now Accenture) and Credit Suisse.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.