You are viewing content for 5.0 | 4.9 | 4.8 | 4.7 | 4.6 | 4.5 | 4.4 | 4.3 | Previous Releases


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:

Note

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
Warning

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, date system variables are referenceable in the formula expression.

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

Variable Type Referenceable Note
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.
Warning

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.

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.