Formula Columns

[ccastleman]

Introduction

A formula column contains an expression that, depending on the expression and the expression context, returns a scalar or an array of values of a specific data type. An expression can consist of built-in functions, built-in system variables, and other referenceable objects valid for the expression context.

What you should know before reading this article

We recommend that you be familiar with these concepts in Incorta Docs.

•  Schema Manager
•  About Tables
•  Formula Column
•  Formula Expression

Applies to

Applies to all Incorta versions however as a growing feature there are additional options as well as syntax enforcement in release 4.8 and up.

Let's Go

Using the Formula Builder, there are several ways that you can create a formula column:

Incorta Insight

You can add a Formula to an Incorta Insight directly however this will only be calculated at run time and will have several limitations that can be addressed by adding the formula upstream of the insights in either a Business or Physical Schema.  It is best done in an insight to quickly validate a data value or for a case where the actual formula will be specific to the Insight and not be reusable across multiple Insights.

Business Schema

•   Incorta View formula column
•   View formula column

Physical Schema

•   Incorta Analyzer table formula column
•   Physical schema table formula column

Use cases for moving formulas into a Business or Physical Schema?

• Improve Runtime performance by performing the calculation at load time rather than run time of a Dashboard for more information please refer to Performance Tuning Guide
• Reuse a specific formula across multiple Insights
• Need to change the data type to facilitate the joins. Incorta joins require the data type to match from both sides.  Implicit casting works between Long and Integer only.  Examples:
• String to Integer or Long
• Long or Integer to String
• Timestamp to Date
• String process, such as substring, concatenation
• Date or Timestamp to String by taking components and form a meaningful string, such as period name
• Parse a string as a date
• Change a number, such as epoch or a JDE date to a Date column
• When a formula result is used as a grouping attribute in an Insight, it is a best practice to define the formula as a formula column in the Physical Schema
• Incorta by default disables sorting based on a formula in a list report. You must  define a formula column to be used in a formula as a sorting attribute.   
• Resolve the dependencies between formulas. A formula column cannot be used in another formula column but can be used in a formula expression in a business view.
• When the child table has a flexible foreign key column that is overloaded with values that refer to different tables, create different formula columns with the condition logic to return only the values from a specific parent table.

Related Material

•  Schema Design & Data Modeling
•  Variables