Scoping meeting

After several years of “pie in the sky” conversations and two months of pre-development work I have finally been able to hold the first Scope Meeting for the development of a client’s customer self-serve website. It’s been a long time coming, but now we can start building the crowning glory to their business’ IT systems.

The first session is very important because it allows me to find out how big they want the final product. From there the process of winding back the scope for the first version can begin. Sure, the client can have everything they want, but the pathway to getting it needs to be clearly defined and well maintained right from the start.

While the answer to “Can we do that?” is almost always “Yes!”, the most important part of the reply is “Which version would you like that to be available in?”. The trick to getting it done (and paid for) is to manage the clients expectations. If they want something NOW that’s fine, what is the client willing to delay so it can happen?. If they are willing to make that trade, great, let’s do that. If not, well, sorry, that’s not possible under the current conditions.

I don’t have to hide anything. I’m educating the client so that eventually they start asking these questions before I do. Suddenly the hard part of dealing with the client evaporates.

Post-Implementation Review

After the Initial Specification; Development; Testing and first Live Deployment (Implementation) many companies forget the next step.

Post-Implementation Review.

This can be as short as a five minute meeting to pat everyone on the back for a job well done. The point is this; Is the business happy with the results?

Once the process has been live tested there will be new insight into how things work:

• Were the various steps actually performed in the expected way?
• What was different?
• Did the differences have an impact on the overall process?
• Is it worth trying to improve the results?

IS IT WORTH IMPROVING THE PROCESS?

If there are legitimate complaints about the process, then it needs to be changed and the development cycle starts again. If those complaints aren’t worth resolving then the people involved need to be notified of the decision. That way, when the process is run again, everyone knows what to expect and the process will proceed as smoothly as possible (because you’ll know what areas are going to cause issues and the people involved can adjust their expectations of the outcome). This is how good business processes are developed.

If the outcome isn’t what you expected you have two choices: Change the Process or Change your Expectations.

Customer Sales

A Customer Sales report shows you the total sales figures, over a period of time, for each customer.

Refinements on this usually include:

• Show monthly sub-totals if the period specified spans two or more months.
• Allow filtering; for specific products, customers.

Use it to:

• Find out your best customers – Who should the business invest resources in (care about) the most.
• Confirm pricing breaks awarded to customers are reflected in their sales value to the business.
• Encourage mid-range customers to increase their spend by advising them of the benefits obtainable at the next spend level (price break) – They are already buying your (A, B, C) products, do they know you also sell D, E & F? Do they purchase them from your compeditors? Why aren’t they buying them from you?

You will need:

• Customers list
• Sales history (with links to the Customer list)

A cursur-y look at SQL

I have been rewriting the invoice generating sub-system for the company today. I’m doing it in two steps; a direct port of the (VB6) code that currently runs and then I’ll see if it’s worth adding some SQL trickery to optimise the process. Step two probably won’t happen.

The ported process is hundreds of times quicker to run than the existing code. The original code takes about 10 minutes to complete – SQL Server takes 3-4 seconds. The original code has progress bars and other “this is going to take a long time” entertainment but if it’s only going to take less than 5 seconds to get the same results, I won’t be coding them across. They were only needed because it was taking such a long to complete.

I have used cursors to get through the iterative process of decoding what to do with each item that is ready to be invoiced. I know it’s frowned upon by “them that know” but quite simply, it is the most cost effective solution for the moment – there aren’t millions of invoice lines needing to be processed so the extra work required to “make it perfect” isn’t justified.

An itchy trigger finger

Aren’t triggers just marvellous things? An hour after I had added a simple update trigger to the most active table in the database there were over 220 records in the audit table. With this kind of information I could easily create employee activity demographics that can be used to review resource allocation.

There seems to have been very little CPU load put on to the (DC) machine running the SQL Server. Not much of a surprise there as the system isn’t particularly complex at the moment and most data has a short active time frame after which it is rarely accessed. Future developments will see that change slightly, but even then the system won’t be doing hundreds of thousands of transactions an hour.

Management is quite excited with the increased auditing capabilities and is starting to see palpable benefits to having made the shift to SQL Server. All I have to start doing now is managing their expectations so this excitement can be turned into future expansion contracts.

A form with a view

The full data conversion from MS Access to MS SQL Server last Friday night has been very successful! Not only was there no data loss, but very few changes were required to the interface system to keep it working through the weekend.

Now the final chapter of the full conversion begins: creating views for all the long process queries that used to take minutes to return results.

I have already begun the process, starting with the most troublesome queries that are regularly used. The idea being that the most user time will be saved by optimising them first. The pay off is huge for a small amount of work. As the list to be converted shortens management can decide if they want to continue with the process.

I will also be using triggers to add some proper auditing of various tables (those related directly to cash value transactions mostly) which is sewhat of a worry as the company is going to be able to see just how often records are changed by their staff. All in the line of performing their jobs, of course, but through full auditing analysis of the “surface area” of the various systems can be done and I can begin to reduce it to as small a size as possible.

SQL Server conversion

I’ve been having great success with converting some of my split (front-end/back-end) MS Access 2007 databases to an SQL Server back-end.

The upgrade wizard was only so helpful, I had to turn off all the extra options to get the many years of data into the SQL Server. It has become clear to me that over time the data has had various levels of ‘goodness’ and the integrity rules have been quite open to interpretation, or sometimes just automatically turned off when the situation demands it (compact database function).

What the conversion process has allowed me to do is revisit all the data constraints for all the tables and make sure they correspond to the new system’s requirements. I have had to replace quite a few null values, but the overall effect has been to really focus in on how the system has been used in the past. In spite of the way it was designed to be used.

I am also looking forward to having a much less invasive auditing mechanism through the use of triggers. The old method relied heavily on calling the right sub-routines at the interface level which is not the most ideal solution, but certainly provided a level of functionalty that worked.