MDX Issues, Functions and Operators - Teradata Schema Workbench

Teradata Schema Workbench User Guide

Product
Teradata Schema Workbench
Release Number
16.20
16.10
15.10
Published
June 2015
Language
English (United States)
Last Update
2018-05-25
dita:mapPath
gvf1512702977003.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-4106
Product Category
Teradata Tools and Utilities

MDX is a sophisticated and intricate multidimensional query language. Because each implementation is different, it might not be valid to copy and paste from one implementation to another.

MDX errors that the MDX checker/parser in Teradata Schema Workbench does not catch might result in the Excel message, The query did not run, or the database table could not be opened. Unfortunately, Excel messages are often too generic to reveal the exact cause of MDX issues. You will need to debug each error carefully to isolate the issue.

Use the following tips to isolate issues when using Teradata OLAP Connector and MDX:
  • For a list of MDX functions and operators that Teradata OLAP Connector supports, see Supported MDX Functions and Operators.

    For example, the first parameter of the IIF function is a boolean expression that evaluates true or false. Teradata OLAP Connector only supports boolean expressions that compare strings or numerical values. The MDX ‘IS’ operator is not supported, but often can be replaced semantically using an expression similar to: UniqueName = ‘some string’.

  • Entity unique names (dimensions, level, member, and so forth) are provider-specific. You can use an application, such as MDX Sample, to determine the unique names used by Teradata OLAP Connector. For information on MDX Sample, see Validating MDX Syntax.
  • To avoid issues with spaces and special characters, bracket-delimit member names.
  • Be sure member names are fully-qualified. Names for dimensions, hierarchies or levels can be partially-qualified as long as they are not ambiguous. Unfortunately, it is easy to have an ambiguous situation. For example, a dimension might have the same name as a hierarchy or level. Generally, we recommend you fully qualify and bracket-delimit all names.
  • If available, pretest all your MDX using the MDX Sample application.
  • Scripted subcubes, also called implicit crossjoins, are not supported. The following example includes a workaround.

Example - Troubleshooting MDX Issues

Implicit cross-join (not supported):

select {
 ([Time].[Calendar Time].[ALL].[(ALL)].children, [Measures].[Sales]),
 ([Time].[Calendar Time].[ALL].[(ALL)].children,
  [Measures].[Gross Profit])
} on columns,
[Store Type].[Store Types].[ALL].[(ALL)].children on rows
from [XYZ] 

Workaround:

select
CrossJoin([TIME].[CALENDAR TIME].[ALL].[(ALL)].children,
  {[Measures].[Sales], [Measures].[Gross Profit]}) on columns,
[STORE TYPE].[STORE TYPES].[ALL].[(ALL)].children on rows
from [XYZ]