Looker Blog : Data Matters

Name Game: Step 2 - Computed Dimensions

Lloyd Tabb, Founder & CTO

Jan 15, 2016

Name Game: Step 2: Computed Dimensions

In this step, we are going to create computed dimensions. Most dimensions map directly to columns in the underlying table. In this step we are going to build three new dimensions that are computed. Each of these measures lets us combine the data in interesting ways.

Generalize Year into Decade

We've been looking at names by year. Now we are going to build a decade dimension that combines all years into a single decade.

Generalize State into Region

We can look at data by state, but states near each other tend to have similar characteristics. We are going to take the 50 states and combine them into 5 regions.

Generalize Name into First Letter

We are going to bucket names by the first letter of the name so we can ask questions such as whether certain starting letters are used more often by a particular gender.

Combine Name and Gender into a more specific field

It becomes difficult to compare the female use of 'Riley' with the male use of 'Kerry' unless we have a single dimension that combines the name and gender concepts. We are going to combine them into a single computed dimension.

The Code

This model inherits all the behavior of the model we used in Step 1 and adds the 4 dimensions described above.

#
# bucketing the data by
#   decade
#   region
#   first letter of the name
#   name concatenated with gender

- view: names_step_2
  extends: names_step_1
  fields:
  # computed dimensions
  - dimension: decade
    type: number
    sql: FLOOR(${year}/10) * 10
    value_format: "0000"

  - dimension: region
    sql_case:
      West: |
        ${state} in ('WA','OR','CA','NV','UT','WY','ID','MT',
            'CO','AK','HI')
      Southwest: ${state} in ('AZ','NM','TX','OK')
      Midwest: |
        ${state} in ('ND','SD','MN','IA','WI','MN','OH','IN','MO','NE',
            'KS','MI','IL')
      Northeast: |
        ${state} in ('MD','DE','NJ','CT','RI','MA','NH','PA',
            'NY','VT','ME','DC')
      Southeast: |
        ${state} in ('AR','LA','MS','AL','GA','FL','SC','NC','VA','TN',
            'KY','WV')
      Unset: true

  - dimension: first_letter
    sql: SUBSTRING(${name},1,1)

Explore!

All of the queries below can be created by Exploring From Here. Select dimensions and measures, add filters and bonk the 'Run Button'.

Names by Region

Change the sort order. Notice that the most popular name varies by region.

Explore From Here

Names by Region Where Decade is 2000

Here we've restricted the decade to 2000. Change the sort order and notice how the most popular name varies by region in that decade.

Explore From Here

First Letter - Male vs Female

Change the sort order to various measures to get an idea of the differences by first letter and gender.

Explore From Here

Here's a list of all of the steps:

Go to the next Name Game page, Step 3: Filtered Measures

Next Previous

Subscribe for the Latest Posts