Archive for the ‘Technical Tips’ Category

ICM Testing – Part 2: Test Phases and Traps

May 9, 2008
ICM systems, as every other software system, should be tested in phases. Here are the major test phases (there can be more or less depending on the complexity and size of the project)

Unit Test: Performed by the developer to ensure a specific component performs as per the requirements. Unit tests should be performed as the system is being built.

System Test (ST): While unit testing was performed with only a single rule, system testing tests entire plans. It ensures the entire system performs as expected and as defined.

System Integration Test (SIT): An ICM system often relies on receiving data from other existing systems. For example, it may expect a file received via FTP, from an ETL process or fetch data directly from a CRM system such as SAP or Peoplesoft. SIT will ensure the systems interact the way they should.

User Acceptance Testing (UAT): Once the ICM system is fully tested by the implementers, UAT is when the business user or owner of the system test and review the results of the system. UAT should be performed on the production system, with production-like data.

Other very important tests which should be performed during ST, SIT and UAT include:

Performance Testing: This test confirms if the processing of commissions can be performed within the required window.

Stress Testing: This test verifies how the system behaves if the data load is higher than expected. For example, the compensation data may increase year over year, or may be higher during quarter end, year end.

Regression Testing: Every time a system is altered, there is a risk for a defect to be introduced. Relevant tests should be performed again to ensure the system still works as expected.

Each of those test phases are usually done in parellel to major development life cycle activities – the “V-model” above illustrates this. I will have to keep the development phases discussion for the future but yes, the left part of the “V” is also necessary.

When working with an implementation partner or vendor, the client is usually mostly responsible for the User Acceptance Testing which I will discuss in more details later.

What I must stress as being extremely important is that it cannot be taken for granted that the implementer will necessarily have a rigorous testing methodology and ethics. To avoid having big surprises when reaching UAT, the client should keep an eye on other testing activities and ask for regular status updates.

Another common trap is that getting regular status updates may not be enough. It is important for the project team to design and agree on the test cases and test plan early on. In larger implementations this is often a step which should get a sign-off. This will ensure the implementers know what the expected results should be, which is very different than the implementers assuming they know what these results should be.

YAWN… ICM Testing – Part 1: Introduction

May 8, 2008

Testing is a quality assurance process required to ensure the system works as expected and as defined. I never met anyone who found testing “sexy”, fun, or even interesting. However, it is essential!

No matter how well designed the compensation plans are, if the commission and bonus results are not correct, the implementation will be a failure. We discussed that it is important for payees to be able to correlate their rewards to their performance, but mistakes could prevent them from doing this efficiently. Another result of “bugs” or system defects will be an increased number of complaints from the payees which will consume the compensation team’s time. These errors will also add confusion, making sales people unhappy and ultimatly lead to a lost of confidence in the system.

Traditionally, testing was a task done only once the entire implementation was completed; you built the system, you tested it and you rolled it out. This is one of the best recipes for failure. Test planning should be done at the very beginning of the project and testing performed throughout the development of the system, until it is rolled out.

Testing an ICM system is particularly challenging because of the volume of different cases. A “standard” software system testing may have a test that if a button is clicked, an action will be performed. With an ICM system, every plan, every rule, and every formula should be tested. For example, if there are four quarters, each quarter using its own rate table, each rate table having 8 tiers, and each tier corresponding to a formula, there will be 32 individual formulas to test – 32 test cases.

In my next posts I will discuss many testing related topics including the different test phases, how to create proper test cases and test plans, and how to review results in Excel and in Access.

Fix your Compensation Data in One Click

February 7, 2008

No matter how good and sophisticated an Incentive Compensation Management application we implement, it looks like we always fall back on Excel for something, from data verification and validation to batch order staging.

A few weeks ago, I wrote a quick article on how to use TextPad to make vertical selections. From the comments I received, transforming raw data seems to be a major preoccupation shared by many. Several years ago I created an Excel Macro (a set of instructions to perform some task in Excel) to traverse a speadsheet, validate its data, and transform it into a certain format. Recently I had to dig it up to clean up some mainframe files. Today I modified it and applied it to the Xactly Order Upload Template to demonstrate how macros can be used.

First, download my order upload staging spreadsheet. When you open it, you will see something like the following image plus several columns. The data is intentionally incorrect.

After executing my Macro, the spreadsheet will look like this:


The macro validates one row at the time. When there are anomalies, the cell is highlighted in red. The content of some “incorrect” cells are originally in bold for you to look more closely at what will happen. You will also notice that some data will be corrected.

Here are the actions performed by the macro for each row:
  • First, position the active cell to the first row and check if the mandatory Order Code is present. If this is the case, if it is in the proper format (2 upper-case letters followed by 3 digits in this example).
  • Check if the Item Code is an alphanumerical value (only digits and letters allowed)
  • Verify that the “Quantity” field is not null.
  • Check to see if the “Amount” field only contains an amount (digits, “-” and “.”) – I could write something more complex to make sure I don’t get records like: “22-..2”.
  • Verify that the Unit type is allowed. I hardcoded a check for “USD”.
  • Check that the incentive date is actually in an Excel date format. Remember that it needs to be setup as mm/dd/yyyy for the Xactly upload.
  • Verify that the split amount sums up to 100%. I also make sure that if a 2nd split amount is entered, that the associated second employee ID is entered.
  • Lookup the “Amounts” column again, verify if there are numbers with the minus sign at the end of the number (2.0-), and transform it into the proper format (-2.0). I often get this problem when importing data from raw text files.
  • Finally, verify that the incentive date is unique for every row.

All these steps will only take a few seconds to execute for several hundred rows.

How to Run my Macro

  • Download this spreadsheet to your computer
  • Click on Tools->Macros->Macro (Alt-F8 to be quicker)
  • Select “editFile” (that’s the name of my macro) and click on “Run”
  • Tada! Your errors should now be highlighted in red.
  • Correct the errors, remove the red-fill color and run the macro again if you want to verify your corrections.
How to Modify the Macro
If you are thinking that this spreadsheet is useless for your situation, you are right. The odds for your Order Codes to be exactly in the format xx999 are pretty slim. That’s why I commented the code and made it as clear as possible.

To view the Macro, you only need to click on Tools->Macros->Visual Basic Editor. You will be able to see what code does what by reading my comments. You should be able to easily modify it, even without deep technical skills, and try out your changes. You just need to edit the code, save it, click on your spreadsheet, and run the new macro (Alt-F8). If you break it, you can re-download it.

In a few cases I use regular expressions (regex), which can be a bit complicated and tricky. Fortunatly they are popular and you may be able to find one online that does exactly what you want. If you are curious and want to learn more about regex, this website is a good place to get started.

I hope this example will be useful, and I will be glad to answer your specific questions if you have any.

On to Optimizing Business Rules, Territories/Regions, Filters, Conditions, etc.

January 24, 2008

So you implemented this brand new ICM system, but the application takes forever to run the batch jobs. While you are waiting for the compensation results, you are pulling your hair out, trying to think about a way to make the system run more quickly.

There are many advanced tricks to optimize sales compensation system performance, but here is a very quick and easy way to (sometimes) make the transaction processing run more quickly. It’s far from being a ground-breaking method, but it is often overlooked.

Business logic uses Boolean logic; it consists of conditions, separated by “AND” and “OR”, with a few sprinkled brackets. The order in which the elements are being evaluated is critical for the batch performance. The application will usually “read’ the equation from left to right.

For example, if we check that CONDITION A OR CONDITION B are true, the application will try to find out if CONDITION A is true, and if it can’t find what it’s looking for, it will move on to see if CONDITION B is true.

Optimizing OR
This means that when using an OR expression, it is preferable to try to put the condition the most likely to be true first. In this example, if CONDITION A is true, the application will not even need to check if CONDITION B is true.

Optimizing AND
For an AND expression, it works the other way around. Since both conditions need to be true, it is much preferable to put the condition the most likely to be false first.

Of course, to be able to achieve good results with this method, it is important to know if each condition is more likely to be true or false… it is also important for this likelihood to be as disproportionate as possible.

And putting Or and And Together:
Here is a slightly more complex example just to put in practice what I’m describing. If we are checking that CONDITION A is true, or that CONDITION B AND CONDITION C are true.
A OR (B AND C)

I would write the logic in this way if A was very likely to be true. Otherwise I would write the rule as (B AND C) OR A. I would further tweak this formula if I knew the likelihood of B and C to be false.

This is a bit confusing at first, but it will make complete sense with some practice.

Editing Fixed Field Length Compensation Text Files, Reports and Mainframe Files

January 11, 2008

Today my client asked me if I could take the content of an Excel spreadsheet, and export it to a text file in a very specific format. It needed to follow this format to be imported and interpreted properly by other applications. I said the task would be done quickly, before realizing that the spreadsheet had about 10,000 rows, containing complex sales transaction and compensation information which would need to be edited significantly.

When dealing with fixed field length formatted text files, text reports and mainframe files, it seems common that we need to edit something. For example it could be an ID which changed format from 8 to 9 digits and requiring a leading ‘0’. It could also be fields need to be moved around in the file.

To avoid some headaches and save a lot of time, do yourself a favor and download TextPad. TextPad is a simple (FREE) text editor for Windows which allows you to perform simple but powerful tasks.

TextPad offers th TextPad allows you to do vertical selections.

The image below illustrates what I mean by vertical selection.


By holding down the “Alt” key, and performing a “Click and Drag” just like when selecting text from left to right, you can select text from top to bottom.

As I said, I could select the entire Last Name “column” in this fashion, cut it, and paste it before the First Name “column”. I could also select the first character of the first column and delete them entirely. Finally, if I needed the ID here to be 8 digits intead of 5, I could add manually the missing zeros, copy a few rows of those zeros by using the vertical section, and paste them before the location in which you want them to appear.

Again just to illustrate this these would be the steps to insert characters before a column:

  • Insert manually the zeros for a few rows

  • Select the zeros using the vertical selection method

  • Click on copy or press “ctrl-c”
  • Place the cursor where the new “column” will appear (in this case just before ID 0070)
  • Click on paste or press “ctrl-v”

As you can see, the 2 following rows following 0000017 and 0000023 get the coped “000” appended to them.

That’s all there is to this trick, but hopefully it can, as it did for me, save you countless hours of editing large compensation files row-by-row.