Managing data cubes

This section explains how to create and configure your data cubes.

Creating a data cube

Create a new data cube from the home view by clicking the + Data cube button. The Create new data cube modal allows you to define the source data for a data cube.

add cube modal

Checking the Auto fill dimensions and measures checkbox will automatically detect your schema and create dimensions and measures accordingly.

Another way to create a new data cube is to duplicate an existing data cube and edit its properties.

After creating the data cube you will be taken to the edit screen to adjust any details.

Editing a data cube

Click the pencil icon in the data cube header to edit a data cube.

Cube edit view

Within the edit view you can change the title, description, and default timezone of the data cube. You can also edit and create dimensions and measures from their respective tabs.

Schema detection

Pivot will automatically detect the columns in your data source and recommend some possible dimensions and measures.

Schema detection can be helpful when you are creating a new data cube in which case it will allow you to initialize the data cube with some dimensions and measures.

The other use for schema detection is when you evolve your schema and add columns over time. If you have just added a column and you want a corresponding dimension to be created for it, you can use schema detection to automatically construct that column for you. This functionality is accessible from the Suggestions tab of the New dimension and New measure modals.

Cube edit new dimension modal

How schema detection works

Pivot looks at the dataset metadata and uses the returned list of columns, their types, and their aggregation (in case of rollup) to determine what dimensions and measures to suggest.

Pivot generates dimensions and measures by applying the following rules to the discovered underlying column types:

Limitations

While schema detection is an invaluable tool for quickly getting you up and running with a new data source, it can never do a perfect job. Once you create a data cube you should play with it and tailor it to be perfectly suited to your needs. Don't hesitate to change and delete the auto-generated dimensions and measures - they will always be there in the Suggestions tab.

In particular, schema detection can not detect these common scenarios:

Advanced options

The following advanced options are available when you expand the More chevron.

Data cube advanced

Subset filter formula: Specify a plywood formula that applies a mandatory, hidden filter to all queries made through this data cube. See row level restriction.

Required filter token: Specify a token that corresponds to a filter token defined in a user role. See token based row level access control.

Primary time dimension: Specify the time dimension that corresponds to when the data point was actually added. This is used for checking the freshness of data and also potentially enforcing a time filter. Defaults to Time.

Enforce time filter: If set to true, ensures that every query is filtered on the primary time dimension. This should be used if time unbounded queries are likely to be slow due to the volume and tearing of data.

Instances: Configure multiple filtered instances within a single data cube via a configuration object. See example configuration.

Data cube instances

Refresh time: Configure how the data cube will calculate the data's latest time. Defaults to Query, which means it will query the data to learn about the latest data ingested. This can also be set to predefined in which case latest current time will always be assumed. An alternative 'fixed' time can also be defined as the 'fixedMaxTime' property in the options.

Query caching: Specify query caching behavior. Allowing caching can greatly speed up exploration but can also cause results to be a little out of date especially in realtime rolled up datasets.

Data cube options

Additional data cube configuration options as JSON with any of the following properties:

Time filter presets

Each section of presets in the time filter menu can be configured with custom presets. See an example of latest presets.

Querying options
Visual options

Examples

Instances
{
  "dimensionFormula": "$language",
  "values": [
    {
      "title": "English",
      "value": "en"
    },
    {
      "title": "Chinese",
      "value": "zh"
    },
    {
      "title": "Spanish",
      "value": "es"
    }
  ]
}

dimensionFormula is a plywood formula that operates on a dimension.

values is a list of objects with keys of title, value where value is the value that will be matched with the dimensionFormula expression and title is the label displayed in the UI.

piiMask

{
  "piiMask": {
    "aggregate": "$main.count()",
    "threshold": 10,
    "maskText": "HIDDEN [%i]",
    "otherText": "Other (%i)",
    "hideAggregates": false
  }
}

Note that %i will be resolved as the row index of the datum

latestPresets

{
  "latestPresets": [
    { "name": "1M", "selection": "$m.timeRange(‘PT1M’, -1)" },
    { "name": "5M", "selection": "$m.timeRange(‘PT1M’, -5)" },
    { "name": "30M", "selection": "$m.timeRange(‘PT1M’, -30)" },
    { "name": "1H", "selection": "$m.timeRange(‘PT1H’, -1)" },
    { "name": "6H", "selection": "$m.timeRange(‘PT1H’, -6)" }
  ]
}

name corresponds to the UI label of the preset.

selection corresponds to the interval of time selected.

Note that in the selection value, $m will be resolved as the data's max time and $n as the current time.

Overview

Tutorial

Deploy

Manage Data

Query Data

Visualize

Configure

Special UI Features

Imply Manager

Misc