primary_key

Usage

 view: view_name {   dimension: field_name {     primary_key: yes    } } 

Definition

The primary_key parameter specifies that a dimension is the primary key for its view. The default value is false. For example:

dimension: id {   primary_key: yes   sql: ${TABLE}.id ;; } 

The primary_key parameter has the following qualities:

• Helps Looker to avoid unnecessary GROUP BY clauses.
• Is necessary to make the foreign_key parameter work properly.
• Is required for the proper functioning of symmetric aggregates. When you join views with measures (aggregates), a primary_key must be defined on each side of the join to properly calculate the aggregations.

The primary_key parameter has the following limitations:

• You cannot add this parameter to more than one field in each view.
• You cannot add this parameter to a dimension_group that specifies more than one timeframe.

Creating a compound primary key

If you have a primary key that is comprised of multiple columns, you can create a dimension that concatenates those columns, then declare that as your primary key. For example:

dimension: primary_key {   primary_key: yes   sql: CONCAT(${TABLE}.promotion_id, ${TABLE}.sku) ;; } 

CONCAT works in some databases, such as MySQL, but other databases might have a different equivalent. Redshift, for example, uses || as its concatenation operator.

Using a persistent derived table (PDT) to create a primary key

In cases where there is no reasonable way to identify a unique row as a primary key, you can create a persistent derived table (PDT) to generate a unique ID. For example, the following derived table uses the table's row number to serve as a primary key:

view: transaction {   # sql_table_name: warehouse.transaction   derived_table: {     sql: SELECT ROW_NUMBER as transaction_pk, * FROM warehouse.transaction ;;     sql_trigger_value: SELECT CURRENT_DATE ;;     indexes: [customer_id, product_id, salesperson_id, transaction_date]   }    dimension: transaction_pk {     type: number     primary_key: yes     hidden: yes     sql: ${TABLE}.transaction_pk ;;     ...   } } 

Defining a new primary key in an extended view

If you use the extends parameter to extend a view, the primary key from the base view will be extended to the new view.

To change the primary key to a new dimension, add primary_key: no to the original dimension definition in the new view, and add primary_key: yes to the new dimension definition in the new view.

view: base {   dimension: old_primary_key {     primary_key: yes     sql: ${TABLE}.id ;;   } }  view: new {   extends: base    dimension: old_primary_key {     primary_key: no   }    dimension: new_primary_key {     primary_key: yes     sql: ${TABLE}.new_id ;;   } }