[RETIRED] Extending Model to Include Sensitive Information

(Michael Kaminsky) #1

The content of this article has been updated and migrated to a Looker Help Center article

0 Likes

Connect to multiple databases with single model and PDT?
Dynamically changing Connection
[Retired] Masking Sensitive Fields for Some Users
Using Jenkins to push master 'looks' to separate data models
(lloyd tabb) #2

The trick here is to create another file with an different extension. There is an example of this on learn. The IMDB has a model that run on both sparksql and redshift. There are subtle differences between each model.

We create a file, imdb.base.lookml, that contains the all of the common declarations then include it in imdb.model.lookml and imdb_spark.model.lookml.

Unfortunately, there is no way to directly create the file ‘imdb.base.lookml’ in Looker. To create the file, I went into the git repo, added the file then synced my development environment.

https://learn.looker.com/projects/imdb/files/imdb.base.lookml

File: imdb.model.lookml

# Redshift implementation of IMDB

- connection: imdb

- include: "*.view.lookml"       # include all the views
- include: "*.dashboard.lookml"  # include all the dashboards

- include: imdb.base.lookml

- case_sensitive: false

- explore: title
  extends: title_base
  # Redshift doesn't support lists, remove the fields.
  fields: [ALL_FIELDS*, -movie_genre.genre_list]

File: imdb_spark.model.lookml

# Spark implementation of IMDB

- connection: spark_imdb

- include: "*.view.lookml"       # include all the views
- include: "*.dashboard.lookml"  # include all the dashboards

- include: imdb.base.lookml

- case_sensitive: false

- explore: title
  extends: title_base
  joins:
    - join: cast_info
      type: inner
    - join: char_name
      type: inner
    - join: name
      type: inner
    - join: cast_title_facts
      type: inner
    - join: cast_top_genre
      type: inner
    - join: cast_info2
      type: inner
    - join: char_name2
      type: inner
    - join: name2
      type: inner
    - join: tv_series
      type: inner

File: imdb.base.lookml

- explore: title_base
  extension: required
  view: title
  #sql_always_where: ${title.kind_id} <> 2 
  extends: title_simple
  joins:
    - join: cast_info
      view_label: Cast Member
      sql_on: ${title.id} = ${cast_info.movie_id}
      relationship: one_to_many
      
    - join: char_name
      view_label: Cast Member
      sql_on: ${char_name.id} = ${cast_info.person_role_id}
      relationship: one_to_many
      
    - join: name
      view_label: Cast Member
      sql_on: ${cast_info.person_id} = ${name.id}
      relationship: many_to_one
      
    - join: cast_title_facts
      view_label: Cast Member
      sql_on: ${cast_info.person_id} = ${cast_title_facts.person_id}
      relationship: many_to_one

    - join: cast_top_genre
      view_label: Cast Member
      sql_on: ${cast_info.person_id} = ${cast_top_genre.person_id}
      relationship: many_to_one
      
    - join: cast_info2
      view_label: Cast Member (also in Title)
      from: cast_info
      sql_on: ${title.id} = ${cast_info2.movie_id}
      relationship: one_to_many
      
    - join: char_name2
      view_label: Cast Member (also in Title)
      from: char_name
      sql_on: ${char_name2.id} = ${cast_info2.person_role_id}
      relationship: one_to_many

    - join: name2
      view_label: Cast Member (also in Title)
      from: name
      sql_on: ${cast_info2.person_id} = ${name2.id}
      relationship: many_to_one

    - join: movie_companies
      sql_on: ${title.id} = ${movie_companies.movie_id}
      relationship: one_to_many
      
    - join: company
      view_label: Production Company
      sql_on: ${movie_companies.company_id} = ${company.id}
      relationship: many_to_one

    - join: movie_companies2
      from: movie_companies
      sql_on: ${title.id} = ${movie_companies2.movie_id}
      relationship: one_to_many
      
    - join: company_2
      from: company
      view_label: Production Company (also in Title)
      sql_on: ${movie_companies2.company_id} = ${company_2.id}
      relationship: many_to_one


    - join: movie_keyword
      view_label: Title Keyword
      sql_on: ${title.id} = ${movie_keyword.movie_id}
      relationship: one_to_many

#     - join: movie_has_keyword
#       view_label: Title Keyword
#       sql_on: ${title.id} = ${movie_has_keyword.movie_id}
#       relationship: one_to_many

    - join: movie_keyword_2
      view_label: Title Keyword (also in Title)
      from: movie_keyword
      sql_on: ${title.id} = ${movie_keyword_2.movie_id}
      relationship: one_to_many

    - join: movie_genre
      view_label: Title Genre
      sql_on: ${title.id} = ${movie_genre.movie_id}
      relationship: one_to_many
      
#     - join: movie_is_genre
#       view_label: Title Genre
#       sql_on: ${title.id} = ${movie_is_genre.movie_id}
#       relationship: one_to_many

    - join: movie_genre2
      view_label: Title Genre (also in Title)
      from: movie_genre
      sql_on: ${title.id} = ${movie_genre2.movie_id}
      relationship: one_to_many
      
    - join: movie_language
      view_label: Title Has Language
      sql_on: ${title.id} = ${movie_language.movie_id}
      relationship: one_to_many

    - join: movie_language2
      view_label: Title Has Language (also in Title)
      from: movie_language
      sql_on: ${title.id} = ${movie_language2.movie_id}
      relationship: one_to_many

    - join: movie_color
      view_label: Title
      sql_on: ${title.id} = ${movie_color.movie_id}
      relationship: one_to_many
      
    - join: movie_country_rating
      view_label: Title Rating
      sql_on: ${title.id} = ${movie_country_rating.movie_id}
      relationship: one_to_many
      
    - join: movie_country_rating2
      view_label: Title Rating (also in Title)
      from: movie_country_rating
      sql_on: ${title.id} = ${movie_country_rating2.movie_id}
      relationship: one_to_many
      
    - join: movie_weekend_revenue
      sql_on: ${title.id} = ${movie_weekend_revenue.movie_id}
      relationship: one_to_many

    - join: movie_release_dates
      view_label: Title Release Dates
      sql_on: ${title.id} = ${movie_release_dates.movie_id}
      relationship: one_to_many
      
    - join: movie_release_facts
      sql_on: ${title.id} = ${movie_release_facts.movie_id}
      relationship: many_to_one
      view_label: Title
      
    - join: movie_budget
      sql_on: ${title.id} = ${movie_budget.movie_id}
      relationship: many_to_one
      view_label: Title
      
    - join: title_location
      sql_on: ${title.id} = ${title_location.movie_id}
      relationship: one_to_many
      view_label: Title

    - join: title_extra
      view_label: Title
      
    - join: tv_series
      view_label: TV Episode
      sql_on: ${title.episode_of_id} = ${tv_series.id}
      relationship: many_to_one
  
      
# When joining in title, here are the joins you might want to use
- explore: title_simple
  extension: required
  joins:
    - join: movie_revenue
      sql_on: ${title.id} = ${movie_revenue.movie_id}
      relationship: many_to_one
      view_label: Title
1 Like

Templatized Dashboards with Forced Filters
Extending Explores to a Different Model
Connect to multiple databases with single model and PDT?
(Michael Kaminsky) #3

@lloydtabb thanks for the detailed write-up! This works for us for now, but I’m hopeful that future-Looker will have a more elegant solution :slight_smile:

0 Likes

(Andrew Kraemer) #4

This is really helpful! I was trying to do the same thing but ran into the same issue!

0 Likes

(Rob Schoenbeck) #5

This general idea worked for us as well (we have Explore defs. in a base model file that’s referenced by different model files with different connections, where the connection employs a different access policy), but we ran into Git integration issues with the files we created outside of Looker; we would get strange errors where the include statements were in the files we’d created via Git instead of the model files, and couldn’t see saved changes to our base model Explores in Dev mode.

0 Likes

#6

Hey Rob - if you’re still seeing issues with this, feel free to jump on chat and we can dig deeper. In general, we recommend only changing/creating LookML files within Looker to avoid any Git issues.

0 Likes

(Alex McGrath) #7

It took me awhile to find this thread, but it theoretically could solve a huge issue we’ve had for the last year whereby we need to change a generically named connection any time we wanted to use a different database as the source for our dashboards. It is a terrible setup when you have multiple developers developing on one instance who need different data sources simultaneously. It also causes a lot of issues with PDTs when we change this connection as frequently as we need to. It would definitely help our use case a lot if this was something that was integrated into the UI, as even you guys attest that editing lookml files outside the UI is not recommended.

0 Likes

(brettg) #8

@amcgrath83 - As of version 4.8 you should be able to drag and drop files into the IDE when in developer mode. If the file is not recognized as a view, dashboard or model file based on the extension, it will be bucketed in the “other” section of the IDE. Hope this helps!

0 Likes

(Alex McGrath) #9

Thanks so much @brettg!

0 Likes

(Peggy Beard) #10

Just a note, the extends functionality does now work with the fields: parameter.

Here is an example:
explore: products_ext {
extends: [products]
fields: [products.category,order_items.returned_date]

}

0 Likes

(Mark Goodwin) #11

Hi All! Just wondering if there is a more straight forward way? Since the post is 3 years old? Happy to follow all of these instructions but just making sure there isn’t something easier out there that I missed. This works really well for data structures that share the same table definitions (because of security, etc.)

0 Likes

(Mark Goodwin) #12

Having trouble getting Looker to “see” the base view. Any help? We created a new file (named op.lkml) and did a drag and drop into the Looker IDE. The file showed up under the Other section, but when we reference the base explore from another view, it can’t be seen (in the extends statement). I’m sure the include statement looks correct, so I’m a little hung up on what to try next.

Do we need to do a “push” to production with the added file first? Doesn’t seem like that would be necessary — but maybe it is.

0 Likes

(jeremy.eckman) #13

Hello @Mark_Goodwin- so if you’re looking to restrict access to a certain field (essentially a column in a database) you’ll want to limit access to models that have access to that field as explained in our permissions doc (link here.) That’s why this method has a nice appeal, since some users need access to a certain field in an explore while others shouldn’t, but they should all have access to those explores, other than that field(s).

In regards to looker ‘seeing’ the file, you will actually want to append the name with .model.lkml for a model or a .view.lkml for a view (Looker will hide these extensions once the file is put in the correct area) so in your case, op.model.lkml for the base model file.

If you have any additional questions, please let us know. You can post here, or we can work on a more specific issue with you by email at support@looker.com or by coming on chat. Best, Jeremy

0 Likes

(Mark Goodwin) #14

Thanks! I was able to get this working specifying the .model.lkml name. Don’t forget to use the extension: required parameter in order to make sure that the base model is hidden.

0 Likes

(sam) archived #15
0 Likes

(sam) closed #16
0 Likes

(Tig Newman) #17

Also check out this article to see how now user attributes can be used with liquid to redact the values for some users.

0 Likes