Consultants use External BAQ to point right back to Epicor Database

Epicor ERP 10
,
22

@HLalumiere, not every consultant is conscientious as you. I was like Calvin, a consultant for one company for twenty years. I never withheld source code from any client. Documentation is a start but a reliable script for reproducibility is really what consultants should leave behind. (Did someone just mention DevOps? :thinking: )

Just a few points:

  • Epicor should allow the addition of indexes within the Framework. Sometimes an index is needed, maybe in the _UD table or else where for performance.

  • External BAQs original purpose was to access non-Epicor databases and bring in legacy data or do integrations more easily. It’s a great feature in my opinion. Using it to bypass security or improve performance was not why it was created.

  • I can’t think of a single reason for external DLLs with the advent of REST and Epicor Functions.

  • Other than large data dumps, which shouldn’t be frequent, I haven’t run across a query that wouldn’t complete in under two seconds - and that’s in SaaS. Anything more is generally caused by a poorly designed solution or one based on users trying to make their Epicor system act like their old one.

A good consultant sometimes has to sell a different solution or even a non-solution in favor of a poorly designed one that requires trickery. Many companies are looking at the cattle model and not pets. With ERP systems, we tend to create unique snowflakes/pets out of our systems. But these systems are more difficult to maintain and upgrade. Again, I point people to the book, “The Unicorn Project” to see how an ERP Systems analysis discovers this better method of running and maintaining enterprise systems and integrations.

That’s my $1 worth of input since apparently there is a coin shortage…

23

Agreed, it would make life simpler.

Sure, but SQL is SQL… Besides, there is no difference in maintainability. If you are connecting to a third party database, you still need to remember to update the connection string in Epicor.

For new developments, sure. But you have to remember we maintain code for some customers that was written years ago, before the Epicor code editor even supported Linq. So you can see why in some cases it made a lot more sense to write proper modern C# with Linq in Visual Studio as an external DLL rather than writing .NET 2.0 spaghetti code with a mess of nested loops. Then as time goes by, we don’t rewrite it all just to bill the customer. We maintain what is there. There’s no immediate reason to change any of it.

I write probably 50 to 100 BAQs a year, and I have encountered MANY issues, some of which were bugs that were submitted and corrected by Epicor a few months later, some were just nonsensical errors due to the BAQ not actually executing the query it is showing you, others would run in circles until timeout, again with the actual SQL running in a few seconds. Right now, the BAQ editor is pretty good, and in general works reliably, but just 2-3 years ago that wasn’t the case at all, for example, as recently as two years go, you couldn’t compare an empty string in a join condition in a normal BAQ. Even so, it is still missing many SQL features. Might not bother someone who isn’t used to writing queries, but it’s very annoying as a guy who has been writing SQL for ~25 years, being able to prototype a query that works perfectly and then having to complicate it massively because, for example, you can’t do a SQL APPLY in Epicor…

There are very few customer requests we cannot achieve. Solving the customer’s problem is priority one as a consultant. Even if it sometimes means using filthy tricks. Of course I’m not advocating actively looking for occasions to use tricks, but sometimes you just do what you need to do to ship a working and performant solution.

24

+1 To this statement.

There are plenty of times when allocated budget does not include optimization or pixel perfection. We dont typically have the same luxury as a staff developer to mull over and try different approaches - especially so when it comes to unforeseen roadblocks, which occur plenty ( we get some doozies in regards to creative requests).

As a consultant It is a tightrope to walk between quoted hours for a deliverable solution vs quoted hours for a highly tuned, work of art, particularly so with what customers will accept as a quote. What I think will take 10 hours typically should be quoted at 14 and the customer thinks it can be done in 6.

My suggestion to anyone here is if you have the knowledge, skill and time, you should so the work yourself - because that is what you are paying the consultant for. However, I am sure that most of you know, even with a resource as absolutely fantastic as EpiUsers, it doesnt necessarily solve all your problems. You always have the option as well to use a consultant to the point of working out the tough bits of your project and then maintaining it and making it your own or even hiring a consultant to, well, consult with when you get stuck.

Before becoming a consultant, I certainly had a different view on what a consultant was. I thought a consultant was someone who knew it all, an unstoppable machine. This is why I was concerned about my entry into the field since I most certainly did not know it all (and still dont for that matter). But what I do have is loads of experience on many different aspects of Epicor development, the good and the bad (cough, cough, infinite loop) and each problem that I run into that stumps me, only makes me more powerful.

Consultants are just normal people, they just deal with the subject matter everyday. With all that said, all consultants are not created equal - do you homework.

25

Here here…

26

Whoa, whoa, whoa. Just to be clear, I hope I am not coming off as consultant-bashing here. Like I said, I’ve been there - longer than I haven’t been consulting. And maybe I’m just a little raw in that we just did an upgrade and moved from a co-lo to Azure. This exposed a BPM that called an external .DLL. The purpose was to reduce steps in the process with Lot tracking. Great idea. But we don’t have the source code for it and the person who hired the consultant is no longer with the company. There may be some documentation somewhere but there was no time to search for it during upgrade weekend. And some of us didn’t even know it existed to test for it. Could it have been done in a BPM code-block? Absolutely. Did the consultant set it up this way to guarantee future business? :man_shrugging: If so, it didn’t work.

I will agree not all consultants are created equal. Some are more technical in nature and they tend to use the tools they are most familiar with: C#, SQL Server, Customizations, etc. I’ve talked to MS Access programmers who scoff at ERP systems because their desktop client is so much faster but all of that speed is lost to the lack of integration and the amount of extra work that people had to do downstream to keep the business running. But other consultants have immersed themselves into the business flow and understand how the Epicor toolset works, the transaction flow, and the financial and compliance implications. And yet others think about upgradability and sustainability. Finally, and maybe firstly, we should all consider business continuity. With disasters like hurricanes, pandemics, and malware, some thought to quick recovery is more and more important. (Check your Garmin lately?)

As the book “The Goal” made clear, it’s not the speed of any one part of the system that matters - it’s the speed of the entire system. Users are often the worst offenders of this because they too just want to optimize their own job without regards to work up or downstream.

Fortunately, most if not all of the consultants active on this group are very much in the big picture camp.

27

Agreed. As long as you can explain to the Customer the trade-offs and say “for X we can do it this way, it is in accordance to Epicor Best Practices…” or “for X we can save you 2000$ and do it this way, which isnt horrible but …”

I learned the smaller the organization the easier it is to do anything. Once you get into Publicly Traded Companies or even larger private ones where roles are segregated, you tend to stick to the tools because you don’t just get CRUD Access to SQL. You’re lucky if you even get Read-Only.

No Bashing really. I am just saying I had my share and just want folks to be vigilant. Just recently a reputable Epicor Company sent X Company an invoice for 40hrs worth of work. I asked for the code so I can help X Company assess what’s left… Turns out the 40hrs of work was WIZARD GENERATED CODE with 0 TWEAKS TO IT (Add UD06 to EngWorkBench). Just be careful out there, a Status, a Certifications means nothing. We have plenty consultants here on the forum who ask “What does All Companies Checkbox on a BAQ Do”. Then they go and make External SQL Views. First understand the tools :slight_smile: before making a default “I’ve done it this way decision”.

Also Epicor Supports EFT. Our entire EFT is written in SQL because it was easier? No turns out it was what John Does was familiar with, it isnt bad… But Epicor EFT did already 80% of the work for us.

@HLalumiere you might be an exception, seems like you understand your options, tools and their purpose. Many Don’t.

28

True. But often times the Consultants don’t educate the Customer or the Consultant Company doesn’t use the product as a Mfg Plant would and doesnt know about X functionality or how Centralized AP works etc… Neither does the Customer… so both just think “Customization” when it could have been “Just enable this checkbox or buy this module”. :slight_smile:

If you educate us, the quote can change. Sometimes you will even lose the Quote if you educate me that Epicor can already do X… But you gain respect and long business relationship.

Just had a Customer ask to build them a Epicor Mobile Warehouse App… 50K… sounds fun… easy money… or perhaps I tell them that Epicor already has one and lose 50K… hmm tough decision. I could use the Money to pay off my car. 10K annual maintenance… I could even compete with Epicor and resell it to others if they let me keep the rights… hmm. I’ll prob tell them go Buy Epicor’s and test it and if it doesn’t work come back.

29

I’ll have to read that one; couldn’t agree more with this statement. I constantly find that the conversation around coding a new feature, making a process change, etc., is only concerned with this imaginary ideal linear process where users never make a mistake and every business scenario fits perfectly into some template… no one stops to think about how to make the solution / process resilient and self-correcting when mistakes happen. People are so eager to calculate how much time saving there will be from, say, removing a couple button clicks from a process, but in my experience saving button clicks rarely adds up like people think it does (integrations seem to be the worst offenders of this). If your users are like mine, you’ll know that the lion’s share of wasted time in ERP comes from user confusion and poor communication. Every sales order has 10 email chains associated with it, users spend half their day in Outlook, and yet they think removing a button click or adding some new module is the key to turning everything around… :roll_eyes:

I like to use the fixing-potholes-in-roads analogy when making my case for this type of thinking… Fixing potholes is not about making the traffic (process) go faster; it’s about preventing crashes and damage to the cars – where all the real time is lost…

30

I have been a consultant for 30+ years but a System Analyst by profession. Old generation as you can gather…

If it is not sustainable I don’t solution or recommend it. But at times there are customers that would not accept my recommendations even though I flag in red practicality and sustainability. The fact that I know a lot about Epicor, business practices and have the best for the customer in my heart doesn’t phase some customers, and the solution they choose eventually bites back.

31

Well, this was a hot topic! So here is my story: I started as a customer and saw an excellent potential in Epicor 9. I soon had a job at Epicor as a Consultant. I was soon promoted to Senior Consultant and worked fairly autonomously within Epicor. I now work independently and rely heavily on this forum to keep my skills up to date.

I know that by the time E10 was rolling around, I found I was going back to older clients with E9 and updating the code to work with the “better” practice for performance. While I was no Progress/ABL “expert”, I was definitely a go-to person for questions within Epicor the company. But, I know my skill improved and was even a little ashamed of my old code. However, there was no one to train me and no documents of best practice anywhere within Epicor (Development was off limits and CSG was, well, not reliable).

During this time though, it was apparent that customers were having performance issues. I was often brought on to solve poor BPM/BAQ performance. It wasn’t hard to find BPM code that was 3-4 times too much code as well as causing too many DB calls and memory usage. BAQs were limited in scope in E9, so the fixes were minor, but sometimes, I would use a BPM to populate a UD field instead of a join in a BAQ.

Along came E10. The framework is so much faster that it begins to hide poor performance. This is good and not good. We can see from the use of this site alone, people are posting code and getting responses of either much simpler code, or (even better) logic that removes the need for code all together. We are starting to get a list of things in E10 now that can be considered “best practice”. Even in BAQs, I see complexity where is doesn’t belong. Sometimes however, I have found that making a subquery shaved execution time in a quarter on a BAQ, so “more complex” is often relative.

All this to say, I have not been “certified” since E10.0, but that doesn’t change my capabilities. I definitely am not the top programmer as I am always learning, but I know I provide excellent solutions to my clients. Is it perfect? Possibly, but probably not. It is the best I know at the moment the code was written (like most people). But, the code comes from honed skills, like many of the consultants on this forum.

@hkeric.wci is right when recommending that you have a consultant’s work reviewed by another (non-biased) party until you become comfortable that the work is at or above par.