Map a Logical Table Source's Logical Column to a Calculated Item

Create a calculated item when you need to derive the logical column's data from two or more physical columns or tables.

You can create calculated items where formulas are applied pre-aggregation. These are two examples:
  • 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.
  • Use 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 change data 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.

You must include a physical table source to make its columns available for use in a calculation. See Add Logical Table Sources.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Logical Layer Logical layer icon.
  4. In the Logical Layer pane, browse for and double-click the table with the column mappings you want to modify.
  5. In the logical table, click the Sources tab.
  6. In the logical table sources list table, click a logical table source to select it and then click Detail view to open the properties pane.
  7. Go to the pane's Column Mapping section, locate and double-click the column mapping that you want to create a calculated item for.
  8. Click Open Expression Editor and create and validate the physical item's calculation. In the Expression Editor click Save.
  9. Click Save to save the semantic model.