Extraction Transformation and Load (ETL) – some thoughts on a large IT transfer project

In 2017 I was involved in an important work project to transfer all the records in a legacy system that was being deprecated by the vendor into another maintained system. We were in some ways fortunate because both systems had been designed by a single company and they were encouraging us to transfer. We had delayed transfer for several years already but were aware that we now had to move. The vendor did have some tools in place , had staff dedicated to such transfers and were offering favorable consultancy rates. The amount of data was not horrendous in computing terms but they were far far beyond the remit of the ability to cope with any sort of manual data correction and the system was an absolute core system upon which several departments completely depended. These were systems that all departments are in from the moment they start the work day to the end. Generally its unusual if they are down for more than 5 minutes in a month, all work pretty much stops when they stop and in no circumstances could they be down for more than a day without special dispensation and coordination to indicate to manage customer expectations.

The whole project was a success although it was challenging. Here is an outline of the steps we took. As ever order here is important in most of the steps.

Step
Inform managers of all involved sections and ensure they are on board – identify and ring fence budget

Step
Appoint project manager on vendor and client side
draw together team to perform transformation.

Step
Draft time table creation of how long it will take putting in place planning for tutorials on systems and consultancy.

Step
Request managers to put forward staff on all sides willing to be involved

Step
Identify any omissions in knowledge and start to identify how this can be remedied. Kick off and complete acquisition of said staff.

Step
Meeting with lead staff to confirm buy in. Request buy in from staff including ring fencing of holidays etc.. to ensure key staff are available at required times.

Step
Set up test systems that all individuals have access to and ensure that the old and new systems can be viewed simultaneously by individuals. Ensure that the domain specialists can identify processes that will be mirrored from the old system to the new system

Step
Give DBAs or those that will be doing data transfer access to databases of source so that they can start thinking of how they can pull out information.

Step
Training for all individuals concerned in new systems.

Step
In new system start tasking individuals with how they are going to do the simple processes – eg register a record approve a record alter a record and get reports out. If possible allow new champions to start to define things like reports.

Step
Start making up any new lookup fields compared with old lookups and also start tasking individuals with creation of reports and letter that will need to be done.

Step
Start mapping the data from old system to new system – excel spreadsheets can be used for this that show the data going from the old system and what fields they are going to go into in the new system. Divide this task up between domain users – this step needs to be done after old and new systems are on domain users machines. As part of this the applications in question should expose if possible the table and field names of the source and target fields. With the systems we were involved in this was possible both for the old and new systems.

For each form on the two systems try to identify the below

Source table.field Target table.field

Also get them to map the lookup table values if direct transfer is not possible or if alias id are used in these lookups.

Source table.field.value=Equivalent.Target table.field.value

Step
Give both mapping documents to the ETL people to allow them to start writing the queries. It is unlikely that there will be a straight transfer across from table to table. While it would be expected that field and table names will be completely different it will be expected that table structure will in certain places be different in this respect it would be good to have a really nice schema diagram of both source and target.

Step
Allow data individuals to write scripts that can be run live against present initial system – if necessary doesn’t need to be live live could copy every night and then perform on 1 day old database backend – which is what we did. This means work can go on in old system and then at a touch of a button.

Encourage DBAs to be able to run these scripts every day to ensure that running them for go live is absolutely no issue. Our scripts only took about half an hour to run so this wasn’t an issue. I was personally involved in writing the SQL for those and I had systems in place to cross tab the amount coming into each new table so I could see new records and information from the old system trickling manually into the system and then being transferred.

Step
Test data input into new system

Step
Check test data input into new system with reference to domain users.

Step
Confirm go live date ensure staff available for issues

Step
Go live to production and start all new procedures ensure staff technical and domain key players on hand to make flexible solutions to things

Step
Project review on going maintenance and improvement of new system

Step
After suitable time turn off of old system if possible.

Simple Bat File to open multiple Web Pages in One Browser Window with alternate Tabs

Simple but can be useful

Useful if you are wanting to open multiple tabs in a browser window at the same time

@echo off
start "Simple Search" "https://planning.westlothian.gov.uk/publicaccess/search.do?action=simple&searchType=Application"
start "Google Maps " "https://www.google.co.uk/maps/@55.8625775,-3.6759593,17z"
start "WLC Spade" http://gis.westlothian.gov.uk/wml/spade/"
start "idoxEDRMS Login" "http://cc-dmsapp-01:8080/IDOXSoftware/secure/IG_Main?url="

Pivoting Relationships from Many to Many – One to Many – One to One – and then Key Value – Relationships and Schemas

There are a whole host of applications in which you may wish to record the biological or legal relationships between individuals. Here is a short investigation of some of the subtleties related to the options you have to model this data.

The actual biological parent to biological child relationship is somewhat more subtle than the classic description in books would have you believe (usually these equate the parent child relationship as a one to many relationship.

Strictly speaking biological parent to biological child is a many to many relationship (which in MS Access is a recursion of 2 one to many relationships) but it is a specific version of the many to many relationship. One parent can have many (read infinite) children but one child can only ever have 2 parents – one biological mother linked to one biological father (ignoring latest developments in science). Of course a one to two relationship is still a type of of one to many relationship it is a more specific type of one to many relationship but one to many nonetheless – so to model all relationships you still need two one to many links. If you consider the unique combination of mother and father as a group in itself then you can model all relationships again as only consisting of one group of one to many relationships rather than as two one to many relationships (which we presently have defined as one one to many and one one to two relationship). To do this you would have to make a mother and father a unique pair who can have infinite number of children but a child can ONLY have one mother father pair.

Many to many relationship

To a certain extent this looks like a one to many relationship as there appears to be only two tables (albeit one being aliased). This is correct but to record a dependents relationship with both parents two separate records with separate PKIDs need to be input into the persons junction table actually making it a many to many relationship.

By viewing the parents PKIDs as a group we can convert this many to many relationship back to a one to many relationship by adding a further field into the persons junction table. Now the relationship between a dependent and its mother and father can be recorded in one record in the personsjunctiontable.

It is not initially clear but a one to many relationship where the many is a finite number (ie not infinite – in our example 2) – can be re-modelled as a one to one relationship as follows.

In fact one thing that I learnt when I started reading about relational databases is that relational not only relates to the relationship between tables but the relationship within tables.

Thus we can separate a table of columns back out into a one to many relationship by pivoting out the column names and making them a value within a field themselves linking them to a field and then adding the ability to add a value. This works because the number of columns is finite. I believe this is the thinking behind a key value database.

Looking at the tables resulting from data input you would get

What makes this particularly good is that a lot of attributes can be stored against an individual and if you forget a column name you simply add it to the field name table and it becomes an additional option in the KeyValueData table. This can be particularly good if you are not sure of the set of column names that your users may wish to use. It has the disadvantage that validation will become harder as the values are often a mix of value types and cannot be so easily tied back to a particular list as the source of the field will change according to the value of its relative field name. Here for example I could easily add Mother and Father to the table of field names but a user would simply type these in rather than having them validated as per the first three relationship structures.

MS Access like development environments for the Web – 3 alternatives

So you would like to construct simple applications that you can at the moment create in MS Access but you want to do it on the web. By that I mean you would like to create a data driven application with somewhat complicated forms that can be accessed by anyone through either IE or Chrome anywhere in the world with a simple login screen at the front to prevent simply anyone accessing the applications collecting the information into a database. What are your options for programs that will assist you in a MS Access like environment rather than going the full IDE deep dive – Visual Studio route – for what I consider to be a reasonable fee?

From my experience the unicorn of access on the web is slowly coming to fruition BUT for the vast majority of people with a budget similar to that for MS Access – lets say £200 ($250) a year for unlimited applications there is simply nothing which is quite as easy and powerful as MS Access. Some are pretty close but simply not as stable and require typically several magnitudes greater amount of configuration. WYSIWYG design isn’t quite as WYSIWYG and stability is a few orders lower than the desktop.

What you are probably looking at can typically be described as either RAD tools for the Web, a Low Coding Platform or something called a Code Generator any of those phrases can be useful for Google.

Assuming you don’t have your own servers whatever you do you will need to spend money on a web host.

The minimum this is likely to cost you is in the region of $15 a month. If you don’t want to spend the next 6 months learning about the insides and outsides of frameworks then I would suggest you go to one of the below three providers who all provide complete environments set up and ready to go with their particular generators pre-installed. This is good value it is extremely difficult to beat these guys on cloud hosting costs and unless you are very advanced and have very particular requirements its a waste of time to try. All three of the below providers will allow you to create limitless number of applications albeit you are limited by the space you hire on your web server. Similarly distribution will be limited by the quality of web server you sign up for. In all likelihood if you have few users it is unlikely that the coding front ends of your applications will be a limit to the number you create more likely the size of databases you are attaching them to and the shear time you have available to create applications.

For a period I was paying a monthly amount for a Nubuilder Pro hosted platform. This performed well and I could create an unlimited number of applications. As it was so hosted I skipped the step of learning some of the deeper parts of the initial configuration. I hope at some point to go back to this. It is open source and seems well maintained with a very dedicated developer. The developer re-wrote much of it and at March 2019 it latest re-incarnation is Nubuilder Forte.

Be warned n-tier web applications do not play as friendly as the desktop you WILL be slower to construct applications than you are on the desktop, getting into it WILL take time and a bit of commitment, you WILL have far less flexibility regards coding, there WILL be less people about to ask questions and there is far far less WYSIWYG design capabilities, error trapping is poor and errors are far more likely to be fatal and the really big warning is that on release of new web frameworks you may not necessarily be able to update without a full site re-design (A fact that comes as a nasty surprise to many CIOs and Project Managers when they realise that they are locked into front end system replacements every 4 or 5 years ) Know how to get data to your local environment out of the back end and accept that the front end is ephemeral and not likely to last in the same way as your desktop applications. (Your database will last but don’t expect to be running it through the same front end ten years from now). Accept that you will now have monthly or annual rental fees for cloud provision.

That said the design of these items is significantly faster than its ever been.

Scriptcase and ASP Runner dot net (Xlinesoft also produces a PHP equivalent generator) have free downloads that are good for getting started.

Commit to one and go for it. – I’ve got both PHP and ASP.NET solutions.. Nubuilder only connects to MySQL whereas Scriptcase and ASPRunner.NET connect to pretty much any database. I started with Nubuilder and am using ASPRunner.net as well because importantly it connects to SQL Server and it was easy to get up and running in MS Azure. Scriptcase is php based and I believe the applications it build require some kind of security program to sit on the web server this put me off – they do however have hosting that you can sign up for which is pre-configured. Their IDE is web based which could be a winning advantage. One of the great advantages of ASP runner dot net is that the program produces an open web application that should run on all stock servers. I found Nubuilder Pro (now Nubuilder Forte) really conceptually elegant which despite its rather drab looks is incredibly flexible the applications it produces are however limited to MySQL and non responsive (But being non responsive you get get more detailed forms!). I would probably be able to change it’s look if I was prepared to get my own server and install everything on it myself. That is not something I have time to do at present.
Nubuilder hosts its IDE in the browser which again is an advantage. ASPRunner.net is more traditional in that you have a program running on a desktop that creates the plumbing of your application which you then need to push to a server for publication  this has the advantage that you get to see the plumbing in the background which makes backup of the site easier but publishing slightly harder.

You may have heard of other generators / design applications out there for example – Zoho Creator / Alpha 5 / Outsystems these hold your hand even more but as a result are even more proprietary and won’t fit in that budget of £200 per year ( by quite a long way!)

Some further information on costs – nubuilder being open source in theory could scale for very little money espectially if you have your own servers already. Scriptcase and Xlinesoft ASP Runner product have an initial fee followed by annual subscription – you may be able to configure it so that you can create unlimited applications for that one fee (if you have good access to web servers ) but it is likely that initially there will be some kind of variable cost per additional application you wish to build. I am using MS Azure with ASP Runner dot net and a developer database costs me about £5 a month with each application being hosted in a web application service which again costs £5. With both Scriptcase and ASP Runner if you stop paying the annual fees your applications will continue to work you will just not get version upgrades. You will be able to step back into version upgrades but you may need to restart your subscription with an additional fee.

Nubuilder Forte Link

Scriptcase Link

ASP Runner – PHPRunner and ASPRunner.Net Link

Good luck

The Economics of Immortality (part 2) and a real reason for moving platform.

The announcement in December 2014 that Microsoft was releasing a version of Visual Studio Community to virtually everyone for free and then again a further announcement in February 2016 of Microsoft’s purchase of Xamarin and subsequent release with VS community edition suggests to me that my theory that cost of development tools should steadily reduce to next to nothing is on the button. Still it comes as a surprise that it seems to be happening so quickly. This can only be a good thing especially when considering that increasingly, any application can be developed locally and sold globally or at least published internationally.

In my opinion it is looking more and more likely that the best reason for moving legacy systems to new platforms will in the future not be cost savings but rather revenue possibilities of resulting systems. In a word Software as a Service. But why will anyone one want to create their own Services rather than paying to be a customer of others? Two reasons – number one – at present the field is wide open and in many fields there is a complete lack of rich software out there applicable to individuals problem, secondly the caution with which some individuals will relinquish their information and knowledge of their tools – in that respect revenue generation might just be a nice optional bonus. I do think that different categories of software will progress at different rates and maybe in that respect we can look to Accounting software to see the future for other categories. Everyone is interested in money and the shear ubiquity of the requirement for accounting support has focused resources heavily on tools resulting in a healthy market of options for customers for online software. There are still sticking points in the form of cost especially when being charged on a per seat basis but it is no longer really practical for any individual organisation to develop their own accounting software especially when you can choose both on quality and price in the wider national markets. But outside of accounting the choice of products seem to be somewhat scarce.

So it seems its early days for Software as a Service but certainly maybe the question should be not what legacy systems need replacing but…

What tool do you have at your work which you think with a redesign could be used by others by publishing to the web?

Administering a Web Application some thoughts

In the last couple of years I have started administering a bought in Web Application from an outside vendor. I have found this really quite educational. I will not reveal the name of the product the vendor seems neither better nor worse than other vendors and many of the issues I am experiencing I see repeated with other admins of other web applications.

The software consists of various pieces of middleware linking SQL Server and the .Net and Javascript files. It uses a fairly standard Microsoft Stack to deliver a single page application. The big selling point was that that it was widely available through browsers pretty much anywhere on the web.

We have had it up and running in anger for a couple of years now and I have about 75 users all internal.

The vendor has created a new version and we now need to update. In discussion with the company they tell me that I should not be doing this myself and it is standard practice to get them in to upgrade the version. We already use it more as a stack technology to build our own applications I would want to continue this in the future. They have indicated that it will cost money to get them in to upgrade the software. This is triggering alarm bells because the existing configuration is very complicated, errors are quite regular and error messages tend to be pretty cryptic or non existent. I also like doing setup myself as it teaches me the ins and outs and speeds my learning up for systems which have non-trivial configuration.
A project board has been set up senior management are on board and well its grown legs arms and tails.

We have not widely publicised the application to the general public and they do not access the application at present.

The application itself requires significant amounts of configuration to set up correctly and parameters tend to be static and buried deep in very large HTML files. All this leads me to conclude that configuration in the new version is likely to be just as awkward. The company’s help desk is very good and there is extensive documentation but the sheer obscurity and quantity of configuration parameters makes the documentation the size of a phone directory, difficult and opaque.

Here’s some questions I should remind myself of when looking at New Web Applications in the future.
Are staff really so distributed that you require wide distribution?
Is it an internal or external distribution? – If not external do you really need a web enabled application.
Do you have internal expertise that is interested in developing applications?
With Web Applications or Local applications the backend database technology could well be the same. Internal local area development is likely to be far more flexible / cheap / quick and could motivate staff additionally it can be far more rich in its scope. It would be possible for instance to develop rich interface internally and a minimal interface externally using something like Lightswitch. This would take advantage of the strengths of both development environments.

There is a big push towards web applications I just think IT managers need to carefully consider their positions these applications tend not to be cheap and have a number of disadvantages that seem to have been pushed out of sight.

Appropriate User Interfaces

I have three guitars 10 windsurfers and 4 bikes (5 if you count a static exercise bike) I have three regularly used computers (tablet , desktop and laptop) – four if you count my phone. My bikes are not duplicates one is for racing, one is for training, one is for commuting and the final one for mountain biking. I often use my commuting bike instead of my car. I try my hand at DIY and have got to the point of building a cupboard containing drawers only for tools I have so many tools I cannot list them all from memory.

I get the impression that companies are pushing for all applications to be pushed to the web. Fine but if you have invested in an amazing building with an amazing Local Area Network why push absolutely everything to the web? Milk your assets often the longer you leave it the more stable the web environment will be – it still continues to experience massive change. Web has its advantages but flexible cheap very rich User Interface design doesn’t seem to be it. The benefit of database driven applications today is that the backend is totally independent of the User Interface. Developing an internal local area based UI can be totally independent of building a web based user interface.

Carefully consider and don’t be afraid to use the full range of tools available to you get the backend right and then work on a very very usable front end. Users want quick usable design that fit their needs they really don’t care about the technology.

Business Continuity

I see Business Continuity frequently evidenced as a reason by management to disallow staff responsibility for their business systems. The belief being that individuals will become too central to the operation of the organisation and will become somehow irreplaceable.

For the record I would like to state that I consider this to be failed logic.

Parents with children will encourage them to take responsibility by trusting them with tasks. Why is this important? Why? Because one day the parents won’t be there to support their children who will have to stand on their own two feet and it is better for the offspring to make mistakes with the parents there to catch them than to wait till the parents are gone and then for them to make the same mistakes. Many parents consider that their children do things in inefficient ways nonetheless they continually trust them. The world continues to progress and the human race continues to be the most successful species on the planet (hands down). It is the mechanism of passing knowledge down from one generation to another. So giving responsibility and trusting children far from decreasing continuity has a long history of ensuring it.

If anyone uses business continuity as a reason to prevent individuals from being given responsibility you might want to highlight the above point to them.

The Handover – where the dragons lie

woman-see-dragons-everywhere-2

The handover – often someone figures out that something needs to be done – maybe they have realised an important omission or maybe there is a problem that only comes to light in a process when you start designing.

For whatever reason you can’t do the work yourself and you need to hand the work to someone else. This I consider to be a dangerous task the threat of information loss is immense. Here are some points I try to ensure to minimise this ever present risk.

Give it to an identifiable person – I always find it curious how top management consider it important that they know each other and feel comfortable in each others presence they carefully pick their colleagues and treat them with respect by ensuring accurate names are used and generally constantly talking to each other often informally and flexibly. However I have seen systems implemented by management which actively discourage these same forms of informal communication and actually discourage personal responsibility.

Give that person tasked responsibility for the whole task and allow them the flexibility to tackle it how they see fit. People adjust processes so that they are more productive and will use their personal skills in the workplace often when they were not hired on that basis. A regular example of this is probably individuals who can speak multiple languages. I have seen several situations where a customer from another nation arrives and someone in a team happens to speak their language in a flexible environment that person often gets involved in that task simply because good communication is the first step in tackling a problem. I was in the Premier Inn in London and I was pleased to note that the staff all had badges on them noting not just their names but the flags relating to languages they could speak. It was nice to see waiters with multiple flags on them. Gave you just a small bit of background on the person and encouraged customers of particular nationalities to seek out correct staff.

Give it to someone who is familiar with the domain (scope of work) complicated tasks need not just technical skill but domain specific skill. Most people know how a booking systems works as nearly everyone is familiar with booking things online. But would you expect a general programmer to be familiar with the process of registering births marriages and deaths or submitting tax returns for instance. If someone doesn’t have domain specific skills give them extra time.

Close physical proximity is a bonus – so called water cooler conversations are often about work even if it may stray into what might be considered gossip. For some even then gossip may inform you of particular tender subjects that allow you to better consider the respect of your colleagues and better conduct yourself in conversation to get the optimum of work between individuals. It also allows for constant review of process and answer of questions when they arise. I prefer in e-mails to ask single questions at a time. I find that if you store up questions in a massive e-mail the first two questions are asked and the others are just omitted. As far as I can tell this is a human trait. Its my experience that when I sit close to individuals I ask questions when they arise which when answered usually completely negate the need for further questions. This uses comparative advantage which is always a bonus.

Concentrate on the outcomes not the technology that they are using especially if there are no costs to something let them use what they want. Databases are databases – spreadsheets are spreadsheets – they prefer the dvorak keyboard to qwerty keyboard good for them. For some spreadsheets are better for them than databases others will always choose databases – Does it really matter. I have seen some really excellent spreadsheets for tasks that I would have preferred to use databases. You could argue one over the other for particular tasks and maybe even prove that one was better than the other on somekind of mathematical basis. But would SQL Server really be better for your granny than a spreadsheet. NO.

Ensure that the individual will have a continual link to the system post development – he should recognise that the stability and the continuation of the system is as important as simply pushing something out the door.

At least initially continually check up on the individual and ensure that things are progressing as intended. This requires time at the beginning but you should be able to wind it down after that.

Handovers are fraught with danger don’t make them be fraught with danger for you.

Project Management – Things that help lead to successful outcomes

FinishLine

Many roles within organisations now require good project management skills especially when it comes to implementing new IT systems and applications. But are there things that can be put in place at the beginning to improve your chance of success. I would say yes and if I am involved in a project my personal guidelines are as follows;

Step 1 : Get Stuck In
The benefit of computers is that manipulated electrons are essentially free and immortal. Try to rearrange a few. If you aren’t getting anywhere wipe them and then re-arrange them some more. Even if you are not successful you are successful in knowing that one particular arrangement cannot be achieved. You are creating a machine just like children do with Lego or engineers create with bricks and mortar except your bricks can immediately be removed and copied infinitely and each additional brick often costs nothing. In most organisations you will quickly come up against configuration and security problems. Configuration and security problems come out of nowhere often and can be project killers best to know about them up front.

Step 2 : Know your Technology
If you don’t know it at the beginning you better hope you know it at the end – go to step 1 if you are struggling with step 2 – That’s recursion for you.

Step 3 : Increment often and test constantly
Set short deadlines and try to regularly meet with client to show progress – can be frustrating if clients start going off on tangents

Step 4 : Know the Process
To date I haven’t been asked to design any systems that I have had particular difficulty in understanding the process. Undoubtedly I think this would be different if I was trying to create an application for geology exploration or for instance mapping or maybe translation. The mathematics behind those kind of applications are complicated. Most business processes tend to be remarkably simple and the simple act of normalizing the data is usually enough for me to get to grips on how the system will be used.

Step 5 : Build in redundancy
Properly normalize your data build in extra fields if you want even if they are not used – for example collecting information on individuals I always add a field for date of birth even if its not spec’d invariably someone comes along and says actually it would be useful to know what age our customers are.

Step 6 : Have privileges
There’s nothing that will slow down a project quicker if you have to hand over responsibility of tasks to uninterested individuals who are not part of the project team. Better to have those people in the team and make sure they are on board with the importance of following through with the project.

Good luck and happy hunting

Comparative Advantage

My opinion on fixing things is that if someone has to sit around and watch you fix something the cost of the job is not just your time in fixing it but it’s also the cost of the time of the person sitting around and watching you fix the thing and the unhappiness that they feel in being pretty helpless.

Hence another reason to empower users.

How do you truly asses the cost of a job – if you are in private business it tends to be everything that you do to undertake the work. How long it is going to take you to get to the location to do the work , how much it will cost you to get to that location. In some large businesses things like time to get to a location to fix the item tends to be totally overlooked – this can be a not insignificant amount of time even if there is no revenue cost. I don’t care who you are I’m of the opinion you want to be reducing wasted time as much as possible.

Developer Operations and Empowering Users My Opinion

Recently I was listening to a Channel 9 Microsoft podcast talking about how their products could be used in developer operator environments. Interestingly the podcast talked about Developer Operations being a subdivision of the central IT department and how once an application was up and running the developer would never hand over support to a separate team to maintain the code. Regards this definition I would go further and suggest that organisations should encourage super users aka citizen programmers to develop processes and applications themselves. A mid-way strategy might be to station professional developers with the applications within the sections for whom they do the work and responsible to the divisional head and NOT the IT section. I suggest the benefits of this approach are as follows;

Geographical location – Not all users work in offices where you have access to 47 inch screens with instant Skype, team viewer access and 24/7 high bandwidth internet access. Those supporting an application sometimes end up talking to Betty the cleaner who is on a landline 20 metres from the computer with the issues, the only reason you are speaking to her is because she just happened to pick up the phone, on further questioning she does have an e-mail account but hasn’t used it for a couple of weeks, she politely tells you everyone else has gone home but is sure if you phone back on Monday someone will help out. You think damn if I could just sit at the machine and see what’s actually going on. Also describing what can be UI graphical issues in text or speech is incredibly inefficient 95% of the useful information is often lost in translation. It can be impossible to articulate or describe UI problems except by demonstration. A Developer operator that is local doesn’t have to rely on understanding the problem second hand their first experience of the problem is often first hand even if it’s not they often immediately familiarise themselves with it first hand. What’s better than having an excellent logging system for tracking problems? Yes you bet, not needing one in the first instance.

Aligning your staff with Corporate Objectives – there is a reason rental houses tend to be more run down than privately owned properties. If something goes wrong it its not going to be the occupiers cost to fix it – its not my job to make it work well. This is rarely the case with people who build and operate their own systems their motivation quickly aligns with the organisations objectives – if they can make it work better by predicting problems and fixing them before they arise, their life is made easier so, they tend to get in gear.

Motivation – if they can make it work better by predicting problems and fixing them before they arise their life is made easier, that is a powerful motivator to get things right and to predict problems before they arise. The people who fight for systems are usually the users. I’ve come across a lot of IT support where they get it to an “acceptable” level which usually means it gets a completion tick often the minimum is done to get the thing working. I was once on a project and was trying to get an icon working for the web application. I got the icon from the central team but wasn’t very happy with the look of it as I thought it looked bad. I asked the project manager he agreed it looked crap but his closing comment was “who cares it was authorised”

Education – Hey guess what you learn things about your work if you know how your tools work. You start making connections that feed back to improved practices. There’s an actual name for this Constructionism proposed by among others Seymour Papert – actually the ideas been around for about as long as people have been able to think.

A quote attributed to Aristotle ;

“For the things we have to learn before we can do them, we learn by doing them”

I believe people to be naturally intelligent with the ability to pick things up – especially if they are immersed in the problem – case in point learning a language. Everyone learns it by immersion and because they have to. I don’t like the implication that because most people aren’t going to be the next Einstein they can’t be taught things I have yet to come across a colleague, myself included who hasn’t been more useful because they know more rather than less.

Reaction Time – for insurance purposes people are duty bound to mitigate damage. Now they do better job at this if they are actually on site when things happen. Central developers often have very little visibility on some of their projects and it may be days before they hear about something. Often the time to reaction can be critical in reducing damage. A water leak spotted in five minutes is fixable – left for a day and its a disaster. Granted the majority of applications aren’t that important however the same principle still applies.

An on hand developer operator doesn’t preclude additional support whereas central support requires it – they do the easy stuff they call for help for the difficult stuff. Additionally they are reliable contact for accurate description and dissemination of important information. This can be very useful. It is easy to get outside help in when things are needed it is not easy to suddenly train up local staff to accurately describe a difficult problem.

Good pedigree – hang on here’s some famous developer operators – Mark Zuckerberg programmer entrepreneur / Jeff Bezos entrepreneur and computer scientist / Bill Gates entrepreneur and programmer / David Braben entrepreneur and programmer / Sergey Brin computer scientist and entrepreneur. You might know what companies they founded. Do they promote dev-ops at their companies – I think they do. Oh and I even left out Elon Musk

Prioritisation of Work Load – Local users are far better at judging importance. The importance of a job may not relate to its difficulty or complexity. This makes it a requirement to understand the domain when deciding on what problems need to be solved first. Here’s an example – a company is going to close on a deal if they can print out and deliver a tender document. The users were a bit rushed and left it to the last minute. IT support know roughly what is going on but aren’t completely ear to the ground on tendering so the main printer support guy is off on holiday. Besides he thinks in the normal course of events a dropped printer is not an emergency. Extreme example but this kind of reward / problem ranking happens all the time. Ok computer support systems allow you to rank problems – that kind of fuzzy logic is exactly the kind of things that never really works out well and usually doesn’t allow for altering of priorities after submission. This can lead to businesses adopting the everything is important or nothing is important default setting. The first has people shouting at each other for totally unimportant tasks while the latter leads to speed of a sloth.

Security is a phony excuse – you have no defense other than vigilance against a sustained attack from an internal person who is talented. Setting ridiculous security levels on everything to protect against a few valuable parts is totally counter productive. The de-motivation of staff and subsequent inefficiency of process is a far greater danger to the viability of your business than fraud is likely to be. Resulting reduced levels of visibility which ironically can increase the opportunity for fraud and likelihood of major errors.

Trusting Your Gut or When Not to Use Computers.

wargamesAs good as computers are at calculating numbers they are really really bad at doing somethings that humans are just great at like making decisions based on fuzzy logic or translating things between languages. In such situations always go for the human at least until C3P0 comes along. Watson cost $3 million to build, good if you want to play trivial pursuit but ask it to choose between competing software vendors and well you will be nowhere.

That’s you numerical based ranking systems for software purchase!!!

SEARCHING RECORDS – My UI Design Patterns

Well if it is good enough for Google

One single variable field with filtering only occurring after return has been hit. Search is across complete range of fields and searches for a string within all of the fields. Search string is maintained after search and results shown below. Don’t make the search case sensitive unless there is a good reason for doing so.

Security the double edged sword.

TribesandTechnology

I had a dream about wildlife parks and tribes this week and I realised in the morning that sometimes when we are all crowded around a computer we don’t all know as much about the systems in place that we should.

What’s the best way of remedying this? – giving everyone access to break down barriers or setting up security cordons and only allowing access to the privileged few?

40 Useful Facts to Know about a System

40

A list of points I should remember to ask when doing ongoing system development, total re-design or external purchase.

For alternative systems rank both existing and new and compare.
Collection of facts is as objective as I could make it – interpretations and consideration as ever is subjective.

I have my own self built project software that records most of these points. The users and user roles is particularly useful my home baked pm software is integrated into my contacts which means if individuals titles and contact details change then the details below are automatically updated.

1. Name of system
2. Version number
3. General Description of the system (written)
4. Date of Creation
5. Name of Creator along with contact details
6. Name of Administrator along with contact details (If different from Creator)
7. Name of the person responsible for backing up the system
8. Backup description
9.Names of existing users – should include administrators and creator
(Name – Organisation Position – e-mail – contact no – approx time used per day – Method of access – Role in system – Description of tasks carried out – Machine tag numbers if required)
10.Names of potential future users
(Name – Organisation Position – e-mail – contact no – approx time used per day – Method of access – Role in system – Description of tasks carried out – Machine tag numbers if required)
11. Operating system runs on (Windows / Linux / Mac / Wine) If Linux please indicate distro
12. Backend Database type (SQL Server, Oracle, MS Access, Filemaker Pro, Omnis, Fox Pro, DB2, MySQL, PostGres, PostGIS, VoltDB, MongoDB, NoSQL)
13. – unc name of database server(s)
14. – unc name of web server(s)
15. – Important directories on particular servers
16. – name of backend database
17. Has the application been designed specifically to allow for geo referencing ( This is not a question as to whether the back end is spatially enabled ) – (Yes / No)
18. Front End technologies – list
19. Main programming languages
20. Is this a client facing system (Yes / No)
21. Could it be a client facing system (Yes / No)
22. Do individuals presently access this from mobile phones (Yes / No)
23. Do individuals presently access this from tablet devices (Yes / No)
24. Is the information available to the general public if they make a freedom of information request. ( Yes / No )
25. Please indicate the information in the system that would not be distributed even if someone requested it. [Description]
26. Is there a statutory requirement to keep this information ( Yes / No)
27. Was the system originally internally or externally created (Internal / External)
28. Capital cost of original construction (if relevant)
29. Estimated annual revenue cost of system ( not interested in wages of individuals only interested in actual costs paid to outside bodies )
30. Can we get a table structure of the system complete with all field names and field types. (Yes / No) – Please attach if yes
31. Is the process unique to the organisation( Yes / No )
32. How often is the design of the system altered ( As and when required, Changes are batched and pushed through periodically, Only in exceptional circumstances, Never)
33. Who can request changes to the system ( Anyone , only management , there is a working group of disparate users who request changes to an outside company , pretty much no one)
34. If that is an outside company makes the changes how often is that done (monthly, yearly, very rarely, never)
35. Are users presently happy with the system ( Yes / No )
36. Are management presently happy with the system ( Yes / No )
37. Any obvious other systems it could be combined with (No – Yes please list)
38. How is performance (Very good, Good, Adequate, Poor, Very Poor)
39. Can you list other organisations that use the same software (No, list of organisations)
40. Lastly where do you consider the application to be strong and where weak and do you have any specific proposals that could significantly improve your interaction with the system. [description]

The Importance of a Booking Confirmation and Careful Communication

rsvp

With booking systems there are a lot of important things that will make a system successful – good operational speed – a well designed table structure – great UI and good stability but without really really great communication and confirmation with the customer you’re missing a golden opportunity.

I would argue that wedding invites represent the absolute gold standard in organising communication between parties in what is essentially a booking contract.

* Firstly you give the responsibility of organisation to a responsible and motivated party who is familiar with the whole process and why they are doing what they are doing.
* Then great care is taken in the timing of when items should be sent out (not too long before not too short)
* Addresses of all parties are carefully and thoroughly checked
* Extreme care is taken in the wording and importantly formating of communications (and often flexible and personal messages included for specific parties)
* Extreme clarity is taken to ensure party’s know who to contact if their circumstances change and RSVPs come back to the persons that sent them out.
* Direct contact is encouraged in situations lacking clarity
The RSVP process is formalised to reduce confusion ensuring that the original party can identify who communications come back from and simplified to require the minimum amount of effort on the part of the guest.
* All the while the process is constantly overseen by senior management who have complete visibilty and problems are tackled when they arise.

A good booking confirmation will act not only as a great piece of promotion for the organisation (you have the attention of the individual concerned they are listening to every word you write) but superb quality and clarity reduces confusion, reduces the requirement for clarifying further work resulting in less errors, increases the likelihood that the individuals will return to do business, probably will encourage them to book more courses and give confidence that the contract will be actually carried out.

To my mind this kind of detail should be born in mind for all system communications.

An important consideration when designing new systems.

The Gunslinger and the Indian

There’s a fight on your computer over a girl called Planned Obsolecence

the-good-the-bad-and-the-ugly-600x254
I get that you need to update software periodically and I definitely want to see software developers paid an appropriate amount but what happens when no one can think of anymore functions to add? With my older applications I definitely change them less and less as time goes on. What then? Can we keep our ten year old software packages which still have the distilled mathematical knowledge from 2 millenia of scientific knowledge 95% of which we have never used?

How will software houses make their money? Temptation is to either by accident or design discourage or deny companies the ability to buy and maintain software outright for local installations rather requiring them to rent from proprietary servers to steady their reducing income streams? (cough splutter splutter Adobe)

I’m sure it will act as a tempting vacancy for the Open Source community, especially on the desktop.

What does the user do?

Go with the native Indian who is less familiar and sometimes rough around the edges but has a far more sustainable and economical way of life or the mercenary gun slinger who you always kind of half suspect has his own agenda and may leave town quickly.

To the horses boys there’s a fight coming.

Internal Development Good or Bad

20_Type9Target
Elite Dangerous from Frontier Developments an example of a game built on internally developed game engine – and they are seriously kicking ass as a result.

It is tempting having been burnt with IT projects to say right that’s it I don’t trust consultants anymore I’m going to try and do everything by myself we cannot trust outside companies with our valuable processes – we are after all primarily purely a process company and our processes are golden to us. I must admit I have had periods of my working life where I have been sympathetic to this view. I have found Internally developed systems great because – they motivate internal teams, they increase knowledge of systems design, they can be completely market leading, they can be incredibly flexible and reactive, they really engender responsibility and accountability, they can be very incremental and adaptable and certain individuals can develop systems often using existing IT infrastructure for solely labour costs. (Why employ capable people if you don’t want to use them?)

Against this there are some fairly big black marks which for some are insurmountable.
They tend to be very person dependent with a lot of power resting with certain individuals
Often those individuals are not necessarily chosen by management and often management really don’t like this.
People move on
Most systems will take a year to at least get up and running and sometimes solutions are needed quicker than this.

As a result I would always suggest a mixed strategy of allowing talented individuals to develop those areas for which there are no good products on the market while encouraging buy in of good tools and good products where tools and products do exist. It really should not be an either or and going down solely down either path could lead to problems. It is of course rare to go solely down the all internal route but I am aware of companies only going down the externally produced route.

But be aware even if you are going down the open source and internal development path – be prepared to invest. Buy good IDEs – don’t skimp on database support. Hire consultants (but please give them focused tasks non delivery of results from consultants is often because they’ve been hired without any real idea of what is required of them) Buy products because they look interesting. Financially support open source projects that are actively contributing – not because your liberal with your money but because value is value chances are you can still choose a cheaper path that benefits you and the providers by not leaving yourself open to the kind of consultancy that costs but does not provide. Most of all its your chance to buy in and vote on the future of your software. Open source providers will sit up and notice pay attention and give real weight to your requests.

Be warned though this kind of imagination and vision requires allowing good visibility and control across large parts of the network something that seems to contradict the general trend towards tighter formal security (at least where I work). I would argue however that tighter security often leads to loss of accountability and responsility (a lack of people who can track through all the programs of an issue) resulting in people and especially management being completely blind sided by problems and counter intuitively greater risk of negilgence and greater opportunity for fraud. [Financial Crisis and the Accounting profession anyone?]

I would add that if you really want to be world class you are going to have to take control of your software.

QGIS – Free GREAT Digital Mapping Software

windglobe A map showing winds over the Atlantic

Looking for a desktop digital mapping package? You really need to check out QGIS it is an absolutely excellent open source geographical information system. At the time of writing the latest version was QGIS 2.4 – the below tips were taken from research into windows version of QGIS 2.2

Full program available here.
Link to www.qgis.org site (English)

Tip : Navigation – Magnification – Plus or Minus mangifier Icons or wheel scroll
Tip : Navigation – Scroll – cursor keys or alternatively the hand icon or hold down the space bar and movement of the mouse when pointer is in the map window.
Tip : Projection – CRS stands for Coordinate Referencing System – lots of different ways of showing what is essentially the surface of a sphere on a flat surface – and more generally referred to as map projection – you will remember from geography. For most UK maps the coordinates are often in Ordnance Survey UK Grid therefore you want the properties of Coordinate Referencing System of the project to be OSGB and you want the coordinate referencing system of the individual layers to be OSGB as well. Once this is done the scaling will be correct and so will the measurement tools.
Tip : View / Panel – allows you to switch on and off menus – very good and very powerful
Tip : Graphical Record selection – Icon in the middle of the toolbar that has a number of differing options – it’s a drop down that allows different things for selection.
Tip : Attribute Record selection – Icon in the middle of the toolbar that allows for table attribute selection. Shows the table and this can be sorted properly.
Tip : Deselect Records – can individually de-select using the keyboard alternatively you can also use the de-select icon in the middle of the top of the screen.
Tip : Browser – brilliant for navigating through the directory and seems a lot quicker than going through the pop up individual menus on the left – for me anyway – additionally you can add an additional browser layer and transfer things between directories. It is an excellent alternative to the file dialogue manager.
Tip : View / Decorations – You can add things like scale bar and copyright to the map window here – very intuitive and nice finishing touch to your projects.
Tip : Labelling – Make scale dependent – highlight the layer you are interested in and right click. Now select the Labels option and within the Size section change the drop down from points to map units.
Tip : Labelling – Threshold the labelling – right click on layer and then go to the Rendering section and select scale based visibilty and adjust accordingly.

Above interpreted from the QGIS manual see:
Link to PDF version of QGIS v2.2 manual