Using a dynamic Dimension by parameter / Problem: lot of joins

[Maike1]

I use a parameter for selecting a field, i want to see in mey look/visualization like this:

parameter: pardimVar {
label: "_DimensionSwitch"
type: unquoted
allowed_value: {label: "BillingKind" value: "billingkind"}
allowed_value: {label: "BrandType" value: "brandtype"}
allowed_value: {label: "Category" value: "category"}
default_value: "category"
}

dimension: dimVar {
label: "VarDimension"
view_label: "_dynamic Dimensions"
label_from_parameter: pardimVar
sql:
{% if pardimVar._parameter_value == 'billingkind' %} ${dim_billingType.billingKindDescEng}
{% elsif pardimVar._parameter_value == 'brandtype' %} ${d_hersteller.markenart}
{% elsif pardimVar._parameter_value == 'category' %} ${d_materialklasse.category}
{% endif %}
;;
description: "use this dimension in combination with the DimensionVar Filter in the __requieredFilter!"
}

In the SQL query, which Looker generates, three joins are always generated for all three suggestions, even though I only use one join. How can I ensure that only the join that is actually used is generated?

This is a very simplified example of my application. In my actual code, I have 17 joins, which consume a huge amount of resources and make my model very slow.

[sam8]

Interesting question!

Have you tried referencing the fields with Liquid syntax instead of using the SQL reference syntax?

https://cloud.google.com/looker/docs/liquid-variable-reference#accessing_variables_from_other_fields

[Maike1]

Hi @sam8
thank you for response. I think I don’t undersand. do you have a Expample for your suggestion?

In my model i have a factTable “facts” and 3 Dimensions like “dimBillingKind”, “dimHersteller”, “dimCategory”. (StarSchema)

So If i want to select a field from dimension dimCategory the expected sql is:
Select
sum(value), billingKindDescEng from facts join billingType on a.billingid = b.billingId

but the sql which is generated is:
Select
sum(value), billingKindDescEng from facts a
join billingType b on a.billingid = b.billingId
join d_hersteller c on a.herstellerId = c.herstellerId
join d_markenart d on a. markenartId = d.markenart_id

[sam8]

That makes sense. Since the d_hersteller and d_markenart tables are referenced in the dimension SQL, Looker will join in those tables every time.

I haven’t tested this, but I think you could work around this by referencing these values as Liquid objects rather than using Looker’s SQL reference. My theory is that the Liquid will only resolve for the case that is true, and so Looker will only generate the joins needed for the field that is selected. The LookML would look like this:

dimension: dimVar {
label: "VarDimension"
view_label: "_dynamic Dimensions"
label_from_parameter: pardimVar
sql:
{% if pardimVar._parameter_value == 'billingkind' %} (( dim_billingType.billingKindDescEng._value ))
{% elsif pardimVar._parameter_value == 'brandtype' %} (( d_hersteller.markenart._value ))
{% elsif pardimVar._parameter_value == 'category' %} (( d_materialklasse.category._value ))
{% endif %}
;;

(please replace the parentheses with curly brackets to match the liquid variable reference syntax - Community won’t let me use that syntax presumably for safety reasons)

[Maike1]

Hi Sam8, thanks for trying to find a solution. If I try this:

i get neither field in the selection nor join in the sql statement.

[sam8]

hm, that makes me think that neither of the “if” conditions are being met.

First, it would be a good idea to add some “else” statement to the “if”.

Then, I recommend checking what the parameter value is actually outputting. Try making a field with only (( pardimVar._parameter_value )) in the “sql” parameter. Then we can use that to test the values of pardimVar.