Stephen Smith's Blog

Musings on Machine Learning…

Archive for July 2010

Accpac Multi-Version Support

with 2 comments

Sage ERP Accpac has the ability to dynamically add accounting applications. This means independent software vendors (ISVs) can produce Accpac modules that are just installed into an existing Accpac installation. They don’t modify any of the original Accpac files, they just install into a new directory under the Accpac program tree. Additionally when you install a new version of an Accounting Application, it installs into a new directory. You still use the old one, until you go into Data Activation and activate the database to use the new version. Until you do this you keep using the old one. This is all done on a company by company basis, so you could be running one company at G/L 5.5A and another at G/L 5.6A. This means you can activate your companies one at a time on different weekends, perhaps doing one first as a test case (a separate test system would be better), before activating everything to the new version. It also allows you to activate some applications, but leave others at older versions; until customizations get updated (there are compatibility restrictions that are enforced). This feature was put into Accpac for Windows originally due to customer feedback from Accpac Plus (on MSDOS) where people complained about having to convert/activate everything at once.

This is very different from all-in-one accounting applications. These are deployed as a single EXE, when you install a new version, either at install time or the first time you run it, it converts all the data to the new version and you must do this, you cannot continue using the old data. If you want a third party application, it has to be compiled into the main EXE making it very difficult to get a selection of third party applications.

Multi-Version support has allowed us to do things like support multiple application versions on Accpac Online, but using the same version of System Manager. This allows us to host customers running both 5.5 and 5.6 applications on the same server. Basically each Accpac Online Citrix Server runs the latest System Manager and then has all the supported application versions installed. Customers can then choose when it is convenient to move to a new version, they aren’t forced to move on the weekend that we install the new version. This is in contrast to companies like SalesForce.com where you are automatically moved to any new version whether you want to or not. This improves our TCO since we don’t need to deploy separate Terminal servers for each Accpac version.

Although, not always popular, this feature has allowed us to do staggered releases. If development has finished System Manager, G/L, A/P and A/R we can release these while development continues to finish I/C, O/E, P/O etc. Then users can immediately get the benefit from the new features and improvements in the financial modules while still using the older operations modules. We try to release everything at once, since this minimizes disruptions for installing new versions and causes less confusion.  But this does give us flexibility depending on business needs. It also allows us to beta a few modules at a time allowing longer beta periods for the core modules.

If you’ve looked at the Accpac programs folder in Windows Explorer, you see a directory structure under the Accpac programs folder of a whole bunch of directories like GL56A, MI56A, TS56A, etc. Basically for each Accpac SDK application, a two character prefix is registered with a central registry. This is then owned by the ISV. There are a number of these two character application prefixes that Sage reserves such as GL, AP, AR, TX, BK, IC, OE and PO. Then the version number is of the form number, number, letter and it can be assigned any way the ISV likes (as long as it increases from version to version). Each version of each product then has its own unique directory. So nothing from GL56A will overwrite anything in GL55A; each version is fully preserved. This is similar to our multi-language support. For the French, we don’t just replace the English strings with French strings; there are ENG subdirectories that contain the English strings and FRA subdirectories that hold the French strings. This way we can install as many languages as we like, and have them all usable at once.

Database activation is something that is common in modular Accounting Packages. If you aren’t using a module, you don’t need to activate it. In this context activating means adding its database tables into the database and showing its icons on the desktop. This shouldn’t be confused with license activation where you are activating your license to show you’ve paid for the product and are free to use it. We keep track of which applications are activated and which version in the CSAPP table in the database. This table contains the list of activated applications and their versions. A separate copy of this table is in each Accpac database. When we open a company we read this table first and then use it to populate the icons on the desktop. When you run data activation, it builds its list by finding directories with names of the form AANNA under the Accpac programs folder, it then chooses any applications that aren’t in CSAPP or have a newer version to populate its list of applications to activate.

This can’t be an entire free-for-all. Generally an application like O/E will feed transactions into A/R and if it uses new features in A/R then it needs a new version of A/R. Usually the higher up the stack the application, the more other applications it requires to be at its version. The Database Activation program will ensure you activate things in a manner that will work, or it either stops you activating or disables applications until their dependencies are activated. Each application has an xx.ini file (like say AR60A\AR.INI) in its application directory; in this file is a compatibility section that looks like:

[Compatibility]
BK=6.0A
TX=6.0A
GL=6.0A,S
SYSTEM=6.0A
OE=6.0A,B
PM=6.0A,B
EW=6.0A,B

This is for A/R 6.0A and specifies that it requires Bank and Tax 6.0A. A/R doesn’t require G/L be present (if it isn’t it will export its G/L batches for import somewhere else), so GL=6.0A,S means that G/L isn’t required, but if it is present then it must be at least version 6.0A. The ones with the ,B after them are applications that we know depend on us, so Activation can automatically offer to activate them when you choose this one.

Another key enabler of this, are our Accpac Views (or Accpac Business Logic Objects). The View interface remains fixed and we make an effort to keep it compatible from version to version. This benefits us, since this is how Accpac Accounting modules communicate with each other and it benefits customers since the interface to the business logic remains compatible, protecting customer’s investments in customizations. Further we have to be careful to keep the entire programmer APIs in System Manager compatible so we don’t break older versions. This is good for Accpac and good for ISVs since they don’t have to worry as much that a new version of System Manager will break them.

Hopefully this article sheds some light on how the modular nature of Accpac allows us to support things like multiple versions, multiple languages and installable applications.

Written by smist08

July 30, 2010 at 5:37 pm

Accpac and Business Continuity

with 7 comments

Introduction

I remember long ago when hard-disks in PCs were new, that I never trusted what I saved until I had it backed up to some floppy disks. These days, a lot of people don’t seem to worry about the hard drive on their PC failing. In fact I think more people back up because they are worried they will get a virus or Trojan and have to re-install their computer from scratch. But even with better reliability hard drives can fail and even with virus scanning software, you can still get infected.

It used to be that you did a backup every night and if something failed, you said oh well and restored from backup. Often you would lose the day’s work when things failed, and then get behind another day (if you were lucky) while the system was restored. Generally you backed up to tape, perhaps doing a full backup on the weekend and incremental backups on weekdays; but, you could backup your entire hard drive to one tape. Tapes were never that reliable and restoring a backup was always a nail-biting experience. People started using CDs and later DVDs to backup to as these were more reliable, longer lasting and far easier to restore from (especially individual files).

However hard disk technology fast outpaced all these backup technologies. You can now get 2 tera-byte hard drives for around $100.  This is 500 4Gig DVDs. So as it stands, really, the only way to backup a large hard drive is to another large hard drive. Along these lines there are many solutions from various RAID and disk mirroring solutions to replicated database servers.

Further today’s businesses can’t afford to have their computer systems down for any length of time. Businesses are now much reliant on their computerized systems and can’t run manually for a few days while the automated system is fixed. Today’s businesses require their systems are available and working properly 24x7x365. Most businesses now develop a formal Business Continuity Plan (http://en.wikipedia.org/wiki/Business_continuity_planning). This is a formal plan that documents how the business will recover from all sorts of common and uncommon disasters.

All these backup and redundant systems cost money, so there is a business decision to make. What does it cost you, when your systems are down? Do you run with many disparate data centers or one centralized one? What are the environmental risks to your data centers (earthquakes, hurricanes, tornadoes, etc.)? Backup generators can be quite expensive and require quite a bit of maintenance. Some redundant systems impose a performance penalty while you are running normally; is it worth living with this penalty compared to how often you need the redundancy?

Hardware Solutions

At the hardware level, first there are all sorts of redundant drive solutions. There are lots of ways to mirror hard drives so if one fails, the other will be used. These can either be through multiple drives in a server made redundant through RAID (http://en.wikipedia.org/wiki/RAID) or via various NAS (http://en.wikipedia.org/wiki/Network-attached_storage) and SAN (http://en.wikipedia.org/wiki/Storage_Area_Network) solutions. The thing to remember here is that although the disk drives are redundant, the enclosure holding the drives isn’t. This means if something in the enclosure fails, then the drives are inaccessible until they are placed in a new enclosure (server, NAS, etc.). There is also the problem that being in the same box, they could all be damaged by a flood (bathroom above overflows), a power surge (ESCOM), a fire, or some other local event.

Most companies have uninterruptable power supplies (http://en.wikipedia.org/wiki/Uninterruptible_power_supply) for individual computers, the data center or the whole building. Most servers, these days, have dual redundant power supplies (http://en.wikipedia.org/wiki/Blade_server), this is since the power supply can be damaged by power surges, or generally power supplies seem to fail as often as disk drives. Redundant fans help, along with temperature monitoring software. Check your fans to ensure they are clean and working. Many BIOS’s will do a diagnostic on the fans at startup and let you know if one has failed.

Most companies like to have their data in multiple geographic locations. At a minimum these means off-site backups where backups are regularly moved to and stored at a separate location. This means if your local data center is damaged, once you get your hardware up and running again, then you can restore your backups and continue business. For many businesses this isn’t sufficient. They can’t be down while the data center is restored. Generally businesses would like a hot standby at an alternate location that can be switched to take over, if not automatically then very quickly.

Database Features

Most modern databases including SQL Server provide quite a bit of functionality to help solve these problems. This can include mirroring or replicating databases to multiple locations to being able to backup the database server without shutting it down. There are quite an array of options and these are to allow tradeoffs to be made between redundancy and performance. For instance if you completely mirror a database to a different geographic region, then a user will need to wait for a transaction to be committed to both databases before proceeding, this can be slow since the link to the remote connection will be slower than the link to a local server. So the database vendors introduced ideas like lazy replication (http://en.wikipedia.org/wiki/Optimistic_replication). In this case the user proceeds as soon as the transaction is saved on the primary server and then this server lazily performs the updates to the backup server. This then means that if the primary server fails, whatever is in the lazy queue at the time will be lost. However the tradeoff is that hopefully the gained productivity while the primary server is working out weighs the few lost transaction in the rare instances when it fails.

Accpac

Everything that has been discussed so far can be used by Accpac, but doesn’t really have much to do with Accpac. These are all database and server features and not Accpac features. But Accpac is a very flexible product that allows many configurations. So let’s spend a little time discussing Accpac configurations that fit nicely into these scenarios. Remember that to get up and running again, you need to have working Accpac programs, registry settings, site configuration database as well as the company database. You want to ensure that none of these is a single point of failure, that each has a way to either be restored or a way to switch to a hot standby. Generally the program files, registry settings and site configuration don’t change often whereas the company databases are being written to continuously. Ideally you would have hot standbys to switch in when anything fails, but practically this presents problems, like not being able to have two computers on your network with the same name.

If you are using SQL Server and configure Database Setup with the server name, then since this is shared by everyone, you only need to change the server name in one place in Database Setup to get everyone using a new server.

Workstation Setup

In this configuration, the Accpac programs are stored on a central file server and the database server is either this file server or more usually a separate database server. Each workstation is configured to point to this file server and the orgs.ism files on the file server points to the database server. If an individual workstation fails, then that one person can’t do any work until that workstation is replaced. This isn’t so serious because only 1 person is idle rather than the entire Enterprise. Usually the IS department will have a standard “ghost” image that they can restore on a new workstation and get that person going again in a few hours.

The database server contains the most valuable data. It needs to be backed up frequently. Using mirrored drives (RAID 5) or using a mirrored server is a big help.

The file server can fail. Usually you would want this running on mirrored RAID drives. You want a good backup and ghost image, so you can restore things quickly in case of a catastrophic failure. Windows Servers has a feature called DFS (Distributed File System) (http://www.informit.com/articles/article.aspx?p=174367&seqNum=2) (http://en.wikipedia.org/wiki/Distributed_File_System_(Microsoft)); I haven’t tried DFS, but it looks helpful. Another option is to have a restoration contract with your vendor, for instance Dell offers a service where they will restore your server to running in less than 4 hours (quite a long time, but often acceptable).

Terminal Server

In this configuration, all the users access Accpac by logging into a Terminal Server session. All the Accpac programs and configuration files are installed on this Terminal Server and then access a separate Database Server. This configuration has some of the same issues as the previous one, in that the site directory will be only on one server and needs to be made redundant, or easily restored. For terminal server usually you would set up at least two terminal servers, then since you have them have your users split between them (either via load balancing or just assign them). This way you get maximum performance when everything is working, but if one fails you switch those users to the other and away they go. Just make sure that you have enough memory that you can run with all the users on the one server (perhaps a bit slower) for the day or two until you get the second one fixed.

Accpac 6

Once we have migrated to a true web based application, then where ever in this article we mention Terminal Server would become Web Server. Then all the concepts would be the same.

Reporting Server

In either the workstation setup or terminal server case, you probably now have a mirrored SQL Server that isn’t doing anything when things are working well. One thing many customers do is identify users that only do reporting and point them at the replicated server. Then this moves some of the reporting burden from the primary server to give better performance all around.

Conclusion

The company would setup a configuration like that below.

They would have a well documented business continuity plan. All the machines would be ghosted (http://en.wikipedia.org/wiki/Ghost_(software)) so that any failed machine can be restored using new hardware quickly. Redundant components will be used where ever possible, such as using RAID 5 mirrored disks within each unit, in addition to having redundant hardware. The backup terminal server and secondary SQL Server would be located in a different geographic location than the primary equipment. The company would test switching over from one computer to another to ensure the documentation in the business continuity plan is correct and that they can actually do it. They would purchase the equipment from a vendor that can quickly replace any failed component (perhaps with a less than one day service level agreement (SLA)).

Update 2013/06/08: Note that Windows Server 2012 now support high available file shares via SMB3 – http://www.infoworld.com/d/microsoft-windows/windows-server-2012-brings-high-availability-file-shares-214851. This is a good way to keep your file servers HA.

Written by smist08

July 24, 2010 at 10:39 pm

International Support in Accpac

with 10 comments

Introduction

Sage ERP Accpac has installations in hundreds of countries. Accpac has traditionally had strong sales in Canada, the US, the Caribbean, South-East Asia, Africa, the Middle East and Australia/New Zealand. Additionally we have scattered sales in many other regions. How does Accpac address the varied needs of so many legislative jurisdictions, locations and cultures?

Everything in this article applies to both Accpac 5.6A as well as the forthcoming Accpac 6.0A. However as we move to becoming a web based application, we inherit all the good work performed by the WWW standards bodies, which are very good at ensuring the Web is accessible to all. One such standards body is: http://www.w3.org/International/. For instance in https://smist08.wordpress.com/2010/05/28/how-to-layout-forms-in-sage-erp-accpac-6/ I talk about how our new screens will automatically adjust to string lengths as well as how they can automatically lay themselves out in right-to-left format.

Multi-Lingual

First off Accpac is multi-lingual. Accpac supports installing more than one language at a time and languages are associated with users. There is not a French version of Accpac nor a Spanish version; instead you can add French or Spanish as installed languages. Then you can have some users running French, some Spanish and some English. This is especially important for International companies with branches or divisions in several countries. Of course the data in the database only has one version of each string, so if a French user enters say a batch description in French then a Spanish user will see this description in French. But all the forms, reports and messages will be in the correct language for each user. Out of the box we support English, French, Spanish and Chinese (Simplified and Traditional).

Regional Settings

At the next level we have to take care to honor regional settings. These include things like date formats (mm/dd/yy versus dd/mm/yy, etc.), number formatting characters (whether a comma or period is a thousands separator or decimal point), money symbols and such. Generally we just have to be careful to use the values set in the host Browser or Operating System and use them; along with a number of settings we configure in Common Services.

Multi-Currency

Accpac has always had multi-currency ability. Generally you set your company with a “home” currency, and then all incoming/outgoing monetary amounts are in a “source” currency. We always report amounts in source and home currencies. You have to be careful in reports that you add up amounts in the same currency or the totals won’t make any sense. Most reports provide a table of the totals of each source currency and then a grand total in home currency. We support currencies with 0, 1, 2 or 3 decimal places. We also support money amounts with 18 significant digits (15 before the decimal and 3 after). This is more than the 16 digits that double precision floating supports (a method used by many competing Accounting Packages). When converting currencies we support either multiplying or dividing the exchange rates. We maintain centralized tables of all exchange rates, but you can override these at any point.

We also support some additional amounts in third currencies. For instance in Singapore, by law if a company does over 50% of their revenue in a given currency, say USD, then they have to use that currency as their home currency. However they still need to report all sales tax in Singapore Dollars. So maybe they mostly sell to the US, but make a sale to Thailand. So they are paid in Thai Baht, convert the amount to USD for their sales totals and report the sales tax in Singapore Dollars. This is the reason for our “tax reporting currency” feature that allows you to report your sales taxes in a different currency than your home currency.

In version 5.5A we overhauled the currency revaluation process. This was to comply with the new international standard FRS 21. Although this won’t be required in North America for some time, it was required much sooner in other jurisdictions, such as South-East Asia where multi-currency transactions are very prevalent. This new method is quite a bit more complicated than what Accpac was previously using, but this shows our commitment to addressing International concerns and maintaining International standards.

Localization

We don’t produce localized versions of Accpac, there is only one version and we add general support for all jurisdictions there. Rather than shipping an Australian version that supports Australian sales tax and an American version that supports US sales tax, we ship one version with a powerful enough sales tax engine that can support all the sales taxes we have encountered. This then easily allows International companies to use the same Accpac programs and databases for all their transactions.

The reason we added surtaxes to our sales tax module a few versions ago was to support sales taxes in India. In India there are 5 sales taxes and two of them are surtaxes (taxes on tax). So rather than just adding a customization for India we added a general feature, so if any other regions have surtaxes like India, then they are already set. Our hope being that we have a strong enough general support to handle whatever is thrown at us.

Fortunately GAAP (Generally Accepted Accounting Practices) are applied fairly universally. As more countries compete globally and look for global investment, more and more countries are insisting on GAAP. Generally all countries are adopting standards that are agreed on internationally, just that the adoption times can be quite different.

This isn’t to say we wouldn’t produce localized modules. The nice thing about Accpac is that you can add program modules without requiring changes to the base product. Hence we can offer an XBRL Financial Reporting module in Singapore to meet Singapore government reporting requirements. In a similar way Sage regions can develop custom modules for their market, perhaps such as an import/export tax module for India.

Market Differences

A big difference in each geographic market is also the definition of an SMB (Small to Medium Sized Business, http://en.wikipedia.org/wiki/Small_and_medium_enterprises). Our typical sales in Africa and Asia are to much larger companies than we usually sell to in North America. This leads to quite a difference in the feature requests our Product Management team receives from around the world. For instance we might receive requests from Africa for much more sophisticated Inventory processing, whereas from North America they are often looking for product simplification and ease of use.

This is a bigger cause of regional differences than local regulatory requirements. Often the requests we get from one region for one type of industry would be valuable all over the world for that sized company and that particular industry. It’s just that we seem to sell to different industries and different sized companies in different regions.

Summary

Certainly being an International product has its challenges; but, there are lots of rewards as well. I really enjoy traveling to the International Sage Insights and Visions conferences in Australia, Africa and Asia. I find the challenges and accomplishments of the partners in each region have a lot in common. I’m always amazed at the way Accpac is customized and deployed to handle very diverse needs.

Written by smist08

July 17, 2010 at 7:51 pm

Accpac and It’s Databases

with 15 comments

Sage ERP Accpac supports running using either Pervasive.SQL (http://www.pervasivedb.com/Database/Pages/home.aspx), Microsoft SQL Server (http://www.microsoft.com/sqlserver/2008/en/us/) or Oracle (http://www.oracle.com/us/products/database/index.html) Database Servers. This blog posting is going to talk a bit about how Accpac Accounting applications access the database, give some rationale for the way we do things and look at where we may go in the future.

Accpac Plus, the original character based product for first CP/M (http://en.wikipedia.org/wiki/CP/M) and then MS-DOS (http://en.wikipedia.org/wiki/MS-DOS) used a proprietary built-in database. When this product was developed there weren’t standard off the shelf packages to use. This was a simple ISAM based package that was fast and easy to install and configure. The limitations were that:

  • It didn’t support database transactions so if something failed the database was left corrupted.
  • It used record locking as its multi-user model, so once too many people were doing things, too many records would be locked and things would grind to a halt.
  • It didn’t have a server version, so relied on file system locks which were hard to support for all the different network operating system.
  • It didn’t support SQL and hence didn’t fully support ODBC or any other standard interface mechanism making it hard to use external tools like for reporting.

So when Accpac for Windows was developed, we made the decision to use standard off-the-shelf databases. We decided to base our new database support on transactions and to define a set of requirements that a database would need to support to work with Accpac.

CA-Accpac/2000 originally shipped supporting Btrieve 6.15, and then with version 2.5A we added support for Microsoft SQL Server 6. Later we added support for IBM DB2 and Oracle Databases. We can easily move Accpac data from one database to another via our Database Dump and Load utilities.

Along the way we’ve evolved our API and easily moved from database version to database version. Many third party tools integrate with Accpac through open database interfaces. We currently have a very robust and scalable solution.

Accpac Database API

Accpac is a three tier application with the business logic separated from the database. Our business logic objects (the real core of our Accounting Applications) communicate with the database via a common database API. Then we have database drivers that translate this API into calls to the database server currently being used. How the database driver accomplishes this is entirely up to the driver. Our SQL Server driver uses ODBC to communicate with the SQL Sever Native driver. Oracle uses the Oracle ODBC driver. For Pervasive we communicate with both the relational layer using SQL calls, and with the transactional layer making direct Pervasive API calls. The key point is that all this detail is invisible to the Business Logic. The application programmer creating our business logic objects, just needs to know our Accpac database API to do his job. Then all the differences in the databases are isolated to the Accpac database driver. If a new version of a database server is released and Accpac needs to be updated to support it, then its just a matter of providing a new database driver, without requiring any other application changes.

The Accpac database API includes a number of record oriented API calls, a number of meta-data calls and a number of set based operations. The record based APIs operate on one record at a time, such as read a record based on a supplied key, update that record. The meta-data calls return information about the structure of the database, things like get a list of all the tables in the database, get information on all the fields in a table. The set oriented API calls operate on a set of records at a time. Such as read all records where the description starts with A, or update all records changing any with customer number 1200 to 1400, or delete all records with status “deleted”.

Accpac Architecture Diagram

Reporting

Reporting works a bit differently. We use Crystal Reports as our reporting engine and then Crystal provides the mechanisms to have database independent reports. From Accpac the same Crystal Report is used for Pervasive.SQL, Oracle and SQL Server. We just point Crystal at the correct database and provide the necessary connection information and then let Crystal do the rest. We provide an Accpac reporting API which then handles the details of talking to Crystal. Originally this API was used to talk to CA-RET the Computer Associates Reporting tools, then it was converted to talk to Crystal’s crpe32.dll interface, later to Crystal COM’s interface. With Accpac 6.0A we are upgrading to Crystal Reports 2008 and will use the .Net interface for the VB UIs and the Java interface when running Crystal from the Web. Even though the way we do reporting has changed quite a bit over the years, this has been largely invisible to the applications which just talk to Accpac System Manager’s API and then let System Manager take care of the details.

The Accpac Financial Reporter accesses the database through the Accpac Business Logic and hence the regular Accpac Database API. Accpac Intelligence and Accpac Insight work in a similar manner to Crystal and provide their own database independent mechanisms to access the various databases. Most other reporting solutions also work this way.

Database Requirements

Accpac does have a number of minimum requirements for any database it supports. This includes things like number of fields per table, number of keys supported, record length and such. One key requirement is that the database supports database transactions. As long as a database meets these minimum requirements then it is possible to write a database driver for Accpac to use that database. Note that supporting SQL is not a requirement.

Database Transactions

At the core of Accpac’s database support are database transactions. Any changes made to the database have to be done inside a transaction. A transaction is a group of database operations that are either all applied to the database, or none are applied to the database. So the application never has to worry about half of them being committed to the database. A key differentiator of database servers is how well they handle transactions. The application rule for Accpac is that when the database is changed, operations must be grouped together into a transaction so that the database always has full application integrity. This includes things like the General Ledger never being out of balance. When G/L entries are posted, each entry is a transaction and the database goes from one in balance state to another. This means that if something crashes on either the server or workstation, or an unexpected error occurs then the database will not end up in a corrupt state. Database integrity is always maintained via database transactions.

Multi-User Model

Then our multi-user model builds on the transactioning foundation. Within Accpac we use optimistic concurrency (http://en.wikipedia.org/wiki/Optimistic_concurrency_control) as our multi-user model. If two users go to edit the same record, both users bring the record up on their screen at the same time, then they make some changes to various fields, then both hit save. Now both users started with the same record and made independent changes and both want to save, what should the system do? In the most basic optimistic concurrency model, the first person to save wins, their changes are committed to the database and the second user gets an error message the “Record was modified by another user”. With optimistic concurrency we can be a little more clever than this, and for the second user, we could re-read the record and see what the first user changed and if they are unrelated fields then combine our changes and re-save the record. We do this in a number of cases, but it requires a bit of application support to define what “un-related fields” means. The alternative to optimistic concurrency is to lock the record when the first user reads it, and not let the second user read the record at all. The second user would get a message saying that someone is editing the record and they can’t access it. This saves the second user possibly losing some work. However it imposes a huge multiuser penalty since if a lot of records are locked, then it is highly likely people are going to trip over the locked records all over the place and be blocked from doing work. They won’t be able to load the record just to view it. Accpac Plus used this model of multi-user processing and it restricted Accpac Plus to pretty much only having 25 users, since after that, the number of locks would just cause too many problems. Most systems with high numbers of users use optimistic concurrency.

So what does optimistic concurrency have to do with database transactions? Most document entry in Accpac involves updating and inserting many different database tables. What happens if you are save an O/E Order and you have updated 5 tables and then on the sixth you get an error? You can either re-read the record and reconcile the two sets of changes or you have to abort saving the order. If you have to abort saving the order, you need to do a transaction rollback to undo the changes to the first 5 tables. Fortunately with database transactions this is easy, and you never have to worry about corrupting the database if you encounter an error (multiuser or otherwise). So for complicated header detail entry, you can only really do optimistic concurrency if you are based on a transactioning model.

Stored Procedures and Database Scale-out

A common question is why we don’t use stored procedures? Besides the problems with portability across databases and the attendant lock-in to one solution, there are definite problems with scalability. Since Accpac is aimed at the mid-market, like most such products we don’t support arbitrary database scale-out. This means if you have trouble with the performance of the SQL Server, you just can’t add another SQL Server to solve the bottleneck. We of course do support many configurations with multiple database servers, but there are restrictions. However you can have as many application servers as you like. If you run workstation setup type client server then every workstation is an application server and takes a share in running the business logic. If you deploy via Terminal Server or Citrix, they you can add as many of these as you like (either dedicating users to specific servers or adding a load balancer). So we want to put as little load on the database server as possible, to allow users to use one database server for as long as possible. If we ran stored procedures on the database server then this puts considerable load on the server and makes the database server become a bottleneck quite quickly. People often point out that they are getting slow, but SQL Server is only say 15% utilized, and we say add another applications server. They say why not shift more work to the database server. The problem being that once the database server is full, you’re done. Adding application servers is easy.

We support a couple of configurations with multiple database servers. One is to split a big company into multiple divisional companies within Accpac then run each divisional company on a separate database server. You then combine the data for financial reporting using G/L Consolidations. Another configuration is to add a second database server that is a replicated copy of the primary. Then if you have a number of users that only do reporting then you point them to the replicated server (and you can have as many of these as you like). However you can only have one primary server where database updates occur. But often reporting is quite a load on the database server and moving this to another server can really help.

True database scale-out means you can add database servers at will, but this requires the application be structured quite differently. For instance for Order Entry you might have one server designated the primary, it would contain a small Order header table with the order number and a link to the database that actually contains the order. So as people perform Order Entry, each order only requires one simple insert on the primary server and the actual order is placed on one of the other servers in a round robin fashion. This then allows scale-out of the Order Entry process, since you can add as many of these slave database servers as you like. The complexity comes in for reporting, since now to generate a report on a range of orders, you have to consolidate the data from a number of database servers, greatly adding to the complexity.

The Future

Originally our next database target was going to be MySQL. One reason being to reduce the TCO for our customers, as Sage as a very good deal for distributing MySQL with our applications. Another reason is to implement SaaS down the road, where to be economical, we have to ensure that we don’t have to pay all the subscription revenue to other software companies. However since Oracle bought MySQL, it’s become very questionable whether MySQL will maintain its TCO advantages or whether Oracle will invest in it sufficiently to keep it competitive. So right now we are taking a wait and see approach to MySQL.

Another approach that is becoming popular are the so-called “NoSQL” databases (http://en.wikipedia.org/wiki/NoSQL) which stands for ‘Not only SQL’. These databases are all about database scale-out, but since they think about large scale scalability from the start, they tend to be very different from relational (SQL) databases. Often they load the entire database into memory, and will naturally partition the database over multiple servers in a redundant manner. Often these databases remove the “hard” parts of SQL to make data access faster and more scalable. One example of these is Google Fusion (http://googleresearch.blogspot.com/2009/06/google-fusion-tables.html).

Along these lines the Apache foundation has been developing the Hadoop platform (http://hadoop.apache.org/) along with the HBase database (http://hbase.apache.org/). This is a very ambitious project to provide the entire infrastructure necessary to build fully scalable large scale web based applications like a Facebook or Twitter.

Some of the ideas from the Web have a place in Accounting Applications. For instance quick natural language Google-like searches. If the database is partitioned over 5 servers then these server can each produce their part of the results in parallel and then they can be combined and returned. The key point being the parallelism employed to return possibly difficult queries quickly.

Some ideas are perhaps not going to be so popular. For instance when you refresh Facebook you often get different results each time you do it. Facebook sends out the query to a bunch of servers and then combines all the results returned in a short fixed amount of time and displays your page, but any results coming late from a slow server are ignored. Will accounting users appreciate getting most of the results quicker rather than all the results slower?

Database technology is the foundation for Accounting Applications like Sage ERP Accpac. Most Accounting Applications started life using flat file ISAM packages or home grown solutions. Then at some point made the jump to standard SQL databases. Certainly we’ve gotten a lot of mileage out of SQL, but as the amount of information in data warehouses and other corporate databases grows, where we join to data out on the internet, are we at the point where we are going to see the start of a transition to the next big thing in databases?

Written by smist08

July 10, 2010 at 4:23 pm

Migrating Customizations to Sage ERP Accpac 6

with 12 comments

As Accpac makes the transformation from being a Windows Desktop Application to becoming a true Web Based Application, people often ask: how will we migrate our current customizations to the new platform? I talked about how to customize the new Web Based screens in: https://smist08.wordpress.com/2010/02/05/sage-erp-accpac-6-customization/; but, didn’t really discuss a strategy for migrating existing customization to this new model.

Ultimately, the new customization model is far more powerful than the current model and this will allow far more complex customizations. It will also be much easier to perform some customizations that currently require quite a bit of code. However it took the channel quite a bit of time to master the current model. It took quite a while for everyone to learn the complexities of VBA programming and our UI customization model. Now we will be starting over.

Care will have to be taken when scheduling and estimating conversion projects. Perhaps starting with easier ones first, to learn from and become experienced in the new technologies before tackling the bigger more complex jobs.

Customization that Remain Largely Unchanged

For report customizations we are still using Crystal Reports and so all current Crystal customizations can still be used. There are fewer database changes for version 6 than we have had in the last few versions, so most customized Crystal Reports should work with no changes whatsoever. We will be moving to the newer Crystal 2008 reporting engine which means you can now use any features introduced in Crystal 2008. In the same way your Accpac Financial Reports will continue to work fine as will anything you have created with Accpac Intelligence or Accpac Insights. Additionally we now have the new “Accpac Inquiry” (https://smist08.wordpress.com/2009/12/11/sage-erp-accpac-6-0-adhoc-query/) which you might want to construct some custom queries for.

Any customizations that talk to the Accpac COM API or the Accpac .Net interface will also remain mostly unchanged and should continue to work. Basically just following the usual upgrade procedures that you follow for any new version.

Any customizations that use View subclassing will continue to work. The View layer is largely unchanged. You should re-instantiate your Views with the View template in the Accpac 6.0 SDK, but this is the same procedure you should do with any new version.

For these API type customizations, it usually involves running a VB EXE program or VBA macro. These can be run from any workstation that has workstation setup installed, but as we run workstation setup on fewer and fewer workstations, you might want to think about running these on the Web Server.

Customizations of the new Web Based UI model

First a couple of caveats:

  1. We will continue to ship the current VB based UI forms for many versions. This means you can continue to use your current screen customizations for quite some time.
  2. We will continue to ship the current Accpac Desktop to allow you to run things exactly as you do today.
  3. Version 6.0A will only have new screens running inside SageCRM. Most other screens will move to the new Web Based technology for 6.1A.

However you will want to consider a strategy for moving your customer’s VB based customization to the new Web Based forms.

If the customizations only involved changes to the form design or changing text, then the customization in 6.x will be very simple and won’t require any coding. All the screen definitions and strings are stored in simple text XML files that can be edited either with special purpose tools (like a visual screen designer) or with a simple text editor like Notepad.

If the customization requires quite a bit of extra processing logic, then this code will need to be written in Java. The programming model is similar to that for the VB UIs. You subclass the UI and add your logic. There are collections of data sources and controls that you can tap into to get events to trigger your logic and allow your logic to manipulate the forms.

Expect a learning curve, to learn the new technologies. To become familiar with Java programming. To understand the programming and customization model used in our new Web Based UIs and how to deploy your updated UI controls on the server.

Although you can customize the current UIs just like you could customize the VB UIs, there is also a new alternative that lets you extend/customize the Accpac SData feeds. With this you can customize the code on the server that generates the data for the UIs. Sometimes this will be a better place to apply customizations. And is a new option you have available.

Like when we introduced VBA, it was a while before people were using it to extensively customize our screens. It took time to learn it and it took time to figure out what could be done. Like then, we will need to provide quite a bit of training, either live at our various conferences or via on-line training on Sage University. The good news is that the new customization model is more powerful than the current model and once you do master it, you will be able to more easily customize the look and feel for the product than ever before.

If the customization involves communications with programs running on the client’s workstation or involves saving files on the client’s workstation, these sort of operations may not be allowed from a Browser based application. There are definite restrictions on what you can do, in order to maintain workstation security. There are some things that you might do right now on the workstation that you might consider moving to the server or might need to find another approach, perhaps by communicating with a cloud service using web service calls. These will be dependent on what exactly you are trying to do and what is allowed.

The main points of customization are:

  • Various text files such as XML Screen definition files, XML string resource files and configuration files.
  • Cascading Style Sheets (CSS) that control the overall look and feel of the web pages.
  • Using Java to programmatically enhance the UIs.
  • Using Java on the server to programmatically enhance the Accpac SData feeds.

Older Web Technologies are Being Dropped

The Accpac SOAP Web Services are being removed in version 6.0A, so you will need to move any customizations using these to either the Accpac COM API, the Accpac .Net interface or to the new REST based SData Web Services being introduced in version 6.0A.

The Web Desktop is being removed. We will only be including CAB files for screens used by the SageCRM integration. All other Web Based VB screens are being removed. Any customizations that require the old Web Desktop will need to be moved to the new Web Desktop/Portal.

Summary

Any customizations that involve only the database, reporting or the business logic will continue to work or only require minor tweaks similar to the past few versions.

For screen customizations, you can continue to use your current VB customization for quite some time. However you should start to plan how to move these customizations over to the new Web base model.

Any customizations based on our older Web Technologies need to be moved over the new sooner than later.

Written by smist08

July 3, 2010 at 5:15 pm

Posted in sage 300

Tagged with , , , ,