Retrieving actionable information is key to a business’s operational excellence, but obtaining that data without the right tools can be difficult and, in some cases, impossible. Acumatica Cloud ERP enables the retrieval of actionable information to make critical business decisions using Generic Inquiries (GIs).

What are Generic Inquiries?

Acumatica Cloud ERP features powerful data analysis tools and capabilities. Generic Inquiries are one such feature. They allow users to create customized queries without extensive programming skills. You can picture Generic Inquiries as asking Acumatica Cloud ERP a question, and the response is data that can be visually presented in meaningful ways as an answer to this question.

In an overview video covering Generic Inquiries, Harsha Sarjapur, the founder of Acumatica partner InfoSourcing Inc., says that, when you use Generic Inquiries, “You’re trying to inquire [of] the system by saying, ‘Show me a list of all customers,’ or ‘Show me a list of all vendors,’ or ‘Show me all sales orders created last week.’ You’re inquiring about something.”

These Generic Inquiries—all customers, all vendors, all sales orders—and others like them are prebuilt and readily available in Acumatica Cloud ERP, but you can extend these existing inquiries or create your own to pull exactly the information you want to see, every time. Such GIs can be used to:

• • Create custom dashboards to visually analyze specific information.
  • Generate customized reports for important key performance indicators (KPIs).
  • Dissect data layers by drilling down to uncover actionable insights.
  • Identify and correct data discrepancies or errors using AI.
  • Quickly generate reports to address immediate business needs on the fly.
  • Trigger workflows, automating actions based on specific data conditions.
  • Create audit reports, ensuring compliance with internal/vendor policies and external regulations.

And much, much more. What separates Generic Inquiries from the “asking the system a question” metaphor is that you don’t pose the question in natural language. Instead, Generic Inquiries use Structured Query Language (SQL), which is, as Geeks for Geeks puts it, “a standardized programming language used to manage, manipulate and interact with relational databases.” Relational databases, like the one Acumatica Cloud ERP employs, organizes data into tables. Rows in these tables represent individual records (like a customer or vendor), and columns represent attributes that fill in details for those records (like customer name, contacts, locations, phone numbers, email addresses, etc.).

Though Generic Inquiries use SQL, you are not required to know it. In fact, the GI creation tools make the construction of your GI effortless and straightforward.

Generic Inquiries are part of Acumatica Cloud ERP’s “no-code customization” promise, grounded in our belief that you have the right to “fully adapt and customize the solution to meet your evolving business requirements.” This means that your business process experts—those who know your organization best—can use Generic Inquiries to configure Acumatica to your operations, without extensive technical knowledge. There’s no need for in-depth SQL programming training or a larger team of developers. You just need to know how your business works and what specific details you want to extract and analyze from your data.

However, while Generic Inquiries are extremely user-friendly, it’s also critically important to understand best practices for optimizing them. This ensures that they run efficiently, that you always receive the right information quickly, and that they do not impact other users and system performance.

Let’s dig a little deeper into a few issues you may face with unoptimized Generic Inquiries, how to diagnose these issues, and what best practices you can employ to optimize your GIs and avoid unnecessary system performance impacts.

Signs You Need to Optimize Your Generic Inquiries

As with any other query or request, Generic Inquiries naturally become more complex as deeper insights are sought. But when left unoptimized, complex Generic Inquiries can cause noticeable inefficiencies.

For instance, you may be experiencing long wait times while opening a GI, and other users may be complaining about system performance when your Generic Inquiries are running. It may also seem like your dashboards, populated by a complex GI, are constantly refreshing. This is more or less a struggle between your dashboard refresh rate and the length of time it takes to run the GI. This struggle can be further exacerbated if many users must run the same GI at the same time.

A GI with many joins (that is, one that combines data from multiple tables) can be considered complex. Careful consideration must be taken in what types of joins are used, what order they are defined in, what fields the joins use, and whether those fields are indexed as needed. The top inefficiency seen in most cases is the unintentional retrieval of more records of data than is needed. The second is joining tables on fields that are not indexed, causing the database to “scan” the table for each record joined. Avoiding the use of User-Defined Fields (UDFs) in joins is also key.

GIs can further be used to create Business Events. It is critical to ensure that the automated GI is efficient and to take care with what type of Event is used.

Knowing how to diagnose these issues—to understand where GI intricacies are causing unnecessary inefficiencies—is essential to being able to use complex inquiries to your greatest advantage.

Reaching a Diagnosis

If you find you are having difficulties with a Generic Inquiry, begin the diagnostic process by working backwards. Don’t think about the GI itself. Think about the data the GI is trying to access.

Ask yourself the following questions:

• • What information am I looking for?
    • You must know what you need before you can ask the system to retrieve it for you.
  • Does the data exist, or can it be calculated from preexisting data?
    • If not, you may need to create new tables or columns within existing tables containing the information you need.
  • If the data does exist or can be calculated, where is it located? Is it situated for optimal GI results?
    • If getting to your data is complex (too many joins, for example), the GI might eventually time out before any results are rendered.
  • Is the data normalized?
    • Eliminating data redundancy and dependency and having a clear and specified purpose for your database relations will enable your Generic Inquiry to know where it is supposed to look for the information you need.

Working through these and many other questions using a structured approach institutes the use of best practices, which will reduce the overall GI creation effort, especially in testing, and produce performant and efficient GIs.

Implementing Best Practices: The Life Cycle Approach

As businesses grow, complex requests for information become more common. It’s, therefore, crucial to put in place a process that addresses this increasing volume of intricate and complex requests while maintaining best practices and keeping GIs from impacting system performance or even causing unexpected system behavior.

The Life Cycle Approach is a form of intentional development that institutes the following best practices, as they apply to Generic Inquiries, from conception through deployment and monitoring.

• • Begin by building and using a test environment to construct and test GIs. Ensure your test environment mirrors the production environment by using Backups or Snapshots.
  • Avoid using any new GI in your production system until you are sure it is working accurately and efficiently in the test environment.
  • Before creating a Generic Inquiry, make sure you have a clear understanding of your business issue and exactly what information you want the GI to retrieve to address this issue.
  • Use the “Inspect Element” identification tool on the Screens that contain the data of interest to identify where this data resides.
  • The DAC Schema Browser is readily available to help you determine what data is required, where it is stored, and what fields should be used to create the table relations that will retrieve the desired information.
  • Apply filters at the joins if possible. Let the database work for you by filtering within the joins.
  • Apply filters (data ranges, for example) and conditions (like “ignore if less than x”) that minimize the size of the retrieved information and maximize GI performance.
  • Maintain your GIs by applying updates and upgrades in the test environment, so you can ensure they work properly before using them in your production system. Review the Release Notes for every Acumatica Cloud ERP release, so you can stay current with the changes made that could impact your Generic Inquiries.
  • Use the tools not available in SaaS, such as SSMS, which is applicable for MS SQL deployments, in conjunction with built-in tools, such as System Monitor, the Request Profiler, and the Trace facility.

For anything else, reach out to the Acumatica Community, where you can converse, share knowledge, and ideate with other active Acumatica developers and business process experts.