MicroStrategy ONE
How to Build Analysis into Metrics with Custom MDX
You can build sophisticated analysis into your MDX cube metrics by creating your own custom MDX. This allows you to further combine the analysis capabilities of MDX and MicroStrategy. This section provides some tips and best practices on how to build analysis into metrics with custom MDX.
Creating such analysis requires appropriate knowledge of both MDX and MicroStrategy. MicroStrategy does not validate any custom MDX created by users to build metrics for MDX cubes.
MDX has strict rules about the inclusion or exclusion of dimensions, hierarchies, and attributes on the report template and in custom MDX formulas. Some MDX formulas expect related attributes to exist on the template, and they may return incorrect results (or an error) if the attributes are omitted. Other formulas may return unexpected results if the attributes are included. As a result, certain custom MDX formulas may not be appropriate for ad-hoc reporting and you should be aware of the possible limitations of the custom MDX you create.
Creating your own custom MDX allows you to draw further analysis from your MDX cube source into MicroStrategy. Any expressions that are valid in a WITH MEMBER clause may be used, allowing metrics built in MicroStrategy to employ the data manipulation capabilities of the MDX cube source. The custom MDX is placed into a WITH MEMBER clause defining a member of the Measures collection for the scope of the query.
To use MDX to create your metrics, you must enclose MDX in double quotes (""). For example, "[Measures].[Total Sales]" is valid syntax for a metric defined with MDX, returning the Total Sales data from an MDX cube.
Since MDX is passed to and run against your MDX cube source, you must use the names and identifiers used in the MDX cube source to identify the data to be retrieved. For SAP BW, the technical name should be used.
In the example, "[Measures].[Total Sales]" in your MDX cube source, your metric data is identified as Total Sales and it mapped to a metric named Revenue in MicroStrategy. When you are creating custom MDX to retrieve this data from your MDX cube source, you must use the identifier for the data within the MDX cube source.
You can also perform basic arithmetic in your MDX, such as applying a multiplier to the Total Sales data: "[Measures].[Total Sales] * .06"
Along with these simple expressions, you can also use MDX functions to create more advanced analysis. When you include an MDX function in your custom MDX, the function is passed to the MDX cube source and processed as a pass-through function. For example, you can use the MDX year-to-date (YTD) function to create transformation-style analysis on your MDX cube data: "sum(YTD([Quarter].CurrentMember), [Measures].[Profit])". This expression returns year-to-date values by quarter for profit data.
See the following topics about specific methods for building analysis into your metrics: