MicroStrategy ONE

Creating derived metrics

Before you begin

You must have the MicroStrategy OLAP Services product to create derived metrics. If you would like to evaluate OLAP Services, contact your MicroStrategy Account Executive or e-mail MicroStrategy directly at info@microstrategy.com

You need the Create Derived Metrics (MicroStrategy Developer) and/or the Web Create Derived Metrics and Derived Attributes (MicroStrategy Web) privileges to create derived metrics. These privileges are part of OLAP Services.

A derived metric is a calculation based on metric data included in the report definition. You can use derived metrics to perform column math, that is, calculations on metrics included in the report definition, without regenerating or re-executing SQL against the data warehouse.

Derived metrics are easy to create and can be made on-the-fly when you are viewing or creating a report, allowing you to create a metric while focusing on a given report. A typical case is to create a derived metric to perform calculations between columns of metric data. For example, derived metrics can be used to subtract the data of one metric by the data of another metric (Metric1 - Metric2) to obtain a new metric calculation.

Since derived metrics are created within a report, they can only be used for the report in which they are created. Derived metrics cannot be saved as individual objects in the project, and therefore cannot be applied to other reports in the project.

You can create a derived metric in any report, as long as you have an OLAP Services license. This includes reports connected to an Intelligent Cube, reports returning information directly from a data warehouse, Freeform SQL reports returning information from Excel spreadsheet, and so on.

You can create any type of derived metric by defining derived metric expressions using the Input Metric Formula dialog box. This includes shortcut metrics, which are a special subset of derived metrics -- percent-to-total, rank, and transformation metrics.

The procedure below describes the high-level steps for creating a derived metric with the Input Metric Formula dialog box. For an example of creating a derived metric, see Example: Average profit per customer with transactions.

Prerequisites

  • Before you begin creating a derived metric, review the Best practices for creating derived metrics.

  • You need the Create Derived Metrics (MicroStrategy Developer) and/or the Web Create Derived Metrics and Derived Attributes (MicroStrategy Web) privileges. These privileges are part of OLAP Services.

To create a derived metric

  1. Open a report in MicroStrategy Developer.

  2. From the Insert menu, select New Metric. The Input Metric Formula dialog box opens.

    From the Insert menu, select New Training Metric to use derived metrics as part of Data Mining Services to create training metrics. For information on training metrics and creating predictive analysis, see Using Data Mining Services. For information on creating training metrics using the derived metrics feature, see Training Metric Wizard.

  3. The window on the left displays the Report Objects, which shows the components (attributes, attribute forms, metrics, custom groups, consolidations, and so on) included in a given report, even if the components are not displayed in the layout of the report. In MicroStrategy Developer, double-click or drag-and-drop an object to use it to define the derived metric.

  4. Add functions and operators by typing their syntax or characters. You can also click the f(x) button to open the Insert Function Wizard, which guides you on including functions and operators.

  5. Continue to add report components, functions, operators, constant values, and other valid metric formula objects to complete your formula.

  6. To add the level at which to calculate the metric, enclose the metric formula in parentheses. Type the attribute name between curly braces {} after the metric formula. If the attribute name contains a space, enclose the name within brackets []. For example, ([Unit Cost] * [Units Sold]) {Customer}, where Unit Cost and Units Sold are metrics and Customer is an attribute, is a valid expression.

    For more information on metric levels, see the Advanced Reporting Help.

  7. Once you have created the desired expression, click Validate. An error is displayed if the expression is invalid.

  8. In the Metric Name field, enter a name for the new metric.

    When naming a MicroStrategy object, you must follow the naming convention rules for your particular database platform. Using a word reserved by your database platform can result in an error when the report is executed. Refer to your database documentation for a list of these database-reserved words.

  9. Click OK to create the new derived metric for your report. The Input Metric Formula dialog box closes and the derived metric is added to the report.

  10. To format the values or headers of a derived metric, see Formatting derived metrics.

Related Topics