References → Decimal Data Support

Introduction

Decimal data types are exact numeric data types whose precision (total number of digits) and scale (number of digits in the fractional part) define their precise value. Decimal data type stores exact numeric data values, preserving exact precision. Decimal data types are essential for high-precision calculations, especially monetary values. Databases and data management systems handle decimal data differently. They also support different precisions.

How Incorta handles decimal data

Now Incorta can discover, read, store, and manipulate decimal data (including money, numeric, decimal, and number) from different data sources, ensuring high-precision calculations. Incorta supports decimal numbers with a precision of up to 18. It discovers and stores decimal data with a precision higher than 18 as double.

Incorta’s support for decimal data types starts with discovering decimal columns in the supported data sources, then extracting and storing decimal columns in parquet. The Engine extended the numeric data type to include decimals and supports decimals in formulas and functions.

Decimal support in data discovery

Incorta automatically discovers all decimal data types in selected data sources. The decimal data types that Incorta can discover include numeric, number, money, small-money, decimal, dec, fixed, etc. Incorta discovers decimal columns along with their precision and scale defined in the data sources.

Incorta supports decimal columns with a precision of up to 18 and a scale less than the precision.

• If the detected precision and scale fall into the ranges that Incorta supports, Incorta will automatically assign the decimal data type to the column with the source precision and scale.
• If the detected precision is greater than 18, Incorta will infer the decimal column as double. However, you can manually change the column data type to decimal and assign supported values to the precision and scale.

As a schema manager, you can change the detected precision and scale, either in the Schema Wizard when creating a physical schema or an object or in the Schema Designer after creating it. However, the value of the precision must always be less than or equal to 18, and the scale less than the precision. When manually changing the data type of a column to decimal, the initial values of the precision and the scale are 18 and 0 respectively.

Supported data sources and connectors

Currently, only SQL-based connectors, such as Oracle and MySQL, can discover decimal data types in the respective data sources.

Multi-source tables

For multi-source tables with different column types, Incorta infers the data type of the output column in the following order of precedence: string, double, decimal, long, and integer. For example, if the data type of one column is decimal while the data type of the other column is double, then the data type of the output column will be double. And if the data type of the other column is long or integer, the data type of the output column can be decimal if the calculated precision is less than or equal to 18.

Decimal support in data extraction and loading

During a load job of an object (usually, a physical schema table) that has one or more columns with the data type of decimal, Incorta applies specific rules to extract and store decimal data. After the extraction of decimal columns, Incorta stores them in parquet.

Extracting and storing data as decimal

When Incorta automatically discovers a column as a decimal or you, as the schema manager, change the column data type to decimal, Incorta reads data from the data source and stores it as follows:

• For numeric columns,
  • If the digit count of the integral part (the whole-number part before the decimal point) is greater than the difference between the precision and the scale specified in the column definition, Incorta rejects the row and throws a numeric overflow error.
  • If the scale of the source value is greater than the scale in the column definition, Incorta rounds up the fraction part to fit into the defined scale.
• For string columns and non-metadata data sources, Incorta converts the string value to a numeric value and applies the same rules of a numeric column. If Incorta fails to convert the string value to a numeric one, it rejects the row.
• For null values in decimal columns, Incorta reads and stores them as null.

Extracting and storing decimal data as other data types

As a schema manager, you can change the data type from decimal to another data type. In this case, Incorta reads data from the data source and stores it as follows:

• When converting a decimal column to integer or long, Incorta drops the fraction part in the decimal number.
  • If the integral part of the decimal number is less than the minimum supported value of the selected data type or greater than the maximum supported value of the selected data type, Incorta rejects the row and throws an out-of-range error.
• When converting a decimal column to double, Incorta stores the number as is.
• When converting a decimal column to string, Incorta stores the number as is.

Decimal support in the Analyzer and dashboards

Incorta also supports decimal data while using the Analyzer, whether when creating Analyzer tables, Analyzer views, or insights. You can reference decimal columns in most of the visualization types.

You can reference a decimal column as a data column as well as an Individual Filter, Distinct Filter, Aggregation Filter, or Sort by column. You can deal with decimal columns in the Analyzer as you deal with all other numeral columns, thus you can reference decimal columns in formulas, apply conditional formatting, define decimal places, apply number formats, and so on. In addition, you can reference decimal columns in dashboard filters: Prompts, Applied Filters, and Filter Options.

Incorta also respects decimal data when downloading or sharing insights as Excel files.

Visualizations not supporting decimal data

Incorta supports visualizing decimal data using all visualization types except for the following:

• Pie Donut
• Combination
• Area Range
• Combo
• Dual X-Axis
• Map
• Bubble Map
• Heat Map
• Packed Bubble
• Sankey
• Gauge
• Solid Gauge

Decimal support in formulas and functions

Incorta starts supporting decimal expressions in formulas and functions. Formulas respect data type precedence if the formula has at least one decimal expression. For example, if a formula has a decimal expression and a double expression, the output will be a double expression. If the other expression is integer or long, the output will be a decimal expression.

• If the returned value of a formula is a decimal expression, the formula determines the resulting precision and scale.
• If the resulting precision is greater than 18, it is set automatically to 18.

Calculating decimal numbers

Aggregation and arithmetic operations on decimal data type return a decimal expression with the highest precision that Incorta supports, which is 18. On the other hand, Incorta calculates the scale depending on the operation type.

The following table shows how Incorta calculates the scale (S) in different operation types.

For example, adding a decimal (18,7) column to a long(18,0) column results in a decimal number if the integral part of the resulting value fits into the calculated precision and scale (18, 7). Dividing the same 2 numbers results in a decimal number if the integral part of the resulting value fits into the calculated precision and scale (18, 6). If the integral part does not fit into the calculated precision and scale, Incorta throws an error.

Functions supporting decimal

Most functions in Incorta that support numeral expressions support decimal expressions as well. These formula functions accept decimal expressions as input values. The output of some of these functions can be a decimal expression.

The following are the functions that support decimal expressions:

• The Aggregation functions
• The Arithmetic functions
• The Conversion functions
• The between() Boolean function

Referencing a decimal expression in an unsupported function throws an error.

A new function for converting to decimal

Incorta introduces a new conversion function, decimal(), to convert an input expression to a decimal one.

The decimal(exp, [p,s]) function converts a double, integer, long, decimal, or string expression to a decimal one with a defined precision and scale.

• When referencing an expression in the new function, Incorta can infer the precision and scale of the function result.
• When referencing an integer or long column, Incorta can also infer the precision and scale.
• When referencing a double or string column, you have to define the precision and scale you want for the function result, otherwise, the Formula Builder throws an error.
• If the integral part of a given value can’t fit into the given precision and scale, Incorta throws an overflow exception.
• This function doesn’t allow nesting.
• If the input expression is evaluated to an empty or invalid string that cannot be converted to a decimal, Incorta throws an error.

Overflow exceptions

When the formula or function result is a decimal, Incorta calculates the precision and scale of the resulting decimal expression. If the result does not fit into the calculated precision and scale, Incorta throws an overflow exception. An example is when the result requires a number of digits in the integral part that exceeds the difference between the calculated precision and scale.

Limitations and Known Issues

• Not all connectors, visualizations, or functions support decimal data.
• For data sources that don't support discovering decimal columns, you mustn't change the column data type to decimal to avoid failing the extraction of the table.
• Decimal columns can’t function as key columns. In addition, you can’t encrypt decimal columns or reference them in a join or a presentation variable.
• For some data sources, the Loader Service may reject rows with decimal data without showing an error.
• Incorta SQL tables and views don’t support decimal data.
• The Excel Add-in, Component SDK, and Mobile App don’t support decimal data.
• Saving an Incorta Analyzer table that references decimal columns with high precisions and scales throws an overflow error although the table displays data correctly when you create it in the Analyzer.
• PostgreSQL materialized views cast the result of the min and max formulas as double although the input columns are decimal.