Transformation components

All transformations have the following components:

Member attributes: This component contains the attributes to which the transformation applies, that is, the different levels to which the rule applies.

For example, in the Last Year transformation in the MicroStrategy Tutorial, the member attributes are Year, Quarter, Month, and Day.

Member tables: These tables store the data for the member attributes.
For an expression-based transformation, each member expression is based on a specific table, generally the lookup table corresponding to the attribute being transformed.
For a table-based transformation, this is the transformation table defining the relationship. For example, in the Last Year transformation, the member tables are LU_YEAR, LU_QUARTER, LU_MONTH, and LU_DAY, for the member attributes Year, Quarter, Month, and Day, respectively.
Member expressions: Each member attribute has a corresponding expression.
For an expression-based transformation, this is a mathematical expression. In the most generic case, this expression uses constants, arithmetic operators, mathematical functions, and columns from the warehouse, typically the attribute ID column.

For example, you can create a Last Year transformation using Year_ID-1 as the expression. However, many cases can exist where the data is not conducive to such calculation. For instance, if you store Month as 200001 (January 2000), you cannot subtract one and receive December 1999 as the result.

For a table-based transformation, this is simply a column from a specific warehouse table specifically populated with data supporting the transformation. The rule is then not encapsulated in an expression but directly in the data of the column. Since the data defines the rule, this approach provides considerable flexibility in the transformation definition. It is particularly effective when no straightforward formula can express the rule. In fact, in the case of a many-to-many transformation, a separate table is required.

For example, in the Last Year transformation, the member expressions are LY_DAY_DATE, LY_MONTH_ID, LY_QUARTER_ID, and PREV_YEAR_ID. These are all columns from the lookup tables set in the Member tables field.

Mapping type: This component determines how the transformation is created based on the nature of the data. The mapping can be one of the following:
One-to-one: A typical one-to-one relationship is “last year to this year.” One day or month this year maps exactly to one day or month from last year.
Many-to-many: A typical many-to-many relationship is year-to-date. For one date, many other dates are included in the year-to-date calculation.

Many-to-many transformations can lead to double-counting scenarios. For example, consider YearToDate defined as a many-to-many transformation and Revenue (YTD) as a transformation metric. Suppose this metric is used on a report that does not include the Day attribute, which is the member attribute on the template. In the report, a range of dates is specified in the filter. In this instance, the Revenue (YTD) metric will double count.