Business Activity Query (BAQ) writer is a very powerful tool in the Epicor ERP suite. So powerful, in fact, that it’s a bit like a chainsaw – really good at what it’s meant for, but if used without the proper knowledge and training it has the potential to cause some real damage. This damage may come in the form of some unexpected results or an impact on system performance. Not quite the same as losing a limb in a chainsaw accident, but undesirable outcomes nonetheless. As a starter for ten, here are a few pointers to take into account when developing BAQs in Epicor ERP:
1. Understand the data
When you have a sound knowledge of the basic tables within Epicor 9, creating BAQs becomes a lot easier. BAQs tend to link tables together for you automatically, but sometimes they either do not link at all or they link to the wrong tables.
A prime example is the OrderHed/OrderDtl/OrderRel and Customer Table. Epicor 9 has three customer fields in the OrderHed table and one in each of the OrderDtl and OrderRel tables. If you add these four tables to a BAQ, it may well link the customer to the wrong table or to the wrong field within the correct table.
2. Understand the volume of data
This is all about using the correct tool for the job. Many requests come in for BAQs that are actually requirements for other tools, such as Enterprise Performance Management, Advanced Financial Reporting and Canvas Planner. BAQs in Epicor ERP are a good way to extract small volumes of data, but if you want to compare month-on-month figures then consider using other tools.
Whenever you run a BAQ it is going to fetch all the data every time. Year-to-date comparisons against last year are generally going to mean a long response time – and an impact on other users. The same goes for a Crystal Report.
The benefit of Epicor AFR or EPM on the financial side is that they use a replicated database that you can aggregate. This means the bulk of the data is built once and then incremented through replication – much better for queries on large amounts of data.
3. Understand the business process
Epicor 9 offers many different ways to process data through the system. If you are asked to produce a BAQ listing the Sales Order Shipments, you need to understand how your business processes Sales Order Shipments. Are the qtys simply shipped from stock or direct from jobs? Or is it a combination of the two?
Another example would be selecting shipments that you want to sort by postcode area for delivery days. If you select by date range and postcode area first, you’ll end up with a much smaller retrieval set than if you select by date range alone. So, bear in mind how you configure your BAQs to support your business processes (the clue is in the name Business Activity Query).
4. Understand the requirement
Within any organisation, there will be some terminology in use that differs from Epicor 9’s terminology. For example, the term ‘Allocated Quantity’ can mean a lot of different things, depending on where you work. It’s better to have a discussion that defines exactly where the ‘Allocated Quantity’ is to be found or calculated from before you build a BAQ that is not going to provide the “correct” results.
5. Understand the technology
Epicor ERP’s BAQs build progress queries that are converted into SQL queries (if the database is SQL). As such, the starting table can be very important to the performance of the BAQ. I believe the best starting point is the table that gives you the widest base. For example, a Sales Listing should start with the OrderRel table (assuming that level of detail is required) and link into OrderDtl and then into OrderHed.
Write a comment