Transformation Metrics: Creating Time-Series Analysis

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.

Strategy 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.

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 Strategy 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 Strategy. 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.

For steps to create metrics, including transformation metrics, in Workstation, see Create a Stand-Alone Metric.