Share custom dimension between different views to avoid rewrite

  • 6 July 2022
  • 1 reply

My use case is the following:

I have many views with a “country” dimension that is populated with ISO-8601 country codes, I would like to create another dimension at LookML level to avoid having to re-write the mapping and edit it in a single place in case I forgot or want to modify a mapping.


My first solution works and consists in writing a dimension with a sql CASE like so:

dimension: country_name {

  type: string

  map_layer_name: countries



        WHEN ${TABLE}.country = "IT" THEN "Italy"

        WHEN ${TABLE}.country = "DE" THEN "Germany"

        WHEN ${TABLE}.country = "ES" THEN "Spain"

        WHEN ${TABLE}.country = "FR" THEN "France"

        WHEN ${TABLE}.country = "AT" THEN "Austria"




I would like to include this code in every view withouth repeating it, is there a way to include this code in my views, have a common view to derive this field from? or am I missing something else entirely?

This topic has been closed for comments

1 reply

Userlevel 1

The way we’ve solved it is to have a country dimensional table in our DWH.

  country_code    country_name    local_currency   region_name   enabled  
-------------- ---------------- ---------------- ------------- ---------
no Norway NOK SCA&CEE true
se Sweden SEK SCA&CEE true
es Spain EUR Atlantic true
fr France EUR true
pl Poland PLN SCA&CEE true
it Italy EUR true
de Germany EUR true
gb United Kingdom GBP Atlantic true
pt Portugal EUR Atlantic true
at Austria EUR SCA&CEE true
ie Ireland EUR Atlantic false
... ... ... ... ...
br Brazil BRL true

Then we’d have all these fields as dims in it’s own, which then is joined to an explore or extended with other views.

If adding this to your Source is not an option, you can also add these to a PDT that never refreshes on its own (the data is static pretty much).