ERP and Crystal Reports

Introduction

I put a questionnaire on how people liked Crystal Reports up on the “Sage Partners, Employees & Alumni Networking Group” group on LinkedIn and received a lot of good discussion. I’ve previously blogged on customizing Crystal Reports for Sage 300 ERP (Accpac) here. This blog is more about the history of how ERP to Crystal integrations have gone in the past and the various challenges faced.

History

Accpac (now Sage 300 ERP) was originally developed by the Basic Software Group in the eighties in Vancouver. They started a project to have a new WYSIWYG report writer as part of the product rather than the codes based approach being used. This project was cancelled and some of the people involved quit and started their own company to develop Crystal Reports (then Quick Reports). This was originally developed as an add-in product for the MS-DOS based Accpac Plus. Then they generalized the product to dynamically look at any database and that became the Crystal Reports of today. The original founders then struck it rich when they sold their company to Seagate in 1994, which was trying to branch out from hard disks to software. Eventually Seagate gave up on this and Crystal went private and independent as Crystal Decisions. Shortly after in 2003 it was sold again to Business Objects. Then in 2008 Business Objects was bought by SAP where it lives today.

All of Sage 100, 300, 500 and X3 ERP use Crystal Reports as their main reporting engine. Sage HRMS and Sage CRM also use Crystal. We all adopted Crystal long ago at some stage or another. Sage 300 ERP adopted Crystal when we switched from using CARET (the Computer Associates report writer) back at version 3.0A.

Most products started integrating with Crystal in the 16-Bit world of Windows 3.1. We all integrated via the DLL interface that was to the DLL: crpe.dll. This interface let us print reports to a printer, preview or file. It let us set parameters that were passed from application’s entry forms to Crystal for things like to/from ranges. The Crystal runtime that included crpe.dll was installed to a directory c:\windows\crystal. This meant that any product that installed the Crystal runtime would usually overwrite what was already there, meaning the last product using Crystal to be installed would usually work, whereas most other products using Crystal would then stop working. This was the famous DLL hell.

In these days we had to use native drivers for accessing the database, Sage ERP 300 shipped two versions of its reports, one set using the native Btrieve driver and the other using ODBC for SQL Server. This tended to be a maintenance headache. Having multiple reports for different databases, or even different page sizes like letter vs. A4 vs. legal. Having different reports depending whether G/L is used or not. Over the years as Crystal functionality has improved and ODBC drivers have improved, we’ve been able to reduce the number of report versions. We now ship one version of reports using ODBC that is just configured to look at the correct database regardless of type.

Then we all started to move to 32 bit Windows with the advent of Windows 95. Crystal produced a 32-bit version and the new interface DLL was crpe32.dll. This was a close match to the 16-bit version and programs could work with Crystal in the 32 bit world very similarly to how they did in the 16 bit world. However DLL hell still remained. Plus we started to see the appearance of Terminal Server and Citrix. To keep users separated on TS, anything that a user installs to the Windows directory actually goes to a local Windows directory. The problem now is that the Crystal directory would go to the local user’s private windows directory and hence Crystal would only work for the user that installed the product and not for any other users on the Terminal Server. This then led to a manual procedure of copying the Crystal subdirectory either to the main Windows directory or each user’s local Windows directory.

With Crystal Reports 9, Crystal moved to eliminate the DLL hell problem. They dropped support for calling the crpe32.dll directly and moved the Crystal runtimes file over under Program Files under a version labeled directory. This allowed multiple version of the Crystal runtime to be installed at the same time and fixed the problems with Terminal Server and Citrix. However applications that used Crystal had to switch from using the crpe32.dll to using Crystal’s COM interface.

Crystal X and XI then followed and these worked very similarly to Crystal 9. Another nice thing was at this point Crystal had adopted an extensible file format, so the file format stopped changing between versions making upgrades easier.

Crystal Reports 2008 then dropped their COM API and now only supports integration via .Net and Java interfaces. This presented a number of problems, namely since these interfaces were quite different than the ones that went before them. Plus Crystal Reports was a fairly major upgrade to XI and seemed to introduce quite a few bugs. At this point ERP packages integrating to Crystal either had trouble with the new interfaces or had trouble with existing reports not running properly. This led to a bit of a low adoption of CR 2008. However the file format remained the same, so you could use CR 2008 to edit reports that would later be rendered in the ERP package via an earlier Crystal runtime, and again co-existence of multiple versions isn’t a problem due to the side-by-side DLLs.

Crystal has now released Crystal Reports 2011 and this fixes most of the problem in CR 2008. So we should start to see ERP packages moving forwards again. This version still only supports .Net and Java interfaces but has fixed a number of bugs and deficiencies that were causing adoption problems.

Challenges

For setting parameters and running reports, most ERP packages can do this fairly handily. However when you start to push the integration, what are some of the problems you can run into?

Sage ERP 500 (previously MAS 500) tries to simplify report customization by actually generating reports based on a higher level report designer built into the Sage ERP 500 product. This then eliminates a lot of the challenges in customizing reports inside the Crystal Designer. However this means that Sage ERP 500 uses the Crystal Design API and this API changes a lot from version to version making upgrades much harder. Sage ERP 500 also controls access to its database via a customer ODBC driver that then adds the Sage ERP 500 security model to the generic ODBC access.

Sage ERP 300 (previously Accpac) has a concept called “datapipes”. These were originally created for CARET as a performance enhancement and then taken over to Crystal. The concept is that you use these in place of ODBC to connect to the database and then these datapipes use the Sage 300 ERP database ERP to access the database. This allows the datapipes to use special knowledge of the Sage 300 ERP database schema to provide faster access. We use them to “flatten” out subreports, since accessing subreports in Crystal can be slow. We also use these for controlling access to things like G/L accounts with our G/L security module so users don’t see data they aren’t entitled to.

Reports Server

Using the Crystal Reports runtime from an ERP package is fairly straight forwards and easy, it can be installed with the ERP package and usually works quite well, so the user doesn’t have any extra hardware, installation or management problems. If you need to customize reports you need to buy and install Crystal Reports, but to just run reports there is no extra overhead. However much new development by Crystal is requiring the Crystal Reports Server.

The Crystal Reports Server is a central server that manages the printing and distribution of reports. You can use it to schedule when reports run and who gets the results. Most new Crystal development is now requiring a Reports Server to operate. For instance if you want to use the new Crystal Data Universe to make creating reports easier then you need a Reports Server to hold these. Similarly several new technologies like Crystal’s dash-boarding technology require the Crystal Server.

For us ERP vendors, we now have to evaluate the ROI for our customers. Is requiring this new server worth it? Is it worth the hardware, setup and licensing costs? Long term, is this tying the ERP package too closely to Crystal making adopting other technologies harder? At this point I don’t have a clear answer to these questions, but we are working with the new things in Reports Server to see their costs and benefits.

Summary

Crystal Reports has a long and interesting history and has become the de facto standard in report writers across all ERP packages. It is very full functioned and works with any database server. It takes a bit to learn how to use it effectively and is a very powerful tool for Business Partners that use it. The main complaint about Crystal is that it’s hard for end users to customize their own reports, due to some of its complexities.

Five Whys

Introduction

The “Five Whys” is a method for performing a “Root Cause Analysis” to detect the true cause of problems in a product or process. The Five Whys was developed by Taiichi Ohno, the father of the Toyota Production System and is a cornerstone for how Toyota achieves the quality it does.

Software development is a complex process and modern business software is quite large and complicated. If something goes wrong, we have a tendency to just blame the first named culprit, for instance if a bug appears in the field then blame the QA person that tested that feature. Then this is usually accompanied with a lot of talk about people being “accountable” for the work they do. But if you step back a bit you realize that actually a much larger system has failed. The QA person wasn’t the only person to look at that feature. The programmer was responsible for testing it (both manually and providing unit tests) before giving it to QA. A software architect reviewed what the programmer was doing. The entire product went through a regression test before release. This feature was reviewed by a Business Analyst and a Usability Analyst. The product and feature had gone through a customer Beta Testing phase. And then in-spite of all this process and review by all these responsible and accountable people, the bug still made it out into the field. So to just superficially blame one person doesn’t give a good perspective on what went wrong and doesn’t help preventing the problem happening in the future.

The real goal of these methods is to allow companies to learn from their mistakes rather than to assign blame. Generally the process works way better when everyone is confident that this is the real intent. If everyone believes the goal is to assign blame, then the whole process goes political and getting to the truth is usually impossible.

Learning from mistakes has its own pitfalls. One common theory for why successful companies eventually fail is that they dutifully analyze problems and learn from their mistakes. For every problem that happens they develop a process or procedure to prevent that problem ever happening again. The downfall here is that as time passes, the number of processes and procedures created from this process becomes huge. This makes the company very bureaucratic and getting things done very hard. Great care has to be taken that the outcome of these studies doesn’t just bury a company in procedures that get harder and harder to follow. The best result is if the current process can be simplified, perhaps eliminating the step that created the mistake. After all simple processes and procedures are much easier to follow and less error prone. When developing remedies to problems analyzed, careful attention has to be paid to the cost of the solution, so that the solution isn’t worse than the original problem.

Performing a Root Cause Analysis study takes time and attention. So when you want to get started with this, start with the worst and most easily defined problems first. Run a few pilots to get used to the methodology. One good place to start is problems found in the field by customers, as mentioned before these indicate a fairly serious systemic failure which might be useful to fix. Problems found in the field are usually categorized by severity, so you could start by looking at the most severe problem first and then working down the list.

The Five Whys

The basic concept behind the Five Whys is really simple. Basically keep asking why for five times to get past the superficial reasons to the underlying reason. It’s something that we all did as three year olds when we kept annoying our parents by asking why over and over again. Basically as children we perceived that this was a good way to get a deeper understanding of the world, but then we abandoned this technique as we got older (or were scolded enough times). Basically the insight here is that we had it right as three year olds and shouldn’t have given up.

As an example perhaps of an interviewer asking a programmer:

• Why did this defect make it into the product?
  - Because the code review and unit tests didn’t catch it.
• Why didn’t the code review catch it?
  - Because we substituted a reviewer from another team.
• Why did you substitute who was doing the reviews?
  - Because our team was frantically coding and couldn’t spare the time.
• Why were they so busy coding?
  - Because the stories they committed to were larger than expected.
• Why was that?
  - Two key dependencies were underestimated and forced a lot of extra work.

This is a fairly simplified and abbreviated example, but shows the basic idea. Also notice that each question can often lead to several avenues of pursuit for the next level. It’s up to the interviewer to decide whether to pursue several or follow the main thread. The diagram below shows how this might go.

Root Cause Analysis

When you perform a Root Cause Analysis (RCA), you usually follow the following phases:

The Five Whys are usually performed in the “Data Analysis and Assessment” step. The “Data Collection” step before is used to identify the correct people, so you know who to ask the Five Whys to. Typically you want to have a separate Five Whys interview with each of the people involved with the problem and then perhaps additional people depending on the outcome of the interviews.

Generally you do each interview separately and document all the questions and answers. We document everything to do with the process on a Wiki, so everything is kept transparent and visible. After all the interviews you then need perform the assessment to identify the root causes and choose the items you want to pursue. This is usually done in a couple of brainstorming sessions. First everyone studies the documented Five Why interviews then they get together to discuss what happened and make suggestions of how to change processes or make other changes. All these ideas are documented. Then some time is left to think about them and another meeting happens to decide which ideas will be implemented, keeping in mind that we don’t want solutions worse than the problem and don’t want to introduce unnecessary bureaucracy. Then we apply the solutions and inform all affected departments. Follow up is scheduled to ensure that the solution is working and we are getting the desired results, if they aren’t working they should be scrapped or changed.

Summary

Usually when people first read about RCA they take it as a very heavy handed procedure to address simple problems. However when you’ve run a few of these, you quickly realize that most problems aren’t that simple and that solving systemic problems can be quite difficult. I’ve found Five Whys an extremely effective method to get to the bottom of things, where one of the key benefits is that it forces people to take the time to really think about what happened. Then the documentation produced provides a lot of visibility into what happened which usually makes implementing the solution face less resistance.

Doing Work in Small Batches and Limiting Work in Progress

Recently Software Development has been borrowing a lot of ideas from Japanese Lean Manufacturing. In a way this mirrors the progression from the Detroit way of building cars pioneered by Henry Ford to the modern way pioneered by Toyota.

The original Ford production line approach was that a car was assembled on an assembly line by going through a series of specialized stations that were optimized to do one things really well and efficiently, so the car would go from perhaps the attach doors stations to the add Window glass station. Each station was optimized to do its job really well and the priority was to keep the assembly line moving no matter what. If the assembly line ever stopped then the hundreds of people working on it would be idled and this was considered a disaster. If problems occurred then they were noted and fixed post-assembly line. This process was then improved by optimizing each station to be quicker and more automated. Generally this produced a very good system of producing a very high volume of identical cars.

From the outside a Toyota assembly line looks nearly identical. But it operates very differently. Toyota didn’t have the same large market as Detroit and didn’t need the volume. They needed to produce more specialized cars and needed to switch their assembly lines quickly from one run to another. Their emphasis wasn’t on making very specialized machines to do one task quickly; it was on producing general machines that could do a number of different things and could be switched quickly from doing one job to another. Toyota then does small runs on their assembly line rather than one massive long run. The big benefit to doing things in small batches was that quality problems are found much sooner. Hence they can stop the assembly line on the first car and fix a problem, rather than producing thousands of cars with the problem and then possibly not fixing the problem because it’s too expensive at that point. This also re-enforces the practice of fixing problems right away rather than letting them pile up and that it’s more cost effective to have the production line idle while the problem is fixed than having to fix it later.

Another example from the book “Lean Thinking” by James Womack and Daniel Jones recounts a story of one of the authors stuffing envelopes with their two young children. The children wanted to take the approach of folding all the letters, then putting all the folded letters in the envelopes then sealing all the envelopes then putting stamps on all the envelopes. This seems like an efficient and intuitive way to approach the problem. However the author insisted they do it the less intuitive way of one complete envelope at a time, i.e. folding one letter, stuffing it in an envelope, sealing it and putting a stamp on and then going on to the next one. Which way is faster? It turns out that researchers have timed groups of people performing this task and it turns out the one at a time approach is faster! It has a couple of other advantage as well. One is that if there is a problem, you find out right away; if the envelope is the wrong size, you find out on the first letter, not after you have folded all the letters, this could save you then having to refold all the letters to fit the envelopes. Similarly if you take a break you know how far along you are, if you’ve completed 40 out of 100 letters then you are 40% done and you can estimate your remaining time easily. You can’t do this when you do each step completely. This process is called “single piece flow” and is an example of doing a task as lots of small batches rather than one large batch. This is a general principle you want to apply in everything you do, resisting the old Ford type assembly line type mentality. You also receive the first finished product off the assembly line quicker, so you can validate it with your customer before too many more are produced to ensure you are producing the right thing.

Kanban vs Scrum

To some degree batch size is at the heart of the difference between the Kanban and Scrum methods of developing software. Scrum tends to batch things into sprints (usually two or three weeks long) which is much smaller than traditional waterfall development, but Kanban wants to take it further. In Kanban you only work on one or two small things at a time and finish one before starting the next things. You operate on lots of small batches, but you also have to limit how many batches (or tasks) you can have in progress at a time. This is called limiting work in progress (WIP). In Kanban you don’t worry about batching things up into sprints, just keeping the jobs small and limiting WIP. Kanban requires a lot of discipline and often if a development team hasn’t successfully progressed from waterfall to scrum, then adopting Kanban is a disaster.

The goal here is to produce small high quality increments that are immediately validated by customers. This way if you are going in the wrong direction, you won’t get too far before discovering it and not too much time will have been wasted (only a couple of small batches).

How do you get customer feedback, one small increment at a time? Obviously you can’t have dozens of customers download, install and play with a new version of your product every time you add a small increment and then interview them about it. But there are techniques you can use to do just this. The first is to produce your software via “continuous deployment”. This means that every build of your software is automatically deployed to customers whether that means pushing it to a web server or loading it on an automatic software updater service. You need to be able to control which customers get it and you need to be able to roll it back if something goes wrong. To be confident with this sort of procedure you need really good automated testing in place to ensure you haven’t accidentally broken something. Then to find out whether your increments (or batches) provide customer value, you need really good instrumentation in your product to report back on customer usage, whether they use the feature, how long it takes to perform tasks and any other useful statistics.

Software products are large and complicated systems. Managing that complexity is a major challenge. Breaking large tasks down into many small tasks is a powerful tool to reducing complexity. Combining many small but high quality parts results in emergent complexity, but again it is much easier to manage by adding one small thing at a time rather than suddenly throwing several large items into the mix at once.

Summary

Most of the ideas in this blog posting come from my reading the “Lean Startup” by Eric Ries over the holidays. Quite a good book with many useful ideas on product development and innovation. Reducing Batch size and limiting WIP appeal to me to help make Sage more agile and innovative as we move forward.

On Customer Adoption

The Problem

Traditionally with ERP systems, a new version is released every 18 months or so. Typically customers will only upgrade every few versions for a number of reasons discussed below. This leads to a major problem, namely if a customer requests a new feature and we jump on it, then it still could be four years before they see this feature. How this usually works is shown in the following timeline.

In an agile on-line world, this seems like rather a slow process to provide value to customers. Most Sage products have an on-line suggestion web site, such as https://www11.v1ideas.com/SageERPAccpac/Accpac for Sage 300 ERP. However customers tend to give up on making suggestions to these once they enter a number of suggestions and don’t see any movement in the next few months. As more and more people are part of the MTV Generation (or later) then instant gratification becomes the expectation.

Traditionally this is all part of a fairly large and involved Product Development Life Cycle. Product Managers spend their time talking to customers and partners as well as evaluating various industry trends to come up with a list of features for the next version. Usually a number of themes are developed and implemented as major new features. R&D then takes as many such themes and features that they think this can develop in 18 months. If all goes well, these are implemented, extensive testing is performed and an updated version of the product ships after these 18 months. Then if Product Management has done their job properly and R&D has faithfully implemented what was laid out, then customers should be fairly happy with the new version.

Of course things aren’t quite that rigid anymore. Most development groups (including Sage ERP) have adopted the Agile Software Development Lifecycle where the development team is implementing features in small batches taken from a product backlog. The product backlog is in priority order so the higher priority features are implemented first. A side effect of this is that for lower priority items in the backlog, Product Management can swap these out with other higher priority features as business needs or understanding changes. This then leads to greater flexibility and does allow some newer customer suggestions to make it into a product sooner. I blogged on some aspects of this here and here.

Often we are tasked with the goal of shortening our release cycles; say from 18 months to 12 months or even 9 months. However we get a lot of resistance from customers and partners on this. The main reason is that traditionally upgrades are fairly costly and time consuming activities that can often be quite disruptive to a business. As a result customers typically don’t want to upgrade more than every three years, so there is no increase in customer value by providing more frequent releases. Even then the main reason customer’s site that they upgrade is to stay in support, since we only support the two most recent versions or to stay compatible with upgrades from other vendors, notably Microsoft. If you need to know you work well on Windows 7, then you need a more recent version of your products. A large part of this problem has been caused by us software vendors, when presented with a choice to add a new feature to a release or to make upgrading easier, we consistently choose to add the new feature and leave any difficulty with upgrading to the Business Partners to deal with; who, of course, pass this cost on to our customers. Right now 18 months works well because by supporting two versions, customers only need to upgrade every 3 years.

So out of this we have two significant problems to address:

1. Making upgrades easier so it isn’t a huge inconvenience and cost to upgrade.
2. Make the features and functionality we add to our products more compelling so customers cite these as the reason for upgrading and not just keeping up with Microsoft compatibility problems.

Frictionless Upgrades

To address the first point, we’ve been working hard to make the upgrade process easier. This is Sage’s Frictionless Upgrade initiative. We’ve just begun on this journey, but in a number of products we are already seeing results. Such as in Sage 300 ERP we’ve combined all the separate module installations into one master product installations, allow you to activate multiple applications to the new version in one go and to minimize database changes so Crystal Reports and Macros don’t need modifications.

Going Online with services like www.accpaconline.com can help, since now you don’t need to install the software yourself, but you still have the work of making sure custom reports still work and training staff on any changes to their workflow. Generally the process of upgrading can be done well or badly whether you are online or on-premise. Certainly we’ve seen cases where overnight changes to web sites like Facebook have been very disruptive.

Many products such as Windows or Apple iTunes have an auto-update feature, so even though these are on-premise installed on the local computer type applications, they can still be upgraded frequently with little fuss. Expect to see this sort of functionality come to more and more business applications allowing the software to be upgraded frictionlessly without requiring a major database conversion or requiring changes to customizations.

Making Features Compelling

The intent with all the customer visits, customer interviews and other research that Product Management performs is that the features we add to the product are compelling either to existing users or to attract new users. A lot of work goes into researching and collecting all the data that goes into this decision making. Generally this yields better results than being driven by technology trends or by what competitors are doing; however, we tend to not being seeing the results we would like. What is missing?

With the scientific method, like what you see in Physics, Chemistry or Biology, people come up with theories, but these aren’t really believed until they are backed by experimental evidence. In software development we tend to do a lot of work doing research and creating theories and spend a lot of time discussing these theories with customers, partners and analysts. However we don’t take the next step and scientifically test these theories in real customer situations.

We tend to rely on doing studies on customers using prototypes or mockups. But when customers aren’t in their real work environment, doing real work, the results tend to be suspect. Is the customer just telling you they like your new feature to make you feel good, even though they will probably never use it? Are the questions being asked at interviews slanted to get the answers the researcher wants?

I recently read “The Lean Startup” by Eric Ries. This book, among other things, heavily recommends real scientific experiments with real customers in their real environment. No more separate studies with proof of concepts or mock ups. In a way we are doing this now. The only problem is that we don’t know the result until after four years and then it’s very hard to remove something that really didn’t work.

So how do we perform these experiments on real users in their real workplaces doing real work? Sure it’s easy for Facebook to try a new feature in say one town to see what the reaction is, but here we are talking about real business software that people rely on to run their businesses.

The real trick to this is to keep features small and only try small changes at a time. This is often referred to as a Minimum Viable Product or MVP. Only the absolute minimum functionality to do the job is produced. Any additional functionality is only developed (and tested) due to customer demand. This tends to greatly reduce complexity and unnecessary bells and whistles that we see in so much software. This then allows features to be developed and tested quicker. In manufacturing this is referred to as keeping batch size small.

This requires a great deal of good instrumentation in the product, so we know if a feature is being used and if it is helping (are users really more productive entering invoices?). Creating a full release with all the changes from the work produced by a large team over a year is a major undertaking. But adding small changes that have been fully QA’ed is fairly easy. In fact we already do this with hotfixes. In a way each hotfix is an experiment as to whether we have fixed a defect (hopefully mostly we have). So why are small new features any different than hotfixes?

You might argue that SaaS vendors can do this easier, since they can introduced the change for just a few customers by routing them to a different application server. However with auto-update we could do the same for on-premise customers. Give them the new feature and then based on the results, if they are good, roll it out to all customers, but if they are bad, then roll it back for anyone that received it.

These techniques have produced great results in a number of well documented cases, but will they be accepted for business software? I think right now there will be a fair bit of resistance to these sort of practices, but I think it is really a PR exercise to prove to customers that by participating in these sort of experiments, that they will get the features they are asking for sooner and that the new features they receive will be far more valuable. One of the main advantages of these techniques is reducing waste, by detecting things that won’t work quickly and discarding them quickly. This way some really dumb idea can’t make its way all the way through development using all those resources, to just annoy users in the end. Chances are this will start with new connected services or new on-line features, but I think eventually this could become more mainstream for even large ERP and CRM systems.

Summary

New techniques are being developed to develop customer value quickly and to get it into customer’s hands much faster than we have traditionally. However some of these techniques like experimenting on customers running live systems are going to take a bit of good PR to prove their value to get the necessary participation. If this all sounds crazy, give “The Lean Startup” a read, it paints a fairly compelling picture.

Tables and Data Flow of the Sage 300 ERP System Manager Module

This blog posting will be looking at the structure of some of the parts of the System Manager modules for the Sage 300 ERP product (previously known as Sage ERP Accpac). You can find the structure of the individual tables in the Sage 300 ERP Application Object Model (AOM). However this doesn’t tell you how the tables are related or how data flows from one table to another as you post various transactions. The System Manager tables consist of those of the mini-application modules that used to be bundled with System Manager (now just bundled with Sage 300 ERP). These include the 22 tables in Common Services, the 30 tables in Bank Services, the 9 tables in Tax Services and the 5 tables in G/L Sub-ledger Services. We’ll just look at a few interesting cases. Understanding these relationships can be especially important to people writing sophisticated custom reports or BI Views.

The main types of database tables in Sage 300 ERP applications can be categorized as:

• Setup
• Master Data
• Data Entry
• Data Processing
• Periodic Processing

The System Manager tables contain information that is used in common by many of the main large Sage 300 ERP accounting modules like Account Receivable or Purchase Orders. The main concentration of tables is of the setup variety since these are setting up the company wide options and properties for the rest of the system.

Administrative Services keeps a few tables outside of the main database. Typically these are required to sign-on to the main database. These include the list of companies you can sign on to and the file with all the user ids and passwords. These are stored in a proprietary ISAM format derived from the old DOS based Accpac Plus database system.

A number of Currency and Security related tables are stored in the System Database. A System Database is shared by a number of Company Databases so that you can update this information in one place and have it used by many companies. The data in the System Database is replicated into each Company Database; so that when we are running we don’t need to do cross-database joins or open extra database connections to get this information. This way we get the advantages of sharing without the overhead of using extra database related resources.

Setup

There are many setup tables across the System Manager functions:

Bank Services

BKOPT (BK0010): Options.

BKTT (BK0003): Distribution Codes (formally known as transaction types).

BKTTX (BK0860): Bank Distribution Codes Tax Data.

BKDISTH (BK0445): Bank Distribution Set Headers.

BKDISTD (BK0440): Bank Distribution Set Details.

BKCCTYP (BK0240): Credit Card Types.

BKGLREF (BK0470): Bank G/L Integration.

Tax Services

TXAUTH (TX0002): Tax authorities.

TXCLASS (TX0001): Tax classes.

TXGRP (TX0003): Tax groups.

TXRATE (TX0004): Tax rates.

TXMATX (TX0902): Tax rate matrix. TXMATX controls interaction with TXRATE.

Administrative Services

A4WUSER (AS0003): Users. This is an ISAM file and not in the database.

A4WCUST (AS0004): Customization directories. This is an ISAM file and not in the database.

ASORGS (AS0020): Companies.

CSSEC (AS0001): Security groups. This is in the system database and cloned to all attached company databases.

CSAUTH (AS0002): User authorizations. This is in the system database and cloned to all attached company databases.

CSUICSH (AS0005): UI Cust. Profile Headers. This is in the system database and cloned to all attached company databases.

CSUICST (AS0006): UI Cust. Profile Details. This is in the system database and cloned to all attached company databases.

CSUSCST (AS0007):  User UI Customizations. This is in the system database and cloned to all attached company databases.

Common Services

CSCOM, CSCOM2 (CS0001): Company profile. (One view, two database tables).

CSFSC (CS0002): Fiscal calendars.

CSOPTFH (CS0011): Optional field headers.

CSOPTFD (CS0012): Optional field values.

All the currency tables are in the system database and cloned to all attached company databases.

CSCCD (CS0003): Currency codes.

CSCRT (CS0004): Rate types.

CSCRH (CS0005): Currency tables.

CSCRD (CS0006): Currency rates.

CSEUR (CS0010): Euro fixed conversion rates. This was only used during the transition period to the Euro. If the Euro falls apart, we may need one to transition out of the Euro.

CSSKTB (CS0030): Schedules.

Optional Details:

CSSKAP (CS0032): Schedule Application Links.

CSSKCB (CS0034): Schedules.

CSSKID (CS0036): Access to Scheduling.

Master Data

The only master data in System Manager is the always important Bank Accounts.

BKACCT (BK0001): Bank Accounts.

BKCUR (BK0002): Currencies.

BKFORM (BK0008): Check stocks.

BKACCT has many more detail views to do with checks, deposits, entries and NSFs; but, we’ll cover those in the following sections.

Data Entry

Reconcile Statements

Most documents like checks, deposits, NSFs, etc. actually come from the sub-ledgers. The Bank module is mostly to reconcile these once your bank statements come in. However you do need to enter Bank entries and to start you need to enter some manual checks and deposits to setup the Bank balances correctly. This section covers all the tables involved in the process of entering bank documents and reconciling them. In Bank nearly everything is a detail of BKACCT, so this appears again at the top.

BKACCT (BK0001): Banks.

BKTRANH (BK0845): Transaction Headers (can be withdrawal or deposit).

                BKTRAND (BK0840): Transaction Details.

BKENTH (BK0665): Bank Entry Headers.

                BKENTD (BK0660): Bank entry details.

BKDCI (BK0104): Direct clearing interface. Superview on top of BKTRAN to help the UI reconcile withdrawals and deposits.

Journal Entry

The entry function in G/L Sub-ledger Services handles the case of whether G/L is activated or not. Rather than sub-ledgers writing to G/L directly, they write to these views. Then sub-ledger services either writes these through to G/L, or if G/L isn’t present then it writes them to CSV files that can be imported into another G/L.

GPGLPO (GP0950): Transaction transfer. The superview stores data in the following views:

GPGLBB (GP0100): Subledger transactions.

GPGLBH (GP0120): Subledger entries.

GPGLBD (GP0110): Subledger details.

Data Processing

Bank has two big processing functions, namely printing checks and then posting reconciliations. Tax Services then has calculating sales tax and keeping sales tax records.

BKREG (BK0009): Check register. Sub-ledgers put the checks they want printed here, then printing of checks is done through an icon in bank BK5000 – Check print User Interface.

BKPOST (BK0101): Reconciliation posting.

BKJCTL (BK0020): Posting journal control.

BKJRNL (BK0011): Posting journal.

BKJTRANH (BK0655): Bank journal transaction header.

BKJTRAND (BK0650): Bank journal transaction details.

BKJENTH (BK0665): Entry posting journal header.

                BKJENTD (BK0660): Entry posting journal details.

BKJERR (BK0012): Posting journal errors.

TXCALC (TX0901): Tax calculation (low level engine).

TXDCALC (TX0910): Tax document calculation (higher level engine).

TXALLOC (TX0904): Tax allocation (superview).

TXAUDH (TX0011): Tax tracking.

TXAUDD (TX0012): Tax tracking details.

Periodic Processing

There are a set of superviews that perform various periodic processing functions. These Views typically don’t have tables behind them and perform maintenance type operations. Some of these include:

BKCLEAR (BK0103): Clear reconciliation posting journals.

BKDCHK (BK0100): Bank Integrity checker.

TXCLEAR (TX0903): Tax audit clearing.

TXDCHK (TX0990): Tax integrity checker.

ASINTCHK (AS0010): Admin Services Integrity Checker.

ASINTPR (AS0023): Data Integrity checker superview.

ASRST (AS0021): Restart Records. (Actually an ISAM file and not in the database).

CSDCHK (CS0009): Common Services Integrity checker.

GPGLFMT (GP0800): Segment substitution.

GPGLPE (GP0930): Transaction transfer.

GPDCHK (GP0099): G/L Sub-ledger Services Integrity checker.

Summary

Hopefully this blog posting provides a bit more insight into how the various System Manager modules operates and hopefully helps when you use them, interface to them or are creating custom reports.

 

Tables and Data Flow of the Sage 300 ERP Payroll Module

This blog posting will be looking at the structure of some of the parts of the Canadian and U.S. Payroll modules for the Sage 300 ERP product (previously known as Sage ERP Accpac). You can find the structure of the individual tables in the Sage 300 ERP Application Object Model (AOM). However this doesn’t tell you how the tables are related or how data flows from one table to another as you post various transactions. There about 99 database tables in either the U.S. or Canadian Payroll module, so obviously we can’t describe each in a single blog post. We’ll just look at a few interesting cases. Understanding these relationships can be especially important to people writing sophisticated custom reports or BI Views.

The main types of database tables in Payroll can be categorized as:

• Setup
• Master Data
• Data Entry
• Data Processing
• Periodic Processing

The Payroll modules feed data into both General Ledger and Project and Job Costing. Payroll also interacts very closely with the Bank module for the processing of paychecks. Each Payroll module contains a main common portion which is mostly shared; these contain all the files that start with cp or up. Then for each Payroll there is a Payroll calculation module which has the country specific tables, reports and calculations; these include the tables that start with: CT or UT. Often as the government changes the Payroll rules and rates, we just need to updated and distribute updated CT and UT applications rather than the larger core CP or UP application.

In the descriptions below we will list the U.S. Payroll table name and View IDs. However to get the Canadian Payroll versions, just replace the UP with CP and you have it. Most of these are compiled from the same source code (which uses PR) and then UP or CP are substituted in as part of the product build process.

Incidentally this is the same Payroll module that is used in the Sage HRMS SQL Payroll product, so everything here applies there as well.

Setup

Setting up Payroll correctly is the most crucial task to have things run smoothly. If all the employees and their earnings and deductions are configured correctly then processing Payroll will go much smoother. The Setup Tables are:

UPOPTS (UP0023): Options.

UPCLAS (UP0006): Class codes.

UPDTLM (UP0007): Earnings/deductions. (UPDTLX, UP0099 is UP0007, without some of its functionality for faster read access.)

UPINCL (UP0015): Include list. Used by UPTXMS, too. Holds the applicable taxes for each E/D.

UPDIST (UP0009): Distribution codes.

UPWCCH (UP0036): Worker’s compensation master.

UPWCCD (UP0037): Worker’s compensation codes.

UPOTSC (UP0022): Overtime schedules.

UPSHFT (UP0025): Shift differential schedules.

UPSHFD (UP0038): Shift Differential Details.

UPSHFB (UP0039): Shift Differential Billing Details.

UPTXMS (UP0029): Company payroll taxes.

UPINCL (UP0015): Include list.

UPDIST (UP0009): Distribution codes.

UPTXMO (UP0124): Taxes Optional Field Values.

UPWRKC (UP0027): Work Classification Codes.

UPOFH (UP0120): Optional Field Locations.

UPOFD (UP0121): Optional Field Values.

UPGLREF (UP0057): G/L Reference Intergration.

Master Data

The main master data file for Payroll is the Employees. Then secondarily we have Employee selection lists.

UPEMPL (UP0014): Employees

UPEMPD (UP0008): Employee earnings/deductions.

UPEMPT (UP0010): Employee taxes.

UPEMPC (UP0053): Employee notes.

UPEMBK (UP0201): Employee EFT Banks.

UPEMPO (UP0122): Employee Optional Field Values.

UPEMTF (UP0062): Employee Tax Fields (Implicitly handled by UP0014).

UPESLH                (UP0045): Employee selection lists.

UPESLD (UP0046): Employee selection list members.

There is no master of monetary balances as GL has.  The detail for all checks issued is kept in the following, which serves as a master and detail archive all in one.

UPCHKH (UP0048): Check Header. (Holds completed and un-posted checks.)

UPCHKD (UP0049): Check Details.

                UPCHDO (UP0134): Check detail optional field values.

                UPCHJB (UP0056): Check job details.

                                UPCHJO (UP0144): Check job details optional field values.

UPCHKC (UP0052): Check comment details.

UPCHKE (UP0202): Check EFT details.

UPCHHO (UP0133): Check header optional field values.

Data Entry

The main data entry task for Payroll is the entry of Timecards. A common integration is for ISVs to feed Timecard data into Payroll from an external system (like a time clock check-in system). You may also need to enter some manual checks.

UPTCHD (UP0031): Timecard headers.

UPTCDT (UP0032): Timecard details.

                UPTCDO (UP0128): Timecard detail optional field values.

                UPTCJB (UP0042): Timecard job details.

                                UPTCJO (UP0141): Timecard jobs optional field values.

UPTCHO (UP0127): Timecard optional field values.

UPMCHD (UP0019): Manual check headers

UPMCDT (UP0020): Manual check details.

                UPMCDO (UP0130): Manual check detail optional field values.

                UPMCJB (UP0043): Manual check job details.

                                UPMCJO (UP0142): Manual check job detail optional field values.

UPMCHO (UP0129): Manual check optional field values.

Data Processing

The Calculate Payroll process is the central part of Payroll. This takes all the setup, employee and timecard data and calculates the Payroll. This includes all taxes and deductions, sets up everything to print checks and advice and record all the values that need to be accrued.

UPCALC (UP0083): Superview to calculate payroll.

UPCLCO (UP0137): Calculate payroll optional field values. Since UPCALC has no data, these optional fields have no header.

UPCALE  (UP0084): E/D/T List, helper to UP0083

UPTXMC (UP0100): Include List, table helper, used internally in conjunction with Tax Master by UP0084.

UPCALT (UP0085): Timecard List, helper to UP0083.

UPCALA (UP0086): Cost Center Allocations (there is data here).

UPCHKR (UP0088): Check Run Header, temporary table helper to UP0083.

UPCTRL (UP0026): Concurrency Control.

UPXCPT (UP0064): Calculate Payroll Exceptions, produced by UP0083 if errors detected.

Once the calculate Payroll is performed, the next big process is printing all the checks.

UPPMAN (UP0065): Process Manual Checks (called by UP0078 , UI ‘Process’ button, and Process Icon).

UPCTRL (UP0026): Concurrency Control.

UPXCPT (UP0064): Calc Payroll Exceptions, produced by UP0065 if errors detected.

UPHHAU (UP0021): History Audit Headers.

UPHHOA (UP0135): History audit header optional field values.

UPHDAU (UP0024): History Audit Details.

                UPHDOA (UP0136): History Audit detail optional field values.

UPPCKS (UP0078): Print Checks, controls the Print process and calls Post (UP0050).

UPPCKF                (UP0089): Print Checks Trans, temporary helper to UP0078.

Updates the following staging tables for printing. When you print checks, part of the header is transfer to the BKCHK table which drives check printing. Then the Payroll Crystal check forms will join back to the following two tables to get all the Payroll specific data they need for the check and advice.

UPPCKH (UP0079): Print Checks Header.

UPPCKD (UP0080): Print Checks Details.

Post All Checks

Called internally by UP0078, UP0083 and UP0012.

UPPOST (UP0050): Post checks.

Updates the following tables as needed:

UPMCHD (UP0019): Manual Checks Header

UPMCDT (UP0020): Manual Checks Detail

UPCHKH (UP0048): Check Header

UPCHKD (UP0049): Check Detail

UPYTDS                (UP0047): Employee YTD summaries

CPEUIC (UP0030): Employee EI history (Canadian payroll only).

Periodic Processing

There are a set of superviews that perform various periodic processing functions. These Views typically don’t have tables behind them and perform maintenance type operations. Some of these include:

UPMAIN (UP0018):  Delete inactive records.

UPDCHK: (UP0016): Integrity checker.

Update All employees:

UPGMET (UP0058): Globally modify process.

Uses the following table:

UPGLBM (UP0051): Globally modify list.

Summary

Hopefully this blog posting provides a bit more insight into how Payroll operates and hopefully helps when you use Payroll, interface to Payroll or are creating custom Payroll reports.

 

Unit Testing Web UIs in Sage 300 ERP

Introduction

Unit Testing is a technique to test individual units of source code. Usually in the Java world this means a set of tests to test an individual class. The idea is to test the class (or unit) in complete isolation from the rest of the system, so calls to other parts of the system would be stubbed or mocked out. Unit test are typically written by the software developer to perform white box testing for their class. Unit testing is only one sort of testing there would be other types of testing for various integration testing, user testing, load testing, manual testing, etc.

The goal of unit testing is to show that the individual parts of a program are correct and then to deliver the following benefits:

• Establish a well-defined written contract of what the code must do.
• Find problems early in the development cycle.
• Facilitate change and refactoring since you can rely on the unit tests to prove things still work.
• Simplify integration since you know the building blocks work correctly.
• Provide documentation, the unit tests show examples of actually using the class being tested.

In Extreme Programming and Test Driven Development, the unit tests are written first and then passing the unit tests acts as an acceptance criterion that the code is complete.

JUnit

JUnit is a unit testing framework for Java programs. It is integrated into most IDEs like Eclipse and it is easy to run the tests from build tools like Ant. Usually in Eclipse you have a test tree with all the test classes, usually a test class for each real class and the test class containing all the tests for the real class. JUnit support a number of annotations that you put on the methods in the test class to specify which ones are tests to run as well as optionally specifying any order or dependency requirements (the best unit tests run completely independently of each other). Then it adds a number of methods to call to test for pass and failure of the tests (via various forms of assert) and to report the results back to the test framework.

GWT

The Google Web Toolkit (GWT) supports unit testing with something called GWTTestCase which bridges GWT to JUnit. If you want to interact with GWT controls then you have to use this. However it’s relatively slow. You want unit tests to run blazingly fast. A unit test should only take a few milliseconds to execute. If it takes longer then it won’t be run often. You want it so your unit tests can be run every time you compile and so they don’t slow you down as you work.

We use GWTTestCase to test our extension of the GWT CellTable widget. These tests take 10 minutes to run. This is way too slow to expect them to be run on a regular basis. Hence they don’t provide the immediate feedback to a developer working on this widget that we would like.

Fortunately since in GWT you are writing all your classes in Java, if you structure your program correctly you can have most of your classes not interact with GWT or easily mock the GWT part of it. Then you can use JUnit directly to unit test your program.

MVC

One of the key goals of us enforcing MVC design patterns on our developers creating UIs is to facilitate good unit testing. This way if most of the code that needs testing is the Model and Controller, these can be tested with JUnit and we don’t need to use GWTTestCase. This greatly simplifies unit testing and greatly improves the productivity and speed of the tests. Usually our View part is very small and mostly implemented in the SWT framework, so the unit testing is then in framework unit tests (many of which are GWTTestCase), and not in the actual UIs.

EasyMock

EasyMock is a library that can dynamically “mock” classes that aren’t part of the test. With unit tests we only want to test the one class at a time and we want the tests to run quickly. We don’t want to require a particular database be in place and then have to wait for the tests to run as it makes database queries. We want the tests to run in milliseconds and we want the tests to run in spite of what might be happening in the rest of the system. To this end the test framework needs to replace the real classes that the class being tested calls and interacts with, with something appropriate for testing. These test replacement classes then have the ability to return unexpected or rare results such as network disconnection errors, or other rare hard to setup type of scenarios. One way to do this is to write “stub” classes that replace the real classes and then only have code to do what is required for the testing. Writing and maintaining stub classes is difficult since you need to keep them in sync with the classes they are stubbing; keeping these correct can become a major amount of work.

EasyMock offers an easier way. It generates the classes on the fly using Java’s proxy mechanism. This way the class interface is always up to date since it is generated from the real class. You first run in a “recording” mode where you record what you want the various methods to return to the test cases. Then you switch to “play” mode and run the tests.

For this to work effectively, you must design classes so classes they use can be mocked. This usually means creating them externally and then passing them to the constructor rather than creating secret internal instances. Generally this is good object oriented design anyway and usually difficulty in constructing unit tests is a “code smell” that something is wrong with the design.  For simple utility classes or simple classes in the same package, it’s often easier to leave them in place rather than mock them, then they get a bit of testing as well and as long as they run quickly, shouldn’t be a problem.

Example

Below is one of the tests from the BOM Detail popup form in Order Entry. This shows the structure of a JUnit test and shows how to mock a big class (namely the Model) that this class interacts with. Notice that the Model is created externally to this class and passed into the constructor, so it is easy to mock. First we record what the mock class should do and then we switch to replay mode to do the actual test.

public class NestedViewBOMDetailsCallbackTest
{
private static final int TEST_PARENT_COMPONENT_NUMBER = 42;

/**
* Tests that the callback’s onPopupClosed handler will filter on the child
* BOMs of the parent component number that was passed in at callback
    * creation time.
*/
@Test
public void testOnPopupClosedFiltersOnChildrenOfCallerParentBOMNumber()
{
// Set up the mock model and the expected method calls that should be
// made on it by the class under test.
OEDocumentBOMDetailsModel mockModel = EasyMock.createMock(OEDocumentBOMDetailsModel.class);
mockModel.filterOnChildBOMsOf(EasyMock.eq(TEST_PARENT_COMPONENT_NUMBER), EasyMock.isA(EntryFilterCallback.class));
EasyMock.expectLastCall();

// Tell EasyMock we’ve finished our setup so that any time we interact
// with the mock, EasyMock records what calls were made on the mock.
EasyMock.replay(mockModel);

// Call the class under test to exercise the method under test.
NestedViewBOMDetailsCallback callback = new NestedViewBOMDetailsCallback(mockModel, EasyMock
            .createMock(BOMDetailsView.class), TEST_PARENT_COMPONENT_NUMBER);
callback.onPopupClosed(null);

// Check that the expected method calls on the mock happened when we
// exercised the method under test.
EasyMock.verify(mockModel);
}
}

Summary

Unit testing is an effective form of testing that does find quite a few bugs and helps keep code running smoothly as a system is developed and refactored going forwards. Sometimes the discipline of unit testing forces programmers to ensure they fully understand a problem before coding resulting in a better solution. Certainly this can’t be the only form of testing, but it is an important building block to a quality software system.

Competing Web Development Systems

Introduction

The pace of development of Web based applications has been frenetic. The pace of development of tools to program the Web is also frenetic. There are new programming languages, libraries, servers and systems announced daily. Whatever happened to the good old days where you got a new version of Visual Studio and an updated C compiler every three years or so? In this blog posting, I want to look at the problems that people are trying to solve and the innovations they are trying to introduce to address them.

One nice thing about working at Sage is that we have so many products; someone will be using one of the systems mentioned in this article. For instance SageOne and Sage Billing Boss are written in Ruby on Rails. Sage 300 ERP and SageCRM use GWT extensively. Other products use JQuery and Node.JS. Several of the mobile offerings were produced with JQuery Mobile. So within Sage we have some opportunity to compare these on real non-trivial projects.

The Perfect Programming System

The real goal of all this is delivering real value to customers (end users). All other goals are in support of this. Nearly everyone is adopting some sort of Agile development process centered around delivering value (or even delights) to their customers. Once you are delivering customer value, then the next question is always: how to do that faster? Everyone are looking for a software development toolset that delivers customer delights and improves productivity.

That’s the high level goal, but what are the more detailed goals:

• Quick to enter changes, ideally just edit some code, save it and refresh the browser to see the results.
• Easy to test. Meaning there are good ways to write unit tests that can run quickly every build. That the system promotes test driven development.
• Easy to maintain. The system produces readable code that is easy for another programmer to figure out and change.
• Little impedance between problem domain and language. When thinking about what you want to do, it isn’t a huge change of perspective to write the code; somehow the code reflects what you want to do naturally.
• Easy to learn. You don’t need to know 3 programming languages, 5 scripting languages and 27 runtime libraries all of which interact in a complicated way to get up and running.
• Large ecosystem – lots of tools and libraries to save you work.
• Produces correct code. You don’t have to worry about unexpected syntax errors or other strange unexpected runtime errors that testing might have missed. By the same token you don’t have to spend a lot of time testing for and removing these sorts of things.
• Easy to install and setup. It’s easy for programmers to setup the development environment and get up and running quickly.
• Easy to deploy. It’s easy to transfer changes from the development environment to a live environment.
• Ability to integrate with (consume) modules that are part of legacy systems.
• The generated code is very small, fast and scalable.
• Great development tools for debugging, code analysis, etc.

In life, there are many tradeoffs to make. The same in programming. You might want three things, but you can only have two. Which do you pick? Which do other people pick? Usually with a large set of goals, some practical tradeoffs need to be made. Usually there will be a lot of disagreement in a group of people as to which goals are the most important and which ones can be lived without. Often the group will break up into different camps each with the conviction they have made the correct choice and theirs is the correct way to do things. So too with programming and hence all the different camps of web developers. Here we’ll look at a number of the camps and the tradeoffs they are making. Obviously I have my own opinions and preferences and the members of these various camps will probably disagree with my assessments.

For instance would you want to sacrifice several of the items for scalability? Everyone would like to be as popular as Facebook, but if you are a new web site, then perhaps it’s better to go live quicker to get feedback rather than spend the extra time engineering scalability. Often it’s more important to get feedback quicker so you can adapt your plans rather than over-engineer and be slower to market and slower to react. This fits in with the “fail fast” methodology of innovation where you are trying lots of things to see what works, you want them live as fast as possible to see what is good. Then you can take the good ideas and engineer them for the projected growth once you know what that will be.

Client Side Programming

As a Web developer you are targeting being able to run inside of Browsers. Here you can rely on HTML, CSS and JavaScript. Nothing else is common and nothing else will run on a large enough selection of Browsers and devices. If you don’t care about rich interaction or looks, you can just write using HTML and server side code, but this is Web 1.0 and probably won’t be very popular with your customers. There are good layout tools, or just text editors for writing HTML whether it’s static or generated by a server program. For CSS the tools aren’t as good, but there is help from libraries like CSSPie or Sass. Then for JavaScript, you can either write directly in JavaScript or use something that compiles another language into JavaScript; the merits of either approach are fiercely debated. Of course when writing in JavaScript you can use libraries like JQuery to hide the complexities and provide a lot of missing support.

Server Side Programming

You control the server and you control what runs on it, so you can theoretically use any programming language that can receive HTTP requests via a network socket. But usually you want something that integrates with a Web Server like IIS or Apache. There are good server frameworks to create the server programs in Java, .Net, C or any of a thousand scripting languages. Nearly every programming language has been adapted to write Web server applications with, even COBOL.

Client and Server Together

Although it is possible to program the Browser and the Web Server completely separately and independently. Perhaps only communicating through a well defined RESTful Web Services protocol. Usually we would like to leverage the skills of one to help with the other. We would also like a unified debugging and problem solving system. We would also like to reduce the number of specialized tools that people need to learn. Wouldn’t it be nice if the separation between client and server is largely transparent to the programmer? How well the client and server environments are harmonized is often a critical deciding factor in decision making.

Some Leading Development Platforms/Libraries

JQuery is a leading framework for developing using raw JavaScript. The innovation with JQuery was separating the JavaScript code out from the HTML that it controls. It uses a method to interact with the HTML very similar to CSS. The Query part of JQuery refers to how you build queries to specify which DOM objects to operate on, rather than having the JavaScript attached to each DOM object directly. JQuery has a powerful extension framework allowing many powerful libraries to be added to JQuery like SproutCore. A key goal of JQuery is to separate the JavaScript code from the HTML and then to insulate the JavaScript programmer from many of the Browser dependent parts of JavaScript like making AJAX RPC calls. The difficult part of JQuery is how it interacts with the DOM, although very powerful is quite complex and tricky. JQuery by itself is a client framework, but it integrates to pretty much any server framework. In fact Microsoft has given up on running .Net in the Browser and is now promoting using JavaScript with JQuery communicating back to .Net running on the server.

Node.js is a JavaScript server framework. For people who love JavaScript and JQuery, it then kills them to have to program the server in something else like Java, .Net, PHP or Ruby. They love JavaScript and would like to do all their work in JavaScript. With the Chrome Browser, Google introduced the incredibly powerful V8 JavaScript engine. Node.JS then uses the Chrome V8 engine to run JavaScript on the server. Additionally the Node.js developers consider using a thread to execute each web request as too much overhead (nearly every other server framework uses threads). So they also brought all the asynchronous ideas associated with JavaScript from the Browser to the Server. So anything that is executed outside of Node.js is done purely asynchronously, such as calls to the database, operating system or such. This is very powerful and very low overhead for highly scalable systems. However it is fairly difficult to program with (according to me, not Node.js fans). To help tame the asynchronous problem, Bruno Jouhier, an Architect at Sage developed streamline.js which he talked about here.

Ruby on Rails is a server framework for developing the server side of things in the Ruby scripting language. In a way Ruby on Rails is a contradiction, since it is a framework that was developed to eliminate frameworks. Many people feel that frameworks and libraries just get in the way. Why do you need all these middleware components to connect things up? Why can’t the UI just access things in the database directly without a lot of messing around with intervening layers? Ruby on Rails implements web screens using a strict MVC model, where Ruby helps generate the Model part directly on SQL database tables, so the Model part is your business logic. After all why would you need anything else? Ruby on Rails is a server framework similar to ASP or JSP, but people want AJAX web applications, so you write any richer or interactive parts of your web pages in JavaScript with JQuery that then talks back to the Ruby components on the server.

Google Web Toolkit (GWT) is a framework where you program in Java which is then compiled to JavaScript for the Browser half of the application. The server side is programmed in Java which runs as a Java Servlet under a Java container like Tomcat or Jetty. The nice thing is that you do all your programming in Java so you are only using one language. You also get to use all the thousands of tools and libraries that have been created for Java over the years. Since it’s a compiled language, you will not get runtime errors due to typo’s in your code like you do for scripting languages (some just ignore the errors which is even more confusing). On the downside you have to compile your code, so you can’t run it as quickly as you can with a scripting language. I’m not really sure which is faster, since when I use scripting languages I’m always fixing undefined variables and syntax errors, so although I can run quickly, I have to run many times to get things working. GWT also lets you use the Java symbolic debugger to debug your code which is very helpful.

Dart is a new project from Google that attempts to combine the best of the worlds of JavaScript/JQuery/Node.JS with Java/GWT. The idea is that Dart is a structured object oriented language (like Java) but you can choose whether variables need to be defined and adds a number of dynamic features usually only seen in scripting languages. Then the idea is that Dart can be run in some Browsers natively, allowing scripting type rapid development and for Browsers that don’t support it (like probably IE), you compile Dart to JavaScript. Dart isn’t ready for primetime yet, and it isn’t clear whether it will succeed, but it does show that the pace of development of these tools and systems is continuing at a rapid pace. Some theorize that the real intent of Dart is to replace Java as the tool used to develop Android applications, mainly due to all the frivolous lawsuits from Oracle.

Which to Choose?

The choice comes down to which items are most important to you in the list of what makes a perfect programming system. Which one gives you the best vibes, or feels right for what you want to do. Often this is heavily influenced by your current experience; what technologies are you most fluent in already; which new ones will you need to learn. This usually goes beyond a single person and you have to consider the expertise and experience of your development team.

A big influencer is whether you are writing a new system completely from scratch or you have to extend or integrate to a large existing system. If you need to extend or integrate then often your choices are limited to things that can easily do that. If you are writing a new system then you are much free’er to choose. For the new system, often it depends how much you want to rapidly prototype, versus how much the requirements are set and you want to implement quickly.

You also have to consider the longevity of the tools you are choosing. Perhaps you don’t care since you just want to rapidly prototype and start from scratch every few months. Or perhaps you know the system you are developing will need to be maintained and extended for the next ten or twenty years. Then you have to look carefully at the health of the open source community around the tool, or the commitment of the company behind the product to keep developing it.

Summary

There are many more systems in use. As I mentioned, nearly every programming language ever invented has a Web Development framework. People fuss quite a bit about which one to use, but I think once you bite the bullet and dive in, you can’t go that wrong with any of these. Also most of these can interact and interoperate at different levels, so usually you can build a composite system using many different tools (you just have to learn them all). Anyway the Web continues to be a platform that promotes innovation and fosters continuing learning.

20 Years of Change in ERP

Then

Previously I blogged on where I think ERP is going in the next ten years, recently I was thinking about where we have come from over the last twenty years. I started working on the Accpac product at Computer Associates back in 1992 to work on Accpac Plus for Windows^*. At that time our customers installed our software from 5¼” floppy disks onto PCs running MS-DOS connected to Novell Networks. Usually the Accounting department had a separate LAN from everyone else due to security concerns. In QA we validated the hardware and BIOS we would run on, supporting only certain IBM and Compaq models. Going online usually meant having a CompuServe or AOL account connected via a modem on your phone line.

Evolution of ERP

The core of an ERP system is the double-entry accounting method which has been around since it was codified by Luca Pacioli in the 15^th century. The basics of how this works has largely remained the same since then. Efforts to “modernize” double-entry accounting have repeatedly resulted in financial fiascos like Enron. Much of the work over the past twenty years has been expanding the ERP system beyond basic financial accounting. Expanding to the front office through CRM, expanding to the HR department with HR and Payroll functionality, expanding into planning with advanced operation modules and project and job costing.

Now

Jump ahead 20 or so years to the present day. The product name is now Sage 300 ERP. We’ve progressed from Windows 3.1 through 95, 98, NT, 2000, XP, Vista to the present day Windows 7. We’ve progressed through 5¼” to 3½” floppies to CDs to DVDs to now downloading and installing the product from the Internet. Not only is the Accounting department typically on the main corporate network (usually a Windows Server network), but horror of horrors they are even connected to the Internet. We now support any PC, Workstation or Laptop that runs Windows regardless of manufacturer.

A lot of these changes are due to the industry maturing, buying a new computer isn’t such an adventure as you hope its BIOS is compatible with what you need to run. You can now safely buy computers from thousands of manufacturers running various CPUs from multiple companies like AMD or Intel. You can trust that you can use any USB device with that computer. Very rarely do you ever install a new hardware piece in a drive bay or motherboard slot. Most people have a dedicated high speed link to the Internet.

Another advance that we’ve seen is the rise of virtualization. Who would have thought twenty years ago that you could run another copy of your operating system in another window? Twenty years ago, multi-tasking barely worked. Now we can run all sorts of virtualized operating systems on our laptop at once. We have servers hosting dozens for virtualized servers that can be accessed from anywhere. Virtual images even integrate with the host environment so when you run a program you don’t know if it’s native or virtualized.

Twenty years ago, Windows was strictly single user. Now with Terminal Services and Citrix, Windows is truly multi-user. Users can access a central Windows server using a thin client to see their desktop. This allows easier centralized installations and management, since only one central server needs to be maintained. Additionally there is client software from all sorts of computers and devices to access your Windows session. With this you can access Sage 300 from a table like the iPad or from a non-Windows PC like a Macbook. Certainly UNIX/Linux fans aren’t impressed with this, but it did bring multi-user back into the mainstream.

Online

Utilizing the multi-user and virtualization technologies allowed us to offer a cloud version of Sage 300 ERP with AccpacOnline.com. With this, Sage manages the datacenter and handles all the tasks like backing up the data, keeping the hardware and software up to date. Then the customer just has to use our ERP package, accessing it from a Citrix client. This then gives our customers a choice between buying or subscribing to our product. We have been doing this for ten years or so now. The diagram below shows the choices this gives you.

Connected Services

Today we enhance your ERP system with “connected services”. These services all run in the cloud and your ERP package connects to them for additional functionality and services. A prime example is the Sage Payment Solutions connected service which allows you to take credit card transactions right in your application and then have it call the online service to process the transaction in real time. The diagram below shows our connected service web strategy, how our on premise or hosted business applications access all these cloud services.

Summary

We’ve come a long way in twenty years from MS-DOS character based applications to 16-Bit Windows applications to 32-Bit Windows applications to Web applications. We’ve had many improvements in hardware, operating systems and connectivity. All this generally keeps things from getting boring or stale. As we move forwards we are seeing ERP spreading to devices beyond PC to tablets and phones and much great utilization of connectivity, sharing and social interaction that this enables.

^* You can still see remnants of the old names in the product files. For instance there is the runtime\plus.ini which is still left over from the original name. In the original 16-bit version all the DLLs started with capw (CA Plus for Windows) and later were changed to start with a4w for the 32-Bit version. Generally if you look through the file names, or the SDK API documentation you can tell when something was introduced by the naming convention used.

Stability Testing of Sage 300 ERP

Introduction

As we roll out Web versions of the Sage 300 ERP (Accpac) accounting modules, we need to ensure that the system can run for a long time without requiring a server re-boot or other manual corrective action. This is far more important in a Web environment than client/server. In client/server the Accpac Business Logic typically runs on the client and if it crashes (which is bad), it only affects one person. In the Web if something crashes then it could affect all users of the system. Many regular GPFs can be captured and handled just causing one person to redo their work; however for more severe errors they could crash a server process or even the server itself. Rebooting a server is a long time for people to wait to continue their work (especially if there is no one around to do the reboot).

As we develop Sage 300 ERP we want to ensure that the system is completely stable and can run for months and months under heavy multi-user load. Hopefully the only time you need to reboot your server is when it installs the monthly patch Tuesday Microsoft Updates.

Why Do Things Crash?

In both client/server and web computing we need to deal with basic GPFs which are usually caused by programming mistakes like memory overruns. In the early days of Windows these were very prevalent, but in more modern times these can often be caught and handled within the program. Plus modern programming systems like Java and .Net work to prevent these sorts of problems by not supporting the programming paradigms that lead to them. Even for C programs, there are better static code analysis programs and methods to test and eliminate these sorts of bugs. Generally these sorts of problems are fairly reproducible and easily diagnosed and solved in a source code debugger.

The harder to reproduce and diagnose problems usually involve running the system for a long time with a lot of users before something bad suddenly happens. The problem can’t be reproduced with just one user. It’s not known how to reproduce the problem in a shorter time. Often once we know what the problem really is, then we can construct an easier way to reproduce it, but of course now we don’t need it, on the other hand throwing in a unit test to make sure this doesn’t happen again is a good idea.

These sorts of problems usually start to occur when the system is stressed and low on resources. Sometimes the problem is that our program has a resource leak and is the cause of the system being low on resources. Another cause is multi-user. We run as a multi-threaded process on the server where the individual threads handle individual web requests. If these aren’t coded properly they could conflict and cause problems. The Java and .Net environments don’t really help with these situations and often make things harder by hiding the details of what is going on making these harder to solve. These are the problems we really need to avoid.

How to Find These Problems

How do we solve these problems? How do we test for them? We use a combination of manual and automated testing to find if we have these problems and then employ a number of tools to track what happened.

Usually every Friday we get a large number of people to participate in manual multi-user testing where everyone accesses a common server and performs transactions, either as part of functional testing that needs to be done anyway or following scripts that concentrate effort in areas we are concerned about. Then anything weird that happens is recorded for later investigation.

We also use automated testing. We extensively use Fitnesse and Selenium as I blogged about here and here. We can string all these tests together, or run them repeatedly to create long running tests that the system is stable and that nothing bad happens over time. Another very powerful tool is JMeter which performs load testing on Web Servers. Here we record the HTTP requests that the browser makes to the server as users perform their tasks, then we build these into tests that we can run for as long as we want. JMeter will then play back the HTTP requests to the server, but it can simulate any number of users doing this. This gives us a way to easily automate multi-user testing and run with far more users and for longer periods of time than manual testing allows. Below is a screen shot of JMeter with one of our tests loaded. It might be hard to read the screen shot, but notice under the Runtime item is a Pick One Randomly item. This item will pick one of several tests randomly and run it. Then we simulate a large number of users doing this for long periods of time.

 

How to Fix These Problems

The key to fixing these sorts of problems is diagnosing what is going on. Usually if you can find the correct place in the code where things are breaking down, then fixing the problem is usually fairly easy. The real trick is finding that one line of code that requires fixing. Though major refactoring’s are required now and then, and then you need to re-test to ensure you have really fixed the problem and not introduced any new problems.

At the most basic level we include a lot of logging in the product, so when we run these tests we have logging turned on and can study the logs to see what happened. This only gives clues of places to look and often we have to add more logging and then re-run the test. Logging isn’t great, but it’s very tried and true and often gets you there in the end. We use log4j for our server logging.

For memory leaks, the Java JDK gives a number of good tools to diagnose problems. The Java JDK includes the tool JConsole which you can connect to the JVM used by Tomcat and monitor the memory usage. It also records the number of classes loaded, CPU utilization and a few other interesting statistics. But one really cool feature is the ability to generate a dump file of the Java Heap at any time. From the MBean tab in JConsole you can choose com.sun.management – HotSpotDiagnostic – Operations – dumpHeap. The p0 parameter is the name of the dump file and should have an .hprof extension. By default the file will end up in the Tomcat folder. After you invoke this you get a binary dump of the Java heap, now what to do with it? Fortunately the JDK includes another tool called jhat which is used to analyze the heap and then run a web server that lets you browse the contents of the heap from a web browser. From this you can see all the Java objects in the heap, and hopefully determine what is leaking (usually one of the objects with a very high instance count). You can also see the contents of each object and who references it.

One good tool for solving these problems is ReplayDirector from Replay Solutions. This is a commercial product that records everything that happens in the JVM. You can configure your Tomcat service, so that ReplayDirector records everything that happens as you run. It doesn’t matter if you call outside of Java since it will record the call and the result. Then you can play back the recording in the regular Eclipse Java debugger as often as you like to figure out the problem. When testing, the testers can put marks in the recording to indicate where and what happened. You can set breakpoints on any REST or other HTTP call into Tomcat, you can expand log4j messages to see them even if they aren’t logged. Below is a screenshot of the main ReplayDirector Eclipse plugin. This was recorded on a server being driven by JMeter. The green line is CPU usage; the blue line is memory usage. From the looks of the blue line, you can probably guess that I’m looking for a memory leak. I can set a breakpoint at a later SData request and then break into the program and look around in the debugger to see what is going on.

Summary

Ensuring a Web Server can keep on running reliably for long periods of time can sometimes be quite a difficult task. Fortunately the tools that help with this are getting better and better, so we can monitor, record and reproduce all the weird things that testing reveals and ensure that our customers never see these sorts of things themselves.