How to restrict Download Data to 25 rows in a tile

[aleishon]

Hi all,
I created a tile that includes columns from different views and custom fields, I set a row limit of 25, enabled "Show the first 25" in Edit Formatting, and unchecked the option Download_without_limit in admin role user settings.

However, the end user still has the option to download custom, which allows them to export the entire table with all results. The 25-row limit is not working as expected.

How can I ensure that the user can download only 25 rows?
They should not have access to the full data set.
Thanks for your help.

@sam8 

[sam8]

Interesting question!

You're right that unchecking "download without limit" won't prevent users from entering in their own numbers like 5000. That feature is designed to prevent users from downloading result sets with millions/billions of rows, which could slow down your database and/or Looker.

I don't think there is any way to limit download ability to just 25 rows. Why do you want this? Is it a data privacy issue? If so, I think there are more robust ways to get to this. (For example, if you're only relying on row limits to hide the appropriate data, what happens when the data changes? When a user adds a filter or changes the sort? So many things could cause that set of 25 rows to change.)

Can you apply a filter to the tile that limits the data to only what the user should see? If you share more about your use case, I may have additional suggestions.

[aleishon]

Hi Sam! Thanks for your response!
Yes, the dashboard has security restrictions because customers access it, and each one can only see their own data. However, their sales are also compared and ranked against the total sales of all customers.

The views already have security applied based on the logged-in customer. They can see their own data and the total sales of their region, but not the data of other customers.

That’s why the tiles we’re building are rankings. In the background, we are processing millions of rows, but the data accessible to each customer for analysis is limited.

Is there any alternative to limit what users can download?

[sam8]

Thanks for clarifying!

I think the canonical solution for this is to use the download_with_limit permission, as you've already been experimenting with. This still caps the downloads to 5000 rows. 

A hacky workaround I can think of would be to filter the data at the tile level so that only 25 rows are returned from the query (without relying on the LIMIT clause!). Could you make a dimension that returns the row number (syntax will vary depending on your database), and then filter on the row number being less than or equal to 25?

[aleishon]

Hi Sam!

I tried to create a new dimension to filter rows less than or equal to 25, but it didn’t work. My tile has hidden columns, and when I download the data, it includes the hidden ones too.

Now, I’m trying a different approach. I created a new view(derived_table) from my original tile, added a parameter, then joined this view to a new explore. In this new explore, I used sql_always_having with the parameter. This way, I can download exactly 25 rows, no matter the download options.

But this is not the best solution either because I have to move each tile to a new view, join it in the second explore, and also find a way to pass filters to the second explore. I ended up with double the views, two explores, and I can’t delete anything because the new views depend on the old ones. 
But doing it this way means a lot of extra work, and I’m not sure if, in the end, everything will work as I need. I also have to retest security, etc. I haven’t finished yet, but that’s the path I’m following.