Metabase is quickly becoming one of the more popular business intelligence tools in recent years. In addition to being an affordable, open-source software, it offers a 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, such as from third-party apps or CSV files.
There are currently 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. Metabase can then access your data through the BigQuery data warehouse. However, this process is extremely redundant and inefficient, with the use of many intermediate tools.
Another method is to use a cloud-based data management platform, such as Acho. It supports various data sources, including third-party apps and 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 you can build a dashboard on Metabase without connecting to a database.
Import data to Acho
First, add your datasets to the “Resources” page on Acho. You can upload flat files such as CSV and Excel files.
Acho also supports connection to a variety of databases and third-party apps, such as Salesforce, HubSpot, and BigCommerce. All you need to do is enter your credentials to pull your data into a resource.
You can also easily integrate with APIs to import data from other sources.
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
Once you have your resources, you can go to the “Workspaces” page and create a new project. Then, import all of the resources that you need to build the dashboard into the project.
Projects are where you can view and transform your data to prepare it for export. You can import data from different sources and combine them together using the Join action. (Visit here to learn more about how to combine datasets together)
You can make additional transformations to your tables using the many no-code tools available or by writing SQL queries in the SQL editor.
For example, in our Zillow data, each month’s price is stored in a separate column. Typically, to make a time-series chart we’ll need all of the price data to be stored in a single column. To transform the table into the desired format, we can apply the Unpivot action to collapse these columns into two columns: month and price.
When building a dashboard, you’re probably looking to display some metrics. How can we calculate them? Through Formula or Pivot Table, you can compute various metrics, such as monthly percentage change in price. Formulas are great for calculating new columns based on existing columns, while pivot tables are powerful for summarizing large amounts of data.
Export data to Metabase
Once your table is ready, click the “export” button to send the table to Metabase.
When you export, you will be taken to Workflow. Drag the Metabase block to the canvas under Destination and link the table that you’d like to export by dragging from the table to the Metabase block.
If this is your first time exporting to Metabase, there are additional steps you’ll need to follow to set up your credentials. Visit here to see how you can set up your export or contact us for assistance.
Once you finish setting up your export, you will find your data on Metabase. Now you can click “Ask a question” to create a variety of visualizations including charts, tables, maps, and indicators.
This is an example of a dashboard we built with our imported Zillow data!
For real-time data, set up a scheduler
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 and turn on the scheduler. Once enabled, your tables will update automatically in Acho. Then, go to Metabase to sync the database schema. Metabase will now acquire new data points from Acho as your data is updated in real-time.
Benefits of using Acho to connect to Metabase
Benefits of using a data management platform like Acho include simplicity of the data engineering and ETL processes, ease of use, centralization of data, and processing speed.
1. Simple data engineering
Acho simplifies the data engineering process and helps you to skip all of the 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. But your work is not done there. Maintaining or updating your database is demanding and complex as well.
With a platform like Acho, you don’t need to worry about any of these technical issues.
2. Easy-to-maintain ETL pipeline
Acho allows you to create an ETL pipeline to automate your data cleaning process. Once your pipeline is built, you’ll be able to view your loaded table whenever you log on to Acho. There’s no need to run a query to request data from the database again. This helps keep your data ready and at your fingertips.
3. Ease of use
Although Metabase itself already has a user-friend interface, its GUI is basic and minimal. More complicated transformations have to be achieved by SQL queries.
On the other hand, 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 into the format you want.
4. Centralized data
A data management platform 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 Metabase’s database connection issues. 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.
5. Speed
Acho takes advantage of cloud computing and is thus able to run queries quickly, no matter how big your data is. Metabase currently cannot process large datasets very well. When your table is too large, say, over 10 GBs, the query becomes slow and can even crush your system. By running your queries on Acho, you can reduce your table size before it’s processed by Metabase.
Hope this article helps! Contact us in the chat box if you have any questions.
