Creating Metrics from MDX Cube Data

When you map your MDX cube data into MicroStrategy, you can take advantage of MDX (MultiDimensional eXpressions) to create metrics. Metrics created with MDX combine the robust set of MDX functions and expressions with MicroStrategy analytical tools such as prompts. You can also use basic arithmetic expressions to create these metrics from MDX cube data.

Once you create metrics using these techniques you can include them in your MicroStrategy reports and report filters in the same ways that you can use any MicroStrategy metric. You can also use prompts in these compound and custom MDX metrics (see Using Prompts within MDX Cube Metrics). The metrics created in this way for an MDX cube are stored in a Compound Metrics folder within the Metrics folder for the MDX cube.

Metrics created to map to your MDX cube data are related only to their associated MDX cube. Therefore, these metrics cannot be directly integrated with data from a separate relational data source, except by using calculated expressions in Report Services documents. For information on creating calculated expressions, see the Designing and Creating Documents section of the Document Creation Help.

You can create metrics that map to MDX cube data using either of the following techniques:

• Compound metrics: A compound metric is any MicroStrategy metric with an expression that includes a MicroStrategy metric and an arithmetic expression. The expression can be as simple as a metric multiplied by a constant value, such as Discount * 1.5, where Discount is a metric mapped to data in the MDX cube. These metrics can also reference multiple MicroStrategy metrics within the MDX cube with an expression such as Revenue - Total Expenses, where Revenue and Total Expenses are both metrics, to build a Profit metric.

  You can use MicroStrategy analytical and aggregate functions such as SUM, COUNT, and AVG with metrics mapped to MDX cube data only if the metric you create is defined as a smart metric. If you do not make the metric a smart metric you can only use basic operators (+,-,/,*, and so on). For general information on smart metrics, see the Basic Reporting Help. For examples of smart metrics, see the Advanced Reporting Help.

• MDX customization: Rather than relying only on MicroStrategy to create MDX to return data from your MDX cube source, you can create your own custom MDX to return data for a metric. This technique allows you to use MDX functions and flexibility to query and report on your MDX cube data. The MDX you create is passed to your MDX cube source to be executed and to return the data. You can reference one or more MicroStrategy metrics mapped to MDX cube data using custom MDX just as you can with a standard arithmetic expression. To use MDX to create your calculated measures you must enclose MDX in double quotes (""). For tips and insights on how to build analysis with MDX in MicroStrategy, see How to Build Analysis into Metrics with Custom MDX.

You can create these metrics during the initial importing and mapping procedure of your MDX cube data with the MDX Cube Catalog. These metrics can also be created as a later modification to an MDX cube with the MDX Cube Editor.

The following procedure uses the MDX Cube Catalog: