What is a transformation? - Microstrategy

A transformation is a schema object that encapsulates a business rule used to compare results of different time periods. Usually defined by a project designer, transformations are used in the definition of a metric to alter the behavior of that metric.

Recall the example used in the Introduction, the TY/LY comparison. To calculate this year’s revenue, you can use the Revenue metric in conjunction with a filter for this year. Similarly, to calculate last year's revenue, you can use the Revenue metric in conjunction with a filter for last year. However, a more flexible alternative is to use a previously created Last Year transformation in the definition of a new metric, last year’s revenue. With a single filter, on 2003 for example, the two metrics Revenue and Last Year Revenue will give you results for 2003 and 2002, respectively.

Since a transformation represents a rule, it can describe the effect of that rule for different levels. For instance, the Last Year transformation intuitively describes how a specific year relates to the year before. It can in additionexpress 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.

The definition of the association between the original value and the transformed one can be represented in an expression that uses columns of the warehouse, constants, arithmetic operators, and mathematical functions. However, it is sometimes desirable to precalculate these values and store them in a table designed for the transformation. This process is sometimes referred to as table-based transformation. The advantage is the possible use of indexing to speed query times, but it requires the management of an additional table in the warehouse. Returning to the TY/LY example, you have the option of using a simple formula such as Year - 1 in the definition of the transformation or precalculating the data and storing it in a column in a table.

Microstrategy Topics