1. Home
  2. On-premises 6.0
  3. Guides
  4. Concepts
    1. Aggregate filter
    2. Alias
    3. Apache Avro™
    4. Apache Kafka™
    5. Applied filter
    6. Base table
    7. Bookmark
    8. Business schema
    9. Business schema view
    10. Business schema view column
    11. Business schema view formula column
    12. Column
    13. Connector
    14. Dashboard filter
    15. Dashboard folder
    16. Dashboard runtime filter
    17. Data agent
    18. Data alert
    19. Data destination
    20. Date system variable
    21. Distinct filter
    22. Expression
    23. External data source
    24. External session variable
    25. Filter expression
    26. Filter expression session variable
    27. Filter option
    28. Formula column
    29. Formula expression
    30. Full load
    31. Global variable
    32. Incorta Analyzer Table
    33. Incorta Analyzer Table column
    34. Incorta Analyzer Table formula column
    35. Incorta Analyzer View
    36. Incorta Analyzer View formula column
    37. Incorta SQL Table
    38. Incorta SQL View
    39. Individual filter
    40. Insight filter
    41. Internal query expression
    42. Internal session variable
    43. Join
    44. Join condition
    45. Join condition column
    46. Join filter
    47. Join filter column
    48. Level based measure
    49. Load filter
    50. Local data file
    51. Local data folder
    52. Materialized View
    53. Materialized View Column
    54. Materialized View Formula Column
    55. Measure filter
    56. Miscellaneous system variable
    57. Multi-Source table
    58. Physical schema
    59. Physical schema table
    60. Physical schema table column
    61. Physical schema table formula column
    62. Physical schema table runtime security filter
    63. Presentation variable
    64. Prompt
    65. Query system variable
    66. Runtime business view
    67. Runtime business view formula column
    68. Runtime security filter
    69. Session variable
    70. Shared Storage
    71. SQL Interface
    72. Upgrade Readiness
    73. Variable
    74. Visualization
  5. Tools
  6. References
  7. Integrations
  8. Data applications
  9. Releases

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:

PropertyControlDescriptionConfiguration
Show in AnalyzercheckboxSelect to show as a selectable column in the Data PanelSelect or deselect checkbox
Nametext boxEnter the formula column’s name; used in the fully qualified name of the columnValid characters: A-Z, a-z, 0-9, $, _
Name must begin with a letter character
Labeltext boxEnter a user friendly nameutf8-md valid characters, including emoji 😀
Typeread onlyThe Incorta Data Type of the formula columnInteger, Double, Long, String, Date, Timestamp, Text, and Null
Functiondrop down listSelect the Incorta Function of the columnDimension or Measure
Column FormulaFormula BuilderCreate a formula for the column valuesSee Formula Builder tool

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.

Note

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 TypeDescription
BooleanEvaluate to True or False, a boolean value
Conditional StatementsReturn a value based on the evaluation of a condition. The return value data type must match the data type of the arguments.
ConversionConverts a a value of a specific data type or manipulates a date or timestamp value
AggregationSummarizes the data in a column and returns a value of type integer, double, or long
ArithmeticPerforms a row by row calculation and returns a value of type integer or double
AnalyticCompute values over a group of rows and returns a single result for each row of type integer, double, date, or timestamp
FilterFilters data to those items before or after a specified date
DateReturns a date or timestamp value
StringReturns a string value

Date system variables

Date system variables are available to use in a formula expression for a physical schema table formula column.

Warning

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.

Important:

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 TypeColumn TypeReferenceableNote
AliasData-backed columnYes
AliasFormula columnYes
Business Schema ViewData-backed columnNo
Business Schema ViewFormula columnNo
Incorta Analyzer TableData-backed columnYes
Incorta Analyzer TableFormula columnYes
Incorta SQL TableData-backed columnYesThe Incorta SQL Table is an Incorta Labs feature in this release.
Incorta Analyzer ViewData-backed columnNo
Incorta Analyzer ViewFormula columnNo
Materialized ViewData-backed columnYes
Materialized ViewFormula columnYes
Physical Schema TableData-backed columnYes
Physical Schema TableFormula columnNoThe formula expression cannot reference another physical schema table formula column from the same physical schema table or from a child table.
Physical Schema TableFormula columnYesThe 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.
Note

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 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 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.

Warning

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.

Warning

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.