Deprecation Notice: "decimals" parameter and "int" field type (3.38+)


(Nicole Beyer) #1

As of Looker 3.38, the decimals parameter as well as the int field type are being deprecated and moved to the Admin > Legacy Features panel.

The reason for these deprecations is to simplify how Looker handles number values by providing a single field type (number) and a single, flexible way to format them through Excel-style format definitions.

Deprecations

1. Deprecated decimals parameter

Starting in 3.38, the decimals parameter is deprecated. It will continue to work so long as the LookML “decimals” Parameter and “int” Field Type legacy feature is turned on, but each occurrence in a model will produce a deprecation warning in the LookML Validator.

The new way to format numbers is with either the value_format or value_format_name parameter (see below).

2. Deprecated value truncation when decimals is not specified

Prior to 3.38, a number field that did not specify decimals would truncate the number value to zero decimal places. (i.e 0.535 would become 0).

This could result in misleading queries and developer confusion. To remedy this, number fields will now show the entire number as returned by the databse by default. To format the number differently, use value_format or value_format_name.

This change in behavior is also controlled by the LookML “decimals” Parameter and “int” Field Type. Disabling this legacy feature allows this new, clearer default behavior.

3. Deprecated int field type

Starting in 3.38, specifying type: int on a dimension or measure is deprecated. It will continue to work so long as the LookML “decimals” Parameter and “int” Field Type legacy feature is turned on, but each occurrence in a model will produce a deprecation warning in the LookML Validator.

type: number should be used instead.

The purpose of the int field type was to show number values with no formatting (thousands separators) or decimals, as is often desired for ID fields. Since deprecating type: int in favor of type: number, Looker is now smart about whether or not to show thousands separators and decimals by default based on the name of the field. If the field name is id, ends with _id, or if primary_key: true is set on the field, no thousands separators or decimals will be shown.

If a field should not have thousands separators or decimals but does not follow one of the above patterns, the equivalent of type: int can be achieved by using type: number with the id value format:

- dimension: my_cool_dimension
  type: number
  value_format_name: id
  ...

See below for more information on the value_format and value_format_name parameters.

Formatting Numbers

The proper way to format numbers is with value formats. A value format can be defined on the fly for a field with the value_format parameter, or a named value format can be defined and referenced by multiple fields via the value_format_name parameter.

1. value_format parameter

The syntax for defining a value format in LookML is the same as for Excel. Example:

- dimension: order_profit
  type: number
  value_format: "#,##0.0"
  sql: ${TABLE}.order_profit

This will cause all order_profit values to be displayed with thousands separators (commas) and one decimal place.

2. Custom value_formats and the value_format_name parameter

If a format is used for multiple fields, it can be helpful to define it once and simply refer to it by name whenever it is needed. This is done in a model file as follows:

- value_formats:
  - name: telephone
    value_format: "(###) ###-####"

Then to use this named value format for a field:

- dimension: phone_number_formatted
  type: number
  value_format_name: telephone
  sql: ${TABLE}.phone_number

Looker also provides the following set of built-in formats:

Name Description Format String Example
id number with no commas 0 1234
decimal_0 0 decimals #,##0 1,234
decimal_1 1 decimal #,##0.0 1,234.5
decimal_2 2 decimals #,##0.00 1,234.56
decimal_3 3 decimals #,##0.000 1,234.567
decimal_4 4 decimals #,##0.0000 1,234.5678
usd_0 U.S. dollars $#,##0 $1,234
usd U.S. dollars w/ cents $#,##0.00 $1,234.56
percent_0 percent w/ 0 decimals #,##0"%" 12%
percent_1 percent w/ 1 decimal #,##0.0"%" 12.3%
percent_2 percent w/ 2 decimals #,##0.00"%" 12.34%
percent_3 percent w/ 3 decimals #,##0.000"%" 12.345%
percent_4 percent w/ 4 decimals #,##0.0000"%" 12.3456%

Legacy Features End-of-life Schedule
Looker 3.38 Release Notes
Why don't my downloaded Looks have decimals?
[Analytic Block] Redshift Admin
Looker 5.4 Release Notes
Looker 5.8 Extended Support Release Notes
Why don't my downloaded Looks have decimals?
(Scott Hoover) #2

In some circumstances, you’ll have to replace a single line with two lines. For example:

- dimension: age
  type: int
  sql: ${TABLE}.age

becomes

- dimension: age
  type: number
  value_format_name: decimal_0
  sql: ${TABLE}.age

Looker’s native search and replace doesn’t support this type of pattern replacement. To handle this, I recommend search and replace using perl or sed or some other scripting language on the file system. If your Looker is hosted, ask a support analyst to assist you.

Something like this might do the trick:

perl -p -i -e 's/type: int/type: number\n\t\tvalue_format_name: decimal_0/g' *


(Nate Pickens) #3

@sdhoover Replacing type: int with type: number shouldn’t require adding a value format in most cases. The article has been updated to explain in more detail why this is.

[Hack for doing this via Looker redacted, as it does not actually work.]


(Segah A. Mir) #4

“%0A” actually does not work. It shows correctly the newline on a search page, but then when I press Replace, the newline does not make it into the files.


(Segah A. Mir) #5


(Nate Pickens) #6

My bad @segahm. I’ve removed the hack from my comment above. Again though, in most cases you should not need to replace type: int with anything more than type: number to continue with the same behavior. If you do need to replace with multiple lines, then you’ll have to do so with the help of an outside script.


(Alan Treanor) #7

What is the logic around decimals and the field name? Does Looker automatically show decimals for all fields not identified as IDs? If not, can you detail the logic? We could go through and test these but it would be easier to bulk convert our integer fields.


(Nicole Beyer) #8

Hey Alan,

For now we are using some simple logic to determine if the field is an ID or not. In the case that we believe it is an ID we will not show commas or decimals.

A few of the things we use to determine this are:

  1. Is the field declared as a primary key
  2. Does the field name contain the string _id
  3. Is the field simply called id

If you can think of any other cases we should take into account we are definitely open to suggestions!


(Rick Saporta) #9

@nbeyer
Some common ID string alternatives you might want to consider adding

  • SKU
  • UPC
  • ISRC
  • ISBN
  • EAN
  • IAN

(all are some variation of a standardized ID code)


(brettg) #10

Update here - if you have already converted to new lookML, you can effectively mimic a multi line find and replace as proper yaml indentation is no longer needed to appropriately parse a project. Something like the screenshot below would work: