Explore
Explore functionality is in beta.
Explore allows anyone to ask and answer questions from data without needing code. Explorations use a drag-and-drop interface that allows users to quickly visualize their data in charts, pivots, and tables. Explorations also provide methods to group and aggregate data, create spreadsheet calculations, filter data, and join tables together.
Explore shows up in three places in Hex:
- Exploring from a published app
- Exploring directly on a database table
- Explore cell within a notebook
- Explore cells are available for everyone.
- Exploring from a published app or directly on a data connection is available on Teams and Enterprise plans.
- Users need an Explorer or Editor role or higher to use explore features.
- Users need Can explore permissions or higher on a published app in order to explore from it.
Exploring data from Dremio is not supported on explore cells.
Where to use Explore
To explore from a published app, hover over a chart cell, table, or pivot table, and click Explore. The data source of the exploration will be the dataframe or database table that the project creator chose as the source of that cell.
To explore directly on a data connection, there are many entry points:
- From the + New project button in the home page, use the dropdown to select New exploration. From here, a database table can be selected as the data source.
- From the Explorations tab in the home page, click New exploration in the upper right. From here, a database table can be selected as the data source.
- From the home page, use the 3-dot menu from a database table in the “Recent data” section and click Explore. This will create an exploration with the table as its data source.
- Access the Data Browser via the Data tab on the left side of the home page and use it to find your desired table. From the table preview, click Explore. This will create an exploration with the table as its data source.
To create an Explore cell in a notebook, use the More button in the add cell bar and click Explore. Within the Explore cell, you can select any dataframe or database table as the data source.
Exploration functionality
Within an exploration, you can choose to view a visualization, a table, or a split screen displaying both options. Toggle between these choices at the top of the exploration.
The table and visualization always reflect the same data, with the table providing a tabular representation of the chart's contents. They will remain in sync as you make changes. To change the visualization type, click the chart type dropdown when the toggle is set to show a visualization.
Users can drag and drop columns from the field picker into the drop zones of the chart in order to render a visualization. Alternatively, users can click on the pills above the chart in order to assign columns to different chart configs (like X-Axis and Y-axis), or use the + from fields in the field picker to assign to chart configs. Click Show options in the upper right to configure more advanced chart details.
To create a pivot table, click Pivot Table from the chart type dropdown in the upper left of your exploration. Drag and drop columns from the field picker into the drop zones of the pivot in order to add fields as rows, columns, or values. It's also possible to click on the pills in the top bar above the pivot in order to assign the pivot configs, or use the + from the fields in the field picker.
Actions like formatting, selecting time units, and setting scale types can be performed by clicking on the headers of fields in the table, or by clicking on the configuration pills above the chart or table.
To filter, use the Filters section in the upper left. Alternatively, click any place where the column appears (the field picker, column headers in table, or pills above the chart or table) to pull up the column configuration menu, and click Filter... to pull up the filter config.
To group or aggregate data directly in the table, drag a column towards the table to see the Group by and Aggregation drop zones. Alternatively, click the empty Group by and Aggregation pills above a table to configure.
Tables have detail columns, which do not impact your visualization but do allow you to see the raw rows that make up the tabular data. These detail columns can be especially helpful when you want to understand the row-level data that makes up the values represented in a chart. To add or remove detail columns, click the pill for Edit detail columns above the table, or drag columns into the Detail table drop zone from the field picker. To collapse or expand the detail columns, use the Expand and Collapse buttons above the table.
Joins
Joins are currently only supported for explorations that use a database table as their data source.
To join data from another database table, click the + in the upper right of the field picker, or the + Join data button at the bottom of the field picker. This will bring up the Data Browser, from which a target table can be selected.
In the modal, select the columns to perform the join on, and the unique key columns for each table. Hex will make a best guess at what the joining and unique columns should be, but will display a warning if no columns can be detected or if an issue is detected with your selected columns.
After Join table is clicked, Hex will perform a left join from the base table to the joining table. The columns from the joining table will appear in the field picker of the exploration.
Once a table is selected, configure which columns to join on and the unique key columns for each table. Hex will make a best guess at what the joining and unique columns should be, but will display a warning if no columns can be detected or if there appear to be any errors with your selected columns. After Join table is clicked, Hex will perform a left join from the base table to the joining table.
Hex will detect duplicate rows that may arise as a result of the join. Using the unique keys specified, Hex can accurately calculate aggregations on the base and joining tables of one-to-one, many-to-one, one-to-many, and many-to-many joins.
If there is a mistake in the join logic, Hex will flag a warning in the join config. If you’d like to proceed with the join, acknowledging that the join may produce inaccurate results, click the down arrow from the greyed out Join table to force join.
Calculations
To add a calculation, click the + next to Calculations on the left of the exploration. See more information about how to define calculations and their supported syntax here. One caveat is that calculations within explorations currently do not support aggregates - calculations within explorations must be scalar. Once a calculation has been defined, it can be added to a visualization or table, like any other column.
If your exploration has a join configured, calculations in the exploration are currently only able to reference columns in the base table.
Exploration access
Users with Editor roles (or higher) can use explorations. Users must have access to a data connection in order to create an exploration that uses the data connection; this includes explorations directly on a table from the data connection, or explorations on a cell in a published app when the cell has an upstream dependency on the data connection.
To explore from a published app, users must have Can explore permissions (or higher) to the project.
If an exploration link is shared with another user, the recipient must have the appropriate role, data connection access, and app permissions (if applicable) listed above in order to view the exploration.
Bookmarking and sharing
Explorations cannot be saved or published, but they can be bookmarked. You can think of bookmarking explorations in the same way you would bookmark a URL in your browser; clicking on a browser bookmark takes you to a website URL. Bookmarking makes it possible to revisit an exploration in the future. You can’t share a browser bookmark with a friend, but you can send them the website URL. Bookmarks work the same way!
To bookmark an exploration, click the bookmark icon in the upper right. This will prompt you to name your bookmark.
To access all your bookmarked explorations, visit the Explorations tab from the homescreen. If you’ve bookmarked an exploration that stems from a cell in a published app, you can also access that bookmark from the Explore button on that cell.
To share an exploration, simply copy the exploration URL (from your browser or the link icon in the upper right) and send it to the intended recipient. If the recipient has access, they will be able to view it. The exploration does not need to be bookmarked to be shared.
When an exploration (bookmarked or not) link is shared, the exploration is not a collaborative document in the same way that a notebook is. The behavior differs slightly depending on how the link is shared:
- When User A copies an exploration link via the link icon in the upper right and sends it to User B, the link will contain the exact state at the time that the link was copied. Any changes made by User A in the time between copying the link and User B opening the link will not be reflected in the shared exploration. No additional changes made by either User A or User B will be reflected in either user's browser.
- When User A copies an exploration link via the browser URL and sends it to User B, the latest version of the exploration (including any changes that have been made by User A since the link was copied) will be rebuilt in User B's browser. Any changes made by User B will not be reflected in User A's browser. Once the link has been opened by User B, any additional changes made by User A will not be reflected in User A's browser.
Recent explorations
If you forgot to bookmark an exploration and want to pick your work back up where you left off, you can check the Recents tab of the Explorations page to find recently-created, unbookmarked explorations. If you created an exploration and closed your browser tab or navigated away from the page without bookmarking, that exploration state will be stored under Recents. You can think of Recents as your browsing history. Recent explorations are only retained for 14 days, so if the insight is one you'd like to revisit reliably, consider bookmarking the exploration.
Converting to a project
To convert an exploration to a project, click Convert to project in the upper right.
If the exploration was created by exploring from a published app, all necessary upstream cells from the app will be copied into the new project. The final cell in the project will be an Explore cell that holds the exploration you configured prior to converting to a project. If the exploration was created directly on a database table, converting to a project will create a new project with a single Explore cell that holds the exploration you configured prior to converting to a project.
Converting an exploration to a project allows you to do many things:
- Add multiple explorations (or other cells)
- Feed arbitrary dataframes into the exploration
- Publish an app
- Use the more robust version history and permissions systems that come with a project
Explore cell
The exploration interface can be brought into a notebook environment with an Explore cell. From the More menu in the add cell bar in a notebook, click Explore. This will create a cell that behaves in the exact same way a stand-alone exploration does. The Explore cell can accept dataframes or database tables as an input. If an Explore cell is added to the app, the visualization, rather than the table, is what will be displayed in the app. From the perspective of the published app, the Explore cell will behave in the same way as a chart or a pivot cell.
A published Explore cell only shows the selected visualization or pivot table. It does not show the field picker, table, configurations, or other elements; users will need to explore from the cell in the published app in order to use the Explore functionality.
Exploring with OAuth data connections
When exploring on a database table from an OAuth data connection, users will always be required to use their own OAuth tokens. If an exploration performed directly on a database table is shared between users, the underlying query will always rerun using each user's token; it will not be possible to view data outputs that were a result of another user's token.
When exploring from an app that has token sharing disabled, users will always be required to use their own OAuth tokens when exploring from the app.
When exploring from an app that has token sharing enabled, exploring from the app will use data that was a result of queries that ran using the shared token. If fresh queries against the database need to be executed when using an exploration, they will never be executed using the shared token; instead, new queries will always be executed using the user's individual token.
How to set up an app for optimal exploring
There are a few ways projects can be fine-tuned such that their apps are optimized for exploring:
- Dataframes that feed into charts, pivots, or explore cells should have plenty of columns. When a user explores from a cell in an app, the columns available to them in the field picker are populated by the columns of the dataframe that feeds into the chart/pivot/explore cell. Having plenty of columns gives the end users choices when it comes to slicing and dicing the data, instead of being limited to the few columns that were used to configure the chart or pivot.
- Prevent, to the extent possible, aggregation upstream of charts/explore cells. For example, say that one were to roll up distinct users per day in a dataframe and then chain that into a chart cell. Because distinct users can’t be re-aggregated from daily into weekly or monthly grains, exploring from the chart cell won't allow users to accurately slice-and-dice distinct users along other dimensions. Instead, perform aggregations within chart cells, pivot cells, or explore cells in order to allow workflows like these to be possible.