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)
- 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.
Below are the properties of a formula column:
|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|
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.
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:
When you create a formula column it will have access to the following function types.
|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|
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.
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.
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.
In the Formula Builder, in the Functions and Variables panel, date system variables are referenceable in the formula expression.
Formula expressions in a physical schema can reference the following variables:
|System Variable||Yes||Only date system variables are valid, the $user system variable is excluded.|
|External Session Variable||No||Formula validation will produce an error.|
|Internal Session Variable||No||Formula validation will produce an error.|
|Global Variable||No||Formula validation will produce an error.|
Incorta versions prior to 5.0 did not invalidate physical schema table formula columns created in the formula builder that included session variables and the $user system variable. If you have physical schema tables that include these session variables in a formula column the schema will encounter a load error. Referenced formula columns containing invalid session variables in a dashboard will not contain values. Physical schemas encountering these errors will need to have the erroneous formula columns resolved to properly load.
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.