Epicor/Kinetic provides a way for us to access databases other than Epicor’s own using BAQs, and providing that data to dashboards. How’ve you gone about using that feature?
I’ve got two uses, now:
Using Kinetic to access unstructured or differently-structured data from our previous ERP (or more specifically, tables copied to a separate database under our main SQL managed instance)
I’ve also made a custom EFx library for sending automated emails based on complex templates
How many of y’all use external data sources and external BAQs?
We have used it for years now to access data outside ERP. Works great.
We primarily use them for dashboards and external reporting from within Kinetic.
We use it to get data from the old ERP, our nesting software and SolidWorks PDM, all on SQL databases.
But the On-prem deprecation is pushing us in another direction. We are basically forced to move to the cloud at some point, and there are no alternative to external baq for the cloud… other than RestAPI of course
We’re on cloud and got the read only DB…so we’re able to tie in Kinetic data with external data on the local side. Fair amount of Access development here for reporting purposes. There’s still a “gotcha” with Access though…it doesn’t like ODBC connections to tables with too many indexes (Customer, JobHead, PartTran among others). Workaround is to build a connection in Excel via PowerQuery, update THAT spreadsheet and then refer to that in Access as a data source.
If you have EDA, you should also be able to combine Kinetic data with external sources via Sync.
What we do for example is creating an external baq that joins data from Epicor and the nesting software. Then, this external BAQ is used in a dashboard directly in Epicor. We like that solution since everything stays in Epicor, so no Excel/Access files. Those dashboard can also be updatable.
The key here is joining data from different system in the same query, then feed it to an Epicor dashboard. I do not know any way to do it in cloud.
I used an external datasource once to do multi-year analytics. I was asked to replace an Excel spreadsheet with updated numbers for the current year using a BAQ. This would take several minutes to run on our live database (or time out), so I moved the number crunching to a PHP server + Availability Group copy of the DB, where I could break up the processing in smaller iterative chunks, and then pulled the result back in with an external BAQ.
Also, I once used a PHP script on an external DB copy to answer the question “What menu items can a user access?” Seems like a simple question, but you have to perform thousands of iterations in a loop, build up an access list in some kind of array, and discard the duplicate results. Now all I have to do is select a username in a dropdown, and I can see what that user can access.
That is how i think we will be using it when we have our second site ‘Live’ as they currently have a lot of 3rd party reporting from various databases that is ‘External’ of ERP and it would be much better if it was done through ERP.
We had a bunch of history on our old ERP (SAP) for different units we built and serviced. Rather than keep that old infrastructure going (a huge security problem) we migrated the information to an access database and then used the SQL upsizing tool to bring this into a proper database. I built dashboards over the various tables and now staff can look at that history without IT having to keep an old system going. We have a “problem” that some of our trailers continue to be used past 30 years so the history for those continues to be accessed.
We also had a spreadsheet that engineering had used since the company was founded in the 80’s with the serial numbers of all the units we built. They had a tab for every year with slightly different columns in it. It was a ripe candidate for corruption so I converted the whole thing to a SQL table and built an external BAQ.
I have other applications that I have ongoing monitoring powershells for tablesizes and server stats and the external BAQs bring visibility into the Epicor menu.
We have our UKG (payroll) cloud service import into a UD table but I built an external table to track changes. I compare last week to this week every Sunday and can tell when someone was added or terminated. (Our HR department is notorious for not informing us on terminations)
The ability to have one BAQ call another opens the possibility for a BAQ based on Epicor tables could be joined to an external table but I have not tried that yet.
We recently migrated to Epicor Kinetic, and one of the challenges we’ve been facing is getting sufficient guidance from the Epicor team regarding external data connectivity and integration.
I would greatly appreciate it if you could share any information, best practices, or documentation on how you established the external connections and integrated Kinetic data with external sources. Your approach using Power Query and Access is particularly interesting and could be very helpful for our reporting requirements.
You’d need to order the read-only database connectivity option - contact your CAM for pricing. I don’t have specifics but I think it’s tied to number of licenses or companies or something of that sort. Once it’s set up by Epicor, it took me maybe 15 minutes to switch and test our ODBC connections, moving from our original on-prem configuration to our new cloud instance.
Don’t really have any documentation on it…all I got was the new cloud server address, port and user/pass. A lot of it was trial and error for making our reporting work - as testing progressed, I found it easier to download Kinetic data to temp Access tables…the ODBC performance was slow if it was a mixed bag of Kinetic R/O and local tables.
EDIT - look for KB0105139 in EpicCare…that’ll help you get the ball rolling.
