Concepts → Physical Schema Table Formula Column
A physical schema table formula column contains a formula expression that returns a scalar value of a specific data type and functions as either a measure or a dimension. During a load job, the Loader Service processes the formula expression and persists the returned values to shared storage. To define a formula expression, you can use the Formula Builder.
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|
Based the output of the formula expression, the Table Editor will automatically infer the data type of a formula column. You can modify the formula expression to explicitly cast the data type of the return value. For example, you can use a built-in function to such as
int() to specify that the formula expression returns an integer value.
Some databases such as Oracle and SQL Server will store a time component for a column of the type
DATE. For this reason, Incorta will infer a Timestamp data type for a database DATE column.
A formula expression returns a scalar value or array of values. These values are of a specific type. A formula expression can consist of built-in functions, date system variables, and referenceable columns.
There are several groups of built-in function available for use in a formula expression for a physical schema table formula column. All functions return a scalar value:
|Conditional Statements||Return a value based on the evaluation of a condition. The return value data type must match the data type of the arguments.|
|Conversion||Converts a a value of a specific data type or manipulates a date or timestamp value|
|Arithmetic||Performs a row by row calculation and returns a value of type integer or double|
|Date||Returns a date or timestamp value|
|String||Returns a string value|
Only date system variables are available to use in a formula expression for a physical schema table formula column
Prior to Incorta 5, the Formula Builder did not invalidate a formula expression for a physical schema table formula column that included an internal session variable, external session variable, global variable, or the
$user miscellaneous system variable.
A referenceable column has a fully qualified name. A referencable column can be a data-backed column or formula column. Depending on the table or view type, a formula column may be evaluated at runtime instead of during a load job.
In a physical schema without joins between entity objects, the only referencable columns in a formula expression for a physical schema table formula column are within the table itself. Joins between entity objects in a physical schema determine what objects are referencable in the formula expression. For example, a physical schema table formula column for a child table includes referencable columns in the child table itself and all related parent tables. In contrast, a parent table that is itself not a child table to another parent table, can only make reference its own columns.
Here are the possible types of referenceable columns in a physical schema table formula column:
|Table or View Type||Column Type||Referenceable||Note|
|Business Schema View||Data-backed column||No|
|Business Schema View||Formula column||No|
|Incorta Analyzer Table||Data-backed column||Yes|
|Incorta Analyzer Table||Formula column||Yes|
|Incorta SQL Table||Data-backed column||No||The Incorta SQL Table is an Incorta Labs feature in this release.|
|Incorta View||Data-backed column||No|
|Incorta View||Formula column||No|
|Materialized View||Data-backed column||Yes|
|Materialized View||Formula column||Yes|
|Physical Schema Table||Data-backed column||Yes|
|Physical Schema Table||Formula column||No||The formula expression cannot reference another physical schema table formula column from the same physical schema table or from a child table.|
|Physical Schema Table||Formula column||Yes||The formula expression can reference a physical schema table formula column from a parent table in the formula expression. This can also be a reference to grandparent table, meaning a table that is both a child table in a join and a parent table in a join.|
A load job will reveal an error for a non-validated formula expression for a physical schema table formula column.
When creating a physical schema table formula column, consider the following:
- Filter expressions
- Lookup function
To aggregate data in a physical schema, consider creating an Incorta Analyzer Table with an Aggregated Table insight. An Incorta Analyzer Table supports insight filters, the creation of formula columns, and using a either a Listing Table or an Aggregated Table for an insight.
You can also aggregate data in a physical schema using a Materialized View.
You can write a formula expression to convert the data type of a table column such as:
- String to Integer
- String to Long
- String to Date
- String to Timestamp
- Date to String
- Integer to String
- Long to String
- Timestamp to String
- Timestamp to Date
Here is a list of builtin conversion functions:
formatDate()- convert a date or a timestamp to a string
parseDate()- convert a string to a date
parseTimestamp()- convert a string to a timestamp
removeTime()- convert a Timestamp column to a Date column
epoch()- convert a date or a timestamp to a epoch number
double()- convert a string to a double
int()- convert a string or a double as an integer
If you need to cast or convert a Timestamp into a Date, you have several option:
- Modify the Type from Timestamp to Date and the Loader Service will
- If the external data source is of the type SQL Database, consider modifying the
SELECTstatement for the Query property to use a supported SQL built-in casting or conversion function
- Create a physical schema table formula column of the type Date and create a formula expression that removes the time from the timestamp using the
In a child table, you can use a formula column to denormalize a parent table relationship. When there is a join between two physical schema tables, it is possible to create in the child table a formula expression for a physical schema table formula column that reference a data-backed or formula column in a parent table. It is recommended that the parent table contain a key column to ensure uniqueness.
A physical schema table formula column persists in shared storage. You can use a physical schema table formula column in an a filter expression for an insight filter, a dashboard runtime filter, or a dashboard filter. An insight filter may exist in an insight on a dashboard tab, in an Incorta Analyzer Table, and in an Incorta View.
You can use a physical schema table formula column from a child table as the join column with a parent table within the same physical schema. For example, when the child table has a flexible foreign key column that is overloaded with values that refer to different tables, consider using two or more physical schema table formula columns. Each formula column contains a formula expression with conditional logic that serves to return only the values from a specific parent table. It is recommended that the parent table contain a key column to ensure uniqueness.
Avoid using a physical schema table formula column to create a cross-schema join for a physical schema table that enables incremental loads as this is unsupported in this release.
To return a scalar value, you can use the
lookup() built-in function in a formula expression for a physical schema table formula column. This approach helps retrieve a value from an unrelated table using an existing foreign key. As input arguments, you can specify the lookup column in the source table, the primary key column in the source table, and the foreign key column in the existing table or a value. As required, you can specify a composite key and also a default return value.
In this release, the Table Editor does not inspect the lineage dependencies of a key column. In other words, if a schema developer changes how a column functions from a key to a dimension or measure, this may inadvertently invalidate a formula expression that uses
lookup() without warning.