How to connect ERPNext to Microsoft Power BI

1

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

2

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

3

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

2 Likes
4

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.

5

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

6

Ok for sure send me at jgray@cpxz.us

7

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

8
  • 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.

image
image1064Ă—534 36.8 KB

4 Likes
9

Thank you.
I try that for sure.

10

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

11

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

12

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.

13

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
14

Thank You

15

Thanks Man

16

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

17

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

18

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
1 Like
19

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

Regards
Nivedha

20

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:

image
image1382Ă—650 61 KB

Then set up the ODBC connection using the supplied info:

image
image1468Ă—547 178 KB

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

image
image925Ă—1100 53.3 KB

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

2 Likes