Concepts → Physical Schema Table Formula Column

A physical schema table represents the metadata about a certain entity of structured data. The metadata of a table includes the following:

  • Data Source(s)
  • Columns
  • Formula Columns
  • Runtime Security Filters
  • Parent Joins
  • Child Joins
  • Load Filter

A table formula column contains an expression that returns a scalar value of a specific type. As such, Incorta computes and persists to disk the returned values of a table formula column.

You can use the Table Editor to create and edit the table metadata. You can use the Formula Builder to define a formula expression.

Properties of a Formula Column

Below are the properties of a formula column:

Property Control Description Configuration
Show in Analyzer checkbox Select to show as a selectable column in the Data Panel Select or deselect checkbox
Name text box Enter the formula column’s name; used in the fully qualified name of the column Valid characters: A-Z, a-z, 0-9, $, _
Name must begin with a letter character
Label text box Enter a user friendly name utf8-md valid characters, including emoji 😀
Type read only The Incorta Data Type of the formula column Integer, Double, Long, String, Date, Timestamp, Text, and Null
Function drop down list Select the Incorta Function of the column Dimension or Measure
Column Formula Formula Builder Create a formula for the column values See Formula Builder tool

Formula Column Type Casting

Incorta will assign the Incorta Data Type of a formula column based on the output data of the formula. If you need to change the Incorta Data Type of the column you will need to convert the type using casting. You can cast the output of a formula using built-in conversion functions, such as int(), in the Formula Builder.

Physical Schema Table formula expressions

A formula expression returns a scalar value or array of values. These values are of a specific type. A formula expression can consist of builtin-functions, variables, and referenced columns.

A referenced column has a fully qualified name. Here are the possible types of referenceable columns in a physical schema table formula expression:

  • Physcial_Schema.Table.Column
  • Physcial_Schema.Table.Formula_Column
  • Physcial_Schema.Incorta_Table.Column
  • Physcial_Schema.Incorta_Table.Formula_Column
  • Business_Schema.View.Column
  • Business_Schema.View.Formula_Column
  • Business_Schema.Incorta_View.Column
  • Business_Schema.Incorta_View.Formula_Column

When you create a formula column it will have access to the following function types.

Function Type Description Example
Boolean Functions that evaluate to True or False and, between, contains, endsWith, in, inList, isNan, isNull, like, not, or, startsWith
Conditional Statement Statements that return a value based on the evaluation of a parameter case, decode, and if
Conversion Functions that convert the Incorta Data Type of a parameter. In addition, functions that manipulate Timestamp and Date values for different return values day, double, monthName, parseDouble, string, toChar, and year
Miscellaneous Functions with assorted uses descendantOf, lookup, rowNumber, and schemaRefreshTime
Arithmetic Functions that take one or more values but return a single value abs, ceil, exp, floor, max, min, mod, rnd, round, sqrt, and trunc
Date Functions that manipulate Timestamp and Date values addDays, date, month, removeTime, weekday, and year
String Functions that return string values or return values based on string parameters bin, concat, find, ifNull, repeat, rTrim, and upper

Referenceable columns in a physical schema table formula column

Here are the possible types of referenceable columns in a physical schema table formula column:


A databacked column references data that persists to shared storage.

Schema/Table Type Column Type Reference Capability Note
Business Schema View Databacked column No
Business Schema View Formula column No
Incorta View Databacked column No
Incorta View Formula column No
Physical Schema Databacked column Yes Only if the formula column’s table is a child in a join to the source table.
Physical Schema Formula column Yes Only if the formula column’s table is a child in a join to the source table or same table.
Incorta Table Formula column No

Attempting to perform invalid formula references will result in the formula not being validated or an error when conducting a Load of the schema.

Denormalization using a Formula Column

If two tables have a join relationship, the child table’s formula columns are able to reference columns in the parent table. A parent table’s formula columns are not able to access a child table’s columns.

Aggregations, Filters, and Analytics with Formula Columns

If you would like to create formula columns in the physical schema that use aggregation, filter, or analytic functions, refer to formula columns in an Incorta Table. Formula columns in a physical schema table are not able to use these functions in the Formula Builder.

Variable References in Formula Expressions

In the Formula Builder, in the Functions and Variables panel, certain variables are referenceable in the formula expression. Some types of variables all users have access to and other kinds of variables are subject to Discretionary Access Control (DAC).

Here are the various categories of variables:

  • System Variables: Predefined variables within Incorta that return a value as a defined type.
  • External Session Variables: Variables that query an external data source when invoked. Values returned as string.
  • Internal Session Variables: Variables that query data within a schema. Values returned as string.
  • Global Variables: A static variable available to all tenant users that return a value as a defined type.

Formula expressions in a physical schema can reference the following variables:

Variable Type Referenceable Note
System Variable Yes
External Session Variable No Possible deleted data error will occur at table load.
Internal Session Variable No Possible deleted data error will occur at table load.
Global Variable No Not supported in 4.9 or earlier releases

Additional Considerations

Physical Schema Table Formula Columns in an Insight

Physical schema table formula columns in an Insight will enforce their calculation. This is useful when you want to maintain the behavior of a formula column. Insights that do not utilize a formula column properly may result in the Insight not displaying data. Changing the aggregation type of a formula column in the Analyzer will not work properly if the formula’s data cannot convert to the aggregation. The Analyzer cannot edit the formula of the formula columns created in the physical schema or business view.

The Analyzer is able to use physical schema table formula columns in any filter that is applicable. A formula column created in the Analyzer will not be able to be used as an aggregated or distinct filter.

© Incorta, Inc. All Rights Reserved.