Windows Bit-Rot

Introduction

In investigating some performance problems being reported on some systems running Sage 300 ERP, it lead down the road to investigating Windows Bit-Rot. Generally Bit-Rot refers to the general degradation of a system over time. Windows has a very bad reputation for Bit-Rot, but what is it? And what can we do about it? Some people go so far as to reformat their hard disk and re-install the operating system every year as a rather severe answer to Bit-Rot.

Windows Bit-Rot is the tendency for a Windows system to get slower and slower over time. Becoming slower to boot, taking longer to log-in, and taking longer to start programs. Along with other symptoms like excessive and continuous hard disk activity when nothing is running.

This blog posting is going to look at a few things that I’ve run into as well as some other background from around the web.

Investigation

I needed to investigate why on some systems printing Crystal reports was quite slow. This involved software we have written as well as a lot of software from third parties. On my laptop Crystal would print quite slowly the first time and then would print quickly on subsequent times. My computer is used for development and is full of development tools, so the things I found here, might be relevant to myself more than real customers. So how to see what is going on? A really useful program for seeing what is going on is Process Monitor (procmon) from Microsoft (from their SysInternals acquisition). This program will show you every access of the registry, the file system and the network. You can filter the display, in particular you can filter to monitor only a single program to see what it’s doing.

ProcMon yielded some very interesting results.

The Registry

My first surprise was to see that every entry in HKEY_CLASSES_ROOT was read. On my computer which has had many pieces of software installed, including several versions of Visual Studio, several versions of Crystal Reports and several versions of Sage 300 ERP, the number of classes registered here was huge. OK, but did it take much time? Well the first time something that’s run that does this it seems to take several seconds, then after this its fast probably because the registry ends up cached in memory. It appears that several .Net programs I tried do this. Not sure why, perhaps just .Net wants to know all the classes in the system.

But this does mean that as your system gets older and you install more and more programs (after all why bother un-installing when you have a multi-terabyte hard drive?), starting these programs will get slightly slower and slower. So to me this counts as Bit-Rot.

So what can we do about this? Un-installing unused programs should help, especially if they use a lot of COM classes. Visual Studio being the big one on my system, followed by Crystal and Sage 300. This helps a bit. But there are still a lot of classes there.

Generally I think uninstall programs leave a lots of bits and pieces in the registry. So what to do? Fortunately this is a good stomping ground for utility programs. Microsoft used to have RegClean.exe, Microsoft discontinued support for this program, but you can still find it around the web. A newer and better utility is Ccleaner from Piriform. Fortunately the free version includes a registry cleaner. I ran RegClean.exe first which helped a bit, but then ran Ccleaner and it found quite a bit more to clean up.

Of course there is danger in cleaning your registry, so it’s a use at your own risk type thing (backing up the registry first is a good bet).

At the end of the day all this reduced the first time startup time of a number of program by about 10 seconds.

Group Policy

My second surprise was the number of calls to check Windows Group Policy settings. Group Policy is a rather ad-hoc mechanism added to Windows to allow administrators to control networked computers on their domain. Each group policy is stored in a registry key, and when Windows goes to do an operation controlled by group policy, it reads that registry key to see what it should do. I was surprised at the amount of registry activity that goes on reading and checking group policy settings. Besides annoying users by restricting what they can do on their computer, it appears group policy causes a general high overhead of excessive registry reading in almost every aspect of Windows operation. There is nothing you can do about this, but it appears as Windows goes from version to version, that more and more gets added to this and the overhead gets higher and higher.

Auto-Updates

You may not think that you install that many programs on your computer, so you shouldn’t have these sort of problems but remember many programs including Windows/Microsoft Update, Adobe Updater and such are regularly installing new programs on your computer. Chances are these programs are leaving behind unused bits of older versions that are cluttering up your file system and your registry.

Auto-Run Crap

Related to auto-updates, it appears that so many programs now run as icons in the task bar, install Windows services or install programs to run when you log-in. All of these slow down the time it takes you to boot Windows and to sign-in. Further many of these programs, say like Dropbox, will keep frequently polling their server to see if there are any updates. Microsoft has a good tool Autoruns for Windows which helps you see all the things that are automatically run and help you remove them. Again this can be a bit dangerous as some of them are necessary (perhaps like a trackpad utility).

Similarly it seems that everyone and their mother wants to install browser toolbars. Each one of these will slow down the startup of your browser and use up memory and possibly keep polling a server. Removing/disabling these isn’t hard, but it is a nuisance to have to keep doing this.

Hard Disk Fragmentation

Another common problem is hard drive fragmentation. As your system operates the hard disk becomes more and more fragmented. Windows has a de-frag program that is either scheduled to run when your computer is turned off or you never bother to run it by hand. It is worth de-fragging your hard drive from time to time to speed up access. There are third party de-frag programs, but generally I just use the one that comes built into Windows.

Related to the above problems, often un-installation programs leave odds and ends files around and sometimes it’s worth going into explorer (or a cmd prompt) and deleting folders for un-installed programs. Generally it reduces clutter and speeds up operations like reading all the folders under program files.

Dying Hard Drives

Another common cause of slowness is that as hard drives age, rather than just out right failing, often they will start having to retry reading sectors more. Windows can mark sectors bad and move things around.  Hard drives seem to be able to limp along for a while this way before completely failing. I tend to think that if you hear your hard drive resetting itself fairly often then you should replace it. Or when you defrag if you see the number of bad sectors growing, then replace it.

Summary

After going through this, I wonder if the people that just reformat their hard drive each year have the right idea? Does the time spent un-installing, registry cleaning, de-fragging just add up to too much? Are you better off just starting clean each year and not worrying about all these maintenance tasks? Especially now that it seems like we replace our computers far less frequently, is Bit-Rot becoming a much worse problem?

G/L Account Structure in Sage 300 ERP

Introduction

Sage 300 ERP has a fairly flexible mechanism for setting up your General Ledger Chart of Accounts. This is a fairly important activity since it controls how you will be able to run financial reports and dice and slice your financial information. I’m not an Accountant so I might miss some of the finer points of accounting, and it’s always important to follow generally accepted accounting principles as much as possible. In some industries and in some countries your chart of accounts is specified for you. For instance Chine specifies the chart of accounts that companies must use.

So the actual chart of accounts can have some fairly hard constraints on how it’s set up. Fortunately there are some other mechanisms like account groups, account rollup, optional fields and transactional optional fields that can be used to enhance reporting capabilities.

I blogged on the general structure of G/L here. This blog posting is going to look a bit more in depth into the structure of G/L Accounts and how to generate some fairly flexible reports.

Basic Structure

Each G/L Account can be up to 45 characters in length (formatted). It can consist of up to ten segments each of which can be up to 45 characters. One segment must be designated as the Account segment. Each combination of segments is called an Account Structure.

The account segments are defined in G/L Options on the segments tab and are stored in GLABK (GL0022). These are the building blocks for the G/L Accounts and must be defined first. In Options UI you also specify the segment separator character and which segment is the Account Segment. Next you would define the Account Structures in the G/L Account Structures setup UI and stored in GLABRX (GL0023), these specify the various combinations of account segments that you will be using. You also specify which is the default structure code. The Account Segment isn’t validated and can be any value. The other segment can only have specific values and you specify these in the G/L Segment Codes setup UI and stored in GLASV (GL0021). Then you would define the Account Groups you desire in the G/L Account Groups setup UI.

With these values setup, you can now enter your Chart of Accounts into the Accounts UI in the G/L Accounts folder. The Accounts are stored in GLAMF (GL0001).

If you are following generally accepted accounting principles you should have an idea of how you want your G/L Accounts structured and you should have an idea of how you want your Financial Reports to be structured which then dictates your Account Groups. The Account Groups specify the normal F/R reporting categories like “Cash and Cash Equivalents”, “Accumulated Depreciation” or “Provision for Income Taxes”.

Now that you have a structure, creating all these accounts one by one sounds rather tedious. Fortunately there is G/L Create Accounts function that will create all your Accounts en masse. Our Chart of Accounts isn’t a sparse system, meaning that you do need to create an Account before you use it, so this is a very useful tool.

Optional Fields

There are two types of optional fields associated with G/L accounts, one are optional fields that are associated with the actual Account. These are typically used for reports where you are selecting various G/L Accounts to report on and you can then use these optional fields to control which Accounts. For instance the Chart of Accounts report or the Trial Balance report can print Accounts based on the values of these optional fields.

The other sort of optional fields associated with G/L Accounts are transaction detail optional fields. These control information that is flowing from the other ledgers, like A/R Invoices and will store the values for these fields with the transaction details. You can include these in reports like the Transaction or Batch listing reports.

Financial Reporting

Ultimately the final output of your ERP system are the Financial Reports. Generally CFOs want to look at their Financial Reports from all sorts of angles and all sorts of categorizations. Sage 300 ERP is quite efficient at Financial Reporting since it stores special Financial Set records when it posts batches that keep a lot of data all pre-calculated and easy to access. These are all stored in the GLAFS table (GL0003).

The main Financial Report UI lets you choose all sorts of range criteria and sort orders. These are very useful for getting out your financial statements, but within the Financial Statement specifications there are very powerful data inquiry functions and you can use the full power of Excel to manipulate the data, create charts, create pivot tables, show geographic distributions, etc.

The big drivers of Financial Reports are the Accounts, the Account Groups and the Account segments. However all the functions that you place in your financial reports like FRACCT, FRAMT, FRPOST take filters that can include all sorts of criteria on the fields in the G/L Account record along with optional fields that are specified like A.ACCTCLASS = ʺSalesʺ for Account optional fields and T.QUANTITY <= 0 for transactional optional fields. Keep in mind that account optional fields would be used for filtering accounts and transactional optional fields for filtering transactional amounts (like in FRPOST).

Usually you use the account groups to generate the main F/R statements by the usual accounting categories and then you restrict the account segments to get say departmental or geographic reports. Then you use optional fields to get more esoteric views of your financial data.

To some degree the power of the Financial Reporter depends on your having setup your Chart of Accounts properly in the first place.

Number Changers

But suppose you’ve been running for a while and realize you didn’t setup up things ideally for your financial reporting needs? You’ve now got lots of transactions posted for G/L accounts which you feel are in the wrong structure? Now what do you do? The answer is the GL Account Code Changer module that is included with G/L (you need to activate this separately from G/L). This module will do a search and replace on the database and change all instances of a G/L account from one thing to another. This way all your committed transactions and financial data will be set to the new account and your financial reports can now be printed off the more ideal new structure. The Account number changer can also change the account segment separator and the segments.

Summary

Sage 300 ERP has very rich and flexible features allowing companies to create Charts of Accounts that lead to very powerful reporting capabilities. A good knowledge of the whole process is important to design that perfect Chart of Accounts, but if you make a mistake you can always use the Account Number Changer to fix it.

User Roles and Security in Sage 300 ERP

Introduction

Role based security and user roles are terms that are in vogue right now in many ERP systems. Although Sage 300 ERP doesn’t use this terminology, it is essentially giving you the same thing. This blog looks a bit at how you setup Sage 300 ERP application security and how it matches role based security.

Users

First you create your Sage 300 ERP users. This is a fairly straight forward process using the Administrative Services Users function.

Here you create your users, set their language, initial password and a few other security related items.

Security Groups

Security Groups are your roles. For each application you define one of these for each role. For instance below we show a security group for the A/R Invoice Entry Clerk role. In this definition we define exactly which functions are required for this role.

Some roles might involve functions from several applications in this case you would need a security group for each application, but they can all be assigned together for the role.

User Authorizations

User Authorizations is where you assign the various roles to your users. Below I’ve assigned myself to the A/R Clerk role.

If multiple applications are involved then you would need to add a group id for each application that makes up the role.

Thus we can create our users. We can create our roles which are security groups in Sage 300 ERP terminology and then assign them to users in User Authorizations. As you can see below signing on as STEVE now results in a much more uncluttered desktop with just the appropriate tasks for my role.

Further Security

As you can see above in the Users screen there are quite a few security options to choose from depending on your needs. One thing not to forget is that there are a number of system wide security options that are configured from the Security… button in Database Setup.

Also remember to enable application security for the system database for you companies. For many small customers, perhaps application security isn’t an issue. I’ve also seen sites where everyone just logs in as ADMIN. But if you have several users and separation of duties is important then you should be running with security turned on.

Where is Security Implemented?

In the example above we see how security has affected what the user sees on their desktop. Generally from a visual point of view we hide anything a user does not have access to. This means setting up security is a great way of uncluttering people’s workspaces. However this is a visual usability issue, we don’t want people clicking on things and getting errors that they aren’t allowed. Much better to just provide a cleaner slate.

But this isn’t really security, perhaps at most it’s a thin first layer.  The real security is in the business logic layers. All access to Sage 300 functions go through the business logic layer and this is where security is enforced. This way even if you run macros, run UIs from outside the desktop, find a way to run an import to something you don’t have access to, it will all fail if you don’t have permission.

Summary

Sage 300 ERP security is a good mechanism to assign users to their appropriate roles and as a result simplify their workspace. This is important in accounting where separation of duties is an important necessity to prevent fraud.

Sage 300 ERP – Data Integrity

Introduction

Modern ERP systems maintain a company’s full financial history for many years. People want to be confident that all that data is correct and makes sense. So how can you be confident that your database has full referential integrity? Especially after years and years of operation. The Sage 300 ERP Data Integrity function is a way to validate the integrity of a database. Modern computers are much more reliable these days than when our Data Integrity function was originally written, but it still serves a good purpose. In this article we will explore some of the protections to protect data integrity in Sage 300 along with some of the possible causes of corruption.

Database Transactioning

The number one protection of data integrity in Sage 300 is database transactioning. Data is always written to the database in a database transaction. Database transactions always take the database from one state with full data integrity to the next state with data integrity. A database transaction is guaranteed by the database server to either be all written to the physical database or none of it is written, the database server guarantees that you will never see part of a transaction.

For instance as we post a G/L batch, we post each entry as a database transaction and since each entry in a G/L batch must be balanced we guarantee via database transactioning that the G/L is always in balance and hence data integrity is maintained.

Where Do Integrity Errors Come From?

Database transactioning sounds great, and in fact with database transactioning we see very few database problems in Sage 300 ERP. But when we do get integrity problems where do data integrity errors come from?

Below is a list of some of the main causes of data integrity problems. I’m sure there are more. I’m not looking to blame anyone (including myself), just to point out the main causes I’ve seen:

• Bugs in the program. If Sage 300 asks SQL Server to store incorrect data, it will do so in a completely reliable transactional manner. Hopefully our QA processes catch most of these and this doesn’t happen often; but, Sage 300 is a large complicated program and mistakes happen.
• People editing database tables directly in SQL Server Enterprise Manager. For various reasons people might try to put something in the database that the program doesn’t allow, and often this leads to database corruption.
• Third party programs that write to the Sage 300 database directly. We do a lot of data validation checking in our business logic before allowing data to be written to the database, but if this is bypassed then corruption occurs. A common one in this case is not handling currency decimal places correctly.
• Data validation needs to be tightened. Now and again, someone has written data that we accepted as valid that wasn’t. Then we had to tighten our data validation routines. The good news here is that we’ve been doing this for a long time now.
• Bug in the Database Server. We’ve seen database indexes get corrupted which can lead to further problems either after the indexes are fixed (because of other data written as a result).
• Partial backups or restores. We’ve seen people back up the tables for each application independently and then restore them. Perhaps to try to put A/R back to yesterday. But this corrupts the database since there is often matching data that needs to be in sync in Bank, Taxes or perhaps Order Entry. Make sure you always backup and restore the database as a whole.
• Hardware glitches. Even with CRC checking and such, strange errors can start to appear from hard disk or memory hardware failures in computers.

The Data Integrity Checker

To find these sort of problems Sage 300 ERP has a data integrity checker in its Administrative Services. The main screen looks like:

You select the applications you want to check and whether you want to fix any minor errors. Since this can be a long process, for several applications you can also configure which parts within the application to check by selecting the application and choosing the application options in a screen like:

The end result is a report is run that lists all the errors found.

What Does the Integrity Checker Look For?

So what does the Integrity Checker do? Below is a list of some of the checks that are typically made:

• Check the integrity of each file reading each record and calling the View Verify API which will call the business logic to validate the record. This includes things like checking the decimals of a money amount are correct, that the data is correct for the data type, that foreign keys are valid.
• For Header/Detail type relationships there are often total or summary fields in the header like the total amount of an order or the number of detail lines. The integrity checker will read through the details and add up any of these numbers to ensure they match the header.
• Check the database for any detail records that don’t have a matching header record (orphans).
• Each application then knows about all sorts of cross file relationships that must be maintained and the Integrity Checker for that application will validate all of these relationships.

What Does Fix Minor Errors Do?

There is the check box to fix minor errors, but what does it do? Mostly it fixes up header/detail relationships by fixing any total or summary fields in header records. It can also delete orphan detail records. But generally it doesn’t attempt much because we don’t want to risk making things worse.

But it’s Slow

The big complaint about the Data Integrity checker is that it’s slow. This is because it does go through every record in the database as well as checking all the cross dependencies. These days we see company databases that are hundreds of gigabytes in size. Generally the complaint is that you can’t just run it as routine maintenance overnight. That you tend to have to configure what you want to run and do that selectively. It’s also best to run it when people aren’t in the system since it does put a fair bit of load on the system.

But this does open up an opportunity for third party developers. Companies like Tairox and Orchid offer solutions to automate data integrity or to run it as a service.

Summary

Even with super reliable modern databases and hardware, data integrity errors can still creep in and need to be dealt with. Just being aware they exist is half the battle. Also remember that it is extremely important to have regular full backups of your data in case of a really catastrophic failure.

 

The Sage 300 ERP Java API

Introduction

With version 6.0A of Sage 300 ERP we introduced a native Java API to all the Sage 300 Business Logic (Views). We did this in support of our SData implementation which we wrote in Java. This API allows Java programmers to access all the Sage 300 ERP business logic along the same lines as our .Net API and our COM API. This API isn’t built on top of either COM or .Net, it talks directly to the underlying C DLLs in System Manager. This then provides better performance, as well as allows us to compile this part of the system for Linux with no Microsoft dependencies. Internally we usually refer to this API as SAJava.

All the Sage 300 Business Logic objects have the same API, this makes it easier for us to produce these different APIs to facilitate interoperability with all sorts of external systems, allowing the programmers there to write code in a natural manner where any required interop layer is provided by us. The Java API uses a Java Native Interface (JNI) interop layer to talk to our Windows DLLs (or Linux shared objects). This is a one way communication where we only use this to call the DLLs, we never have the DLLs calling our Java code (this direction is often dangerous and leads to the problems often encountered with JNI). Our JNI code handles all the data conversions between Java and C as well as provides exception handling to trap and handle exceptions that can happen in C code (like bad pointers).

I’ve blogged about this API a bit indirectly in the past when talking about how to write server side code for our SData service, for instance here, here and here. Generally to add custom programming to SData feeds you write Java classes that inherit from our standard SData classes to provide this. When you interact with the Views in this environment you use this Java API, but all the libraries are already included and all the details of signing on are handled for you. The framework starts you off at a point where you can directly open and call Views. In this posting we’ll back up a bit to cover full usage, unlike the case where the SData programming framework does a lot of the work for you. So that you can use this API directly in isolation without requiring any other framework.

Getting Started

First to use the Java API, you need to include its jar file into your project. This file is located in the Tomcat\lib folder. This changed a bit between version 6.0A and then the 2012 version. For 6.0A the folder is: C:\Program Files (x86)\Common Files\Sage\Sage ERP Accpac\Tomcat\lib and the file is SystemManager.jar. For the 2012 version the folder is: C:\Program Files (x86)\Common Files\Sage\Sage 300 ERP\Tomcat\lib and the file is com.sage.accpac.sdk.accpac.sajava-6.1.jar. Then you need to import the classes into any source file that uses them via:

import com.sage.accpac.sm.*;

Once you have these things included in your Java project you can start creating objects and calling methods. However due to security you first must sign-on to a session and then create all other objects from this session.

The documentation is in the form of JavaDoc and is located on the DPP Wiki. The 2012 version is here: http://dppwiki.sage300erp.com//javadocs/v6.1/SystemManager/. You can find all the classes, methods and properties here. To access this, you must be part of the Sage 300 ERP Developer Program. A key benefit to joining this program is access to this wiki which contains all the developer documentation that we produce.

Signing On

First you must create a session with some code like:

Session session;

session = new Session(new ProgramSet(), new SharedDataSet(), "ADMIN",
     "ADMIN", "SAMINC", new Date());

This will sign-on your session to the company SAMINC using today’s date as the session date. The ProgramSet and SharedDataSet are used when we deploy in a hosted configuration and run multi-tenant. In this case they must be setup correctly by the system to configure which tenant this session is for. In most normal on-premise applications the indicated calls are fine to give the one default tenant that exists.

Then you must create a program from the session:

Program program;

program = new Program(session, "XZ", "XZ0001", "61A");

If you read my last blog post, this might appear a bit backwards to the COM API where this looks like the session.Init call that comes first. This is true, but the information is required regardless.

Using Views

Now that you have a program you can start opening and using Views. As an example, let’s look at a method that enters A/R Invoices. Like many things I started with macro recording to get the right Views and some syntax. Macro recording produces VBA code, but it isn’t hard to convert this to Java quickly. Anyone familiar with Sage 300 ERP macro recording will recognize the style and variable names in the following method. This method assumes there are class variables for the program and session that were created as indicated above. The key point of the following example is to show how to open Views, compose Views and then use the Views. For more general information on Sage 300 ERP’s Views have a look at this and this.

    public String enterARInvoices()

    {

        int iEntry;
        int iDetail;
        int numEntries = 20;
        int numDetails = 5;
        String sBatchNum;

        View ARINVOICE1batch = new View(program, "AR0031");
        View ARINVOICE1header = new View(program, "AR0032");
        View ARINVOICE1detail1 = new View(program, "AR0033");
        View ARINVOICE1detail2 = new View(program, "AR0034");
        View ARINVOICE1detail3 = new View(program, "AR0402");
        View ARINVOICE1detail4 = new View(program, "AR0401");
        View ARCUSTOMER1header = new View(program, "AR0024");

        ARINVOICE1batch.compose ( ARINVOICE1header );
        ARINVOICE1header.compose (ARINVOICE1batch, ARINVOICE1detail1, ARINVOICE1detail2, ARINVOICE1detail3, null);
        ARINVOICE1detail1.compose (ARINVOICE1header, ARINVOICE1batch, ARINVOICE1detail4);
        ARINVOICE1detail2.compose (ARINVOICE1header);
        ARINVOICE1detail3.compose (ARINVOICE1header);
        ARINVOICE1detail4.compose (ARINVOICE1detail1);

        // Create the batch

        ARINVOICE1batch.recordGenerate(RecordGenerateMode.Insert);
        ARINVOICE1batch.set("PROCESSCMD","1");      // Process Command

        ARINVOICE1batch.process();
        ARINVOICE1batch.read(false);

        sBatchNum = ARINVOICE1batch.get("CNTBTCH").toString();

        // Loop through creating the entries

        for ( iEntry = 0; iEntry < numEntries; iEntry++ )
        {
            try
            {
                ARINVOICE1detail1.cancel();
                ARINVOICE1detail2.cancel();
                ARINVOICE1header.recordGenerate(RecordGenerateMode.DelayKey);
                ARINVOICE1detail1.recordClear();
                ARINVOICE1detail2.recordClear();

                ARINVOICE1header.set("PROCESSCMD","4");

                ARINVOICE1header.process();

                if ( false == ARCUSTOMER1header.goNext() )
                {
                    ARCUSTOMER1header.goTop();
                }

                ARINVOICE1header.set("IDCUST", "1200");

                for ( iDetail = 0; iDetail < numDetails; iDetail++ )
                {
                    ARINVOICE1detail1.recordClear();
                    ARINVOICE1detail1.recordGenerate (RecordGenerateMode.NoInsert);
                    ARINVOICE1detail1.process();

                    ARINVOICE1detail1.set("IDITEM", "CA-78" );                     // Item Number

                    ARINVOICE1detail1.insert();

                }

                ARINVOICE1header.insert();
            }
            catch( Exception e )
            {
                int count = program.getErrors().getCount();
                if ( 0 == count )
                {
                    e.printStackTrace();                   
                }
                for ( int i = 0; i < count; i++ )
                {
                    System.out.println(program.getErrors().get(i).getMessage());
                }
            }
        }
        ARINVOICE1batch.dispose();
        ARINVOICE1header.dispose();
        ARINVOICE1detail1.dispose();
        ARINVOICE1detail2.dispose();
        ARINVOICE1detail3.dispose();
        ARINVOICE1detail4.dispose();
        ARCUSTOMER1header.dispose();

        return( sBatchNum );
    }

Notice that you can explicitly close things by calling the dispose method. This is usually preferred to waiting for the Java garbage collector to reclaim things, it tends to keep down resource usage if you are opening and closing things a lot.

Errors

If a call fails, there are a couple of cases. If it’s a simple expected thing like reaching the end of records when fetching through them then the routine will return a simple return code that you can easily handle in your code. If something worse happens then the routine will throw an exception. As in other Sage 300 ERP APIs, there is an error stack which will contain possibly a number of error messages explaining what went wrong. In the catch expression above we first check if there are any errors on the error stack, if not then we print the stack trace to allow debugging of what went wrong. Otherwise we loop through the Sage 300 errors and print them for diagnostic purposes. When programming Sage 300 ERP, always make sure you have an error handler as it can give you very good information when debugging your program.

Summary

The Sage 300 ERP Java API gives yet another tool for integrators to integrate to Sage 300 ERP from external systems. It is ideal for Java programmers who would like to write their integration entirely in Java. This is often a benefit when the SDK for the external system is itself written around the Java programming language.

Opening Sage 300 ERP Sessions

Introduction

Sage 300 ERP has a number of very flexible external APIs that allow programs to access all the business logic in the program. The business logic is stored in Views that are accessed via a standard API. To start using the business logic from one of our external APIs you first need to sign-on to the API and establish a session. This article is going to only talk about the AccpacCOMAPI which is our main COM API. Sage 300 ERP has an older COM API usually referred to as a4wcom, so be sure to use the newer one we are talking about here. Many of the concepts can be adapted to other APIs like the .Net or Java APIs. However to interact with other COM components like the session manager you must be using the AccpacCOMAPI. The examples in this posting will all be in Visual Basic 6.

This API has been around for a long time, but we recently received quite a few queries through customer support on establishing connections. So I thought it might be worth while writing a blog post on some of the use cases we try to support, some of the functionality that perhaps isn’t very widely known as well as the reasons for why some aspects work like they do.

For a bit more background on the Sage 300 business logic have a look at this blog posting.

Libraries

Sage 300 ERP’s COM API can be used by any tool that understands COM and how to talk to COM objects. The first step is to add the COM object to your project. In VB6 you do this by going to Project – References and adding “ACCPAC COM API Object 1.0”. In some tools you can browse to the DLL and add that, in this case you browse to where ever you installed Sage 300 ERP and then browse for runtime\a4wcomex.dll.

Creating and Initializing

Once you have the library available for you, now you need to get started. All objects in our COM API are created via APIs in our COM API. But first you need to get started by creating and initializing a session object. This is the root object and from this everything else is derived. In VB there are a couple of ways to create the initial session object either:

Dim mSession As New AccpacCOMAPI.AccpacSession

Or

Dim mSession As AccpacCOMAPI.AccpacSession
Set mSession = CreateObject(“Accpac.Session”)

Once you have a session object then you need to initialize it:

mSession.Init “”, “XY”, “XY1000″, “61A”

If you are accessing the COM API from an external program and not an SDK application then the parameters don’t matter. The first parameter is for when an SDK application is run from the desktop to connect them up properly and the other parameters are similarly for SDK application for other APIs like getting you applications help files correctly. Generally for an external application you just want these set with valid value so things will proceed. The application ID “XY” is reserved for non-SDK application to use, so you don’t have any risk of having things confused with a third party application. It is important that you call init before doing anything else. If you do call some other method first then expect to get strange error messages.

Below is the object model of all the objects you can get from an initialized session:

Company List

At this point we still haven’t signed into a company. At this point you can really just sign-on, but you can also get a list of companies that you can sign-on to. This is the API used by Sage 300 to build sign-on dialogs. In the session object is an organizations collection that you can traverse to get the information on the available companies.

For i = 0 To mSession.Organizations.Count – 1
Print mSession.Organizations.ItemByIndex(i).DatabaseID,
mSession.Organizations.ItemByIndex(i).Name
Next i

As you can see by the code, this API was invented by a C programmer and not a VB programmer.

Signing On

The main way you sign-on to a company is to use the open method.

mSession.Open “ADMIN”, “ADMIN”, “SAMLTD”, Date, 0, “”

The main thing you need for this method is the user id, password, company id and session date. After calling this, the next thing you usually do is create a database link and then from the database link create your view objects. Now you can call the views and use all the Sage 300 business logic. The disadvantage of this method is that you need to know the user id and password. But otherwise you are good to go.

Session/Signon Managers

Of course with what we have discussed so far you could create your own sign-on dialog. But why re-invent the wheel. The main Sage 300 ERP COM library is intended to be called from both user interface programs or server processes, as a result it has no user interface functions itself, it will never popup a messagebox or a dialog box. It is strictly processing and no UI.

However we do provide a number of other ActiveX controls that are intended to be used as UI components. Two of these are the Signon Manager and the Session Manager. You only interact with the Session Manager and then the Session Manager uses the Signon Manager whenever it needs it.

So if you don’t want to have to know the user id and password then you use the Session Manager to create your session for you and you get back a session that has been created, initialized and opened for you. The user will be able to enter their user id, password and select the company and session date to use for processing.

To use the Sesion Manager you need to add a reference for “ACCPAC Session Manager 1.0” or access the runtime\a4wSessionMgr.dll. Then you would write some code like:

Dim signonID As Long
Dim mSession As AccpacCOMAPI.AccpacSession
Dim sessMgr As New AccpacSessionMgr

sessMgr.AppID = “XY”
sessMgr.ProgramName = “XY1000″
sessMgr.AppVersion = “54A”
sessMgr.CreateSession “”, signonID, mSession

The intent of the session manager was to facilitate things like workflow management. So the first time someone accesses it, it will create new session and the user will get a signon dialog. However the next time it is accessed, you will just get back the session the user opened the first time. This allows applications to be strung together in a workflow type manner without each step requiring the user to sign-on. If you do want a fresh sign-on, you can set the ForceNewSignon property to true. If there are two desktops signed in and ForceNewSignon is false, then the user will get a dialog box to choose which session they want.

Summary

The external APIs to Sage 300 ERP are very powerful. Since the AccpacCOMAPI is used exclusively by our VB forms to access the Sage 300 business logic, you know that from this interface you can do anything that can be done from a regular UI. All business logic is exposed this way. So the intent of this posting was just to give you a little help in getting started to get at all that business logic.

Voice Input and Concierge Services

Introduction

Some of the most exciting new technologies appearing on mobile phones are around voice recognition and concierge or personal assistant type of applications. These include ambitious applications like Apple’s Siri, along with a number of initiatives from Google including Google Now and Google Voice Search.

The voice recognition by itself is a truly amazing technology, but this is only a fraction of the story. After the voice input is recognized the query is combined with other input, like your location, to determine a lot of context for what you are asking about, identifies the problem domain and gives a truly meaningful answer along with relevant data to correctly answer or respond to your query.

Of all the technologies on Star Trek, we don’t see any sign of a working warp drive or transporter, but being able to ask a computer anything on any topic and get a good answer, we seem to have that now. So perhaps if Star Trek IV was set another ten years ahead, then Scotty wouldn’t have had any trouble interacting with our primitive computers.

Device or Service?

An incorrect assumption is that you can integrate apps running on your phone to these services. This is the wrong way to think about how they work. They aren’t a voice recognition/query engine running on your device. In fact they send all the (nearly) raw input to a major data center to process them. Even though there isn’t a device API for accessing Siri, developers have found clever ways around this, by putting clever things in the contact list and constructing special text messages, but again this is really just using Siri as voice recognition software. The real intent of Siri is much deeper; it’s really a task completion engine.

These engines are really taking your voice input and then mapping them to various problem domains which then talk to many APIs on the backend. The goal isn’t to run an app and then just provide a voice recognition engine that translates voice commands into regular app commands as if the user had typed them. The goal is really that you don’t need device apps. When you ask Siri a question, you don’t need a matching app running, if you ask about airline info, it gets it, if you ask about weather, it gets it. You don’t need to run the right app.

In a way a limitation of current mobile phones is the need to download and install so many apps. Do you really need all of these? Most of the apps on my phone are specialized query information gathering apps like weather, news and such. The real beauty of these new personal assistant type applications is that they eliminate the need for all these other apps. Wouldn’t a phone or tablet be much easier if you didn’t need to find and install all these apps? Isn’t this the original appeal of the Internet to PC users? You don’t need to install dozens of applications (which got more and more painful); all you needed was a Browser and nothing else. To some degree these personal assistant applications become a workable Browser for mobile devices, where you no longer need all these apps anymore. Sure there are some special purpose apps for playing games and performing specialized functions, but generally you can just use Siri, Google Voice Search or Google Now for most things that you probably use Apps for now. Sure these aren’t perfect yet, just like the original Netscape Browser wasn’t perfect, but they are getting there very quickly.

Integrating to ERP and CRM

OK, so we don’t integrate to these new services via Apps talking to APIs on devices, so if we want to integrate our CRM or ERP into say Siri, how do we do it? Suppose we want to ask Siri what is the status of an Order from a vendor, or we want to ask Siri what is the credit limit of a customer I’m about to visit?

The key is to have this information available on the Internet via RESTful Web Services like SData. The reason for RESTful Web Services is that they allow discovery by search engine spiders. Generally shortened URLs give the list of how to build the rest of the URL, this allows a general engine to discover all the data. RESTful Web Services are the new Internet standard and all these services are built to interact with them.

The key is for vendors (like Sage) to make the right agreements with these services, so that the data can be accessed in a secure way, and you aren’t doing something like exposing all your ERP data to the Internet in general. Security and the rules for who can access what are crucial. Standard sign-on mechanisms like OAuth are going to have to be used.

The other thing is that all this data must be in a central location. This means that any ERP or CRM data that is going to be available to these services must be sync’ed to a central cloud location. This then fits in with Sage’s connected services strategy of sync’ing key on-premise data to the cloud (of course if you are already running your CRM or ERP in the cloud then you can skip this step). I blogged about Sage’s Hybrid Cloud here. From Sage’s Hybrid Cloud we can expose the correct data via SData Web Services for anyone that wants to participate in these services. Then Sage can make the correct deals with the services and is responsible that all the security concerns are setup correctly.

This can then lead to a company’s employees and customers being able to make general inquiries into these services and for the right questions have them mapped to a problem domain in the ERP or CRM space, have the backend systems provide answers with relevant data added from the Hybrid Cloud.

None of these services would look into the Hybrid Cloud in real time, they all operate like Search Engines which are continuously polling sites and updating their master databases, then for performance reasons all the real queries are handled as highly optimized Big Data queries against a master search database, so that all questions are magically answered instantly.

Overtime the questions answered can become more and more sophisticated, incorporating more and more sources of business data. Perhaps you can ask Siri: What’s the best way to increase my company’s revenue? And then get back a useful answer.

Summary

I think these personal assistant type applications are going to become more and more prevalent in the mobile world (or even on regular computers). To me it’s exciting to consider participating in this and to think about all the questions that we can help answer.

The End of the Smart Phone Era?

Introduction

I saw this article in Business Insider “The End of the Smart Phone Era is Coming” and was just wondering what effect this would have on business applications like ERP and CRM. Basically will we all ditch our smart phones in exchange for smart eyeglasses? Do we want a virtual world super-imposed over the real world? Is this the way to really be always connected all the time?

Google made a big splash by introducing their vision with this video. Some of the initial reaction ranged from that this was the greatest thing ever to that now you would have absolutely no privacy since Google would see and hear everything you see and hear. Below is a Google glass fashion shoot.

Judging by recent patent applications, Microsoft is also working on something similar. Below is a diagram from Microsoft of some of their thinking.

ERP and CRM

In my world we’ve been battling with moving fairly complicated business application to mobile devices like tablets and phones. We’ve been battling with fitting large amounts of data onto much smaller screens. In a way large flat panel desktop monitors are great for our applications since you can see and manipulate large amounts of data. But sadly everyone wants to do this on their phone, so how do we do that? At this point we are getting a grip on how to do business applications on devices. We are getting a grip on how to handle touch as the input mechanism instead of the keyboard and mouse. We are getting a grip on how to handle the fact that the app isn’t always connected to the network.

Now we hear that smart phones and tablet are just as obsolete as the desktop PC and laptop! So in this world, not only do we have a small screen, but we have to share it with the real world. Plus we have a whole new input model where it’s a combination of voice recognition and eye tracking technology.

I don’t think we’ll want to just super-impose our regular Order Entry screen onto the glasses over the real world. I suspect that rather than port our existing ERP and CRM functionality to glasses, more likely we’ll be re-inventing the way we do many business processes. This probably means a proliferation of new apps.

Physical Inventory Counts

One good application I was thinking of was to do physical inventory counts. This is always a painful but necessary process to catch theft and errors. Now you will be able to run your inventory count app in your glasses. As you walk around the warehouse, you just need to look at boxes and have the glasses record the barcode or QR code to count the inventory. For other items, perhaps you can look at something and then double-blink, the software then compares the visual image to all the pictures in the inventory database to find a match and count that item.

Sales Calls

Now you can have a glasses CRM app. Rather than bring up all your customer information on a tablet and keep referring to your tablet, you can see all the information on a customer right before your eyes. The glasses app will bring up the customer for you automatically based on your location and facial recognition software. Then the glasses can present to you all pertinent information on the customer, like his sales history, buying habits or that he’s late paying his bills. This should really impress your clients since it will appear that you care enough about them to know off the top of your head every detail about them. Then further the glasses can have recorded the whole chat, so if there are any disputes later, they can be reviewed.

Pottery Barn

In our nearby Pottery Barn, the items in the store are for display only. If you are interested in something, you need to talk to a salesperson, who looks up the item on their tablet to find out if they have it in stock in the store, in a local warehouse, in a regional warehouse or will need to get it shipped from the manufacturer.  Now there could be a glasses app that identifies the item you are interested in, perhaps by staring at its QR code and double-blinking. Then it can bring up additional catalog information on the item, including delivery logistics and such. Generally this could streamline the whole (painful) process of shopping at Pottery Barn.

Summary

Will the widespread use of such glasses lead to the true surveillance society? Rather than just a plethora of security cameras recording everyone’s movements, will now everything anyone sees and hears through these glasses be recorded and accessible to law enforcement and the government? Or will we manage the privacy concerns and bring in a new generation of connected uses who look on our current phones as archaic as we look back on the original Motorola brick cell phones?

The Sage Hybrid Cloud

Introduction

We introduced the concept of the Sage Hybrid Cloud along with a number of connected services at our Sage Summit conference back in August. This is intended to be a cloud based platform that greatly augments our on-premises business applications.

This blog posting will look at this platform in a bit more depth. Keep in mind that this platform is still under rapid development and that things are changing rapidly. If we think of better ways to do things, we will. We are approaching this with an Agile/Startup mentality, so we aren’t going to go off for years and years and develop this platform in a vacuum. We will be developing the functionality as we need it, for our real applications. This way we won’t spend time developing infrastructure that no one ends up using. Plus we will get feedback quicker on what is needed, since we will be releasing in quick cycles.

The Hybrid Cloud Platform

Below is a diagram showing the overall architecture of this platform. We have a number of cloud services hosted in the MS Azure cloud. We have a number of Sage business applications with a connector to this cloud. Then we have a number of mobile/web applications built on top of this hybrid cloud platform. Notice that pieces of this platform are already in use, with Sage Construction Anywhere (SCA) being a released product and then Sage 300 CRE already having a connector to this cloud to support the SCA mobile application.

The purple box at the bottom represents our current APIs and access methods, and just re-iterates that these are still present and being used.

The red box indicates that we will be hosting ERPs in this environment in a similar manner to our current cloud offerings like Sage300Online.com. We’ll talk about this in much more detail in future blog posts. But consider this Sage hosted applications version 2.0.

Mobile Applications

We demo’ed a number of mobile applications that we have under development at Summit, some screenshots are here. We are working hard to make these applications provide a first class user experience. We are developing these in various technologies and combinations of technologies to drive the user experience to be the best possible. We are writing both HTML5/JavaScript applications using the Argos-SDK, along with writing applications as native iOS, Windows 8 Metro and Android applications. Plus there are technologies that allow use to combine these technologies to use them both where they make sense in an application.

These mobile applications aren’t just current ERP screens ported to mobile/web technologies, they are whole new applications that didn’t exist before these powerful mobile devices came along to enable these ideas.

ERP Connectors

Each ERP needs to connect to the Hybrid cloud, this is to upload files for items that are needed for lookup in the cloud devices like for finders. As well as to download transactions to enter into the ERP on the connected application’s behalf. The intent is to have one connector for each business application, rather than having to install and configure a separate connector for each connected service (which we hope there will be dozens of).

We want to keep the TCO of the solution as low as possible. To this end we don’t want the end user to have to configure any firewalls, DMZ or web servers. The connector will only call out to the cloud platform. There will never be calls into the connector.  Additionally you only need to configure the connector once with your SageID and away you go.

The connector will use SData Synchronization to synchronize the various files. This way it doesn’t matter if your on-premises ERP is off-line, it will catch up later. This makes the system much more robust since your mobile users can keep working even if you turn all your computers off completely.

SData

We will use SData as the communications mechanism from the hybrid cloud. The cloud will host a large set of SData feeds to be used either by the mobile and web applications or by the on-premises ERP connectors.

Since SData is based on industry standards like REST, Atom, RSS and such, it means it’s easy of pretty much any web or mobile based framework to easily use it. All modern toolkits have this support built in. Plus we provide SDKs like the Argos-SDK that have extra SData support built in.

ISVs

The intent will be that ISVs can use the SData feeds from the Hybrid Cloud as well to develop their own applications or to connect existing cloud based applications to all our Sage business applications. However we won’t start out with a complete database model, we will basically be adding to this cloud data model as we require things for our Sage developed solutions as well as for select ISVs. The intent is to get common functionality going first and then fill it in with the more obscure details later. For instance most connected services will need to access common master files like customers, vendors and items. Then most connected services will need to enter common documents like orders and invoices.

The feeling is that most integrations to ERP systems actually don’t access that many things. So the hope is that once the most common master files are synchronized and once the system accepts the most common transactions, then a great number of applications will be possible.

There will also be parts of the cloud database that don’t have any corresponding part in the ERP. There will be a fair bit of data that resides entirely in the cloud that is specific to the cloud portions of these applications.

SageID

When you are signing on to all these various connected services, we don’t want you to need a separate login id and password for each one. We would like you to register a user-id and password with Sage once and then use that identity for accessing every Sage connected service.

Ultimately we would like this to be the user id and password that you use to sign-on to our on-premises applications as well. Then this would be your one identity for all Sage on-premises and cloud applications. Then all your access rights and roles would be associated with this one identity.

Summary

The Sage Hybrid Cloud is an exciting project. The concept is that it’s starting small with the Sage Construction Anywhere product already shipping and then going to develop quickly as we add other services. This should go quickly since we are leveraging the R&D resources of many Sage products to get new exciting mobile products into market quickly spanning the customer base of many Sage business applications.

Some Experiences from Blogging

Introduction

I’ve been writing this blog for nearly four years now. I’ve written 175 articles and there have been 940 comments (many of these responses by myself). This is a bit of a self-indulgent article on my experiences blogging. Partly because I think blogging is a great communications mechanism and partly because I need a topic this week. I’ve managed to get my readership up to a bit over ten thousand views per month now, which given the specialized topics I blog on, I tend to think is pretty good. I get a quite varied readership with all parts of the globe being represented.

Format

I use WordPress. I didn’t do a lot of research to pick it, I just noticed several blogs that I liked used it and gave it a try. I found it really easy to get going and have basically stuck with it since. It’s important to keep your blog URL the same so people can continue to find you. I never paid for a personalized URL, so if I ever did want to switch away from WordPress it would be hard, since I would need to change the URL of my blog. I’ve only ever used the free version/functionality and never paid for anything, though WordPress makes some money off me by having a few adds on my blog now and then.

I write my articles in MS Word. Then I copy/paste them into WordPress. WordPress preserves most of the formatting so I don’t need to reformat things usually. Sometimes it messes up, but I tend to keep the formatting simple so I don’t confuse it. The pictures don’t copy across, so I need to insert them separately when done.

I don’t really like the contents/indexing of blog articles that WordPress provides, so I use the custom page feature to keep some tables of contents and directories of articles more in a way that I prefer.

Gaining Readers

When you start a blog and post it on a site like WordPress, even if you don’t do anything, you will still get a few views, even if it’s only a dozen a week. So how do you get readers? How do you keep them coming back? There are a lot of articles and blogs on how to promote blog readership, but these are a few things that I find work.

1. Google is king. Most of my views come as the result of Google searches or Google image searches. Usually several hundred from Google and then one or two from Bing, Yahoo, AVG Toolbar, etc. Towards getting good Google results, check your page rank (using the Google toolbar or the Chrome application). If you’re not progressing (from 0 to 1 to 2 to 3) over a year or so, you might need to rethink things. Make sure you get your friends to link to your blog from any blogs or websites they have, since Google largely rates things by how many other sites link to it.
2. LinkedIn is a great way to promote your blog. If you know your audience and can connect to your audience via LinkedIn, then they will see notifications of your posts on LinkedIn, as long as you set it up correctly.
3. Posting notifications on relevant Facebook pages generates a few views, but at least for me, the audience is wrong and I don’t get many views this way.
4. Reddit. Since Digg died, Reddit is supposedly the main referral site for articles. However most blogs are just rejected by the various topic editors and it’s very hard to keep good referrals here. I’ve only managed to do it once, but it did payoff with a record day. But generally I find Reddit too much work.
5. Write regularly. If you write regularly then people will subscribe to your blog to be notified either by e-mail or RSS every time you post. Subscribing readers are the best kind of readers. Plus Google (see #1) favors sites that keep posting original content. It doesn’t take long for all that content to add up to a pretty sizeable set of reference material.
6. Build a twitter following. Tweeting is a great way to promote your blog. I found Twitter worked better a few years ago. My theory is that so many people tweet now, that your posts tend to get lost in the general sea of tweets. Google is putting a bit more weight into social media references, so it can’t hurt.
7. There are sites that claim if you pay them, they will find you all sorts of readers and send your viewership skyrocketing. I don’t really believe this and have never paid for such a service. I’ve also never paid for things like Facebook or Twitter to promote my posts.
8. Have relevant keywords. But don’t go overboard. There has been so much abuse of HTML keywords, that most search engines just ignore them and go for the content. A few good keywords is good, but if you add every word in the dictionary it just makes your page slow to load and the search engines will ignore them. The search engines are much better at deriving these from your content these days.

Generally unless you are a celebrity (or blogging about celebrities), your number of viewers won’t be in the millions (or even hundreds of thousands). But getting a readership in the thousands or tens of thousands isn’t that hard. It just takes consistency, good content and a bit of perseverance. Also remember that if you are blogging on a technical topic, your whole total audience may not be that big, due to the high levels of specialization we see these days.

Geographic

WordPress now will categorize your readership by country. This is based on your IP address which isn’t perfect. At our Sage Richmond office, our internet is routed through Irvine, CA. So if anyone in the office (in Canada) reads my blog then it counts as US (since this is how the IP address is assigned).

My readership tends to follow proportionately where Sage 300 is successful. But there are a few anomalies. Strangely I get quite a few views from Brazil, even though we’ve never really sold Sage 300 there.

Stats

Blogging software like WordPress is great for providing you all sorts of statistics. Like tracking your viewers over the years, telling you which countries follow your blog the most, telling you who is referring people to your blog. I find keeping an eye on these statistics is very addictive. Plus it’s a great feedback mechanism where you can experiment and then get some real data on what the affect is.

Feedback

Blogs are a great way to get feedback. People are quite willing to share their thoughts with you. Generally it’s a good idea to prescreen comments to eliminate spam and other unsavory remarks. It’s amazing how many online scams try to propagate by posting comments on blogs.

Versus Tech Support

I do try to answer most of the questions posted to my blog. But remember it isn’t tech support. When you open a tech support ticket, it is tracked and they will keep working on it till it’s solved. For my blog I might not have any idea and not answer, or I might be busy and it falls through the cracks. Or it requires more back and forth than can be achieved via blog comments. Also remember that blog comments aren’t a replacement for on-line forums, these are much better at gaining answers from a community of people. One of the goals of my blog is to reduce tech support, but blogging on topics that give people trouble or by blogging on ways to troubleshoot problems. But in the end if you really do need an answer and you need things to be followed up and escalated then please do call tech support.

Summary

I find blogging a very rewarding activity. One saying that I take to heart is that you don’t really understand a topic until you can teach it to someone else. I find I learn a lot researching for blog posts and that it can really crystalize my thinking when going through the process of writing an article.

Plus it is enjoyable to attend conferences and have people come up to me and say that they regularly do read my blog.