Transformation Metrics: Time-Based and Other Comparisons

Transformations allow you to apply an attribute-element based offset to compare metric data. For example, a transformation metric can help a user compare last month's revenue to this month's revenue. Although transformations can be applied to any attribute hierarchy, the Time hierarchy is used most often. For the Time hierarchy, the offset can be set as a fixed number of days, weeks, months or years.

MicroStrategy provides numerous prebuilt transformations. You can also create your own transformations as needed. You create transformations in MicroStrategy Developer. You must be a project designer with the Create Schema Object privilege to create transformations. For detailed information about transformations and steps to create them, see the Project Design Help.

You can also create transformation shortcut metrics, which are a quick way to add new metrics based on the existing metrics of a report.

Time-Based Transformations

Metrics use time transformations to compare values at different times, such as this year versus last year or current date versus month-to-date. For example, the Last Year transformation maps each time period to its corresponding time period last year, while the Month-to-Date transformation maps each time period to a set of time periods that comprise the entire month to date.

In the image below, the Actual Amount metric displays account numbers from the current quarter. The Last Quarter transformation is applied to the Actual Amount metric to create the Actual Amount - Last Quarter metric, which displays last quarter's account numbers. The difference between the sets of numbers can then be calculated and displayed in the Actual Amount - Last Quarter Difference metric. Transformations are useful for such time-series analyses, which are relevant to many industries, including retail, banking, and telecommunications.

Although MicroStrategy provides other methods for performing these types of calculations, transformations are usually the most generic approach and can be re-used and applied to other time-series analyses. For example, another common type of time-series analysis is a TY/LY comparison (This Year versus Last Year). You can use filters to create the TY/LY comparison, as follows:

• To calculate this year's revenue, use a filter for this year with the Revenue metric.
• To calculate last year's revenue, use a filter for last year with the Revenue metric.

However, a more flexible alternative is to use a previously created Last Year transformation to define a new metric, called Last Year Revenue. You can then use a single filter on 2003 on the Revenue and Last Year Revenue metrics to obtain results for 2003 and 2002, respectively. While the filter approach requires the creation of two filters, the transformation approach requires only one. Additionally, with the transformation approach, the same transformation metric can be applied to a report with an appropriate filter to define similar analyses on different sets of data, while the filters approach means that new filters would have to be created to build each new report.

Since a transformation represents a rule, it can describe the effect of that rule for different levels of data. For instance, the Last Year transformation intuitively describes how a specific year relates to the year before. It can also express how each month of a year corresponds to a month of the prior year. In the same way, the transformation can describe how each day of a year maps to a day of the year before. This information defines the transformation and abstracts all cases into a generic concept. That is, you can use a single metric with a last year transformation regardless of the time attribute contained on the report. For an example of a year-to-date transformation, see the Advanced Reporting Help.

Non Time-Based Transformations

While transformations are most often used for discovering and analyzing time-based trends in your data, not all transformations have to be time-based. For example, a transformation can map defunct product codes to new ones. An example of a non-time-based transformation is This Catalog/Last Catalog, which might subtract a number from an old product code to convert it into a new one.

Transformation-style analysis can also be supported using the Lag and Lead functions provided with MicroStrategy. These functions can be used to define metrics that compare values from different time periods without the use of transformation metrics. For information on using these functions to support transformation-style analysis, see the Functions Reference.

Creating a Transformation Metric

To create a metric, you must define the metric's formula, which consists of the following:

• Function: The calculation applied to your business data, such as Sum or Count. Depending on how you create the metric, your metric may contain multiple functions.
• Expression: The business data from your data source. The expression can contain business facts, attributes, or other metrics.

Steps are below to add a transformation to a metric. If you want to create the metric by defining the metric's formula directly, see Metric Formula Editor.

The transformation to apply to the metric has already been created. For steps to create a transformation, see the Project Design Help.

To add a transformation to a metric

1. Click Create on any page, and select New Metric. The Select a Function or Template dialog box opens.
2. From the drop-down list, select Metric Templates (at the bottom of the list). Select Transformation.
3. Click Next. The Function Editor opens.
4. From the Aggregation Function drop-down list, select the function to use to create the metric.

5. Define the metric expression by doing one of the following:

   • To type objects to add to the expression directly, type the name of the object in the Expression field. As you type, matching objects are displayed in a drop-down list. You can click an object or continue to type. You can type multiple objects, such as Revenue-Profit.
   • To specify the expression by choosing an object, click the Browse icon . The Select an Object dialog box opens. Either navigate to and select an object, or search for an object.

Add a transformation

1. If the options in the Transformation area are not displayed, click Transformation.
2. Click the Browse icon in the Transformation area. The Select an Object dialog box opens. Either navigate to and select a transformation, or search for a transformation. After you select a transformation, you are returned to the Function Editor.

3. You can remove transformations that you have added to the metric, or change the order in which transformations are applied. Choose from the following:

   • To remove a transformation, click the Delete icon next to the transformation.
   • To change the order of the transformations, select a transformation and use the directional arrows to move it up or down.
4. Repeat the appropriate steps above to define any additional transformations as desired.
5. You can define how the metric header and the metric values are formatted and displayed in a report. For example, you can define how numeric values are displayed, font styles and sizes, and cell display colors. Your formatting will be applied to the metric regardless of the report on which it is placed. For steps to format the metric, see Format Dialog Box for Metrics.
6. Click Save to apply your changes. The Save As dialog box opens. Navigate to the folder where you want to save the metric. Type a Name and Description for the metric, and click OK. Your new metric is saved.

7. You can add levels or a condition to the metric. Choose from the following:

   • By default, a metric is calculated at the level of the attributes on the report on which the metric is placed. You can define the attribute level to use in the metric calculation, regardless of what is contained on any report the metric is placed on. For steps, see By default, metrics are evaluated at the level of the attributes on the report; this is called the report level. The report level allows the metric calculation to adapt to different reports. You can remove the report level from the metric. If you do this, only the level explicitly set on the metric affects the metric calculation, regardless of the attributes placed on the report. You do not have to remove the report level to add levels to the metric. For a more detailed description of the report level, and the impact of removing it, see the Advanced Reporting Help. Do one of the following:To remove the report level from the metric, click the Delete icon next to Report Level. To add the report level to the metric after it has been deleted, click the Add Report Level icon . . For a more detailed description of levels, including examples, see Level Metrics: How to Modify the Context of Data Calculations.
   • To add a condition, see Add the Condition. A condition allows you to apply a filter to only one metric on a report while not affecting the other metrics. For a more detailed description of conditional metrics, including examples, see Conditional Metrics: How to Filter Data Calculations.

Related Topics

Introduction to Metrics for background information about metrics in general