Power BI Odata Alternative

Hi,

This is a good question.  Here are some approaches that I have successfully employed:

1. Have Power BI make API calls directly on Acumatica
2. Set up an external data warehouse.  eg.  For example set up an on premise MS SQL database and maintain the data by either: a) using Power Automate to make the API calls on Acumatica and save the data to SQL (I used some custom connectors here as the out of the box Power Automate connectors to a local MS SQL server leave a bit to be desired - in same cases you find that they only work as described on cloud SQL Server), or b) write a Web API to make the API calls on Acumatica and persist to the external SQL database.

Whether the above is appropriate for you depends a bit on your specific situation I guess .

Also, the above aren’t quite out of the box solutions which is what your questions was asking, but they aren’t far off it.  I would be interested in other alternatives also.

Some functionality that allows you to post a SQL statement to Acumatica via API and get the result would be useful?  It wouldn’t be overly difficult for a developer to implement via a customization (where you could set up a new API call to POST an SQL statement and have that run directly on the DB).  Maybe that’s out there somewhere already and available as an ad on?

I did a quick Google search and found this article from Aug 23 which is relevant to your question:

https://www.augforums.com/an-acumatica-sql-query-idea/#:~:text=You%20can%20run%20a%20SQL,directly%20query%20the%20SQL%20database.

Have only skim read it but saw the “View” option which is something I didn’t immediately consider above.  I disagree with the couple of problems mentioned below (eg. tedious):

The reason I disagree is that if you build the View first, publish, you can create the Data Access Class (DAC) on top of the view just like you can any other table and Acumatica will create the DAC automatically.  Typically I have used views when you want to report on data across tenants in a multi tenant solution, so I see this as a good thing as opposed to a problem.

Is a good article though.

My use case is  generally Web Apps that sit on top of Acumatica and half a dozen other ERP systems so I pretty much always go down the 2a or 2b route.

Thanks,

John.