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:

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

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.