MetaBase - A Powerful Power BI Alternative

Metabase is an open-source business intelligence tool designed to help companies and organizations analyse, visualize, and share their data.

Key Features

• Ease of Use: User-friendly interface, which allows users without extensive technical knowledge to create and view data reports and dashboards.
• Querying Data: Users can run queries on their databases using a simple point-and-click interface, or by writing custom SQL queries for more complex needs.
• Data Visualization: Metabase provides a variety of visualization options, including bar charts, line charts, scatter plots, pie charts, and more.
• Dashboard Sharing: Dashboards and individual reports can be easily shared with team members or embedded into other applications, promoting collaboration and data-driven decision-making.
• Support for Multiple Databases: Metabase supports a wide range of databases, such as MySQL, PostgreSQL, MongoDB, SQLite, and many more, making it versatile for different data environments.
• Customizable Filters: Users can add customizable filters to dashboards, allowing viewers to interact with the data and drill down into specific details.
• Alerts and Notifications: Users can set up alerts and notifications to receive updates when certain conditions are met in the data, helping to stay informed about key metrics and changes.
• Questions and Answers: Metabase allows users to ask questions about their data without needing to write SQL. It translates these questions into queries and displays the results in an easy-to-understand format.
• Ad Hoc Analysis: Users can perform ad hoc analysis on the fly, enabling quick insights without needing to set up complex reports in advance.

Licensing

• Metabase allows you to self-host the platform without any additional fees. There are no user/DB/query limits on the self-hosted version of Metabase.
• The paid version has its benefits in that Metabase will manage the infrastructure (setup/failover) for you and there are a lot of additional modules available on the paid plan (eg.: directory authentication).

Requirements

• VM with Docker and Docker Compose installed. This example uses Docker to deploy Metabase. Check out this post to quickly deploy a new VM and install Docker and Compose using Ansible in under a few minutes.
  • Metabase requires at least 1 CPU core and 2GB ram for the server. For every 40 concurrent users, a PostgreSQL DB needs 1 CPU core and 1GB RAM.

Deployment

Metabase can be deployed as a standalone Docker container, to which you can connect your DBs after setting up the basics.

This example uses Docker Compose to deploy Metabase as well as a PostgreSQL database (optional). First, create a new shared network to house all BI apps.

 docker network create -d bridge shared_network

version: "3.9"
services:
  metabase:
    image: metabase/metabase:latest
    container_name: metabase
    hostname: metabase
    networks:
      - shared_network 
    volumes:
      - metabase_app_data:/metabase
    ports:
      - 3000:3000

  postgres:
    image: postgres:latest
    container_name: postgres
    hostname: postgres
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: metabase
      POSTGRES_DB: metabaseappdb
      POSTGRES_PASSWORD: mysecretpassword
    volumes:
      - metabase_postgres:/var/lib/postgresql/data

volumes:
  metabase_postgres:
  metabase_app_data:

networks:
  shared_network:
    external: true

Initial Setup

• Visit http://{Host_IP}}:3000 and select your language.
• Create an admin account.
• Attach the database. The connectivity details are preconfigured in the Docker Compose file above (optional, you can connect DBs after the initial setup is complete).

Data Management

Metabase offers two main sources of Data:

• Database
• CSV files (enabled under Admin settings > Settings > Uploads)

Uploading CSV data is best suited for ad hoc analysis of spreadsheet data. If you have a lot of data, or will need to update or add to that data regularly, it is recommended setting up a way to load that data into a database directly, then connecting Metabase to that database.

Migrating to a Production DB:

Metabase covers how to migrate your Metabase instance with Docker to a production ready database when you are ready to go live.

Data Visualisation

Metabase is equipped with a lot of opptions for visualisations, from various graphs and grid-type dashboard for flexibility.

This example will use this sample CSV to load and visualise the following example data:

• Download the CSV and navigate to Collections > Your Personal Collection > Upload.
• Upload the CSV and create a new model.
• Click on the Employees model. Metabase automatically detects headers and arranges data in a table.
• Let's visualize the total distribution of employee roles by salary in a pie chart:
  • Click New > Question and select the following:
    • Data: Employees
    • Summarize: Sum of Salary
    • By: Job ID
  • Click Visualise.
  • Click Visualisation and select Pie.
  • Click the gear icon next to the visualization tab, select Measure > ... and change the style to Currency.
  • Click Done, save and add it to a new dashboard.

Conclusion

Metabase is a powerful Power BI and Tableau alternative with a focus on being user-friendly and open source. I highly recommend trying it out if you're looking for BI tools.