User Guide

Getting Started

Help Center

Documentation

Community

Training

Certification

Solved

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

  • 14 September 2021
  • 5 replies
  • 51 views
V

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

icon

Best answer by olga 16 September 2021, 15:44

@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-

 

@Vinay 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.

View original

5 replies

olga
Rank
Userlevel 1

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
Userlevel 6
Badge +1

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?

Dawid
V

@olga 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?

olga
Rank
Userlevel 1

@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-

 

@Vinay 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.

V

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

Reply