The following problem:
I have a department. This department is responsible for several products.
Now I have to make a filter that should look like this in the sql:
where (company_department = ‘Department Name’ or product_owner = ‘Department Name’)
This has to be done in LookML, because this logic has to be valid for every Looker of this exploration and the users shouldn’t/can’t work with custom_filter.
At first I thought that this could not be that hard, but I can’t find a solution.
Why not just created a unified dimension using a CASE WHEN?
Sounds interesting
What syntax do you have in mind?
Short update:
I still haven’t found a good solution.
I have tried the following.
That works. With a single valuation day even tolerable, with several days the performance goes through the cross join into the cellar :-).
Also a direct join is not better, but rather worse:
select * from fact inner join dimension on (x=x)
inner join pdt on (pdt.department = dim.department or pdt.department = dim.activity_owner)
I find this very frustrating, because the logic is really not complex.
Unfortunately there is no way (at least I don’t know any) to insert a value directly into the SQL statement except parameters.
Do any of you have any ideas how I could solve this better?
I believe what @mprogano was suggesting would work. Create a new dimension:
dimension: department_filter {
type: yesno
sql: case
when ${company_department} = 'Department Name' or ${product_owner} = 'Department Name' then true else false end;;
}
or probably even more concise:
dimension: department_filter {
type: yesno
sql: (${company_department} = 'Department Name' or ${product_owner} = 'Department Name') ;;
}
Edit: swapped ` for ’
I also learned you can do this all in LookML if you wanted to…
@moebe if you do run the above code (which is great), maybe sure you swap out the the ‘
and ’
for a single quote (aka '
)
Maybe I’m just totally on the wrong track.
The @PatKearns proposal
dimension: department_filter {
type: yesno
sql: (${company_department} = ‘Department Name’ or ${product_owner} = ‘Department Name’) ;;
}
requires that I know the “department name”.
But I do not. It’s just one department among many.
So the suggestion only works if I am willing (like with a parameter) to maintain all possible departments manually.
But this would really only be a stopgap solution, because I always run the risk of delivering incorrect dashboards to my customers until I notice the presence of a new department in the source system
The same applies to the proposal: @mprogano
As I said, the easiest solution would be to parse the result of a filter directly into the SQL, but that doesn’t seem to work.
But thanks a lot to both of you. At least I have another solution. And having more options never hurts 🙂
Little known trick: if you use a filter-type field, you can define a very custom SQL for it that will be used in the where clause.
filter: department {
type: string
suggest_dimension: company_department
sql:
{% condition department %} ${company_department} {% endcondition %}
OR {% condition department %} ${product_owner} {% endcondition %}
;;
}
(The department
inside of the {% condition %}
tag may be optional when a field is referring to it’s own filter selection, I forget, but you can test. In any case, this is the general form of the tag that can refer to another filter if necessary)
This is great!
Thanks a lot.
Now the suggest_dimension has to work (I’m probably doing something wrong) and then the problem is solved.
Thanks!
@fabio1
I have one more question.
Here in this example I create a new dimension based on fields from two different views.
I have this again and again and think each time in which view this dimension should be created.
Therefore I always consider to outsource such things.
This should be possible via refinements, right?
At the moment I have this:
Now I consider to outsource parameters, filters and artificial dimensions (with Concat etc.).
What do you think about this?
It’s a stylistic preference, but yes, I like that approach to using refinements. It sounds like you may have read my “LookML layering” article.
For defining fields that reference across two views, I like to use separate field-only views, as described in my style guide - expand rule F1 to see an example.
One thing to be aware of - if you plan on using the LAMS linter to enforce the style guide, unfortunately it does not yet work with refinements as they are a new feature. But, all of the style guide rules are very doable by hand, as it was originally a stand-alone style guide before the linter exited.
The style guide is amazing, thanks