From the Sage MAS Team

[#1]

In my previous post on Customizing your ERP System, I mentioned how one of the driving architectural goals of the Business Framework was to support a better implementation of SQL Server as the back end database for Sage ERP MAS 200.  As we wrap up our Beta release and prepare for the controlled release of version 4.45, I thought I’d share the process we went through and how the framework effort has helped us to deliver on this goal.

In the words of a former Product Manager here at Sage (or was it State Of the Art?) who will remain nameless, to support a SQL Server database implementation (in a thick British accent) “all you got-a-do is”:  1) make sure the product works; and 2) ensure that it performs at an acceptable level.

Simple words…not so simple execution.  How do you ensure that “the product works”? 

Looking back at the 3.7x version of MAS 200 SQL, we had to rely on manual testing and the expertise of our QA Analysts to ensure that test plans yielded identical results in both the Providex flat file and SQL Server back-ends. 

Using tools created throughout the Business Framework journey, we were able automate a significant amount of testing.  First using a code coverage analysis tool, a total of 147 data sets (or different company codes) were created utilizing the various options and integration flags, inventory costing methods, etc., to ensure that the core logic in all Register/Update programs were executed.  Then using our business objects (the same ones available for integration purposes, using the Business Object Interface and eBusiness Web Services), we were able to run automated unit test scripts that executed the data entry, register and update posting processes utilizing those data sets.  A data compare utility then evaluates and reports any differences at both a row and column level between the two back-ends for the tables that were modified during those processes. 

This same technique was used to compare the results of all the reports in the system.  A tool was created to generate report settings to exercise different sort and selection criteria which were then used by an automated script that runs all of the reports for each setting and captures and compares the worktables for differences.

These two efforts were very effective in uncovering issues in the data access layer (using ADO -  ActiveX Data Objects) early on in the project.  In addition, as a side benefit, several issues were found in the 4.40 code base as a result of these tests which were subsequently fixed in the various service updates.  Bottom line is we were able to achieve a much deeper level of testing than could have possibly been achieved using manual methods.  By automating these tests, our valuable QA resources were freed up to focus on critical high use areas of the product.

As for performance, our automated testing team created SilkTest scripts to test and time form loads, record retrieval and saves, posting update processes and a number of reports.  A large data set was used so that problem areas were quickly exposed.  The report compare utility mentioned above also timed the duration of printing a report to “Deferred Printing”, so that we could compare the amount of time to print the reports using SQL Server.  Finally weekly multi-user tests were run where all QA analysts and engineers on the project teams would put a load on the system to identify defects and performance issues.

Schema changes made in the Business Framework versions of the modules, eliminated many of the trouble spots in the 3.7x SQL product.  For example, discrete columns and the flattening out of multiple record (or row) types within a table got rid of all the back and forth mapping and translation between the MAS 200 code base and SQL Server database.  Additionally the legacy linked list structure for the data entry line tables was modernized to use a keyed index, eliminating a source of SQL deadlocks as well as cross-linked detail lines.

The majority of performance improvements made throughout the development cycle were in the data access layer, using ADO.  Extensive analysis of SQL Profiler traces identified patterns and how the MAS 200 application requests data from SQL Server.  The majority of I/O within an ERP system is requesting or reading data from SQL Server.  In fact even in an update posting process that inserts and updates many rows, 65% of the I/O is in data retrieval.  Significant gains in performance were achieved during development by optimizing how we request data from SQL Server.  This includes: Query optimizations, to request only the data we are interested in; multiple caching techniques to only request the same data from SQL Server once for a given process; efficient use of statement handles and connection management; prepared statements for frequently used queries; and optimized insert/update statements.

When we did need to make changes to the MAS 200 application code, the object oriented nature of the Business Framework modules allowed us to make performance enhancements in a central base class, providing benefit in many areas of the product.  For example performance changes made to the common routine used to update inventory transactions resulted in improved performance in the SO Sales Journal Update, IM Transaction Update, PO Receipt of Goods and BM Production register updates.

The cache hints for what type of access to use during report printing is maintained outside of the code in meta data, which is invoked by the base class that establishes the connection to the table that is to be cached.  Once again, achieving performance gains without having to change each individual report.  These techniques are in stark contrast to the 3.7x product which required specific changes to each of the many tasks that needed performance tuning.  When changes were made either at the data access layer or through application code changes, the automated unit tests mentioned above were utilized to ensure no regression defects were introduced by those changes.

Thanks for your time and I hope this gives you some insight in to how we approached the SQL Server project and how the business framework effort was leveraged during the project.

Steve Malmgren

Chief Architect MAS 90 and 200