Using Chartio with Integrate.io: Visualizing the Data

In Part 1 we learned how to set up our Integrate.io pipeline to work with Chartio and prepared the data source. In Part 2, we will focus on using the data Integrate.io provides in the Chartio platform.

If you're new to Chartio, you can read through their QuickStart docs (shouldn't take more than 5-10 minutes) to gain some familiarity.

Specifying the Data Source

The first step is to add our SingleStore cluster information to Chartio. This step is similar to how we added the SingleStore database connection in Integrate.io.

Once connected, Chartio will load the schema from the database automatically. We can now explore our data and create some useful visualizations.

Creating Visualizations through Chartio

Chartio has lots of chart types to use in your data visualizations. Usually, you will create a visualization with some analytical or exploratory question in mind. In this example, we will follow a similar approach and try to answer specific analytical questions by implementing appropriate chart types.

Question #1: What are our total orders, sales, discounts given, and taxes?

To answer this question, select the order_id column of the orders table in the data explorer, then specify the aggregation as "count of distinct" to prepare the query.

Once the query is run, Chartio displays the result in the table and automatically creates a "Single value" type chart at the left.

We can configure details of this chart (by specifying settings like title, precision, formatting, etc.) and save it to a Dashboard, which in Chartio is the place where you can add and view multiple charts at the same time.

Like the total orders, the values of total sales, taxes, and discounts can be calculated from the fields total_price, total_discounts, and total_taxes, respectively. This time, we must apply the sum aggregation instead of the count of distinct aggregation.

After adding these values to the dashboard with appropriate settings (currency prefix, decimal precision, and title), the Dashboard should now look like this:

Question #2: How have my sales have varied over time?

To answer this question, include a column representing date-time.

We have added the processed_at column along with total_price and the id column to the query. To plot both the order counts and total sales in the same graph, choose the "Bar Line" chart type for this query. The aggregation levels are set to get total sales and the number of orders for every month.

Question #3: How does the sales traffic vary throughout the week?

One interesting way to visualize this is through a Heat Map of total sales varying over different hours and days of the week. This can provide an idea of peak sales times.

Question #4: Where are my orders coming from?

To visualize this, use the Map chart type and employ Shipping Address Country as the proxy for the location of orders.

To complement a map, we can plot a pie chart showing the percentage distribution of these orders among the countries.

Putting everything together, this is how the Dashboard looks so far:

As you can see, we can keep iterating over and adding visualizations. Here are a few more questions that we can answer using a similar approach as above:

What percentage of my orders are refunded?
How many new customers do we have?
How many transactions are pending?
How many unfulfilled orders do we have currently?

Other Chartio Features

Control Types

So far, the Dashboard shows most of the metrics/charts on an overall level, and there isn't an easy way to filter. What if we want to see the metrics and charts for just a specific period? We can achieve this in a Chartio Dashboard by using Control types.

The following image shows the result of adding the Calendar control to our existing Dashboard. Notice how those metrics and charts have changed (except for the sales over time chart, which we still wanted to show monthly trends):

Refer to Chartio's documentation for detailed steps on how to add Dashboard controls. The key step is adding a filter into the existing charts to respond to the values set in the control.

The following is an example of how we added the filter to the query for total tax. Any charts that should be affected by Calendar Control must include the same filter:

Drilldowns

Drilldowns in Chartio allow the use of Controls interactively through a chart. For example, we can add a Drilldown to our world map so that, if we click on a country, it filters the data to that specific country for the rest of the metrics and charts.

Here is an example of a Dropdown Control type for the country and configured the world map's Drilldown.

Notice the new filter in the total tax query (saying "Shipping Address Country is one of {COUNTRIES}"). The same filter must be added to all the charts that should be affected by this Drilldown.

Now you can click on a country in the world map and all the charts (except for the donut chart, which still shows the distribution across the countries) will change accordingly:

Sharing the Information

A big benefit of visualization platforms like Chartio is the ability to present information to concerned stakeholders in a beautiful and easily understood format. In Chartio, you can simply share a link to the Dashboard, which anyone in your organization can use. You can also schedule and automate the sharing of dashboards by email and PDF. Another option is to create alerts to track metrics and receive notifications when certain conditions are met. Alerts are especially useful when the met conditions require action be taken.

Other Features

Chartio provides snapshot functionality for the Dashboards so that you can keep track of your metrics over time. If your team has SQL expertise, you can directly write queries to fetch the data or tweak the queries generated with Chartio's data explorer interface. You can also create a customized schema derived from the original schema, which can then be used in the queries to create charts. If you're preprocessing for a chart type that's taking a lot of time, consider saving its result to a Data Store.

Final Touch: Keeping the Data in Sync

Adding an automated sync to your integration will keep the data updated frequently. Luckily, both Integrate.io and Chartio can perform this functionality in a few clicks.

On the Integrate.io side, simply create a schedule from the Integrate.io dashboard and specify the frequency at which your pipeline should be run to pull the data. The pipeline will only fetch the data incrementally, which will save bandwidth and computing time.

On the Chartio side, go to DasSboard settings and specify the Auto Refresh Interval.

Conclusion

Integrate.io and Chartio is a winning combination. Integrate.io's components fetch the data, taking care of authentication, pagination, rate limiting, and preprocessing. SingleStore serves as the intermediate data store, and Chartio uses the clean, optimized data to create eye-catching and informative visual data that your stakeholders can use to make true data-driven decisions. Moreover, the approach described in this article is both secure and efficient. Data loads incrementally with preprocessing done only once per object. It is then stored in the location of our choice. When it is transferred from Integrate.io to Chartio, the data is encrypted in transit using TLS to prevent any sensitive information from being exposed.

To learn more about Integrate.io's integration for Chartio, click here. If you'd like to experience the Integrate.io platform for yourself, contact our support team for a demo and 14-day risk-free trial.

Big Data