Metabase is quickly becoming one of the more popular business intelligence tools in recent years. In addition to being open-source software, it offers a pretty user-friendly and intuitive interface that allows anyone to create a dashboard. However, it only supports databases at the moment. You cannot import data from other sources to Metabase. For example, third-party apps or CSV files are still unavailable for Metabase.
There are two methods to read data from other sources on Metabase. The first is to load your data into a database. For example, you can import a CSV file into Google Sheets and connect it with BigQuery. Therefore, Metabase can access data through BigQuery. However, the process is extremely redundant and inefficient.
Another method is to use a cloud-based data management platform, such as Acho. It supports various data sources, including third-party apps or flat files. Moreover, it allows users to perform complicated data transformations without writing SQL queries.
In this article, I will use a housing price dataset from Zillow to demonstrate how to build a dashboard on Metabase without connecting to a database.
Import data to Acho
First, add the datasets to the “Resources” page on Acho. Currently, Acho supports the following data sources:
- Flat Files: .csv, .tsv, .txt and .xlsx
- Databases: MySQL, PostgreSQL, MongoDB
- Third-party Apps: HubSpot, Google Analytics, Shopify, and Google Sheets
You can see more details about how to connect to these sources here. Since Zillow provides public links for people to download the dataset, we can choose API integration to import data on Acho.
Preprocess your data
You can go to the “Workspaces” page and create a new project. Then, import all resources that you need to the project. In a project, you can import data from different sources and combine them together. (Visit here to learn how to combine datasets together)
Additionally, you can transform the table into an appropriate format by writing SQL queries or applying actions with a few clicks. For example, in this case, each month’s price is stored in separate columns. Typically, a time-series chart will need all the price data corresponding to each time period in a single column. To change the table into a desirable format, you can apply the Unpivot action to collapse these columns into two columns: month and price.
Export data to Metabase
Click the “export” button to send the table to Metabase. (Notice that if it's your first time to export data to Metabase, please contact Acho to set up the connection.)
Then, you will find the data on Metabase. Now you can click “Ask a question” to create charts and dashboards.
Set up scheduler to retrieve the latest data
If your data is from databases or third-party apps, you can set up Data Sync to create a real-time dashboard. First, go to the Data Sync page on Acho to turn on the scheduler. Therefore, the tables will update automatically. Then, go to Metabase to sync the database schema and you can acquire new data points from Acho.
Benefits of using Acho to connect to Metabase
First, it simplifies the data engineering process and helps you to skip all complicated technical issues when building a database. For example, to build a database, you have to think about the schema first. Then, you need a server to host your database. Also, maintaining or updating your database is demanding and complex as well.
Second, as a data management platform, it can help you centralize all data sources in one place. This feature allows you to overcome the problem that Metabase cannot blend data from different sources. Moreover, Acho solves some database connecting problems to Metabase. For example, some columns may be missing when you import data from MongoDB. With Acho, you can make sure that all your data points are kept.
Third, Acho takes advantage of cloud computing and thus is able to run queries fast no matter how big your data is. Metabase currently cannot process a big dataset very well. When your table is too large, say, over 10 GBs, the query becomes slow and even makes your system crushed. The solution is to run queries on Acho to reduce your table size and then export it to Metabase.
Fourth, although Metabase has a user-friendly interface, its GUI is basic and minimal. Complicated transformations have to be achieved by SQL queries. However, Acho is designed for both non-technical users and advanced SQL users. Even if you know nothing about SQL, you still can transform your data to whatever you want.
Last, Acho allows you to create an ETL pipeline to automate the data cleaning process. Once the pipeline is built, you can see a table that you want when you log on to Acho. You don’t need to run the query to request data from the database again.
Hope this article helps. Contact us in the chat box if you have any questions.