Lately I installed Metabase on the same virtual machine that I use for ERPNext. I was using Microsoft Power BI and I loved it but I didn’t like the fact that I had to use the On-premises data gateway on my Windows computer to refresh the data a few times a day. I wanted to use real-time data. When I initially tried Metabase, I liked it but it was not as easy to use as Power BI when I wanted to use data coming from more than just one table. I found out that you could achieve pretty much anything as long as you are good at building complex SQL queries which is not one of my best skills. There was also a lack of customization (for example column names) that I didn’t like in Metabase.
While doing researches, I stumbled upon a free software: FlySpeed SQL Query
The free version if fully functional and you get everything you need to easily build complex SQL queries that you will then be able to use in Metabase to get all the data you need. You can use the visual SQL query editor, link tables, sort and make calculation easily and then copy and paste the generated SQL query in Metabase.
I can now stop using Power BI and create all of my Dashboard in Metabase (hosted on the same server and accessible on the same domain name as my ERPNext installation).
If you need help setting up Metabase with your ERPNext installation just let me know and I will help you out. I’m using a Amazon Web Service EC2 virtual machine and it took me a few days to get everything working. I’ll be glad to help if you have the same kind of setup.
@bardbq This is really great info (even if much of it is over my head!). Let me start with a really basic question. What size EC2 instance are you on at AWS? Do you have a sense of how busy that server is and what a right-sized server would be?
Again, thanks for blazing this trail for all of us!
I started off with a t2.large (Ubuntu) EC2 instance since the old m1.medium instance I was using with OpenERP (Odoo) was no longer available and… I was also looking for more performance. The t2.large is perfect for ERPNext. I started with a 10Go volume and switched to a 20Go volume after a while because I needed more space. I use a reserved instance since it’s cheaper. The instance runs 24/7 and the cost is about 0.072$/hour… Monthly cost is about 50$. ERPNext would run on a t2.small and t2.medium but would be slower (lest RAM and CPU power).
You should do like I did, start with a free t2.micro Ubuntu instance and see how it goes… If you need more speed and power then you change it to a t2.small then a t2.medium and eventually a t2.large like me. You can go up to t2.xlarge and t2.2xlarge for heavy usage but I don’t think it would ever be needed… The great thing is that you can change the instance size easily within 1 minute and you don’t have to re-setup/reinstall anything… just re-launch the instance and voilà!
Start small and go big according to your needs. I ran a free t2.micro for a while.
As of now, I’ve got 4 users using ERPNext at the same time while I’ve got Metabase updating my dashboard every minutes and CPU utilization didn’t went over 6% (average is about 4%)…
Before I started using OpenERP (about 2½ years ago) I had no Linux knowledge and I didn’t know what a virtual machine was… I started with a pre-installed Bitnami image of OpenERP on Amazon Web Services and started playing with it… When we switch to ERPNext in June/July 2017th I decided to use a black Ubuntu image and installed everything myself using the setup guide (and installation script) that you can find here on GitHub.
I learned a lot in the whole process and I’ll be glad to help you out if you need assistance!
@bardbq thank you for this insight. Currently we have ERP Next on AWS. But i have a local instance i use to test all my upgrade before applying on live Database. i am having Joining Issues.
I want to Join Sales Invoice Item and Sales Invoice but there is no similarities in the two tables.
What do you suggest i do? As i will be doing more of Joining to get my dashboard.
Hello @kolotayo, check for the “Parent” field/column in the “Sales Invoice Item” table, it’s related to the “Name” field/column of the “Sales Invoice” table!
using: SELECT
si.name, si.customer, sii.item_group, sii.item_name, sii.qty,sii.rate, sii.amount
FROM tabSales Invoice as si
INNER JOIN tabSales Invoice Item as sii ON si.name = sii.parent
WHERE {{date}}
LIMIT 5000
I am trying to install and get Metabase running on the same server as ERPNext but unable to get it to work. I’ve tried multiple instructions available on many different sites but nothing seems to work.
As per my understanding, the default ERPNext installation can be accessed via the browser through the default 80 port so I would want to access the Metabase installation via a differen port (for eg.: http://[IP-Address]:81)
My current setup is an Ubuntu 18.04 server with ERPNext fully running on it.
If you can provide me detailed step-by-step instructions on the list of commands to execute on my server so as to accomplish the goal of running ERPNext and Metabase, it would be really helpful.
