The example Metabase API links covered in this video can be found here: https://github.com/ElishaKay/scraping-the-driving-questions#metabase-api-integration. Metabase is a good, popular open-source BI tool that anyone can quickly install on your local environment to get a simple BI system up and running. Yet when your analytics needs grow, you might face some of these pain points when using Metabase. Data Visualisation, Dashboards & Reporting At Cure.Fit, Metabase is the single point of contact for all data access needs of business users, the only prerequisite being basic SQL knowledge. Holding an active user base of around 800+ individuals who use Metabase, on average, we have found that 450 users use it daily for analytics. Analyze your Google Sheets data in Metabase Getting your data from Google Sheets to Metabase can be a pain. Managing pipelines for all your data sources just isn’t sustainable, but your reporting can’t be put on hold while you wait for IT or Engineering to get to your ticket.

Need crisp insights to make reliable business decisions? Use these tools and generate reports from years of business data.

“Should we increase the spending on Facebook ads this month?”

“How much stock should we ship to the New York store for this week?”

“Do we need three or five cashiers on Saturday evenings?”

Gone are the days when business decisions like these were made on assumptions. Today, you will invariably want to look at data and insights before answering any of the above questions.

Dashboard software is the go-to tool for visualizing business data and extracting insights from it. It helps monitor and measure performance in real time and get a consolidated, single-pane view of important business metrics.

If apprehensions about heavy investment are keeping you from using dashboard software, we’ve got you covered with this list of top free and open source tools.

To be considered for this list, tools had to support the following functionalities:

• Importing data via manual input, file upload, or integration with different data sources, applications, and platforms.
• Visualizing data into different forms, such as charts, bar graphs, and pie charts.
• Providing a single-panel view of the aggregated data.

What does “top” mean? Each of the four tools included in this article has had a minimum user rating of 4.0 on Capterra’s software directory over the past year.

For more details on how we selected the products, you can read our methodology at the bottom of the article.

4 best free or open-source dashboards

(In alphabetical order)

• 
  Cyfe
• 
  Databox
• 
  Metabase
• 
  Zoho Analytics

1. Cyfe

Cyfe is a cloud-based dashboard tool that pulls data from advertising, email marketing, sales, finance, social media, support, and web analytics accounts into one panel.

It connects to over 1,500 third-party applications, including Google Analytics, Salesforce, AdSense, Mailchimp, Facebook, WordPress, and Trello. It can also import internal business data via custom widgets or push APIs.

Marketing dashboard generated in Cyfe (Source)

The tool helps visualize data by different categories (such as department, client, and time period) into charts and graphs. You can export generated reports in PNG, JPEG, PDF, and CSV formats or share the dashboard with stakeholders via a custom URL.

The free module of the tool provides full functionality but limits to two dashboards and a single user. It also limits the dashboard to a 30-day history, even though the module never expires.

Reviewers of Cyfe on Capterra share the following pros and cons:

2. Databox

Databox is a cloud-based business intelligence (BI) tool that imports data from sources such as marketing, sales, social media, search engine optimization (SEO), and project management tools.

The tool connects to over 70 third-party applications, including sources like Google Analytics, Salesforce, HubSpot, Mixpanel, Facebook, and Shopify, via one-click integrations. It also offers over 200 pre-built report templates that can be customized using the do-it-yourself (DIY) Dashboard Designer.

TV screen view of Databox dashboard to display KPIs (Source)

Databox offers a DataBlock library for you to choose data visualizations that can be dragged and dropped into the dashboard. It also incorporates goal tracking to set daily, weekly, monthly, or annual targets for any business metric you’re tracking. You can schedule auto-generated daily, weekly, and monthly snapshots of any dashboard to be sent via email, or create custom URLs to share dashboards with other stakeholders.

The free module of the tool is limited to three data sources, three dashboards, and three users, and only provides one daily refresh (as opposed to real-time or hourly refresh).

Reviewers of Databox on Capterra share the following pros and cons:

3. Metabase

Metabase is an open source dashboard tool that can be set up without specialized technical know-how. It works with multiple data sources, such as Google BigQuery, Amazon Redshift, Teradata, Snowflake, and Mongo.

The tool helps users summarize and visualize data without any SQL expertise. It offers a quick and easy lookup function to help search and filter through data and visuals.

A business dashboard generated through Metabase (Source)

You can schedule emails to share charts and snapshots from the dashboard or manually share them via Slack. You can also set goals for various metrics, much like possible with Databox.

The free version offers full functionality. Being open source, it needs to be hosted on-premise.

This can require higher upfront investment compared to cloud-based tools but gives complete control over the software, with no predefined storage limits, pricing tiers, or caps on the number of dashboards or users.

The vendor shares new features, interface improvements, and bug fixes for free with all users as well as provides a community space for users to share feedback and fixes for the tool.

Reviewers of Metabase on Capterra share the following pros and cons:

4. Zoho Analytics

Zoho Analytics is a self-service BI tool that helps users create dashboards to measure business metrics. It was previously known as Zoho Reports.

It imports data from multiple sources, including cloud drives (such as Dropbox and Google Drive), relational databases (such as Oracle and Sybase), and cloud databases (such as Microsoft SQL and Amazon Redshift). It also supports manual import of data in a number of file formats, such as CSV, TSV, Excel, HTML, TXT, and JSON.

Report created in Zoho Analytics (Source)

The tool allows visualizing data in charts, widgets, pivot tables, geo-visuals, and tabular views. You can create reports by dragging and dropping components into the dashboard and analyze those reports by applying filters and creating drill-downs.

The free version of the tool provides access to two users for up to 10,000 rows of data, three query tables, and five workspaces. Users can create unlimited reports. However, it limits integrations with third-party applications to QuickBooks, Microsoft Dynamics, Xero, Teamworks, HubSpot, MailChimp, Facebook, Twitter, and Google Analytics.

Reviewers of Zoho Analytics on Capterra share the following pros and cons:

Quick checklist for choosing a dashboard tool

Now that you’ve reviewed the top free and open source dashboard software, it’s time to ask yourself which one might meet the needs of your business. Below is a quick checklist to help you out:

✔ Spend some time on our dashboard software buyers guide to learn what the software does, what are its typical features, and how it can benefit you.

✔ Ascertain the number of users who will be accessing the dashboard and ensure that the tool supports that number of users.

✔ Ensure that the tool gives flexibility in the visual representation of data and insights. It should allow you to choose between various formats of charts and graphs as well as to customize reports.

If you’re still confused about which tool is right for your business, spend some time reading reviews from verified users on Capterra. See which features are working for them and what challenges they are facing, and incorporate those insights into your shortlisting process.

Methodology

To select the products mentioned in this article, we identified the most common product listings for dashboard software searches via Incognito Google search on Mar 4, 2020. Products that appeared on third-party listings at least twice during this research were shortlisted for further evaluation.

The shortlisted products were evaluated against the definition of dashboard software: Dashboard software is an information management tool that visually tracks, analyzes, and displays key performance indicators (KPIs) to help businesses make informed decisions and improve productivity and the efficiency of their workflows. Dashboards provide a customizable interface that collects real-time data from multiple sources and displays the generated insights in the form of charts, tables, gauges, and timelines.

The tools were further checked for the following criteria:

• Offering a completely standalone free module of the tool, that did not require the user to purchase the tool after a set period of time.
• Not requiring extensive technical know-how or coding languages to set up.
• Overall rating of 4.0 or more on Capterra software directory between Mar 4, 2019 and Mar 4, 2020.

The tools that met all the above criteria were selected for the article.

Disclaimer:

Listed pros and cons are derived from features listed on the product website and product user reviews on Gartner Digital Markets domains (Capterra, GetApp, and Software Advice). They do not represent the views of, nor constitute an endorsement by, Capterra or its affiliates.

The applications selected in this article are examples to show a feature in context and are not intended as endorsements or recommendations. They have been obtained from sources believed to be reliable at the time of publication.

Looking for Business Intelligence software? Check out Capterra's list of the best Business Intelligence software solutions.

How do you begin combining data from cloud applications with your internal databases to gain insight into your business? Maybe your organization has already standardized on Metabase as your analytics tool, but you're still learning about using it with multiple data sources.

To analyze data from diverse sources, you need a data warehouse that consolidates all of your data in a single location. Most businesses take advantage of cloud data warehouses such as Amazon Redshift, Google BigQuery, or Snowflake.

You can extract data that you have stored in SaaS applications and databases and load it into the data warehouse using an ETL (extract, transform, load) tool. Once the data is available, your analysts can use it to create reports. In this post, we'll look at how to start from scratch and create a report using Metabase, an open source business intelligence (BI) tool that's free to download and use.

Metabase's visual query builder lets you generate simple charts and dashboards, or you can use SQL to create more complex visualizations, as we'll do here. Each query starts by clicking a button to 'ask a question.' Metabase is simpler than tools like Tableau and Power BI, offering fewer features, but it's correspondingly simpler to learn.

Reporting, data warehouses, and ETL

Metabase can run in the environment of your choice via a Docker image, on AWS Elastic Beanstalk, or on Heroku, or you can run it as a native application on macOS or as a Java jar file. I used macOS version 0.31.1.

Per its FAQ, 'Metabase is primarily meant to work with actual databases.' If you want to analyze data in SaaS platforms, the developers 'suggest that you use other tools to build a data warehouse with the data you need.'

That's what we'll do with Stitch, a simple, powerful ETL service for businesses of all sizes, up to and including the enterprise. It can move data from dozens of data sources. Sign up and you can be moving data to a data warehouse in five minutes. Choose at least one of Stitch's integrations as a data source. Most businesses get more value out of their data as they integrate more data sources; for our example, we'll look at data from two separate sources.

Setting up a data warehouse

I used some of Stitch's real data to build a data visualization for this post. Specifically, I was curious to see how many support conversations came from each of Stitch's pricing tiers. To find out, I'd need to use billing data stored in Salesforce and join it with support conversation data from Intercom to create a visualization of support conversations.

My first step was to set up a BigQuery data warehouse to store the data from the two SaaS platforms. Following our documentation, I added to my Google user the permissions that Stitch would need to load data to BigQuery.

Metabase Google Analytics

Using Stitch makes extracting data from a source and loading it into a data warehouse easy. I logged in to Stitch and added new integrations for Salesforce and Intercom, following the instructions in our documentation. From Salesforce I selected the Account table to replicate. Stitch's Intercom integration automatically extracts data from all available tables. From both, I chose the period from which to replicate data.

Once I'd set up my integrations I added BigQuery as my destination and specified a BigQuery project name. Within a few minutes, Stitch extracted the data I specified and loaded it into BigQuery.

Building a sample report

Next I turned to Metabase.

To start, I clicked on Metabase's settings icon, then Admin. I chose Add a Database and entered the names of my BigQuery project ID and other information, which I obtained from the BigQuery dashboard. As part of that process I had to generate an OAuth 2.0 Client ID and Client Secret — a simple process that involved entering some simple information on a screen connected to a Click here link. Similarly, I generated an Auth Code by providing my Google authentication information.

Once you have a database to work with, you can 'ask a question' in one of three ways:

If you choose Metrics, Metabase directs you to create segments and metrics from its admin panel. You can choose Custom for slightly more complex queries on a single table. I wanted to join data from two tables, but a Metabase blog post says:

Our goal with Metabase has always been to provide a way for non-technical users to answer their own questions in a self-serve manner. While joins are a great tool that a skilled analyst or programmer might reach for, we will be trying to add features that expose a highly specific, easily understood operation that someone who isn't SQL fluent would understand.

Metabase Google Analytics Login

Fortunately, we're SQL-fluent around here. I chose Native Query, which lets you paste in SQL code.

A look at the Intercom schema showed me that to associate the number of conversations with the Stitch customer ID, I'd need to join the Intercom Users and Conversations tables. The Users table contains a field called companies that contains information about the companies a given user is associated with. That field is a list that could contain multiple values – a nested data structure, in other words. Many data warehouses don't support nested data structures, but BigQuery does. It stores each list in an array.

Retrieving data from a nested data structure is tricky if you're used to working with fully normalized data. My colleague Erin, Stitch's senior technical documentation manager, set me on the right path with this query:

The SQL UNNEST operator takes an array and returns a table, with one row for each element in the array.

Metabase Google Analytics Training

To get the plan tier information from our Salesforce Account table, I created another query:

I used those those queries, along with data from Intercom's Conversations table, to retrieve the data I wanted, joining Intercom and Salesforce data. I used a case clause to consolidate data from monthly and annual billing plan into a single bar for each pricing tier:

Running this query in the BigData console gave me the data I wanted. Pasting it into Metabase gave me a table of data. To turn it into a visualization, I selected Bar from the Visualization drop-down and chose tier as the X axis. The result was a useful visualization.

Metabase lets you customize bar colors and axis labels. When you're satisfied with your visualization, you can save it in a collection and publish it to a dashboard that other users can share.

So there you have it – a quick walk through the process of using an ETL tool like Stitch to move data from multiple sources into a data warehouse, then report on it using Metabase. Sign up for a free trial of Stitch and start creating your own.