Concepts → Physical Schema Table Formula Column
About a 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.
Properties of a physical schema table formula column
Below are the properties of a formula column:
Property | Control | Description | Configuration |
---|---|---|---|
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 |
Show in Analyzer | checkbox | Select to show as a selectable column in the Data Panel | Select or deselect checkbox |
Data Lineage | link | Available starting with 2023.7.0. Select the Lineage link to open the Data Lineage Viewer v2 that displays a diagram with the entities referenced in the respective column (upstream lineage) and entities where the respective column is referenced (downstream lineage). |
Type inference
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.
Formula expression for a physical schema table formula 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.
Available built-in functions
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:
Function Type | Description |
---|---|
Boolean | Evaluate to True or False , a boolean 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 |
Aggregation | Summarizes the data in a column and returns a value of type integer, double, or long |
Arithmetic | Performs a row by row calculation and returns a value of type integer or double |
Analytic | Compute values over a group of rows and returns a single result for each row of type integer, double, date, or timestamp |
Filter | Filters data to those items before or after a specified date |
Date | Returns a date or timestamp value |
String | Returns a string value |
Date system variables
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, or the $user
miscellaneous system variable.
Referenceable columns
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 |
---|---|---|---|
Alias | Data-backed column | Yes | |
Alias | Formula column | Yes | |
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 | Yes | The Incorta SQL Table is an Incorta Labs feature in this release. |
Incorta Analyzer View | Data-backed column | No | |
Incorta Analyzer 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.
Additional considerations for physical schema table formula column
When creating a physical schema table formula column, consider the following:
- Aggregations
- Conversion
- Denormalization
- Filter expressions
- Joins
- Lookup function
Aggregations
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.
Conversion
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
Helpful built-in conversion functions
Here is a list of builtin conversion functions:
formatDate()
- convert a date or a timestamp to a stringparseDate()
- convert a string to a dateparseTimestamp()
- convert a string to a timestampremoveTime()
- convert a Timestamp column to a Date columnepoch()
- convert a date or a timestamp to a epoch numberdouble()
- convert a string to a doubleint()
- 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
SELECT
statement 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
removeTime()
built-in function
Denormalization
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.
Filter expressions
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 Analyzer View.
Joins
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.
Lookup function
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.