Define primary key on date_date

  • 27 January 2016
  • 7 replies

Userlevel 1

Hi Looker,

At Werkspot, we have 1 explore that has a plain table filled with (day)dates from 2013 until 2030 as a base.

The rationale is that we quite often create reports with Forecast numbers and therefore we want to be able to create reports with dates in the future.

Moreover: I would love to define a primary key on this date-view, because I want Looker to prevent to do an unnecessary “Group By” (when I create a report on day-level).

However, I cannot do this on a dimension_group level; it should be on a dimension level.

This is what I came up with:

 - dimension: date_date
primary_key: true
type: date_date
sql: ${TABLE}.date

- dimension_group: date
type: time
timeframes: [time, week, week_of_year, day_of_week, month, quarter, year]
sql: ${TABLE}.date

However, for the end user this is not ideal, because we now have 1 dimension and 1 dimension-group.

How can I define my primary_key in a way that for the end-user the primary key (in this case: date_date) is simply shown in the dropdown list, together with all the other date-fields?

Thanks, Willemijn.

7 replies

Userlevel 6


Sorry, right now there is no way to do this, but it is a great suggestion. We’ll get it tracked.


Userlevel 1

Thanks for your fast reply Lloyd!

We did a similar thing, but set

hidden: true 

on the primary key field so as not to show it to the users.

Userlevel 1

Thanks for your suggestion Robert.

However, hiding the primary key would not make sense, since the date_date is used quite often. So often, that I want to speed up the reports that are created by our end-users that are using the date_date in their Looks (by preventing Looker to make unnecessary Group By’s) 😉

In other words: the field should be accessible for our end-users.

Userlevel 2

You’ve probably thought of this already, but you can create separate date, week, month, quarter, and year dimensions — whichever you need — defining them using appropriate SQL date functions, and omit the dimension group altogether.

Userlevel 1

Hi Michael,

No, I did not (stupid, huh? 😉 )

Elegant and simple solution!

Thanks, Willemijn.

Userlevel 2

Ik hoop dat ik niet uw werk spotte. :-)