Share custom dimension between different views to avoid rewrite

[nicogaspaw]

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

  sql:

      CASE

        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?

[jpuris-16304282]

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).