Creating Transformations to Define Time-Based and Other Comparisons » Creating transformations » Expression-based versus table-based transformations

Expression-based versus table-based transformations

The definition of the association between an original value and a transformed one can be represented in an expression that uses columns of the warehouse, constants, arithmetic operators, and mathematical functions. This is known as an expression-based transformation. However, it is sometimes desirable to precalculate these values and store them in a table designed for the transformation. This method is sometimes referred to as a table-based transformation.

The advantage of a table-based transformation is the possible use of indexing to speed query times. Another advantage is that table-based transformations provide additional flexibility beyond what formula expressions can produce. The drawback of this kind of transformation is that it requires the creation and management of an additional table in the warehouse. However, once the table is created, it usually significantly decreases the query time. Returning to the TY/LY example, you have the option of using a simple formula such as Year_ID - 1 in the definition of the transformation or precalculating the data and storing it in a column in a table.

A table-based transformation is required when a many-to-many transformation is performed. An example is a year-to-date calculation.

A significant advantage to the dynamic calculation of an expression-based transformation is that the database administrator does not have to create and maintain a transformation table. The drawback is that the system must perform the calculation every time.

A single transformation can use a combination of table-based and expression-based transformations. For example, you can create a last year transformation based on Year and Month. The ID of the Year attribute is in the format YYYY, so the transformation can use the expression Year_ID - 1. The ID for the Month attribute is in the format ‘MonthName,’ so you cannot easily use a mathematical expression. You must use a table instead.

The following sections walk you through creating both a table-based transformation and an expression-based one.