Consultants use External BAQ to point right back to Epicor Database

[hkeric.wci (Haso Keric)]

@tmcgrady

i would be curious to know if these were Epicor consultants or 3rd party. Most 3rd party are not Certified and you run the risk of something like this. Please feel free to share these names with your Epicor CAM, not here, so we may look into this. Keep in mind with something like this if it breaks something support is not obligated to fix it as you may very well have voided your software warranty.

Agreed… I have even seen Epicor Gold/Platinum whatever Partners do everything wrong in Epicor… I wont bash them here or mention names. But there are a handful that hire juniors and charge you 200+/hr and write crap code. If any one ever needs a code review before you pay an invoice, send it in pm - free of charge!

I used Epicor for 8yrs and customized everything to the max… Multi-Company, Multi-Site, International and RARELY do I need to use External BAQ or do stuff outside of Epicor. If you use Epicor properly and have proper processes you can stay within Epicor just fine. #myopinion

I’ve seen people do direct datasets just because they dont know any better how to use RDD. They connect directly to pull a column, funnier even the column exists in the base RDD just needed to be added to the SELECT Statemenent.

[TomAlexander]

I’ve seen this quite a bit and also question why it’s done so often. The security reasons make sense on paper, but I feel those business cases are very rare in real life. I think it’s more a reputation thing that caught on of “external BAQs are faster”. Sure, maybe they are a little faster, but they are also a maintenance problem. When I was a consultant I didn’t think enough about maintenance / sustainability when delivering a solution. Now, as a customer, that’s all I can think about.

[Mark_Wonsil]

@TomAlexander

When I was a consultant I didn’t think enough about maintenance / sustainability when delivering a solution. Now, as a customer, that’s all I can think about.

^^^^^^
This.

You hear that consultants? That goes for all customizations. Think of upgradability and maintainability with our customizations. External queries, alterations to the database (views/indexes/triggers/…, custom DLLs, etc. all can make sustainability more difficult.

[jgiese.wci (Joshua Giese)]

@Mark_Wonsil

You hear that consultants? That goes for all customizations.

The bums will always lose Lebowski

[Mark_Wonsil]

Is Lebowski the head of Professional Services? I’ve seen some productization that ain’t very upgrade friendly.

[jknigga (Jakob Knigga)]

@hkeric.wci

But there are a handful that hire juniors and charge you 200+/hr and write crap code.

I had wondered about this. Some of the customization stuff that I have looked through is just mind boggling. I definitely wondered if the use of the external BAQ was something similar. Basically “this might not be the right or best way, but it’s the way I know.”

[HLalumiere (Hugo Lalumiere)]

I wouldn’t say using external BAQ’s is “wrong” per se. The mechanism is there to be used. Of course building a regular BAQ is preferable for everyone, because of simpler deployment, security, etc… But if I build a regular BAQ and it takes 20 minutes to run, while the exact same query runs in SQL in 1 minute, guess which one is going to get used. There are also queries that are simply impossible to recreate in a normal BAQ. Any query that uses OUTER APPLY, or temporary tables for example. Or a query that needs 10+ subqueries, or multiple PIVOT/UNPIVOTs. Just too cumbersome to maintain or debug in a regular BAQ IMO.

At the end of the day you do whatever you can to make the customer happy. But I agree that a regular BAQ should come first before thinking about an external BAQ.

[HLalumiere (Hugo Lalumiere)]

The flip side of this coin is that sometimes external DLLs, external queries, or additional indexes in the database can significantly improve performance or usability for the customer. If the consultant keeps proper records of everything they develop and package, there is near-zero difficulty added to deployment. Issue may come out if the customer stops doing business with said consultant and neglects to make sure they have all the proper documentation (or if said consultant neglected documentation themselves).

Ask any customer if they prefer a query they use every day running four times as fast versus a half hour more time in deployment when they upgrade.

[hkeric.wci (Haso Keric)]

@HLalumiere

I wouldn’t say using external BAQ’s is “wrong” per se. The mechanism is there to be used.

Absolutely, the right tool for the job I always say. But unfortunately some use it as their default and use Views as a default because it is easier, yes it is - absolutely. But once they try to use the tools it’s not so bad.

I have a BAQ once pasted into Word it spans about 43 pages, it runs in about 9 seconds (CTEs, Recursion on JobProd, PartTran, UNIONs etc…) and it even has a PIVOT for RefCat. Maybe it’s just the way I lay my BAQs out strategically.

1. RAW Tables first
2. Work my way out to Metadata - for example there is no need to join on Customer in SubQuery1 with TranGLC – I can get the Customer Name in TopLevelQuery.

BAQ Speed Improvement TIP

The Way I Segregate Sub-Queries Some Notes I gave to someone for replicating something like Chart Tracker. Get Transactional RAW Data with no meaning (ParTran, TranGLC etc…) Join on tables that give it more RAW Data (LaborDtl, JobProd…) Join on tables that give it human readable meaning (Customer, VendorPP, APInvHed, APInvDtl) Encapsulate via Sub-Queries, make it modular as much as possible for maintenance, addition and what data is necessary to query first and what can wait till end…

But Agreed - Right tool for the job. Also usually when we do have this massive giant Queries they are already outside of Epicor in EDD, EDA, XL Connect, PowerBI.

Typically the Consultant Companies that complain about speed (this is how their BAQs Look Like) [ Not Fake ]:
WTF - #PartnerOfTheYearAward

Their BPMs re-invent the wheel:

You have to rewrite almost everything (left is new one after I was done)

[hkeric.wci (Haso Keric)]

PS: In the Cloud you have to use the Tools, no bypassing it.

[Mark_Wonsil]

@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? )

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…

[HLalumiere (Hugo Lalumiere)]

@Mark_Wonsil

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.

Agreed, it would make life simpler.

• 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.

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.

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

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.

• 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.

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…

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.

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.

[Chris_Conn]

@HLalumiere

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.

+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.

[Hally (Simon Hall)]

@Chris_Conn

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.

Here here…

[Mark_Wonsil]

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? 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.

[hkeric.wci (Haso Keric)]

@Mark_Wonsil

I will agree not all consultants are created equal.

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 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.

[hkeric.wci (Haso Keric)]

@Chris_Conn

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.

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”.

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.

[TomAlexander]

@Mark_Wonsil

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.

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…

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…

[emarkus (Elka Markus)]

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.

[Jason_Woods]

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.