Add a Calculated Column to a Dataset

You can add a calculated column to any dataset that you have imported into a cube. Calculated columns are retained in the project library even when you remove the dataset from the cube canvas.

Procedure

1. Open the cube canvas that has the dataset you want to edit, or drag a dataset onto the canvas from the library.

2. Click the hamburger icon in the dataset header. That icon looks like this:

   

3. Select Calculated Column.

4. Enter the Display Name for the new column.

5. Enter the Formula used to compute the column values.

   This must be a valid SQL expression that can be executed as an item in the SELECT list of a query. These formulas are passed directly to the underlying data warehouse at query runtime, so it must be syntax that is supported by your chosen engine.

   Note the following:

   • Do not use aggregate functions or table-generating functions in a calculated column formula. Use only scalar functions.

   • In case columns you use in the formula are named after protected key words, such columns must be quoted. For example, instead of cast(date as varchar(20)) you should use cast("date" as varchar(20)).

     Consider that database engines have different ways of quoting. Make sure you use the quotation rules for your database.

6. Click Test SQL Syntax.

note

Note: If your data warehouse is an instance of Google BigQuery, this button does not appear. The validation of formulas is not available for calculated columns in datasets that are based on tables imported from Google BigQuery. Click Save to save the calculated column.

1. If the formula is valid, click Save.
2. If the formula is not valid, verify your formula and try again. See Troubleshoot Invalid Calculated Columns.

1. Open the dataset preview to confirm the calculated values are correct. You can search by the column name.

note

Note: If your data warehouse is an instance of Google BigQuery, calculated columns do not appear in the dataset preview.