By Liptan Biswas, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud’s incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.
In the first part of the tutorial, we looked at how to install Metabase for production use on Ubuntu 16.04. In this part of the tutorial, we will learn the basic uses of Metabase. We will run some sample queries on sample database hosted on a MySQL server. We will learn to create saved questions and dashboards. We will also configure emails into Metabase so that our application can send us updated insights on a regular basis.
Using Metabase to Get Insights
Metabase is able to run queries over multiple types of database such as MySQL, PostgreSQL, Presto, MongoDB, MSSQL etc. Because of this feature, you can directly add your database into Metabase and ask questions from it. In a production environment, it is best to create a new user with read-only access just for the Metabase instance. If your database contains sensitive information, you can also create table views according to preferences to display only selected columns.
Although, Metabase provides a sample database on which you can run queries to learn about the platform. In this tutorial, I am using a sample MySQL database server with Employees database to demonstrate the usage of Metabase. Let’s suppose your company has a database named “employees” hosted on MySQL database server. Running these queries will create a new user named “metabase_user” with read-only access to the database. Access will be granted only to the instance with IP address “192.168.0.101”, which is the actual public IP address of your ECS instance.
CREATE USER 'metabase_user'@'192.168.0.101' IDENTIFIED BY 'StrongPassword';
GRANT SELECT ON employees.* to 'metabase_user'@'192.168.0.101' IDENTIFIED BY 'StrongPassword';
Now that our read-only user is ready, let’s add the database into Metabase. Login to your Metabase instance and goto Metabase Admin >> Databases >> Add Database. Select database type and provide connection details.
Once the database is added, you can go to the Data Model tab to check the schema of the database. You can hide the tables or columns which contain redundant or sensitive information. Exit the admin panel and go back to the dashboard so that you can ask questions and learn the insights from the data.
Asking Questions on Metabase
We will start by asking a simple question. Click on New Question button at the top and select custom questions. Now, select the database on which you want to run the query. Select a filter for your query if required. Select the view, you can choose to see the raw data, basic metrics or run custom expressions. Basic metrics include the count of the number of rows, the sum or average of rows, cumulative sum or average, and standard deviation etc. Finally, group your data according to your choice. The following screenshot displays the output of the number of employees by gender in the example database I used. The output is generated by a query on the Employees database with no filter, view set to count the rows and grouped by Gender. The output can be visualized in multiple formats such as a table, bar chart, line and area graph, pie chart etc.
Metabase automatically provides suggestions for filters according to the data. For example, if you are using a filter on a column containing birth date of your employees, you can filter the data by born on a specific date, born in a specific month or a specific year. You can also apply filters such as born before or after a specific date or born between two specific dates. In the next query, I have filtered the data to match the hire date year to “1995”. I also grouped the output by the months. The output is visualized in area graph.
If you want, you can also visualize the data by running native SQL query on the database. Click on New Question and select SQL. Select the database and write your query in SQL format. The output from the SQL statements can also be visualized in different graphs and charts.
Metabase allows you to save the questions you ask for future use, simply click on save link once the output of question is obtained. If you want to run the same question again on updated data, you can simply browse through the saved questions and click on the question to get answers quickly.
Metabase also lets you create custom dashboards, which gives you quick insights into your current data. You can also share these dashboards with your colleagues. To create a new dashboard, move over to the Dashboard tab from the top menu. Create a new dashboard and name it according to your choice. Now add graphs and charts by selecting the saved questions and your dashboard will be ready in minutes.
Setting up Pulses
Pulse is a Metabase feature which enables you to send regular emails or Slack notifications containing important updates to the insights. For Metabase to send emails, you need to configure mail settings in the Metabase administration panel. Setting up emails is also important as it is also used to send password reset emails and invitations. Metabase uses SMTP or Simple Mail Transfer Protocol to send emails. If you do not have an existing SMTP server, you can use the SMTP service provided by Alibaba Cloud DirectMail. It is very cost effective, easy to set up and provides 200 free emails per day.
To create an SMTP server with Alibaba Cloud Direct mail, follow the steps 1 to 3 from this tutorial. Change the email domain, sender address and SMTP credentials, according to your environment. Once you have followed the steps in that tutorial, you will have the following information with you.
SMTP Host: smtpdm-ap-southeast-1.aliyun.com
SMTP Port: 465
SMTP Security: SSL
SMTP Username: mail@example.com
SMTP Password: StrongPassword
The above values are example values, you should find the real values in the DirectMail web console. Go to Admin >> Settings >> Email and provide your SMTP server details.
To create a new pulse, exit the admin and go to Pulses from the top menu. Click on Create a pulse button. Provide a descriptive name for your pulse, select the saved questions whose output is to be sent. Now select recipients and the frequency of the email. You can check if the email is being delivered by clicking Send mail now button. Finally, click Create Pulse button and Metabase will deliver the updated insights regularly right into your inbox.
Conclusion
Though Metabase does not do anything by itself, the insights provided by Metabase are very helpful in understanding different aspects of the organization. We have gone through the basic uses of the Metabase application. We have also configured emails and create scheduled pulses. The next step for you is to invite your colleagues to the platform. Go to Admin dashboard and to people tab, you will find the option to invite your team members. You can also create new groups and restrict the databases accessible to users of certain groups. You can learn more about the uses of Metabase from its official user guide.
