Open-Source BI Tool Metabase for Data Visualization and Analytics

Introduction

Are you a passionate data professional exploring new tools? Try Metabase, an open-source Business Intelligence (BI) tool for creating interactive dashboards from large datasets. In today’s data-driven world, BI platforms like Metabase are essential for extracting insights and facilitating informed decision-making. Discover the power of Metabase in this guide tailored for data professionals.

Learning Objectives

• Understanding necessary terms and concepts of Metabase
• Using the tool for Analyzing Data, Creating Visuals & Interactive Dashboards
• Highlighting notable features of Metabase Open-source edition and it’s key component
• Explaining Metabase Admin Activities including user and group management

This article was published as a part of the Data Science Blogathon.

Metabase, a business intelligence tool, can be hosted on the cloud or on-premise. It comes in open-source and Enterprise Edition. Utilizing Metabase BI, users can efficiently connect to various databases, analyze data, and rapidly build and deploy dashboards. The open-source edition offers a range of features, including connectivity to 15+ DBMS, user and group management, interactive dashboarding, alerting, subscription of dashboards, and support for CRUD operations. This article elucidates the key concepts and useful features of the Metabase BI tool.

This guide focuses on discussing Metabase, which offers both Open-Source (AGPL) and Enterprise Tier licenses, with a specific emphasis on its application in a Windows environment. The latest version, V0.48 (as of Dec ’23), distributes as a Java Archive file (Jar), requiring Java 11 or higher as a prerequisite for running the latest Metabase versions. The article provides insights into the installation process and highlights key features of Metabase’s open-source edition.

Step I : Downloading Metabase.Jar

Download the Metabase Open-source version from the Metabase GitHub repository. Obtain the latest version of Metabase.Jar here.

Step II : Starting Metabase

Place the downloaded Metabase.Jar file in a folder and just double click Jar file for Metabase to start up. 

Or

Using Command prompt  to  run Metbase.Jar. Navigate to directory where Metabase.Jar is present  and Run “Java -jar Metabase.Jar”

Example: Considering  Metabase.jar file is present in folder “C:\Users\Tools\”. Command for the same be below:

C:\Users\Tools> Java -jar Metabase.Jar

You can watch for the Metabase logs as it starts up.

Wait for few minutes and Log into http://localhost:3000/ url using any of popular  web browser 

Note: Metabase, by default, utilizes the H2 Embedded Database as the application DB. For production, configure Metabase with one of the three databases (MySql, PostgreSQL, or MariaDB).

For the First Time Metabase prompts options for Initial setup (Language preference, User ID and password setup and DataSource setup). After Setup is complete, you can see the Home Screen of Metabase.

• Left Pane – Display of Available Collection(folder) and Sample Data Source
• Top Right – Settings and option “New” for creating Questions and Dashboard

Features of Metabase

Collection, Question and Dashboard

Collection in Metabase is where the Questions, Models & Dashboards are stored. Collections is equivalent to folder, you can create sub-folders in it .

Personal Collection for Every User

Every User in Metabase will have a specific Personal Collection  where the user can store their Questions, Models & Dashboards. Contents stored in Personal Collection is not accessible to other users.

Collection for Project  

A single Metabase instance can serve multiple projects. Each project’s contents can be stored in a dedicated collection, accessible only to specific project users.

A New Collection can be created from option New >> Collection

Note : Metabase Admins have full rights to View ,Modify and Archive contents in every collection including users personal collections.

Questions

Term “Question” in Metabase refers to an Individual Visual element. It can be a Bar chart or  Line Chart or Map Chart or anyone of 17 supported Chart in Metabase. You can create questions in the following ways:

• Using Visual Query Builder
• Custom SQL Query

Using Visual Query Builder

In the Top Right, click on option “New” >> “Question” ,Then pick the DataSource you wish to Explore

The Visual Query Builder supports Joins, creation of new columns, Filtering, Summary, Group by, Sorting, and Row Limit operations. You can preview the results after each operation.

Note : Metabase at the backend will frame SQL for every question.

 Custom SQL Query

If the options in the Summary editor don’t meet your requirements, you can edit the question and modify the SQL according to business needs. This process is termed as “SQL Question,” where the user-provided SQL is converted into a question. You can create a new SQL Question by selecting “New” >> “SQL Query.”

Once you create a question, save it to a collection and add it to the dashboard.

Dashboard 

A dashboard is a page that groups multiple questions with a common business context.

Components of Dashboard

• Tabs: Dashboard can have one or more tabs connected by common Filter 
• Questions: Individual Visual element in Dashboard 
• Filters: Filters can control Data loaded into Each Question (E.g : Filtering by Country )
• Text/Heading: Ability to add Description ,Images from URL .Supports Markdown
• Click Behaviour: Options in Question that control the Interactivity of Dashboard 
• Other options in Dashboard: Data Refresh, Email Subscription,  Download Data & Export to PDF

Filters and Click Behaviour

Interactive Dashboarding

Arrange the necessary questions in the Dashboard Tab, and add interactivity by configuring filters and click behavior.

Filters in Dashboard

Add one or more filters to a dashboard. Configure each filter in the dashboard to control one or more questions. After adding a filter, map it to each question on the dashboard. In the backend, Metabase adds a WHERE clause in the SQL.

Steps to Map Filters to Question

1. Click on the Filter icon in the top right and choose the relevant filter type.
2. The chosen filter type will be added to the dashboard header.
3. Click on the settings button in the individual filter. A visual layout of the dashboard is displayed with a list of available columns in each question.
4. Map the relevant column in the question to the selected filter from step 1.
5. The distinct values of the mapped column will be visible in the Filter tab.

Click Behaviour

“Click Behaviour” refers to an operation that is performed when a portion or segment of Visual is selected/clicked while Viewing a Dashboard. 

Example: Click Behaviour can be configured for Selecting a bar segment in Bar Chart ,Clicking a Region in a World Map. It can be configured in each question. You can access this option in Top Right corner of every question when dashboard is in edit mode.

Click Behaviour Operations

1. Drill-Down to see the data records for the clicked portion or segment in chart
2. Navigate to another Dashboard , Navigate to a Question &  Navigate to external web site using URL
3. Update any of available Dashboard Filter . This allows to implement cross-filtering in Dashboard. By Effectively setting up Click-Behaviour, Dashboard can be made Interactive & Intuitive for users.

Dashboard Deployment and other Useful features

Once you create and store a dashboard in a collection, any users with “View” permission can access it. A dashboard or question can be accessed directly via its URL.

Other Notable Features of Metabase 

1. Performing CRUD operations in a table using actions in the model.
2. Configuring auto-refresh for every dashboard.
3. Configuring alerts in questions.
4. Downloading results data as .CSV, .JSON, or .XLSX from a question.
5. Downloading a question as .PNG and a dashboard as a .PDF file.
6. Subscribing to a dashboard via email or Slack.

By Default Admin permission is applied for user who setup Metabase initially. Default Admin can further add other Metabase users and provide them Admin Role if necessary. Metabase Admin options can be accessed from “Admin Settings” by clicking “Gear icon” in Top Right.

Configuring Data Source

Metabase Admin have rights to configure new Data Source under “Databases” options in Admin Settings

User and Groups Management

Below are possible operation that can be performed in Admin Setting under “People” option:

1. View List of All Active Metabase users & List of Deactived users.
2. Add a user and map them to existing Metabase Group.
3. Remove a user from group or Deactivate user & Reactivate user.
4. Create New Group , Manage existing Group (Add or Remove Users from a Group).
5. View or Edit Details of User & Reset password for user.

Note : A user can be Mapped to one or more Metabase Groups

Permissions Management

While “People” option is for managing users and their Group , “Permissions” options under Admin setting is for managing permission between Collection, Groups & Database 

Collection Permission 

Under the sub-option “Collections”. Below are different Permission level that a Group can be mapped into collection

• Curate : Users of a Group having Curate permission can add ,edit ,View ,archive any contents in collection and create sub-collection and can move contents from or to the collection.
• View : Group with View permission can only View the contents of collection ,unable to add or modify the contents.
• No Access : Unable to view the collection itself and unable to access any items in it.

Data Permission

Various permission levels for mapping a group to a database include:

1. Unrestricted: Users in the group can use the query builder to create a question from any table in the database.
2. Granular: Only specific tables in a database can be accessed using the query builder. Admins have the right to configure this permission for each table.
3. No Self-Service: Users in the group cannot use the graphical query builder and cannot see data.
4. Native Query Editing: When enabled along with unrestricted permission, questions can be created using native SQL.

Other Metabase Admin Activities Includes

1. Checking for Latest updates and Migrating from Embedded H2 Database.
2. Configuring Email, Slack and authentication setup using Google or LDAP.
3. Configure Database for CSV uploads.
4. Data type mapping and formatting for Individual columns.
5. Other advanced Setting to enable Metabase embeddings and public sharing.

Conclusion

Metabase’s simplicity and intuitive UI make it an easily adaptable BI tool for data professionals. The open-source edition of Metabase includes all the essential functions of a business intelligence tool, with new features added in each major release. Organizations can initially try and adapt to the open-source edition of Metabase. As needs and user bases grow, a comfortable switch can be made to any of the enterprise editions that Metabase offers. This article aims to provide an overview and highlight key components of Metabase. Future articles will delve into other interesting and useful features of Metabase.

Key Takeways

1. Metabase is an open-source BI tool facilitating interactive dashboards for data professionals.
2. Understand Metabase terms, analyze data, create visuals, and explore admin activities.
3. Install Metabase on Windows with Java 11+, featuring user-friendly UI and essential functionalities.
4. Create dashboards with filters, click behavior, and interactive features for insightful data exploration.
5. Admins manage users, permissions, and configurations, making Metabase adaptable and user-friendly.

Frequently Asked Questions

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

Related