Virtual Column Calculations

Virtual columns in Model allow the creation of calculations to derive new columns from values in other columns within a single row. They are especially useful and important in "Direct Query" models where row level calculations need to be defined against the data. Virtual Columns provide a "No Code / Low Code" approach, obviating the need to write custom SQL queries to perform these calculations. Unlike in a Data Flow, the calculated column is not materialized, but is calculated at run time as part of the query generated by the Pyramid query engine, Pyrana.

Custom Columns vs Virtual Calculated Columns

Custom Columns and Virtual Calculated Columns are almost identical. The difference lies in where they are created and who can create them.

  • Virtual Calculated Columns are built into a semantic model, and are designed by model creators to be accessible to users who can see the model. While access to the virtual columns can be managed via model security (including member level security), the concept is centered around a 'global' calculation that users access in the model itself.
  • Custom Columns are built as custom objects and are additive to an existing semantic model. They are designed by end users (more on a case by case basis) and accessible to those users with access to the calculation logic items (and folders) only. Column access can be managed using the content manager and roles, but there is no member level security option.

There is no difference in performance, analytic or mathematical function between the 2 approaches.

Accessing Virtual Column Calculations

The Virtual Column graphical editor can be accessed from two locations, the Table editor and the Column editor. The functionality offered is identical, which option is taken is down to personal preferences.

From the Table editor, click on the "Add Calculated Column" icon in the table header indicated by the red boxes below:

From the Column editor, click on the "Add Calculated Column" option in the top right hand corner:

Both of these options will open the Calculated Column graphical editor.

For more see: