Can't download all data from reports with table calculations or window measures

[Vinay2]

I want to add few columns and create a derived ‘total’ column in Looker ( something like total = col1 + col2).

I tried doing this through table calculations in the Look and also through a ‘running_total’ type measure in LookML. Both these approaches give me what I want.

However, when I need to download the entire data manually from the look or schedule an extract for the entire data through an email, I get the following error message

Error: Refusing to run query with no limit that Looker cannot stream incrementally. Please choose a smaller limit or remove table calculations.

Error: Refusing to run query with no limit that Looker cannot stream incrementally. Please choose a smaller limit or remove the "window" measure types (such as running_total) from the query

How can I let users download data from reports which have such derived columns? Please let me know if there is any other way I can accomplish this

[olga1]

Indeed, in some cases downloading All Results is not possible, and table calculation is the most common of them.

You can see more of them here https://docs.looker.com/sharing-and-publishing/downloading#all_results

Instead, you should be able to use a Custom limit for downloading, specifying the limit of rows https://docs.looker.com/sharing-and-publishing/downloading#limits

[Dawid]

But isn’t that the whole point of ALL RESULTS. It means ALL, no filters or limits. What are the technical limitations here that Looker returns an error?

[Vinay2]

@olga1 That is a little surprising to me since this is a fairly common use case. In my case, taking a look at partial data won’t make much sense for the users.

So the way I see it, the only solution is to move the table calculations to the data warehouse and consume the data directly from there?

[olga1 · Accepted solution]

@Dawid there is indeed technical limitation - the ability to stream data in chunks. For example, table calculations never stream, running total is calculated similar way so they don’t stream as well. Some dialects don’t support pivots - then they are calculated in Looker, so they won’t stream neither. Check this article https://help.looker.com/hc/en-us/articles/360001285567-What-are-all-the-Row-Limits-in-Looker-

@Vinay2 you can try to move table calculation to the LookML, if that is possible. Like using measure of type: number for the arithmetical calculations over other measures, having dummy measures if you are adding values from dimensions being grouped  - like min(dimension_value) - will give you just one value like in your data tab in explore, but it will actually be a measure. They also can be hidden:yes so that they do not appear in the Field Picker and cause confusions.

[Vinay2]

@olga1 Thank you for the advice! Measure of type: number worked for me in LookML and I was able to download all the data.