Skip to main content

Data cube dimensions

Data cube dimensions correspond to columns in your source table. In a data cube, you can display and filter data by dimension.

You can drag dimensions into the filter bar, show bar, and pinboard panels to interactively explore and analyze data.

In the following figure, the current view within the data cube filters by the Time dimension, __time in the original table. It shows the sum of events by country and number of events:

dimension panel with at least one collapsed and one expanded group

Number of Events is a data cube measure. In contrast to a dimension, a measure represents an output of an aggregation function applied to a corresponding list of dimensions.

Click on a dimension in the left pane to activate the dimension preview menu.

dimension actions

After you drag a dimension into the show bar, click the dimension to set the following options:

  • Sort by: Sort the dimension in ascending or descending order.
  • Limit: Limit the number of values to display. For example, selecting Limit:5 in the above example displays five countries with the highest number of events.
    The limit defaults to 1000 for string type dimensions and 5000 for numeric type dimensions.
  • Overall: Show or hide a row that displays the sum total of the measure for all values returned by the query.
  • Others: If you set a Limit, show or hide a row that displays the sum total of all values omitted by the limit.

Create a custom dimension

You can add new dimensions or edit the existing ones for a data cube. Click the edit icon at the top right to open the Edit data cube page. Click the Dimensions tab in the data cube edit view to display the dimensions in the data cube.

Cube Edit Dimensions

Click the edit icon for a specific dimension to modify its configuration. If the dimension directly represents a column from the source table, make your chances in the Basic tab.

To create a dimension to represent an arbitrary transformation, use the Custom tab. There you can enter a Druid SQL expression as the dimension's formula.

For example, the following expression defines a dimension based on a lookup function for country names:

COALESCE(LOOKUP(t."Country", 'store_sales_country_to_iso31661'), t."Country")

This expression performs a lookup that retrieves the country name based on a mapping between country abbreviations and a country name in the store_sales_country_to_iso31661 table. The resulting data cube includes a dimension with the full country name. In effect, this data cube joins rows from the store_sales_country_to_iso31661 and store_sales tables.

Data cube dimensions and measures can include a description text to help users in the Info pop-up window.

Dimension info

Dimension suggestions

You can use the Suggestions feature to rapidly add many new dimensions. Polaris scans the schema that underlies the dataset and automatically suggests a dimension for any column not already represented.

Dimension types

This section describes the dimension types you can create.

Time dimensions

Druid has a primary time column called __time which it uses for partitioning. You can also create additional time columns.

Druid ingests time columns as regular dimensions formatted in ISO 8601 string format, for example 2017-04-20T16:20:00Z.

When creating or editing a dimension, select Time as the type to configure a time dimension. You can also configure the default granularities that Pivot presents and select a bucketing option.

Pivot uses an algorithm to format time ranges. To override it, go into edit mode for the time dimension and click Format. Enter a specific format to display. For a list of supported formats, see the Moment.js documentation.

The following example datacube shows a time dimension with format ha to display the hour and am/pm in lowercase:

Cube time dimension

String dimensions

Most dimensions are categorical, in the sense that they are represented as strings. String dimensions appear in the left navigation panel with the "A" icon.

When creating or editing a dimension, select String from the type to make a string dimension.

Multi-value dimensions

Select the Set/String type to create a multi-value dimension, which is an array of strings. For example, a multi-value dimension named Cities might contain the following data:

["Paris","London","New York","Sydney"]

Numeric dimensions

Numeric dimensions represent numeric values in the data source. You can apply aggregations to numeric dimensions to create new measures.

When creating or editing a dimension, select Number from the type to make a numeric dimension. You can also configure the preset bucketing granularities that will be presented as defaults.

Geographic dimensions

Geographic units can be a useful way to segment data.

Cube view geo

To use the Geo marks and Geo shade visualizations, create and display a single dimension configured as the Geo type. Select the appropriate Geo encodingthe underlying data must conform to one of the following specifications:

Latitude and longitude

If your event data contains latitude and longitude coordinates, you can configure the Street map visualization to pinpoint events to precise locations on a map.

Create dimensions for your latitude and longitude data. Select the Geo type and the latitude or longitude Geo encoding.

See Street map for information on configuring the street map visualization.

Boolean dimensions

You can use boolean expressions to create dimension values. In the following example, you use a formula to create dimensions for accounts in the United States and specific accounts by name.

t.country = 'United States' OR t.accountName IN ('Toyota', 'Honda')

IP dimensions

You can store a dimension as a complex data type representing an IP address or an IP prefix.

When you ingest IP data, you can use the IP and IP prefix type dimensions in Polaris to search and filter in your visualizations based on IP addresses and IP prefixes.

When you create a data cube, Polaris auto-detects the complex IP columns and applies the appropriate type:

  • IP: An IP address in a 128-bit binary format.
  • IP prefix: An IP prefix in a 136-bit binary format, comprised of a 128-bit address and an 8-bit prefix value.

See the Visualize data page for information on using IP dimensions to filter a visualization.

info

If you create a dimension with type IP or IP prefix on a column that doesn't contain complex IP type data, Polaris converts the dimension type to string. IP filters don't work on strings but you can apply string filters to this data.

Custom dimension examples

Here are some examples of common dimension patterns.

The following examples show expressions as Pivot SQL.

Lookups

You can create dimensions that perform a lookup at query time. To have a dimension that represents a key into another table, set up a Druid query-time lookup (QTL).

You can then use the LOOKUP() function, passing the lookup key and the lookup table name, as follows:

LOOKUP(t.lookupKey, 'my_awesome_lookup')

To handle values that are not found in the lookup table, you can either retain values that were not found as they are or replace them with a specific string.

For example, to keep values as they are, use the lookup key as the fallback value:

COALESCE(LOOKUP(t.lookupKey, 'my_awesome_lookup'), t.lookupKey)

To replace values not found in the lookup table with a string, such as the word missing, pass the string as the fallback value:

COALESCE(LOOKUP(t.lookupKey, 'my_awesome_lookup'), 'missing')
$lookupKey.lookup('my_awesome_lookup').fallback('missing')

Joins

You can create a dimension that performs a join on another table at query time. When you define a dimension, use the PIVOT_LEFT_JOIN function to enable Polaris to perform a lookup-style (left) join on an arbitrary table.

The first argument is an SQL expressionusually a column reference.

PIVOT_LEFT_JOIN(
t.lookupKey,
'my_lookup_table',
'key_column',
'value_column'
)

The following example demonstrates a lookup for customer status:

PIVOT_LEFT_JOIN(t.customerId, 'customer_statuses', 'id', 'status')

URL dimensions

A URL dimension represent URLs in the source data. You can add a URL transformation to the dimension configuration to have a Go to URL action button for the dimension.

To create a URL dimension, edit the dimension and click Add URL. Provide the URL pattern, as in this example:

Cube edit dimension modal URL

Polaris interpolates the string (%s) for the given dimension value. In this example, the value Nepal becomes https://en.wikipedia.org/wiki/Nepal, the Wikipedia page for Nepal.

Extractions

Imagine you have a column called resourceName with the following values:

druid-0.18.2
druid-0.18.1
druid-0.17.0
index.html

You can create a dimension that extracts the version number in the column values:

REGEXP_EXTRACT(t.resourceName, '(\d+\.\d+\.\d+)')

Which returns the following values:

0.18.2
0.18.1
0.17.0
null

Other examples

This section includes common expressions you can use to build custom dimensions from the dimensions that already exist in your data cube.

This example returns the character length for every row of the dimension. Applies to the String dimension type.

LENGTH(t."comment")

This example extracts all numbers from every row of the dimension. If there are no numbers, it returns missing. Use regex expressions to build extractions. Applies to the String dimension type.

(REGEXP_EXTRACT(t."comment", '([0-9]+)', 0)) 

This example makes the entire string upper case. The function also accepts 'lowerCase'. Applies to the String dimension type.

UPPER(t."comment")

This example adds 'End of String' to the end of the dimension's values. Applies to the String dimension type.

CONCAT(t."comment", 'End of String')

This example returns the index value for 'World' in the string for every row in the dimension. In Pivot SQL, the function starts indexing at 1 and returns 0 when no match is found. Applies to the String dimension type.

POSITION('World' IN t."dimension") - 1

This example returns x-y number of characters for every row in the dimension. Applies to the String dimension type.

SUBSTR(t."dimension", x, y)

This example returns true if the value matches a value in the specified set; otherwise, returns false. You can specify the returned values as follows:

$cityName.in(['London','Aachen','Abbotsford']).then('Match Found').fallback('No Match')

Applies to String and Numeric data types.

t."cityName" IN ('London','Aachen','Abbotsford')

This example returns true or false based on whether the given regular expression is matched. You can specify the returned values as follows:

$dimension.match('^Hel*')then('Matched').fallback('Not matched')

Applies to the String dimension type.

REGEXP_LIKE(t."comment", ('^Hel*'))

This example returns true or false based on whether the input string is matched. You can specify the returned values like this: $dimension.contains('hello').then('Hello Friend').fallback('Bye Friend') Applies to the String dimension type.

CONTAINS_STRING(t."comment", 'hello')

You can use math operations in custom expressions. Applies to the Numeric data type.

LENGTH(f."comment") + 2

This example returns an absolute value. Applies to the Numeric data type.

ABS(CHAR_LENGTH(t."channel")-100)

This example raises the operand to the power that you specify. Applies to the Numeric data type.

POWER(LENGTH(t."comment"), 5)

This example returns true if the value matches the input value and false if it does not. Only use single quotes with strings. Applies to String, Numeric, and Boolean data types.

t."comment" IS 'red'

This example performs an AND operation between the value of the dimension and the second value that you input. Additional Boolean expressions include 'or', 'greaterOrEqual', 'greaterThan', 'lessThanOrEqual', 'lessThan', and 'not'. Applies to the Boolean data type.

t."comment" AND (2>1)

This example creates a time bucket of the duration that you specify. Applies to the Time data type.

TIME_FLOOR(t."__time", 'PT5M')

This example returns the largest operand time which is less than or equal to the nearest duration within the specified timezone. Applies to the Time data type.

TIME_FLOOR(t."__time", 'P1D', 'Asia/Shanghai')

This example shifts the operand time by the interval you specify within the given timezone. Do not use a + sign to shift time forward. Applies to the Time data type.

TIME_SHIFT(t."__time", 'PT1M', -5)

This example returns part of a timestamp. Additional possibilities include: 'MINUTE_OF_HOUR', 'MINUTE_OF_DAY', 'MINUTE_OF_WEEK', 'MINUTE_OF_MONTH', 'MINUTE_OF_YEAR', 'HOUR_OF_DAY', 'HOUR_OF_WEEK', 'HOUR_OF_MONTH', 'HOUR_OF_YEAR', 'DAY_OF_WEEK', 'DAY_OF_MONTH', 'DAY_OF_YEAR', 'WEEK_OF_MONTH', 'WEEK_OF_YEAR', and 'MONTH_OF_YEAR'. Applies to the Time data type.

TIME_EXTRACT(t."__time", 'SECOND')

This example casts a number to a time value.

MILLIS_TO_TIMESTAMP(t."dimension")