The Question:
I have defined some measures in a view. I now join this view to another view, and I don’t see the measures anymore, only the dimensions. Where are they?
The Answer:
Symmetric Aggregates allows measures of all types to be brought through joins. However, this is only possible if there are primary keys defined in the views on both sides of the join. In order to have measures come through joins, you need to define a primary key in all views involved.
You can do this by adding primary_key: yes to the primary key field definition, like so:
dimension: id {
type: number
primary_key: yes
}
Note: Primary keys must have completely unique, non-NULL values to be used with Joined Sums and Averages. If your primary key is not unique, you might see an error like the following:
Non-Unique value/primary key (or sql_distinct_key), value overflow or collision when computing sum.
Additionally, if you are in a situation where there is not a single unique field, but a combination of fields are unique, you can make a compound primary key by concatenating together two or more fields in SQL. Read more about how to resolve this error here.
Without Symmetric Aggregates
Some dialects do not support Symmetric Aggregates. When Symmetric Aggregates cannot be used, Looker will only bring the count measures (which become count distincts) through a join by default. This approach also requires a primary key because the count distinct will use the primary key. This prevents inaccurate results and fan outs. We have a full blog post about this here.
There is a list that shows which dialects support symmetric aggregates in the most-recent Looker release.
Consider:
- The use of fields (for Joins) at the join level: Fields enable you to specify which fields (measures, dimensions) from a joined view are brought into an Explore.
- Choosing the correct relationship parameter: The purpose of the relationship parameter is to tell Looker whether to invoke symmetric aggregates when the join is written into a SQL query. It’s important to properly define the type of relationship in order for Looker to calculate accurate measures.