Erpnext To PowerBI

ERPNext
1

Hey all,

I was wondering if there is a way to connect ERPNext (self-hosted) to PowerBI.

I have researched the following options:

  1. ODBC Driver: We can connect using an ODBC driver, but once the report is published, I was not able to refresh it in real-time.
  2. VirtualBox: Using VirtualBox on my Linux-hosted machine is not an option due to the machine’s load capacity.
  3. Data Dump: Dumping data from ERPNext to another Windows-based system is not feasible as it may violate data privacy for some users.

Any suggestions or alternative methods would be greatly appreciated!

2

Hi @dixit_fadadu:

Use ERPNext Rest API (“Web” data source) from PowerBI.

Hope this helps.

4

yes , i have tried that way if api secret key is changed then we need to republish this whole process so i have tried with custom query where firstly i was calling login api from that i was passing apikey and apisecret to my report api but using this method when report is published and if someone download our report they can access user id and password

5

Hi @dixit_fadadu:

  • Create your report in desktop app, from blank report. Use Basic authentication to get data (api_key as user, api_secret as password) in datasource configuration. This way you won’t need to specify authorization header on api call
  • Publish your report on PowerBI
  • Change semantic model configuration, and add credentials.

After credentials change you will need to update this, but you can do it on “cloud” PowerBI, update on desk side is not needed … api_key / api_secret wouldn’t change often … isn’t?

Anyway … tried yet Frappe Insights?
It’s fully integrated with ERPNext :wink:

Hope this helps.

6

@dixit_fadadu

To connect Power BI with ERPNext, you can use MariaDB as a data source. Enter the required parameters such as Host, Database Username and Password.

  • If you are on frappecloud, obtain the read-only database credentials provided by Frappe Cloud.
  • And for self hosted, create a read-only user in your MariaDB database and use those credentials.

You have the option to use Import Mode, Direct Query, or Dual Mode, depending on your data modeling needs and requirements.

1 Like
7

yes we can connect that way but refresh functionality won’t work in this case.

8

Indeed Incremental refresh will not work, but manual refresh on desktop, scheduled refresh of published report, specific interval refresh of direct query are all we could get with database connection.

9

Alternatively, you could also try using Frappe Insights for reporting. The integration is pretty simple: https://docs.frappeinsights.com/

1 Like