Enterprise Resource Planning (ERP): Online Transaction Processing vs. Decision Support
So, you’ve got your ERP system up and running, and before long, the management team wants reports, dashboards and executive data out of the system. That makes perfect business sense, and most ERP systems (including Epicor) have a slew of built-in reports as well as a report designer – Epicor E10 uses SSRS, Microsoft’s flagship product for writing reports.
However, there’s a potential problem. The activity of entering data, called “Online Transaction Processing” or OLTP, is fundamentally different than the activity of reporting and summarizing that data, called “Decision Support”, or DS for short. Before we go further, let me also explain database locking. A lock is a basic database ‘tool’ that prevents other user from changing a piece of data that you are using. There are many types of locks, but for this discussion, a row (record) lock prevents others from editing that specific record– let’s say an invoice. A table lock prevents anyone from editing anything in that whole table. It is our sincere desire to keep all locks as short as possible, for the longer the lock is held, the more likely it is for someone else to want that locked data.
Online Transaction Processing (OLTP) locks individual records to allow parts to be sold, inventory to be adjusted, and invoices entered. Decision Support (DS) locks whole database tables to run a report. When a manager wants to see an invoice report, nobody can be entering a new invoice while the report is being generated! While most locks are handled automatically, they cause delays and in rare cases of a deadlock, data loss.
I’m oversimplifying the issue, but the long and short of it is that Online Transaction Processing (OLTP) and Decision Support (DS) fight each other, all day long. In fact, locking contention is one of the main causes of database performance issues! There are several solutions, but a common one is to simply time the DS to occur after OLTP – that is, after the business closes. Many companies run their reports at night, not only because the system is more available, but all those pesky users aren’t entering data, locking records and causing issues!
A more complex, but also common solution, is to copy the Online Transaction Processing (OLTP) database to a independent Decision Support (DS) database on a regular basis. OLTP users get an optimized database for their activities, and the DS users can run reports all day long without locking the OLTP users out. An ideal solution for a busy database, but it does have its downsides. You’ll need twice the disk space and a method to move the data from OLTP to DS. Our clients use backup & restore, SQL replication, mirroring and all kinds of technology to duplicate the database and prevent the dreaded locking contention.
Need help? Let us know and we’ll help you get your Online Transaction Processing and Decision Support properly segmented for best performance.
If you liked reading the “Eleventh Day of ECHO” return to our main list to read all of the other “12 Days of ECHO” posts.
