It can be tough trying to clean up a large model if you don't know which fields are not being used. The i__looker explores get you pretty close, since you can look for fields that were used two months ago and not since then. However, since history is discarded after 90 days, there is no way using i__looker alone to find fields that had no activity in the last 90 days.
However, the model API can get you this information. So, we just need to combine the two. I wrote this JS snippet to do it, so you don't have to. Just get administrative API credentials, head to the interactive API Docs on your instance and paste the script into your JS Console:
Where to use this
https://< your domain here >:19999/api-docs/index.html
The code to use
(async function(){
var config ={
modelName:"faa_redshift",
timeframe: "28 days",
apiCredentials:{
//Provide API credentials with the admin permission
client_id:"9gC5bCTmrSP5KHCcvrnf",
client_secret:"..."
}
}
console.log("Authenticating")
var auth = await $.post("/login",config.apiCredentials)
var api = (verb,method,data)=>$.ajax({
method:verb,
url:"/api/3.0/"+method,
data:verb=="POST"?JSON.stringify(data):data,
headers:{"Authorization":"token "+auth.access_token}
})
var m=config.modelName
console.log("Getting fields in "+m+"...")
var model = await api("GET","lookml_models/"+m)
var exploreNames = model.explores.map(e=>e.name)
var fields = await exploreNames.reduce(async (chain,e) => {
var priorFields = await chain
var explore = await api("GET","lookml_models/"+m+"/explores/"+e)
return explore.fields.dimensions.concat(explore.fields.measures)
.map(f=>(m+"::"+e+"/"+f.name))
.concat(priorFields)
}
, await [] )
console.log("> Fetched "+fields.length+" field definitions")
console.log("Getting usage for timeframe '"+config.timeframe+"'...")
var queries = await api("POST","queries/run/json",{
model:"i__looker",
view:"history",
fields:["query.model","query.view","query.formatted_fields","query.formatted_filters","query.sorts","query.formatted_pivots","history.query_run_count"],
filters:{"history.created_date":config.timeframe,"query.model":m},
limit:50000
})
console.log("> "+queries.length+" queries fetched")
var fieldUsage = queries.map( q => ([]
.concat((JSON.parse(q["query.formatted_fields"])||[]).map(f => ({fid:q["query.model"]+"::"+q["query.view"]+"/"+f, count:q["history.query_run_count"]})))
.concat((JSON.parse(q["query.sorts"])||[]).map(f => ({fid:q["query.model"]+"::"+q["query.view"]+"/"+f, count:q["history.query_run_count"]})))
.concat(Object.keys((JSON.parse(q["query.formatted_filters"])||{})).map(f => ({fid:q["query.model"]+"::"+q["query.view"]+"/"+f, count:q["history.query_run_count"]})))
))
.reduce(((a,b)=>a.concat(b)),[])
.reduce((to,field)=>({[field.fid]:(to[field.fid]||0)+field.count, ...to}),{});
console.table(fields.map(f=>({field:f,usage:fieldUsage[f]||0})).sort((a,b)=>b.usage-a.usage))
})()
The result should look something like...
Note that this only counts when the fields are used directly. But, the LookML validator already allows you to detect when a field is referenced from another field.
