Back in 2018 I created a post Direct access to Microsoft SQL Database?. The information the group provided was extremely helpful, and I shared the risks of this approach with the decision makers but ultimately they decided to allow direct DB access as it enabled our developers to create new reports significantly faster than the alternative.
With the announcement that on prem is no longer supported, and the fact that in the cloud direct DB access is not possible we now have about 8 years of custom reports that will break when we upgrade to the cloud… So I’m reaching out again looking for help…
In addition to needing to rewrite all the custom reporting code, there is a constraint(non-negotiable) that we must comply with: several people in leadership roles don’t use Epicor, but need the data in Epicor to make decisions. The custom reports that use direct DB access expose the report as a web page to meet this requirement. So I need a new solution that allows me to expose custom reports to people that don’t use Epicor. I’ve been talking to people and searching around and I’m thinking PowerBI could be a good path forward but honestly I have zero experience with it.
What recommendations do you have given this situation and the constraint?
One detail to add is we are on 10.2.400 and we are not in a rush to upgrade, but I do want to find a solution to minimize our technical debt from growing any more…
That’s why using a data warehouse/data lake instead of direct access is what you should look into because the Extraction, Transformation, and Loading of data will always stay the same, and isolated, no matter what ERP, what source system, etc… that piece will stay the same. And then you can hook Power BI, GROW, whatever to that.
We have read-only cloud DB connectivity in use via ODBC/Access. When we jumped to the cloud it only required a change to the connection. Relinked the read-only tables and it worked like a charm. Only gotcha that I found - the Customer table wouldn’t link in Access via ODBC (too many indexes). Set up an Excel file to connect instead, then linked Access to the Excel file.
Really, I thought direct DB access was not an option in the cloud. Do you know if this is available for all customers or was there some extra fee or agreement you had to make with Epicor?
Extra fee…don’t recall what it was exactly. The read-only did have limitations from the Microsoft Access side…probably YMMV depending on what toolset you want to use.