An Explore is where you build and run data queries in Looker by filtering, selecting, and pivoting Dimensions and Measures. The following definitions break it down a little further:
- Query: question using data, displayed in Looker as charts/tables and visualizations
- Dimension: a qualifying field of a query. These are characteristics of the data that will be displayed in your query (examples: Region, Industry)
- Measure: a quantitative field of a query. These are the data points that will be displayed in your query (example: Total Employment, Total Value Added, Kilograms of Greenhouse Gases)
There is a designated area for Explore in Looker, but there are several other access points. When adding a query tile from within a Dashboard, you’ll even see an Explore appear as a pop-up window.
EXPLORE: STARTING FROM SCRATCH
Start building queries from scratch by navigating to a Data Library Section from the Explore dropdown menu.
EXPLORE FROM DASHBOARDS AND LOOKS
An Explore can also be started from an existing Look or a tile of an existing Dashboard. Simply click on the Table Menu (three dots) on any given Dashboard tile choose “Explore From Here”.
Click “Explore From Here” at the top of the page to Explore from a Look.
Define data queries within each of the Data Asset Models listed below. Save query results as Looks or to a Dashboard.
- Commodity Data
- Core Competency Data
- Deflator Data
- Demographic Data
- Employment & Wages by NAICS (Imputed CEW Data)
- Environmental Data
- Industry Data
- Occupation Data
- Tax Data
- Trade Flow Data
Each Data Asset has Dimension and Measure options.
- A Dimension is a qualifying field of a data query or report. These are characteristics of the data that will be displayed in your query (examples: Region, Industry).
- A Measure is a quantitative field of a data query or report. These are the data points that will be displayed in your query (example: Total Employment, Total Value Added, Kilograms of Greenhouse Gases).
Filtering by a Dimension or Measure limits the data displayed in your query based on the rule(s) set.
Clicking on, or selecting, a Dimension or a Measure will add the selection as a column in the data query. Click “Run” in the upper right corner of the screen to populate and update your query after making changes in any of the fields. Increasing the Dimension and Measures in a data query will increase the expected load time. Load times for data queries may be longer in certain Data Library Sections than others, such as Trade Flow Data and Core Competency Data due to the amount of data contained in these sections.
In certain Data Library Sections Filter-Only Fields is a third field in addition to Dimensions and Measures.
The possible Filter-Only Fields include:
- Aggregation Scheme Param: choose to view data at the most granular level, or view data at a more summarized level by choosing to group Industry/Commodity Dimensions by 2 Digit or 3 Digit NAICS
- Dollar Type: Compare data in like dollar years by choosing “Real”. Unadjusted dollar values can be observed by choosing “Nominal”
- Region Type: Choose the level of regionality of data to include in query.
- Field Picker: The Field Picker allows you to pick a Measure from a dropdown menu. In order to include and/or Filter by the Measure selected in the Field Picker you’ll need to use the Measure titled “Metric” to do so.
FILTER DIMENSIONS & MEASURES
Applying Filters to the appropriate Dimensions is critical to ensure unintended data is not included in your data query. For example, when no filter, selection, or pivot is applied to Data Year, then the selected Measures displayed would be the summed across all Data Years in the model. Filter by a single year to view the Measure for just the desired year.
Filtering by a Measure is less common, but can be helpful when setting a threshold on a Measure is appropriate. For example, if I only wanted to see all Industries that employ over 100 people I would filter my employment Measure and choose “greater than” in the Filter window from the dropdown menu and type 100 in the field and click run.
SELECT DIMENSIONS & MEASURES
Selected Dimensions break out Measures by rows. For example, if State is the selected Dimension and Total Value Added is the selected Measure, hitting run will produce a table of 51 rows (one for each State and DC), and two columns one for State name and one for Total Value Added. This table displays Value Added by State. Clicking on the Value Added column heading would sort the table for highest to lowest (carrot in column heading point down). Clicking a second time would sort from lowest to highest (carrot in column heading point up). This is just one simple data query that can be produced using the Industry Data. Selecting an additional Measure would add another column of data points. Selecting Total Employment would add a column displaying Total Employment by State in addition to Total Value Added.
If the Data Year Dimension was selected instead of filtered, then Data Year would be included as a column in the table and Measures would be further broken out by year. Selecting Data Year in the Value Added by State query example would add a Data Year column, and the table would then include a row for each State and Year combination. There would be 19 rows for each State, one for each Year of data available, generating a total of 969 rows (51 x 19). By default the data query has a row limit of 500, so when that limit has been reached, the following message will display, “Row limit reached. Results may be incomplete”. In the data window you can update the Row limit value to up to 5,000. When downloading data, there is a row limit of 100,000 (Click the Gear option in the upper right corner, choose Download, then select Limit option “All Results”). There can be a maximum of 200 columns.
Sort by multiple columns (sort by one column then another just like in Excel) by sorting a column, then holding Shift + sorting the next desired column. In the GDP by State and Year example, the table could be sorted by State then Year, Year then State, or even Year and then Value Added to view States ranked by GDP each Year, and so on.
WHEN TO FILTER & SELECT
In some cases you may want to filter and select a given Dimension, particularly if you are Filtering by several Dimensions. For example, if my research is focused on just NC, SC, and GA, I could filter for these states. Applying this filter without selecting the Dimension would cause each Measure to be a sum for the three states (showing just one data point for each Year). Filtering and selecting on State would include a State column and produce rows for each of the three States.
Pivoting a Dimension will convert the options of the given Dimension into columns, transposing the data. For example, pivoting by Data Set Year will create a column for each Data Set Year so data can be compared over time side by side instead of row by row. With this setup in my Value Added by State example, I could compare Value Added over time in each State row. Alternatively, pivoting on State would allow me to compare it across regions in each Year row.
VISUALIZATIONS & OTHER OPTIONS
Query results will appear in two windows under the Filter window, a Visualization window and a Data window. Updates like sorting in the Data window will affect the Data displayed in the Visualization window. Check the Totals box if you’d like to add a column for each row total. Check the Columns Totals box if you’d like to add a row at the bottom of the table for each column.
The Calculations button allows you to add calculations into your query, such as calculating the difference between columns.
Find more information from Looker:
- Exploring Data in Looker
- Using Table Calculations
- Creating Visualizations and Graphs
- Visualizations Types
Written October 20, 2020