Defining Physical to Logical Table Source Mappings and Creating Calculated Items

Use the Column Mapping tab of the Logical Table Source dialog to map logical columns to physical columns.

The physical to logical mapping can also be used to specify transformations that occur between the Physical layer and the Business Model and Mapping layer. The transformations can be simple, such as changing an integer data type to a character, or more complex, such as applying a formula to find a percentage of sales per unit of population. Applying these transformations is typically referred to as creating calculated items.

The data type of a logical column is determined by its logical table source mappings. For example, if a logical column has one physical source with a data type of VARCHAR(50) not-nullable, and another physical source with a data type of VARCHAR(20), nullable, then the data type of the logical column is VARCHAR(50) nullable. This final type is called a promoted type. Because of the rules governing logical table source mappings, you cannot map physical sources with data types that cannot be promoted such as an INT with a VARCHAR.

To map logical columns to physical columns:

  1. In the Business Model and Mapping layer of the Administration Tool, double-click a logical table source.
  2. In the Logical Table Source dialog, click the Column Mapping tab.
  3. In the Column Mapping tab, maximize or enlarge the dialog to show all the contents, as shown in the image.

    In the Column Mapping tab, in the Logical column to physical column mapping area, you can sort the rows (toggle among ascending order, descending order, and then restore original order) by clicking a column heading.

    Description of GUID-F69370C7-8697-4F0C-BE36-9ED4725D96DC-default.gif follows
    Description of the illustration GUID-F69370C7-8697-4F0C-BE36-9ED4725D96DC-default.gif
  4. In the Physical Table column, select the table that contains the column you want to map.

    When you select a cell in the Physical Table column, a list appears. It contains a list of tables currently included in this logical table source.

  5. In the Expression column, select the physical column corresponding to each logical column.

    When you select a cell in the Expression column, a list appears. It contains a list of physical columns currently included in this logical table source.

  6. To open Expression Builder, click the Expression Builder button.

    All columns used in creating physical expressions must be in tables included in the logical table source. You cannot create expressions involving columns in tables outside the source.

    You can use Expression Builder to create calculated items, in which formulas are applied pre-aggregation. For example, to create the measure "tons sold" using the columns units_sold and unit_weight, you apply a pre-aggregation formula (fact.units_sold*product.unit_weight), and then apply the aggregation rule SUM in the measure object. Another example is using CAST to transform a column of type TIMESTAMP to type DATE for faster display in Answers and other clients, for example, CAST("DB"."."TABLE"."COL" AS DATE).

    You can also conform sources by creating expressions that perform transformations on physical data. For example, you can use the CAST function to transform a column with a character data type to an integer data type, to match data coming from a second logical table source. Other examples include using CONCATENATE or math functions to make similar transformations on physical data.

    See Answers for calculations that need to occur post-aggregation.

  7. To remove a column mapping, click the Delete button. You might need to scroll to the right to locate the Delete button.
  8. After you map the appropriate columns, click OK.

Unmapping a Logical Column from Its Source

You can edit the logical table sources from which the column derives its data, or unmap it from its sources.

In the Logical Column dialog, the Column Source tab contains information about the logical column.

To unmap a logical column from its source:

  1. In the Business Model and Mapping layer of the Administration Tool, double-click a logical column.
  2. In the Logical Column dialog, click the Column Source tab.
  3. In the Logical Table Source list, select a source and click Unmap.
  4. Click OK.