Supporting applications developed in excel and access
No sooner had I posted the proposed subject on Twitter, a tweet came from a well respect IT Service Management author and blogger (to name one of many channels this person operates in) reminding me that if you support one application developed in a "desktop productivity tool" you will soon be supporting dozens or 100's of these types of app's. I certainly agree where large corporates are concerned, the introduction to "user power" around spreadsheets and database programs creates a user application industry that can become un-manageable, The reason for this topic came from a major incident a member of my team dealt with at the weekend which started off as a change which passed the Change Advisory Board but then caused an impact. Unknown to the change board, we had introduced an upgrade to MS Office which resulted in a site which had developed its own work scheduling tool in Excel calling a Major Incident as certain functionality no longer worked. The fact is, no one knew about this "application" as it was not listed in the service catalogue and as far as the users were concerned, it is just a spreadsheet but when it did not work it became a major service issue.
This site has always been about taking the learning's of large businesses and where they are applicable to smaller businesses, allowing them to learn from this experience. So should users be allowed to create applications from spreadsheets or database programs? The reply to my tweet would clearly support a resounding "no", but what if the business strategy supports such behavior?
One of the "4 Ps" of Service Strategy is "Perspective"; a description of a vision or direction. What if small businesses choose for financial reasons to allow applications to be developed this way? Certainly new start-ups may not be cash rich and the development of a customer database could be a natural introduction. It would not take much to "bolt on" an invoicing module which each month runs a report that gets keyed into the accounts spreadsheet and suddenly the best intentions of going down the out of "the box application" gets by-passed with an in-house build approach. This "Perspective" linked to a "Position" to operate at a low cost may create an operating environment where the use of Excel, Access and the like do allow "application" type services to exist.
So can a business maintain support and reduce the risk if such an approach is taken? I would like to offer the following key points as a starter:
Documentation; If you are going to embark on a journey of creating applications using desktop tools such as Excel or Access a key point should be around documentation. Now please don't think this is just about technical aspects (i.e. cell BB17 is an if statement comparing cells ......), it should also include an explanation of the concepts the calculation or function is trying to achieve. Why is this important? Well consider these situations;
- An application is developed in Excel 2002, you upgrade the desktop and move to 2007. Some of the functionality stops working and you have to completely redevelop the "application" in 2007. Having an understanding of the concept as well as the function would allow a new developer to recode the spreadsheet in an effective manner, maybe making use of some of the new functionality to make it more efficient.
- The person who developed the application may move on. Good documentation should provide someone with similar skills the information they require to investigate and resolve issues as they occur without having to spend time unpicking the workings of the application
Service Catalogue; I have always believed that small businesses are at an advantage over their larger corporate counterpart's when it comes to a number of ITSM processes and the creation and management of a service catalogue is one of these areas. The service catalogue should be used to clearly "list" all of the services used by the business and when it comes to applications developed in desktop tools, the inclusion in the service catalogue becomes more important. Lets take something such as a spreadsheet developed by the accounts department. In order to get purchase orders raised easily a spreadsheet has been developed with all of the supplier information within it. Once the request is raised, the spreadsheet prints it in PDF format and emails it to the admin clerk. This is no longer "a spreadsheet" and its content / output is no longer "data". A service called "PO request" has been created and should be clearly shown on the service catalogue with an attribute showing that it has been written in Excel and is dependent on Adobe x. For more information on how an SME could create a service catalogue, contact us requesting Free Report - SL1.
Release Path; We talked briefly in the documentation section about a scenario where documentation could be useful and the concept of release management around applications developed in desktop tools really ends up being focused in two areas;
- The main area to be considered is really around the release management of the core application (i.e. the one that it is written in such as Access). Before embarking on the development of an application it is always to useful to know the overall plan for the application roadmap (e.g. we are not planning to upgrade for the next 3 years) and also the method of deployment (e.g. all new clients will get the latest version but existing clients will stay on their existing version). Understanding the approach to the core application release strategy should help de-risk service issues as a result of a diverse desktop landscape or a full change earlier than anticipated
- Once the core application roadmap is understood, you can then focus on how you will approach the main "developed application". You may want to freeze all development of the first release for the first six months or roll up any enhancements into a twice yearly upgrade. Either way, by establishing a clear upgrade path the number of issues should be minimised. Now a reality check here is needed because we are talking potentially about applications developed by the end users who may retain the ability to continually tweak and develop the application and this is where one of the obvious weaknesses of application developed in this manner start to surface. Their is no real answer and it is a risk that should be clearly understood and discussed if you are prepared to go down this route
Ideally this is where your change control processes come into play and if you would like more information in developing a simple change management process for a small business, please request our short article on this subject.
IT Continuity; So how do you build the provision of such a service into your IT Continuity program? My immediate reply would be "if its that important it should not be developed this way!", but lets be a realist.... I recently visited a large manufacturing company which were managing all of their HR records via an access database. They were waiting for SAP to be implemented and had been using this as a stop gap for a significant period of time. In this case you have got to look at continuity from a number of viewpoints. The most simple is to look at the VBF's (vital business functions) and ensure that you have an alternative way of carrying them out. For our PO process earlier, this could be replicated using a simple paper form and a list of supplier numbers and cost centers. The next aspect to consider is functionality, if the "system is not dependent on data it may be possible to have a point in time copy of the "spreadsheet" or database with all of the macros and lookups working and in the event of the operational copy becoming corrupt it just gets replaced with the working copy. In this situation you must remember to replace your back up copy with a tested working copy every time a development change is made (once again emphasising the important of change management!). Finally if data is important you really only have one choice and that is to ensure that regular copies are taken and stored in a separate location to the original source file. The frequency of these backups will drive the level of recoverability so for example if you are taking nightly backups on a system that records customer orders and you lose the data at 4:30pm you run the risk of only being able to restore back to the previous days backup and potentially lose visibility of that days orders.
This hasn't been an easy topic to blog about because every bone in my body shouts "don't condone it", BUT once again reality check kicks in and we have got to acknowledge that a lot of companies (both large and small) choose to take this approach due to both cost and flexibility. This article does not provide the definitive and comprehensive answer to supporting applications written in the common desktop productivity tool but I hope by putting these thoughts down, small businesses can do so with less risk.
If I can be of any help or if you just want to discuss my viewpoint in more detail, please contact me.
