Looker Pagination

In looker, queries that are created with the Explore page will have a maximum limit of 5,000 rows. This limit also applies to Looks, dashboards, and queries that are created in SQL Runner.

However, there are scenarios where there is business need to view more than 5000 records. For example, in a retail organisation where there are million’s of SKU’s(Item at a store) and based on a certain filter criteria, the table visualisation gives more than 5000 records that business is interested to look at, pagination in looker is very helpful and useful.

In the example below which is looker table visualisation, some details are showed for each store number. There are more than 5000 stores and hence as mentioned in such scenarios, we will leverage pagination to display such reports.

• New view having a derived table as below will be created for pagination.

Note : — the below code is for Big Query.

     view: pagination {
            derived_table: {
            sql: SELECT
            "users"."email" AS "users.email",
            "users"."id" AS "users.id",
            "orders"."id" AS "orders.id"
            FROM
            "public"."order_items" AS "order_items"
            LEFT JOIN "public"."orders" AS "orders" ON "order_items"."order_id" = "orders"."id"
            LEFT JOIN "public"."users" AS "users" ON "orders"."user_id" = "users"."id"
            GROUP BY
            1,
            2,
            3
            ORDER BY
            3       
    LIMIT 1000000000    
          OFFSET {{ number_per_page._parameter_value | times: page._parameter_value | minus: number_per_page._parameter_value }} ;;
        }

• In the above view, we can define multiple other dimensions and measures based on the requirement.
• The other 2 important parameter that needs to be added are

parameter: page {
    type: number

  }
  parameter: number_per_page {
    default_value: "5000"
    type: number

  }

• The OFFSET code is important and needs to be added for pagination. It uses the parameter values “page” and “number_per_page” .
• Before offset, I have added a limit which is required before offset. However, I have given a value which is larger than the the number of rows in my table. This is just to ensure we are getting all the rows on your table, this should be the same as not use the limit, because we are dragging all the rows. Looker will always add a limit at the end of the queries and hence this limit is just to ensure our code works fine. Try removing the limit and the code will through error.
• The “number_per_page” has the value 5,000 by default. This is based on how many rows we want to see at once. This value can change. I would prefer a lesser value (500) always for better performance.
• The parameter page, will be the page’s number user wants to see, this should be a number between 1 — round(count(*)/5000); for example, I have 258,844 rows, so I have 52 pages. I have also set 1 as default in case someone gives page as 0 which will lead to error.

What the OFFSET code “number_per_page._parameter_value | times: page._parameter_value | minus: number_per_page._parameter_value” means?

This is same as

(number_per_page._parameter_value*( page._parameter_value))-number_per_page._parameter_value = (5000 * page._parameter_value) — 5000

The offset calculation goes as below

NUMBER PER PAGE * PAGE — NUMBER PER PAGE

The number per page is fix which is 5000, offset value will be calculated as below

• Page 1

5000 * 1–5000 = 0 ( first 5000 rows will be displayed)

• Page 2

5000 * 2–5000 = 5000 ( 5000 to 10000 rows will be displayed)

• Page 3

5000 * 3–5000 = 10000 ( 10000 to 15000 rows will be displayed)

This way I was able to provide pagination option to business users and I hope this will also help a lot of you for your use case:-)

I did follow this post in looker community for pagination https://community.looker.com/lookml-5/pagination-in-looker-15168. With some changes, I was able to achieve this. Thanks a lot to looker support team.

Happy Learning:-)