FAQ: Why does Looker create dimensions for my numeric columns?

bestpractice
lookml

(Zev Lebowitz) #1

LookML has two primary types of field, Dimensions and Measures. In general:

  • Dimensions are “attributes” that describe a row of data. They can be any type (number, string, boolean, date), and can be used to filter, pivot, or filter data in Explore.

  • Measures are aggregations of numeric values across multiple rows, such as sums, averages, minimums, and maximums.

First time LookML developers often ask why the LookML generator creates dimensions for numeric columns in their database.

In general, it’s best practice to first create a dimension for each numeric column, and then build measures which aggregate those dimensions, for two reasons:

To retain a single reference back to raw columns in your database

Let’s say for example, that I sell shoes and track the cost of each shoe I sell. I certainly want to track my total sales with a SUM measure. I may also want to look at average prices, perhaps to see whether certain types of customers are more willing to buy more expensive shoes.

Old LookML
- dimension: sales_price
  type: number
  sql: ${TABLE}.sales_price

- measure: total_sales
  type: sum
  sql: ${sales_price}

- measure: average_price
  type: average
  sql: ${sales_price}
New LookML
dimension: sales_price {
  type: number
  sql: ${TABLE}.sales_price ;;
}

measure: total_sales {
  type: sum
  sql: ${sales_price} ;;
}

measure: average_price {
  type: average
  sql: ${sales_price} ;;
}

In this example, even though I have multiple measures based off of the sales_price column, my only reference to an actual column in the database is ${TABLE}.sales_price for the sales_price dimension. If we ever need to change the definition to sales price, I only have to update my model in one place.

To filter, group by, or pivot based on a numeric dimension

Numeric columns in your database may also be valuable as dimensions. Since dimensions describe single rows of data, you can use these descriptions to filter, group by, or pivot data.

It’s often helpful to create a [tiered dimensions] (https://looker.com/docs/reference/field-reference/dimension-type-reference#tier) off of a numeric dimension to bucket multiple values together into categories.

Continuing with the example above, I might want to use sales_price dimension, or a tiered version it, to:

Filter: Count how many pairs of shoes were sold for more than $100

Group By: See a histogram of sales by shoe price

Pivot: See how different shoe prices contribute to sales over time

Looker may use the terms dimensions and measures a bit differently from other tools, but retaining a single dimension for each column, or transformed column, in your views ensures consistency in your model and gives your users the flexibility to query data interactively.

If it isn’t logical to let users filter, group by, or pivot on a numeric dimension, you can always apply hidden: false to remove it from explore, but still use it elsewhere in the model.


(Levi Davis) #2

For each old LookML code block in this article and its comments, we just added the New LookML equivalent code.


(Rachel Stewart) #3

It looks like there’s some HTML issues in this FAQ. Does anyone have an example pivot like the shoe price one here? I’m trying to explain the advantages of dimensions for numeric columns to a client. Thanks!


(Izzy) #4

Just fixed that HTML, so that example should be working now. Anyone got any other examples?


(Rachel Stewart) #5

Thanks Izzy! I’m up for any more examples, but this one will work well now that it’s visible.