To quickly get started building charts, try our Data Explorer tour. The tour is accessible via Help > Setup Guide in the top navigation of your Chartio account.
For an overview of building a basic chart using the Data Explorer, see our Quick Start Guide.
Have a quick query you want to try out, but don’t necessarily want to save it to a dashboard? Click the Explore button in the top navigation to open a Data Explorer window that is not attached to any dashboard. If you decide you want to save your chart to a dashboard, you’ll have the option to do so via the Save menu.
The Data Explorer has three main sections:
- The Dataset Interface is where you build your queries. Add a dataset for each separate query you want to include in your chart.
- The Data Pipeline allows you to join datasets and apply transformations to your data results.
- The Chart Preview contains chart settings and other visualization options.
Advanced Data Pipeline
For complex data processing—multiple joins, etc.—try our Advanced Data Pipeline.
The Dataset Interface is where you build your queries. Add a dataset for each separate query you want to include in your chart. If you’d like to join data from multiple data sources, you can do so by querying each source in a separate dataset.
PREVIEW TABLE DATA
Not sure which table you need to use? Click the table icon next to a table name to quickly preview its first 100 rows.
Note: Available for compatible data sources; excludes Google Analytics.
FOREIGN KEY CREATOR
To query columns from two different tables in the same dataset, you’ll need a Foreign Key to connect them. Add a new foreign key without leaving the Data Explorer by hovering over the table and clicking the key icon. To learn more about foreign keys, see our documentation.
Measures and Dimensions
Chartio automatically sorts your columns into two groups: Measures and Dimensions. Generally, you’ll want to drag Measures columns to the Measures field, and likewise for Dimensions. To use a Measure as a Dimension, see our documentation on Bucketing Measures.
Typically refer to quantitative data, such as number of units sold, number of unique visits, profit and so on. In the context of data visualization, measures map to the Y axis of a chart.
Drag or double-click a Measure column to the Measures field. Then, click the column in the editor to select an aggregation.
Refer to categorical data, such as state, gender, product name or units of time (e.g., day, week, month). Generally, dimensions are used to group quantitative data into useful categories (e.g., number of units sold by state) and typically map to the X axis.
Read more about date formatting.
Dragging a column to the Filters field in the Data Explorer generates a list of filtering customization options. These options vary depending on whether the column is a Measure, Dimension (Non-Date), or Dimension (Date).
By default, filters in Interactive Mode each have an AND between them. This means that each row included in your result set must match every filter condition.
However, there are certain situations where your query results only need to match some of the filter conditions. In those cases, you can use an OR filter.
Once there’s at least one filter in the Filters section, an OR button will appear below the Filter box. Click the OR button and drag your column to the new OR field.
Every condition in the same filter field will have an AND between it. You may add as many new OR filter fields as needed.
Chartio’s Interactive Mode date filters don’t necessarily follow SQL behavior. We have created standard date filters to ensure date filtering behavior is consistent for both dates and datetimes across all of our supported data sources.
- between is exclusive, which means the end date is not included
- between and including is inclusive, which means the end date is included
RELATIVE DATE FILTERS
To apply custom date filters, you can use Chartio’s Relative Date variables. For more information, see our Relative Date documentation.
Edit Variable Values
Use the Edit Variable Values feature to customize the dashboard variable values that you’re using in chart filters or in the Data Pipeline. This allows you to preview your chart’s data with non-default variable values.
Note: the edited variable values are only applied to the current Data Explorer session. Values revert to defaults when navigating away from the Data Explorer.
Interactive vs. SQL Mode
Building queries in Interactive Mode and then switching to SQL Mode is a great way to generate the basic structure of return values and even joins, before editing the query with more specific needs.
Any columns dropped into the pane while in Interactive Mode automatically generates an underlying SQL statement. To view the generated SQL, click the Preview SQL button.
SQL mode allows you to write custom SQL against your database. SQL Mode can be useful for complex queries that aren’t feasible in Interactive Mode due to complex joins, subqueries, etc.
Autocomplete column and table names in SQL Mode by using the keyboard shortcut
Control + Space.
Check out the other keyboard shortcuts you can use in Data Explorer!
Chartio saves the last 100 saved queries in each SQL Mode dataset. To access, click the History button in the top bar of your SQL Mode dataset.
To view the SQL being generated for your Interactive Mode query, click the Preview SQL button. The SQL is updated in realtime as changes are made to your Interactive Mode query.
This is also a great way to learn some basic SQL—simply edit your Interactive Mode chart and watch how the generated query changes.
You can add multiple datasets with different queries that will then be merged with your other datasets in the Pipeline. Each dataset is a separate query and can include data from any table or data source added in Chartio. For more information on joining your results from multiple datasets, see our documentation on Merging datasets.
Intro to the Data Pipeline
Chartio’s Data Pipeline allows you to perform transformations on your query results in a series of steps before charting it. These steps include a variety of operations such as column sorting, pivoting data, and adding calculated columns. The flexibility of adding any steps in any order allows you to get your data exactly how you want it.
Merging Data (Joins)
When adding two or more datasets in the Data Explorer, you will need to select a method for merging, or blending, the datasets. Merging datasets allows for powerful post-processing and calculations using Chartio’s drag and drop interface.
For every join option except Union and Cross Join, you can choose how many columns you would like to join the datasets on. View an example.
Combines the columns from all datasets on one or more common dimension when possible, and includes all data from both datasets.
Combines the columns on a common dimension (the first N columns) when possible, and only includes data for the columns that share the same values in the common N column(s).
Combines the columns on a common dimension (the first N columns) when possible, returning all rows from the first dataset with the matching rows in the second dataset. The result is NULL in the second dataset when there is no match.
A Union merge will stack the dataset results on top of each other without grouping or combining the data. Unions can be used to generate lists of data to be printed or viewed in table format. To remove duplicate rows, check the Distinct checkbox.
The result of the Cross Join will be a table with all possible combinations of your datasets together. This can result in enormous tables and should be used with caution. Cross Joins will likely only be used when your datasets are returning single values.
Adding a Data Pipeline Step
Click the +Add Transformation or plus (+) button in the pipeline wherever you want to apply a new transformation step to your query results. You can add as many steps as you’d like, in any order. You can also use each step multiple times as needed.
For descriptions and examples of the available Data Pipeline steps, check out our Data Pipeline documentation.
To preview your to view your data at any point in the pipeline, click the View Data link in a pipeline step. It can be incredibly useful to compare how your data looks before and after a transformation step is applied.
The Chart Preview section is where you select your chart type and other Chart Settings.
Click the Chart Settings button above the Chart Preview to view available settings. Each Chart Type offers different customization options. You can find more information about the settings for a specific chart type under the Chart Settings section.
Popular chart features: