I am currently going through the mindbogglingly dull process of going through the QA process I apply to everything I, or anyone on my team, develops in SSIS. There’s an established approach to this – I have drawn up a checklist of common things that must be in place for each type of package to run effectively. Some of the items checked are set by default in the templates, but I insist on checking them anyway, because “things happen” in development. Most of the checks are simple review points to make sure certain portions of the template (e.g. variables) have been properly updated. A few of them are there so another developer can quickly review whats been done and apply a common sense appraisal of the work.

The process of going through the QA checklist takes maybe about 5 minutes per package at most. However that process consistently reveals two important facts:

  1. Other people don’t do their work to the standards I set
  2. I don’t do my work to the standards I set

Just because you’re a professional who has been doing a job for years doesn’t mean you won’t forget things. Someone will distract you with a question and you lose track of your progress – and a mistake is made. This great article on a checklist for doctors working in Intensive Care units really emphasises the value of not just having a process, but having a simple means of enforcing it. Like a checklist to make sure you did everything you should have done :)

Read More

Testing Business Rules

Everybody hates testing. I hate testing. You hate testing. Bob down the corridor, well – actually he quite likes it, but then he files his pencils in order of length in a special drawer, so he’s probably not right in the head.

However, get it right and you save yourself alot of grief. This is in reference to one client I have worked with who didn’t have a test plan as much as a “Well, if the numbers match the old system when we’ve finished, well, it’ll be right and we’ll sign off”. Not in itself an invalid test, but a lousy way of testing that the details work as expected. As a result of this approach the testing phase of this project dragged on for months, people on both sides were blaming each other for the slow progress and the relationship has gone to pot – driven heavily by inadequate test plans.

So what was so bad? Well, whenever anything didn’t match – on a test case such as “numbers of widgets moved from location A to location B in March” – the only way to find out why it didn’t match was to dig through all systems, find out why they didn’t work – applying all the business rules along the way – and then find out it was down to “ah, we didn’t apply business rule A in the BI layer” – or the client had forgot they didn’t apply business rule B in the old system, but they did in the new. The amount of effort put in digging into this a) frustrated the consultants working on the project and b) made the clients lose patience as every test case that failed to ages to resolve.

Fogged into all this was that often we’d resolve one problem on the test case, only to reveal another. This further dented the client’s confidence (because obviously when it didn’t work it was always the consultancy’s fault – some clients have an odd ability to completely gloss over their own contribution to a project’s problems).

What the tests should have been doing was testing the specific rules that were being applied to the data were being done so – on a rule by rule basis that would allow us to isolate it’s function. So test cases should have been “If Widget is of type A, but not in class C then they should be moved to location B”. This way we could have picked up whether rules were being applied correctly far earlier in the process, and with much less effort.

As an addendum to this, reconciling against old reports should only be done once it’s understood how the old reports work. We had significant problems because the old reports we were trying to align against weren’t applying the same business rules as the new system.

So, what is BI Monkey’s concluding thought? Test the details. Test them in isolation. Make sure that the details work long before you try and test the whole system, and you’ll have a much better chance of success.

Read More