Connecting to cloud-hosted ERPNext 9 from BI tool on PC via ODBC

ERPNext Reports
1

I like to use the free Microstrategy Desktop Business Intelligence & Analytics Solutions for some advanced data analytics on my PC. I have latest ERPNext v9 hosted on erpnext.com cloud. Microstrategy is capable of connect to MariaDB via ODBC. Is it possible to establish such a connection?

I have seen in older posts (year 2015) references to integrating Pentaho. But Microstrategy is currently #1 in ratings and a standard BI tool for some of my clients and potential clients. Plus since it is free, it adds value to the customer experience and satisfaction with ERPNext, without adding any licensing costs.

I hope I can find a way to do it that doesn’t involve quitting the cloud and using ERPNext on local server. Please help. Thanks.

2

I don’t think Erpnext / Frappe cloud allows external odbc connections for security reasons but someone from the core team can confirm this.

3

I am curious to understand how you plan to use BI with ERPNext. I tried to connect my self hosted ERPNEXT with metabase. But did not find sufficient use case to explore.
World be great to collaborate.

4

Hi @Deepak_Pai. Data exploration with BI tools is usually driven by each company based on their specific need for statistics and finding trends and insights that are relevant to their respective departments (sales, marketing, finance, strategy, logistics, etc). In some cases these BI tools use ERP as one data source and merge the ERP data set with other external data sets to deepen the analysis. If in your case you can’t think of such a use case, then there is no benefit to dig deeper. The problem/need has to come first, the solution afterwards :slight_smile:

5

Hi @Pawan. I hope we can find a solution whereby users can directly analyse the data in ERPNext with external BI tools. The other imperative in many companies today is to have a data warehouse where data is loaded from multiple sources including ERP. This is done using ETL tools such as open-source Talend www.talend.com . Both these scenarios (real-time direct BI and indirect via data warehouse) are requirements at least in mid-market companies. We should be promoting the use of data analytics in business as data-driven organizations have competitive advantages.

6

Good insights but I don’t think any of the cloud ERP in the market allow external DB connections, do you know of any?

7

I was scanning through old posts and found @rmehta suggesting the use of API GitHub - webnotes/wn-client: Talk to wn framework over HTTP as alternative to Data Import. I don’t understand what this API is doing, but can we use APIs to extract data out of ERPNext? That may be an alternative to ODBC connection.

8

REST API’s are even available now, you can use them. Just search for erpnext rest api you should find many resources.

2 Likes
9

Thanks for sharing this query and the solution.

10

I attended a Microstrategy conference and met some of their staff who said it is possible if we give them some specs that they develop a custom connector for ERPNext. What is the best document(s) I can supply to Microstrategy team so they understand 1) if a custom connector is needed and 2) if yes then how to create it.