Question

Data Mining with products outside of Epicor


We are a cloud based Epicor Kinetic user and a manufacturer. We are now in our 4th year of using Epicor and seem limited on the data mining ability that is within the system. We have been looking at items like Power Bi and Domo and Pulse but the main constraint seemed to be access to the data in Epicor.

 

  1. Are any of you using any of the above products with your systems?
  1. If you are just using Epicor dashboards and BAQ’s are you able to gather any data you need?

 

 


11 replies

Hi Norman,  I have only been using Epicor for about 8 months and have found similar issues with extracting data using canned reports and BAQs.  The version of Epicor that we are on (9.05.700B) uses Crystal Reports to view reports so I simply installed Crystal Report writer on my machine and created the ODBC connection to the Epicor server on prem.  I create my own Crystal Reports and run them directly in Crystal then export to excel if needed.  My next step is to add a few of these reports to the Epicor menu so the respective departments can generate them as needed.  Hope that helps. 

KJ22,

Thanks for the information. The issue we have is that Epicor does not want to give us access to the data to create the ODBC connections on the cloud based solutions.

Wow.. this is long… Hope you brought coffee TL;DR - Get something purpose-built to do your reporting/bi. Build your own data sets/cubes/data warehouse outside of Epicor. 

Now, for the meat. 

I’ve been in the Epicor ERP ecosystem for over 12 years, first as a user, then a consultant, and now IT Director. I love the Epicor ERP product. Legit, it is the best option out there in the mid-market ERP space. 

Now… From where BAQ/EDD etc. started, Epicor have made SIGNIFICANT improvements. However nothing compares in power, sophistication, and simplicity to using good ‘ole SQL to mine the data you need and then present it with a true BI/Reporting tool. The only advantage I see to using anything that is delivered with Epicor (BAQs/”dashboards”) or sold as a bolt on (EDD or Phocus products) is that it is displayed in the Epicor app and you can jump directly to the trackers. Outside from that,  I have been building far superior data tools outside of Epicor for years. 

The other issue we see with Epicor is that they tend to take a “flavor of the week” with their reporting tools, forcing you to buy a new one every few years and no longer supporting what you had before. Example: We started in FRx with Epicor Vantage (version 8), then we had to buy XL Connect 5 to upgrade to E9. We were then pushed to XL Connect 7 when we upgraded to E10. This spring we just signed off on over $25K for Spreadsheet Server to work with Kinetic since XL Connect isn’t supported any longer. I wonder how long they will stay with Phocas..

What to do..?

Use the power of the SQL database you already own! Standup a Reporting DB on your existing SQL server that points to your Epicor DB. Then develop SQL stored procs that are then called by ANY reporting tool you ,ay choose. 

Advantages:

  1. Primary advantage: Upgrade proof! So long as Epicor remains on SQL, you can use this! When you upgrade to Kinetic or whatever comes after, touch each stored proc once to validate any database schema changes and you are in business. (Really..  in 2018 we touched over 200 stored procs feeding thousands of dashes and reports in just over a week when upgrading from E9 to E10!)
  2. This allows the SAME data set to be presented to multiple tools (PowerBI, SSRS, HTML, heck even Crystal Reports if you still use that.. whatever you want). No need to rewrite a dataset over and over again!
  3. This stored proc method also allows you to use a change control process, which I highly recommend.
  4. This eliminates trying to reconcile why reports don’t match because someone built with they thought they needed without truly understanding the complex data structure in Epicor. 
  5. Allows you to choose a reporting/BI tool that serves specific purposes. Heck, feed data into different systems! 
  6. Heck.. build your own data warehouse and COMBINE data from other sources WITH Epicor data.

Of course… all of this requires that you are an On Prem customer. Cloud customers will be SOL and relegated to what Epicor has to offer.

Happy to discuss this with you, just reach out! scook@districtphoto.com or sabrinaecook@gmail.com 

Sabrina,

 

 

WELL...that was a mouth full of excellent information. I am cloud based so the last line hits to close to home.

I have very little IT in house and Epicor wants me to move to public cloud then pay for a 2nd company just to regen data each night . They will then allow me access to that database to pull itno analytic tool of my choice.

 

Don’t really enjoy this answer from them.

Userlevel 1

Not sure if this is something that you’ve already looked into, but we (as cloud customers) use BAQs called through the RESTful API to feed data into external applications that we have developed, as well as to pull data into Power BI through an OData feed. It can be slow at times, depending on what is being pulled and the complexity of the BAQ, but overall seems to work well for us.

I know that our software development team has built, essentially, a data warehouse external to Epicor that they have update on a schedule (not sure what it is, but could be as often as minutes or less) to pull the data they need from Epicor using REST, so that their applications can grab that data quicker from the “warehouse” than pulling directly from Epicor.

This is helpful as well. Not well versed on the REST API. I might get back with you on this if that works with you?

Userlevel 1

We have just begun looking at EDD/EDA and a third-party organization called BlueSky.  BlueSky accesses your Epicor data, to create Kinetic style dashboards.  I believe the BlueSky product uses PowerBI to generate the displayed data, which also allows for data-mining, based on how the display is designed.  We are on-prem and having issues getting a connection through our Firewall to perform a proper review of the BlueSky application, as it is cloud-based.

BlueSky may be a better option for Cloud Epicor instances than on-prem.

Thank you for the reply. I will look at that one.

 

 

Userlevel 1

Hi Norman, we are an Epicor partner. 

At this time the best solutions we found to perform data mining on Epicor data is : 

  • REST API (this is ok for small amount of data). We use it to access Epicor data in Excel PowerQuerry, PowerBI, Tableau, ClickView...)
  • FastClose. This is a reporting solutions that allow to explore Epicor data and help companies using Epicor to perform faster closing.  The solution exist for different version of Epicor 8, 9, 10 and they have recently now the possibility to access Kinetic Cloud Epicor data. The advantage of this solutions is that it has already all the view prepared for you, so you don’t have to build them in the BI Tools. As an example, FastClose can prepare the data you need for your PowerBI DashBoard. 

You can contact me at tco@pse.be so we can show you some examples of those solutions.

Have a great day,

Thierry

 

Userlevel 1

This is helpful as well. Not well versed on the REST API. I might get back with you on this if that works with you?

 

If you go to

**your server name as shown within Epicor**/api/help/v1/ 

or  

**your server name as shown within Epicor**/api/help/v2/

It will prompt you for your Epicor login credentials to gain access. Once you’re in, there’s a lot of helpful information there to help you navigate using the RESTful API.

Userlevel 1

I will echo what has already been said about Epicor REST services.  We are on prem using 10.2.500.  I am not technical, but have been the primary writer of our BAQs for data extraction.  I am also the consumer of the data.  I have built many ‘dashboards’ / reports for excel, where I extract the data using REST and OData Feed and Power Query.  For a quick and dirty solution, it really cant be beat. 

I have used PowerBI to craft some reports, but knowing how to write dashboards and reports in PowerBI requires a pretty decent time commitment.  

 

We have also used XL Connect and, while good, it does not have great training or support for the product (at least I have not encountered it).  

Reply