How to connect ERPNext to Microsoft Power BI

Anyone know what would be the best way to connect ERPNext to Microsoft PowerBI?

Depending on what you mean by best - two options are to search here for tips or enlist a consultant :wink:

While this is not what you are looking for the process should be similar. They both offer business intelligence.

2 Likes

Ok. So can this be done? Or the better question would be can I consultant show me screen shot of ERP next, working with Power BI and I will be glad to pay for the consultant.

I have done it for a customer. I can send you screenshots early next week.

Ok for sure send me at jgray@cpxz.us

Thank you for this i will try it and let you know.

  • You will need to install Power BI Desktop on a server that has network access to the ERPNext instance.
  • Within Power BI Desktop, add a data source of type MySQL and connect to ERPNext database
  • Build your query and Publish to the Web.

4 Likes

Thank you.
I try that for sure.

Hi lefebvre_bern,

Nice work there, how do you install Power BI on your Server?
I have a Server that i can only access through my terminal.
Please what do you suggest i do ?

Await your Response
Regards

hello Jermaine,

Trust this meets you well.
I want to confirm if you were able to get Power BI with ERP Next Working
Hope to hear from you soon

Regards

No i did not get around to do it.
However you need to ensure your MYSQL server have remote access if you are connection over internet.

Hi @kolotayo

It has been a long time since I have worked around this, but I think that the steps I described above are still good.
As I mentioned, Power BI needs direct access to ERPNext’s database, so ideally Power BI needs to be installed on the same network as the ERPNext server. Also you need to slightly modify the configuration of MariaDB to allow connection from remote host. The rest is pretty straightforward.

1 - Download Power BI desktop Power BI Desktop—Interactive Reports | Microsoft Power BI
2 - Within Power BI Desktop, add a data source of type MySQL, point it to your ERPNext instance and connect to ERPNext database
3 - Build your query and use the built-in feature to publish to Office 365

2 Likes

Thank You

Thanks Man

Dear if possible send the screenshot to me as well gsbaig@gmail.com

Hi @lefebvre_bern, how to connect the ERPNext database to PowerBI? could you please share the step-by-step guide to connect and access?

Regards
Nivedha

Currently there is no direct way to connect PowerBI to ERPNext database. There are 2 workaround solutions available;

1) Using an alternative to PowerBI (Metabase)
Metabase has good enough BI capabilities and can be hosted on the same server as ERPNext. Follow this guide for detailed instructions: [Guide] How to install Metabase (dashboard) along with ERPNext on Ubuntu 18.04 server

2) Using multiple instance groups

  • Setup a mechanism to take a daily backup of ERPNext database
  • Import the backup dump on a MySQL server (acts like staging area) that support remote connections and then connect to it using PowerBI connector / API gateway
  • You can also setup the staging server on localhost if you do not want to get into the API gateway step

Hi @shashank_shirke, thank you so much for your kind reply. I will work on this solution and let you know

Regards
Nivedha

Hey, if anyone is on a hosted plan, it’s super easy to connect to Power BI and Excel through the ODBC driver.

Just go to Frappe Cloud and click this button:

Then set up the ODBC connection using the supplied info:

Now, when you go into Excel or Power BI, just insert data using the ODBC connection. This will connect directly to the database:

Just FYI if you are on a hosted plan. Not sure if any of this is possible with self-hosting.

2 Likes