Optimize your Model with Keys and Indexes

This article describes how to optimize a model using keys and indexes. Adding keys and is an advanced optimization technique. Please see the performance guide for when you should add keys and indexes.

Add or modify pipeline keys

Add or change the primary key

  1. Open an existing model.

  2. On the model screen, select a pipeline from the Pipelines section.

  3. Ensure you are on the step you wish to add the key to and Select the column that will become the main key column of the primary Pipeline Key.

Keys on different steps.

  1. In the design panel, click Make Primary key.

  2. Click Save.

    A blue key icon is added to the column.

Edit the key column

  1. Click the primary column (indicated with a blue key icon).

  2. In the design panel, you can perform any of the following actions:

    1. Add additional key columns to the primary key column to create or change a composite key.

    2. Remove additional key columns from the primary key.

    3. Remove the primary key from the pipeline.

Add additional columns to the pipeline key

  1. On the model screen, select a Pipeline from the Pipelines section.

  2. Select the current Primary key column (indicated with a blue key icon).

  3. In the design panel, begin typing the column's name that you want to add in the Additional keycolumns text box.

  4. Select the appropriate column. The selected column is now marked with a yellow key icon.

  5. Repeat steps 2 and 3 until all the desired columns are added to the main key column.

  6. Click Save.

Remove additional columns from the primary key

  1. On the model screen, select a Pipeline from the Pipelines section.

  2. Select Primary key column.

  3. In the Primary key text box in the design panel, click x on the column to be deleted.

  4. Click Save.

Remove the primary key

  1. On the model screen, select a pipeline from the Pipelines section.

  2. Select the primary key column.

  3. Click the Clear Primary Key link in the General section of the Properties pane.

  4. Click Save.

Note

If the primary key contains additional columns, they remain part of the main column's attribute key, indented beneath it in the pipeline schema.

Add an index

To add a new index to the pipeline:

  1. On the model screen, select a Pipeline from the Pipelines section.

  2. Select the INDEXES tab.

  3. Collapse the design panel and click ➕ Index.

  4. Open the design panel and click ➕ Column.

  5. Select a pipeline column from the drop-down list to add to the new index. More columns can be added to the index by repeating this step.

  6. Click Save.

Note

When one or more columns have been added to the index, this warning may appear on the pipeline:

The index won't be created in the warehouse unless the pipeline is created as a table.

Create a pipeline as a table

  1. Select the COLUMNS tab, expand the Database section on the design panel, and set Create Pipeline As to Table.

  2. Click Save.

  3. Click Process Pipeline.

In this section: