Exploring data in Looker

Stay organized with collections Save and categorize content based on your preferences.

This page introduces you to data exploration with Looker. After you read this page, you should understand where to start pulling data in Looker, how to modify a report to see more detail, and how to drill down to gain deeper insights.

Explores are the starting point for exploration

In this example, you operate an e-commerce store. The Explore menu presents a number of Explores for looking at your e-commerce store data. An Explore is a starting point for a query, designed to explore a particular subject area. Select the Explore option from the navigation panel to open the Explore menu:

From the Explore menu, you can search for, select, and view an Explore:

In the A Ecommerce store model, there are Explores for order items (the products associated with an order), orders (purchase events), products (information about inventory products), and users (individuals associated with purchase events). For example, when you have questions about items associated with an order, you probably want to start exploring from the Order Items Explore.

The data shown in an Explore is determined by the dimensions and measures you select from the field picker at the left. A dimension can be thought of as a group or bucket of data. A measure is information about that bucket of data. In Looker, dimensions appear as blue columns and measures appear as orange columns in your data table.

The following query displays the number of orders per day by querying the Order Items Explore and displaying one dimension (ORDERS Created Date) and one measure (ORDERS Count).

In this case, all order records have been grouped together by day (the dimension). Then you've asked for a count of orders (the measure) for each of those days.

If an Explore contains modeled queries, you can use Quick Start analyses to populate fields. The next section provides an in-depth overview of Quick Start analyses and how to use them as a starting point for exploring data.

Quick Start analyses

Modeled queries are available as Quick Start analysis options in Explores. Quick Start analyses provide a helpful starting point for quickly running and building analyses:

Each Quick Start analysis option will display the name of the analysis and, when available, a description.

For more information on how developers can model prebuilt analyses for users, see the query parameter documentation page.

Choosing a Quick Start option from a blank Explore

To run a Quick Start analysis, click the analysis that you want to see. The analysis will automatically run and display results, including the visualization.

You can modify a Quick Start analysis once it has run by adding or removing fields from the All Fields tab, from Search results, or from the In Use tab in the field picker.

Choosing a Quick Start option once an Explore has run

Once an Explore has finished running, you can select a new Quick Start analysis by clicking the lightning bolt icon next to the Explore name:

This launches the Quick Start menu:

Selecting a Quick Start analysis from the menu runs the analysis and replaces all the previous Explore results except the existing filters.

Quick Start filter behavior

Filters are additive. This means that, when run, Quick Start analyses will include any existing Explore filters. If a selected Quick Start analysis has a filter value that conflicts with an existing Explore filter, you will be prompted to select which filter value to use in the analysis.

In the following example, an Explore includes the filters Orders Created Date is in the year "2019", Orders Status is equal to "complete", and Users State is equal to "Washington":

You click the lightning bolt icon to launch the Quick Start modal and select a new Quick Start analysis to run:

The selected Quick Start analysis has a conflicting filter value for the Users State filter, and you're prompted to resolve the conflict:

To resolve the conflict:

  1. Choose an option.
    • Select Keep current filters to run the new analysis with the existing filter value (Users State is equal to "Washington" in this case).
    • Select Replace with new filters to run the new analysis with its prebuilt filter condition (Users State is equal to "California" in this case).
  2. Click Apply to confirm the selection and run the analysis.

The Explore runs with the updated Users State is equal to "California" filter condition, and it includes any existing non-conflicting filters (Orders Created Date is in the year "2019" and Orders Status is equal to "complete"):

Adding more dimensions for more detail

Whether you added fields to your Explore manually or by selecting a Quick Start option, you can add more dimensions to learn more about your data.

To add a field:

  1. Click a field from the field picker to add it to the query.
  2. Click Run to re-run the query.

Adding the Status dimension splits the counts between order statuses and displays the number of orders that are complete, pending, or canceled.

Sorting data

Some sorting in Explores is performed on the client side (in the user's browser) to reduce the number of round-trip calls to databases, which can be both costly and time consuming. However, this behavior can lead to inconsistencies between Explore results and other Looker content, as sorting between client and database can produce different results — especially if system locales differ.

Unpivoted data on the Explore page is sorted by default according to the following prioritization:

  1. The first date dimension, descending
  2. If no date dimension exists, the first measure, descending
  3. If no measure exists, the first added dimension, ascending

For information on sorting pivoted data, see the Pivots and sorting section.

A field's sort order is also indicated by a number that distinguishes its sort-by order as compared to other fields, by an arrow next to the field name that shows the sorting direction (ascending or descending), and by a pop-up that appears when you hover over a field name. You may want to sort data differently than the default order.

For example, let's see which date has the most orders from returning customers (in other words, customers who are not making their first purchases). Clicking on the Order Items Count column header will sort from highest to lowest. The downward arrow next to Order Items Count indicates that the results are sorted by this field, in descending order. A pop-up that appears when you hover over a field name also confirms the sort order:

You can sort by multiple columns by holding down the Shift key and then clicking on the column headers in the order that you want them sorted:

In the previous example, the arrows next to Orders Created Date and Order Items Count indicate that the table is sorted by both fields, and the order by which the table is sorted. Orders Created Date is the second order-by field (descending), as indicated by the downward-pointing arrow and 2 next to the field name.

Note that if you reach a row limit, you will not be able to sort row totals or table calculations.

You can also create custom sorting using the case parameter.

Pivoting dimensions

Multiple dimensions are often easier to look at when you pivot one of the dimensions horizontally. Each value in the dimension will become a column in your Look. This makes the information easier to consume visually, and reduces the need to scroll down to find data. Looker supports up to 200 pivoted values.

To pivot Explore results by a dimension:

  1. Hover over the dimension in the field picker and click the pivot icon.
  2. Click Run to re-run the query.
  3. To unpivot results, click the field's gear icon and select the Unpivot option, or click the dimension's pivot icon again in the field picker.

Pivots and nulls

A row of data whose value would not appear in a column is indicated with the null value symbol, a zero with a slash across it. For example, on December 21st, there were no completed orders:

Pivots and sorting

You can also sort pivoted dimensions by clicking the title of the dimension. To sort by multiple pivoted dimensions, hold down the Shift key and then click on the dimension titles in the order that you want them sorted. When you're sorting a pivoted measure, any rows with values in that column are sorted first, followed by rows without data in that column (indicated by the null value symbol).

You can also create custom sorting using the case parameter.

Reordering columns

You can reorder columns in the Data section by clicking on a column header and moving the column to its desired position. The Explore's visualization will reflect the new column order after you click Run.

Columns are organized in the Data section by field type:

  1. Dimensions
  2. Dimension table calculations
  3. Measures
  4. Measure table calculations
  5. Row totals

For the most part, columns can be reordered within each field type but cannot be moved out of their field type section.

For example, dimension table calculations can be rearranged among themselves, but you cannot place a dimension table calculation in between two measures.

One exception, however, is that you can use the arrow next to the row totals checkbox on the Data tab to move the row total column from the far right of the data table to just after the dimension table calculations.

Columns under a pivoted dimension can be reordered, but the order of pivoted dimensions can be changed only by changing the sort order, not by manual reordering.

Removing fields

To remove a field from an Explore:

  1. Click the selected field in the field picker or click Remove from the column's gear menu.
  2. Click Run to re-run the query.

You can also remove all fields in an Explore using the keyboard shortcuts Command-K (Mac) or Ctrl+K (Windows).

Field picker

The field picker includes the following elements:

  1. Explore name — Displays the name of the current Explore. A lightning bolt icon will also appear for Explores that have modeled queries, allowing you to access Quick Start analysis options after an Explore has run.
  2. Search bar — Displays the search bar.
  3. All Fields tab — Displays all available fields for an Explore.
  4. In Use tab — Displays all Explore fields that are currently in use.
  5. View level summary — Displays the total number of selected fields from a view. This number is shown when the view is collapsed, and when it is expanded.
  6. Field-specific information and actions — Displays a field's current and potential functions in an Explore, as well as more details about a field.

  7. Explore summary — Displays the total number of fields in an Explore (including custom fields and table calculations when permissions allow) in the bottom left corner, and the Go to LookML link in the bottom right. Go to LookML directs users to the explore definition in its LookML project. This link is visible only to users with the see_lookml permission.

Field-specific information and actions

The icons next to each field provide more information about the field and indicate the available options for that field. The icons are visible when you hover a cursor over a field:

These icons appear on the All Fields and In Use tabs, and the Pivot and Filter icons appear in the Popular Fields search bar drop-down if your admin has enabled the Popular fields in Explore search Legacy feature.

Click an icon to filter or pivot by the field, to provide more field information, or — when permissions allow — to create a custom field that is based on the field:

  1. Pivot icon — Click this icon to pivot or unpivot a field in an Explore. This icon will appear gray when a field is not pivoted and bold when a field is pivoted.
  2. Filter icon — Click this icon to add a field as an Explore filter or to remove a field as a filter. This icon will appear gray when a field is not a filter and bold when it is an active filter.

  3. Information icon — Click this icon to see more detail about a field:

    • All users will see the field's data type, description (when available), and LookML field name (in view_name.field_name syntax):
    • Users with the see_lookml permission will see the definition of the LookML field's sql parameter, as well as an option to navigate to that field in the LookML project:

  4. Three-dot Options menu — The three-dot Options menu is available only if the Custom Fields Labs feature is enabled and if the user has the create_custom_fields permission. The only exception is for dimension groups in the In Use tab.

    Users with the create_custom_fields permission can click the three-dot Options menu to quickly create custom fields depending on a field's type. For example, the following three-dot Options menu shows the custom field options that are available for the Profit dimension, which is a number data type:

All Fields tab

When you open an existing Explore, the All Fields tab is displayed by default. This tab is the starting place for building an Explore and displays all the available fields that you can select for a query. Similarly to the classic Explore field picker, fields are organized alphanumerically by type (dimensions, followed by measures) under the name of the view or view label in which they are defined. Each field will show field-specific information and actions, such as a field's current and potential functions in an Explore:

Selected fields will appear highlighted in the corresponding field type color (blue for dimensions, orange for measures, green for table calculations), and corresponding field icons (pivot, filter) will appear in bold without you needing to hover your cursor over a field when it is active. For example, the field Profit in the field picker shown here is highlighted in dimension blue, indicating that it is selected. You can tell that this field is not pivoted or filtered because all corresponding field icons are not bold and do not appear when you aren't hovering over the field.

Click a field from the All Fields tab to add it to or remove it from an Explore query. Additionally, you can click the appropriate icon to filter, pivot, or perform other field-specific actions from the All Fields tab.

If the Custom Fields Labs feature is enabled, custom fields and table calculations are listed under Custom Fields; and users with the create_table_calculations permission can create and edit table calculations, and users with the create_custom_fields can create and edit custom fields by clicking the Add button or by choosing a custom field option from a field's three-dot Options menu. Users must have the create_table_calculations or create_custom_fields permission to see the Custom Fields view label in an Explore with no existing table calculations or custom fields, and must have the create_custom_fields permission to see the three-dot Options menu. The only exception is for dimension groups in the In Use tab.

In Use tab

The In Use Tab shows all fields that are currently active in an Explore, organized alphanumerically by view or view label, and whether they are dimensions or measures:

The In Use tab also displays an updated Explore summary at the bottom of the tab. The bottom left corner displays the total number of active fields in an Explore. A Go to LookML link is available in the bottom right to users with the see_lookml permission. Go to LookML directs users to the explore definition in its LookML project. The preceding example shows that there are currently four total active fields in the Explore.

Removing fields from the In Use tab

When a field is in use, you can remove it from an Explore by clicking the field's name.

You can also remove all selected fields (including custom fields and table calculations) by clicking Clear all, or you can remove all fields (including custom fields and table calculations), except those that are active filters, by clicking Clear fields, keep filters. Neither of these options will remove Custom filters; to remove a custom filter you need to manually click the checkbox next to the filter.

Alternatively, you can choose to filter, pivot, or perform other field-specific actions from the In Use tab by clicking the appropriate field icon.

In Use tab field-specific icons and actions

The icons next to each field indicate the field's current and potential functions in an Explore query. For example, the field Created Date is currently filtered, as indicated by the active bold Filters icon.

If the Custom Fields Labs feature is enabled, custom fields and table calculations are listed under Custom Fields when used in an Explore; and users with the create_table_calculations permission can create and edit table calculations and users with the create_custom_fields can create and edit custom fields by clicking the Add button or by choosing a custom field option from a field's three-dot Options menu. Users must have the create_table_calculations or create_custom_fields permission to see the Custom Fields view label in an Explore with no existing table calculations or custom fields, and must have the [create_custom_fields] permission to see the three-dot Options menu. The only exception is for dimension groups in the In Use tab.

When a dimension group is active in an Explore's data table, users can use the three-dot Options menu to replace a selected timeframe with another, if available, without having to manually deselect one field and select another field:

When you select a new timeframe from the Switch To list, the Explore automatically reruns with updated results. When you're using the Switch To function, only timeframes in the Explore data table, not filtered timeframes, will be replaced.

The search function empowers you to quickly select the specific fields you need to build Explores. There are several ways to select fields from a search:

  1. By entering a search term in the search bar
  2. By entering a search modifier, or combination of search modifiers and search terms, in the search bar

If the Popular fields in Explore search Legacy feature is enabled, users can also select a field from the Popular Fields drop-down.

Starting in Looker 22.0, the Popular Fields drop-down is unavailable unless an admin has enabled the Popular fields in Explore search Legacy feature for your Looker instance.

Clicking in a blank search bar displays a list of Popular Fields to choose from. Popular Fields are fields that are most commonly selected by users in an Explore and can be helpful when you're creating an Explore from scratch.

The Popular Fields drop-down list displays the following:

  • A field's name
  • An icon depicting a field's data type
    • If a field is currently active in an Explore, a dot will appear next to the field name, instead of the data type icon. In the preceding example, Orders Count and Order Items Cost are currently active in the Explore.
  • A field's description (when available)
  • A field's view or view label

Hovering over a field in the search results reveals icons that indicate the field's current and potential Explore functions. When a field is currently active in one of the three following ways, the corresponding icon will appear bold. The icon will appear not bold when the field is inactive. For example, the selected field in the search results above, Profit, is inactive in the Explore — for this reason, all three icons appear not bold.

  • Clicking the + icon adds a field to the Explore results table. If a field is already in an Explore results table, the gray inactive + will be replaced by an active bold x. Click the active bold x to remove the field from the Explore.

  • Clicking the double-arrow icon adds a field to an Explore table as a pivot. If a field is currently pivoted, the field can be unpivoted by clicking the active bold double-arrow icon, or by clicking the active bold x icon to remove the field from the Explore table entirely.

  • Clicking the funnel icon adds a field as an Explore filter. If a field is an active filter, it can be removed by clicking the active bold Filters icon.

Entering a string in the search bar will filter the field picker to display only the fields, views, and fields with descriptions that match all or part of a search string.

To perform a search, begin by entering a term. In this example, you're searching for items in the field picker that match the term cost:

The search term match is underlined in each item, including field descriptions. For example, in the preceding search, Profit is included in the filtered field picker results, because part of the description matches the search term:

The filtered field picker features the same functionality as described in the All Fields tab section.

If you want to narrow down the list of fields in the field picker, you can perform a modified search using is, type, has, and tags or tag. Modified searches are useful if you want to select from a result of field types, such as dimension or measure, LookML data types, such as type: string or type: number, or fields that have a specific element, such as a description.

Modifiers can be combined with other modifiers and search terms in searches. For example, the following search narrows the displayed field picker fields to those that are dimensions with descriptions:

is:dimension has:description

To perform a modified search, input a modifier into the search bar. Supported modifiers include:

is — Identifies a field type:

  • is:dimension
  • is:measure
  • is:filter
  • is:parameter

type — Identifies fields with a specific LookML data type:

  • type:distance
  • type:duration
  • type:location
  • type:number
  • type:string
  • type:tier
  • type:time
  • type:yesno
  • type:zipcode
  • type:average_distinct
  • type:count_distinct
  • type:list
  • type:max
  • type:median_distinct
  • type:min
  • type:percent_of_previous
  • type:percent_of_total
  • type:percentile_distinct
  • type:running_total
  • type:sum_distinct
  • type:date_date
  • type:date_raw
  • type:date_time_of_day
  • type:date_hour
  • type:date_hour_of_day
  • type:date_hourX
  • type:date_minute
  • type:date_minuteX
  • type:date_second
  • type:date_millisecond
  • type:date_millisecondX
  • type:date_microsecond
  • type:date_week
  • type:date_day_of_week
  • type:date_day_of_week_index
  • type:date_month
  • type:date_month_num
  • type:date_month_name
  • type:date_day_of_month
  • type:date_fiscal_month_num
  • type:date_quarter
  • type:date_quarter_of_year
  • type:date_fiscal_quarter
  • type:date_fiscal_quarter_of_year
  • type:date_year
  • type:date_day_of_year
  • type:date_week_of_year
  • type:date_fiscal_year
  • type:date
  • type:sum
  • type:percentile
  • type:median
  • type:count
  • type:average
  • type:date_time
  • type:date_time_of_day
  • type:date_hour
  • type:date_hour_of_day
  • type:date_hourX
  • type:date_minute
  • type:date_minuteX
  • type:date_second
  • type:date_millisecond
  • type:date_millisecondX
  • type:date_microsecond

has — Identifies fields that have a specific element:

  • has:description

tags or tag — Identifies fields with a LookML tag:

  • tag:braze_id
  • tags:braze_id
  • tag:email
  • tags:email

Displaying totals

Sometimes a summary of your data is useful. You can add column totals to your report by clicking the Totals checkbox in the upper right and then running the report:

If your report contains more than one dimension, you can choose to include Subtotals in your table visualization:

You can also add row totals to your report, but only if you've added a pivot to your report:

If you've added row totals, and your query exceeds any row limit that you've set, you will not be able to sort the Row Totals column (although you can sort dimension and measure columns as normal). This is because you might be missing rows in your data that should be included in your totals. If you run into this issue, you can try increasing your row limit (up to 5,000 rows).

When totals aren't available

There are some cases when totals won't be available:

  • Column totals are available only for measures and table calculations that exclusively reference measures, not for dimensions or table calculations that reference dimensions.
  • Row totals are available only for measures, not for table calculations that are based on dimensions or dimensions.
  • Certain types of columns won't be totaled, because of database limitations or because the value would not make sense as a total. For example, you can't add together a list of words.

Things to consider with totals

Additionally, there are some things to keep in mind about how totals work in certain situations:

  • Columns that count unique items might not add up as you expect, since the same item might show up in several categories but be counted as only one unique item in the totals.
  • Some table calculations that perform aggregations, such as calculations that use percentile or median, might not add up as you expect. This is because table calculations calculate totals using the values in the Total row, not using the values in the data column. See the Display potentially confusing table calculation totals as nulls Help Center article for troubleshooting tips.
  • If you've filtered your report by a measure, totals may appear to be too high. However, in actuality, what you're seeing is a total for your data before the measure filter is applied. In other words, the measure filter may be hiding some data from your report, even though that data is included in the total.
  • If you've used totals with merged results, Looker calculates totals on each of the component queries and uses those totals in the merged result. Therefore, totals may appear too high, because what you are seeing are totals calculated before the results were merged. One way to avoid this is to align the filters on each query.
  • Similarly, if you've placed row or column limits on your report, and your report exceeds that limit, totals may also appear to be too high. However, what you're seeing is a total for your data before the limits are applied. In other words, the limits may be hiding some data from your report, even though that data is included in the total.

In the situations described in the third and fourth bullets, above, it is possible to calculate totals only for the data you can see. To do so, you'll need to use a table calculation, explained later on this page. For a column total, use sum(${view_name.field_name}). For a row total, use sum(pivot_row(${view_name.field_name})).

For information about displaying subtotals in table visualizations, see the Table chart options documentation page.

Drilling down into the data

Every query result is the starting point for another query. Clicking on any data point will drill down, creating another query refined by the data point you clicked. In the following example, December 21, 2019 has had 39 orders. Clicking on the count of 39 shows you details about those specific records.

Drilling deeper ...

In the drill overlay, you can see all the orders placed on August 2, 2017. From here, you can:

  • Click the Explore from Here button to open an Explore that uses the fields in the drill overlay as a starting point.
  • Click the Download Results button to download the data using the same options as shown on the Downloading content documentation page.
  • Click on the drillable Order Items field for an individual customer, William D., to see all the items in their order.

And deeper still ...

If you click on the Order Items field for William D., you see a list of all the order items in William D.'s order.

Of course, this isn't the end of the road. Like any query in Looker, the results are linked so you can keep drilling, exploring, and arriving at new insights.

Drilling into dashboards

If your Looker admin has enabled the Dashboards in Drill Menus Labs feature, dashboards will appear in the drill menu if they have a filter on the field you are drilling into.

For example, suppose you have a dashboard called Business Overview that has a global filter on the field State. In this example Explore, the Business Overview dashboard appears as an option in the dress menu for the State field, since the State field is used as a filter on that dashboard:

A single dashboard is listed in the Dashboards section of the drill menu for the State field.

When you select the name of the dashboard in the drill menu, Looker takes you to the Business Overview dashboard, with the filter for the dashboard set to the value for the State field that you chose.

For more information about how filters on dashboards are assigned to fields, see the Adding and editing user-defined dashboard filters documentation page.

Copying values

Looker makes it easy to copy all the data from a table column. To do so, hover over a column label, click the gear icon, and then choose Copy Values:

This data can then be pasted into a document or a tool like Excel.

The presence of links or actions is indicated by an ellipsis (…) following the value in a column.

Your Looker developers may have added clickable links to your data:

When you click on the data in the field, Looker provides an option to open the destination of the link. In the preceding example, the developers added a link to the State column. When you click on a state value, Looker provides an option to perform a Google search for that state's name.

Using data actions

Your Looker developers may have added data actions to the dimensions or measures in your data. With data actions, you can perform tasks with other tools directly from Looker, such as sending an email or setting values in other applications. These data actions appear in the drill menu under an Actions heading:

In the preceding example, the Phone field has a link to the Twilio service. When you click the phone number and select the Twilio action, Twilio prompts you to enter a message. Twilio then sends that message to the phone number.

Cost estimates for Explore queries

For BigQuery, MySQL, Amazon RDS for MySQL, Snowflake, Amazon Redshift, Amazon Aurora, PostgreSQL, Cloud SQL for PostgreSQL, and Microsoft Azure PostgreSQL connections, the Explore page provides an estimate of the cost of the query. Select one or more fields from the field picker and refrain from running the query immediately. The Explore page will calculate the amount of data that the query will require and display the information near the Run button:

For BigQuery, MySQL, and Amazon RDS for MySQL connections, cost estimates are always enabled. For Snowflake, Amazon Redshift, Amazon Aurora, PostgreSQL, Cloud SQL for PostgreSQL, and Microsoft Azure PostgreSQL database connections, you must enable the Cost Estimate option for the connection. You can enable Cost Estimate when you create the connection. For existing connections, you can edit the connection from the Connections page in the Database section of Looker's Admin panel.

The Explore page displays different information depending on the query:

  • For new queries on the database, the Explore page displays the number of bytes that will be processed.
  • For queries that can be pulled from the cache, the Explore page displays the number of rows that will be pulled from the cache.
  • For queries that use aggregate awareness optimization, the Explore page displays the number of bytes that will be processed and the number of bytes that will be saved by using aggregate awareness.

The calculation of cost estimates is dialect specific. Use Looker's EXPLAIN function to see how a query is processed by your database.

Features for developers

Depending on your permissions, you may see several features designed for Looker developers in the Explore field picker:

  • The Go to LookML option on the All Fields and In Use tabs lets developers navigate to the Explore's LookML.
  • The sql parameter definition in a field's information icon menu lets developers see a field's sql definition without navigating to the field's LookML.
  • The Go to LookML option in a field's information icon menu lets developers see the field's LookML.

You may also see several features in the Explore's data table and gear menus:

  1. The Go to LookML option in a field's gear menu lets Looker developers see the field's LookML.
  2. The SQL tab in the Data section lets Looker developers see the query that Looker sends to the database to get the data.
  3. The Get LookML option in the Explore gear menu lets developers copy LookML for the Explore's query, which can be used to add a tile to LookML dashboards, to improve query performance with aggregate tables, or to define native derived tables:

Conclusion

Now that you know how powerful the Looker Explore page is for building queries, displaying results, and discovering insights through iterative searches, you might want to limit your results to just the data you're interested in.