Visualizing ERPNext data using Metabase: Step-by-Step Guide

This is my humble attempt at providing a step-by-step to using Metabase with ERPNext

I am running Metabase v0.27.2 on Windows 8.

metabase00

Unzip the Metabase jar file into a folder. Navigate into the folder and run this command

java -jar metabase.jar

metabase01

Make sure JRE installed.

After running the command, wait for Metabase to be ready.

Open your browser and access through your URL at port 3000

Example:
http://localhost:3000

You are presented with a login page. If this is your first time running it, a setup page will be displayed. You can setup the first user who is an admin by default.

Login with your email and password.

After successful login, you are presented with a welcome page.

On the right top corner, click on the button which has your initials and click on Admin Panel

On the top menu, click on Databases

On the right hand side, click on Add Database

Database Type: Select MySQL

Name: Provide any name that will help you to identify the database connection.

Port: Leave it blank or if you have changed it, provide MySQL port

Database name: Enter the database name here. you can find the name at frappe-bench/sites/[site]/site-config.json

Database username: By default, MariaDB does not allow remote connections. Check this post (Remote Database Setup) to see how to enable remote connection and create a user to connect.

Database password: Enter the password you provided in the previous step

Click on Save.

If all goes well, you will get a confirmation message and the connection will be added. Click on Done.

Click on your initials at the top right corner and click Exit Admin.

You will return to the welcome page. Click on New Question.

There are three options: Metrics, Custom, Native Query. The explanations are below the icons.

For now, we will go for Custom.

Under Data, choose the name of the connection you created. I named mine ERPNext.

After selecting it, a list of tables from the database will be populated. You can search or scroll to the table name. In my case, I selected tabLeave Allocation.

It shows Raw Data under View. This means it will dump everything from the table. Click on Get Answer to display results.

Under the visualization, it shows table. There are different ones you can try. The non-applicable visualizations will be greyed out.

We can customize the table by clicking the cog beside Table. This will help us display the relevant info.

You can unselect some headings and drag upwards or downwards to rearrange them. A preview is displayed on the right side. Scroll to the bottom and click Done.

You will see your new table.

Click on Save to save this question. You can use this process to try out different visualizations for your database.

You can choose to create a dashboard now or later.

Click on Dashboards in the top menu. Click the plus (+) icon on the right side to create a new dashboard.

Provide a name for the dashboard and an optional description.

Click on + icon to the right to add questions you created. Once they are selected, they will placed on the canvas. You can maximize or minimize to suit your needs. You can add more questions to the canvas.

Click on Save when your done.

The middle icon of the 5 on the right hand side is Sharing and Embedding. This is used to share the link of a dashboard and embedding the dashboard in an application aka ERPNext.

That’s a story for another day.

Click on the switch to Enable sharing. Paste the first link in a new tab and the dashboard will be displayed.

I hope I did my best in addressing this. Criticisms, opinions are welcome.

29 Likes

Many many thanks for your excellent writeup.

Can we get this as a promoted post or something ? I look forward to trying this out .:grinning:

2 Likes

Thanks will try out Metabase.

I am using Apache Superset https://superset.incubator.apache.org/ for dashboards and visualization. It is also easy to setup, provides roles and users provisioning.

2 Likes

Hi Yogesh, pls share a guide as well? Thanks

@kevingee What a great write up.Thank so much.Can you please help us to do the write up(step by step) how the database of ERPnext can be linked into Microsoft Power BI.Highly appreciate if you can show us.Thanks
Fred

1 Like

@kevingee thank you for ylur efforts. Highly appreciative of put this out for users are benefitted v

1 Like

Refer to Superset documentation for installation and configuration. You can directly configure your site database as datasource and start creating dashboards.

Hello @yogeshak, please can you share a step by step process of setting up and linking Apache Superset with ERPNext

Greate wok with me
Thanks

Hi got it working and now it doesnt work anymore. Everytime I try to access the metabase on port 3000 I get connection failed (ERR_CONNECTION_REFUSED)

I checked the firewall and everything but I dont get it. Should we make any modification to the /etc/nging/sites-available/default?

Any ideas? Thanks!

Ensure that port 3000 is not being used for some other service.

For more details refer this video.

1 Like

Thanks for taking time to record this and sharing it.

1 Like

refer,

3 Likes

Will superset support Rest API data source?