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