Adding dimensions from a map file

[jserna-lacoe]

I'm new to Looker and just getting started into GCP overall. Our map layer files are sourced from GitHub and dropped into a folder in the object browser. Our model references this file and assigns a property key. In our logical layer, we have an explore which joins various views, one of them being the map layer. The layers are coming in by the primary key join, but only the layers. I have other columns of data in the map file, but they are inaccessible and I have no idea how to get them joined since this looks and feels different than table data. 

Purpose: I have purposefully duplicated rows in the map file and created a dimension which I hope to use to de-deduplicate the layers. Rather than duplicating the table data, my thinking is by duplicating the map data the table will duplicate on join, such that I can use the map dimension which isolates layers into three groups which never overlap. 

Currently, I am using an unduplicated map file, that, once joined, can be filtered by a dimension from the table data. However, this dimension has four categories that can be used incorrectly 3/4 times due to the overlapping shapes that are true to life. I want to create a pre-grouped dimension that eliminates the possibility of a user selecting an incorrect combination to be displayed. I cannot use case when on this data because since the data is unique it won't give me the combination I'm looking for. I would have to union the table onto itself to duplicate rows, or I would duplicate the map data. However, I don't fully understand how maps are used, and if any other attributes can be brought in. 

[corgi-looker]

Hey @jserna-lacoe,

I'm not sure if I can completely answer your question based on what you've given me but hopefully I can point you in the right direction.

I'm making some assumptions here: you're using BigQuery as your data warehouse and that your mapping file is in a format such as GeoJSON that BigQuery can handle.

You could add your mapping file as a table in BigQuery and then join that with your other tables in the model file.

---

@jserna-lacoe wrote:

However, this dimension has four categories that can be used incorrectly 3/4 times due to the overlapping shapes that are true to life.

---

For this, I would just create a dimension which is a case when statement which explicitly creates the combinations which are allowed as 4 different options. You can reference fields from other views using Looker once those views have been joined together in the model layer.

I may be overcomplicating things though, you may not have to load the mapping data into BigQuery, perhaps just using that case when statement and referencing fields from other views is all you'll need. You could then "hidden: yes" the other dimensions which give users many options or "always_filter" or "sql_always_where" parameters on the explore.

Let me know if I'm off base or missing something.

[jserna-lacoe]

Hello! Thanks for engaging on this topic! 

We're talking about school districts on a map. There are districts which cover the same area.

We are sourcing our map file from github, and referencing it in Looker as a map layer. Any dimensions in the map file seem to be ignored, and only the primary key is used. So as long as it joins on the key everything will display -- including overlapping and duplicated shapes, which are an issue when displaying a heatmap.

In order for a case when statement to work, I would need to duplicate table rows, because we would be using one of the district types in a duplicative manner. Rather than do this horrible practice, a simple multi-select filter displays school districts; however, the user must know what are the correct combinations to display. I would like to prevent the user from selecting "incorrect" combinations to display, but in order to do so I would need to duplicate rows to create a new dimension to use as a filter. I'm not happy with either solution, but I prefer not duplicating data.

I'm curious if there are other ways to prevent incorrect combinations using a multi-select filter.

[corgi-looker]

Hey @jserna-lacoe,

I'm not sure if this would help but you could try having a dedicated dashboard for this report and using the dashboard filters. Dashboard filters can be customized to a greater extent than explore filters, such as link filtering. 

I hope this helps, let me know if you have any further questions.

[jserna-lacoe]

I am using a dedicated dashboard with a dashboard filter. District Name is a linked filter from District Type. I have a complimentary list displaying all the names based on the district type combination. What I'm attempting to do is hard-code the filter options that only include valid filter combinations. It would look like: Elementary School District, Elementary School District & Unified School District, etc. The row values are never equal to "Elementary School District & Unified School District", rather it's performing an OR condition that looks for either of these values to be used in the filter. The challenge is this logic must not be done by the user, they must be pre-defined in advance as simple button selections.

I should probably mention we're using Looker Cloud Core. If there are LookML hard-coding filter options that can be applied, I'd like to know more about that documentation.