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 ;;  } }