January, 2009

Data Normalization - Know Your Data

A post here in these ITKnowledgeExchange blogs that recently caught my eye was this one written by Stephen Harris entitled “Data Challenges Can be Solved With Business Intelligence“.  It is a rather lengthy post touching on several points about data challenges and BI.  What I immediately latched onto in his post was what he refers to as a motto - “Thou shalt know thy data“.

While I have never phrased my firm belief in knowing your data in the way he does, I certainly agree that knowing your data is an absolute must.  Furthermore, his reference to cleansing, auditing, securing, managing and refreshing data is also an essential ingredient toward any meaningful reporting - never mind the special requirements for an effective BI implementation.

Once again I find myself “down sizing” information and ideas I read about to the needs of the businesses which I service, the small ones.  I’ve blogged recently about reporting requirements in these economic times, and certainly “…having information about your business at your fingertips…” is critical, not just a “nice to have”.

Reporting, BI and data “cleanliness” all depend to some extent upon the normalization of the data.  I can’t imagine trying to normalize a database without knowing your data.  If you would like a quick introduction to the topic of normalization I found “Introduction to Data Normalization: A Database “Best” Practice” to be an excellent place to start. 

As with so many areas in development there are multitudes of tradeoffs which come into play with the design of a database.  It is absolutely critical that the developer know and understand the data pieces (fields) and how they relate, but just as critical is that the developer understand the reporting requirements and other characteristics of the data, the database itself, the network and hardware platform, and “how” data will be queried.  Many speed issues can actually be caused by a database which has been normalized to such an extent that in order to provide the reporting required in an acceptable time span many extra steps are required to prep the data for the presentation sequence desired. 

The more up close and personal a developer is with the data the greater the opportunity there is to evaluate the data quality.  After there have been a number of changes in the form of additions and subtractions to fields or tables in the database it is a good practice to review the design again to determine if there are changes that should be made to further normalize the database.  My experience indicates that often changes are desired.

Factors Against a Custom Application

My previous post pointed out a few of the reasons why one might choose to create a custom application.  For this post I’d like to further the discussion to review some of the reasons (valid and otherwise) for NOT creating a custom application.  I would include in this category:

• FEAR - Many a story has been told about the “dream” development project that became a nightmare - obviously what there is to do here is to carefully select developer(s)
• It will be “Too Costly” - It doesn’t have to be in most cases
• “Standards” won’t allow it
• Too big of a commitment
• No “New” requirements are on the horizon
• Suitable “Off-the-shelf” software for your industry is available and within your budget range
• Your operation is running efficiently, programs being used suit your needs
• Users are getting what they need from the existing system
• Existing system is technically “up-to-date”

 Certainly if the last four points above hold true for your operation one would be hard-pressed to create a valid case for a custom program - you just don’t need it!  As for the standards issue there’s not much I can say about it.  I have seen companies that have struggled with exceptionally ineffective and inefficient software - only because a proposed app won’t “fit” the “Standard” - I have to seriously wonder about this decision.  In some instances it seems to be that the “Fear” or “Too costly” or “fear of commitment” arguments come into place.

Next Post — “Identifying Potentially Valuable Custom Applications”

Identifying Potentially Valuable Custom Applications

Without fail, in my experience as a developer any potentially valuable custom application project has been the result of identifying some area of operation which:

• Has become unwieldy, requiring too many steps or multiple data entry
• Has become more and more demanding as a result of increased volume
• Always seemed to be behind time - trying to play catchup and not succeeding
• Required information availability not currently or effectively available
• Just “seemed” to take too long to accomplish

My post Application Value is an excellent example of creating a custom application because of unwieldiness.  Another application which I designed that provided for scanning and categorizing of tax exempt certificates was an application which solved all of the issues mentioned above. 

Availability of information is a key factor in identifying value - i.e. An operation may be required to maintain original records  to produce during audits, yet, that same information may also be required at out-lying locations.  The choice can be to maintain a file at each location (trying to keep it up-to-date at all times of course), or somehow provide the means to quickly access the information via a custom application.  “Scanning and Cataloguing” such information can provide the ready access required to solve the problem - and there probably isn’t an “off-the-shelf” program to help!

Bottom line to identifying potentially valuable applications — Review operations closely to identify any of the categories listed above.  Once identified - it’s time to get creative!

Why Create a Custom Application?

First — In the interest of public discloser I must confess that perhaps I am biased on the subject of custom applications.  While writing my previous post I found myself once again becoming excited about application development, and more especially the value that such an application can bring to an operation.  I suspect that thousands, maybe hundreds of thousands of potentially high value applications remain undeveloped because those responsible for the operation do not realize that it is possible to have the kind of efficient, specific and valuable applications developed at a reasonable cost.

Development tools available today provide the means to easily produce an application in stages, creating an initial database, entry forms and limited reporting as users are loading data into master tables etc.  While my experience with development tools other than Visual Dataflex (VDF) is limited, I’m aware that there are multiple paths to produce powerful applications for a reasonable cost.  The applications can easily be multi-user at the start and grow as additional functionality is desired.  No need to use an expensive database, although the tools in most cases can “talk” with multiple databases. 

So, back to “Why a Custom Application?”  I’d list the following:

• Custom Apps can be “lean and mean” - no “extras” needed
• “New” functionality is added only as desired, not at a designers whim
• “Lean and mean” is efficient
• More “understandable” for users - thus easier to use with fewer errors
• A well developed app can provide excellent return on investment
• “Off-the-shelf” software doesn’t really “fit” your needs or requires too much in the way of training, maintenance or “tweaking” for it to be of value
• Your industry doesn’t have any “canned” vertical market software available, or it is designed for too large/small an operation, is too costly, doesn’t “fit” the way you want/need it to.

You can probably think of many others as well, but I believe those above list covers the gambit.

Next Post — “Factors Against a Custom Application?”

Application Value

Earlier this week I was speaking with a client for whom I had developed a custom solution for tracking and maintaining records of truck use mileages.  The company has multiple locations scattered within a 200 mile radius or so, and crossing state lines.  The project started out specifically as the result of an increasing difficulty in getting the individual logs and information back into the corporate headquarters in a timely manner to create the reporting required by the governing agencies.  The existing process required significant time to assemble, organize and prepare reports from the existing logs (…which more often than not were not legible!).  In fact, the person whose task it was to prepare the reports figured they spent somewhere between 8-12 hours preparing the reports each month.

From my developer perspective this was an excellent demonstration of need which could easily be developed.  The goals desired from the program were to establish:

• Timely reporting
• Increased accuracy
• Accessibility
• Painless Operation (read that as EASY) to use

To accomplish these goals a multi-user database application was created to collect log information on a daily basis — a key element of which was an EXTREMELY simplistic user interface for entering the log information by employees without typing skills.  This was accomplished by extensive use of selection lists, calendar date pickers, combo forms and checkboxes - mostly it was odometer readings to be entered with the keyboard.  (i.e. Start/Stop readings).  Validation was built into the program to check the “reasonableness” of the entries.  (i.e. an entry showing a truck doing 1000 miles in a day would not be accepted!).

As (I believe) with any application, the “real” value of the application has come with the variety of reporting now immediately available.  The system is “real time” so a quick report run to the screen any time during the month can easily show “missing” or errant entries which can be followed up on while memories are still fresh. 

As for the “value” of the application (…which by the way has been running for about 5 years now!),  it’s difficult to quantify the accuracy gains, but certainly there is much more confidence in the current data (which looks quite different than previous data, probably because of inaccuracies with previous methods).  As for the time spent preparing reporting - a savings of hours per month has added up over 5 years!  Value of information availability again is hard to quantify, however as a sidebar, using the log has enabled better tracking of preventive maintainance for vehicles — certainly a benefit!

I love hearing reports like this where the value of an application shows such clear value!  (Especially when it’s a piece of my work!)

Software Specifications

It’s been a while since I’ve had to work with a detailed application specification, for which I am greatly thankful!  Generally speaking, my “attitude” towards such specifications has not been one of grateful acceptance as I’ve seen too often how they become so rigid as to “get in the way” of being a truly useful means of communicating the “real” needs of the application to be developed.

This morning Bob Lewis of IT Catalysts once again caught my attention in his newsletter “Keep the joint Running“.  I’ve often mentioned his writing in this blog, and once again I am moved to comment here.  It was a simple paragraph within his writing that prompted me to immediately say “Yes!”, “Right On!”

He stated that “When developing software, or when designing business change even more, adherence to specifications isn’t the goal.  It can’t be, because each set of specifications is used only once, is open to interpretation besides, and usually turns out to be the result of incomplete thinking...”. (Italics added).  Bob’s statement quoted here is exactly the reason why I have such an “attitude” regarding most specifications.

My very first post in this blog “Please Hear What I ‘Really’ Need” was actually my way of stating what Bob says above.  My post is very much the story of a project gone wrong with the “incomplete thinking” of which Bob mentions.  Creating custom applications, specifying custom applications and designing custom applications is NO TRIVIAL TASK!  Don’t try this at home!

Reporting, Reporting, Reporting

My subject for this post - Reporting! (Perhaps you guessed! )

A few days ago I posted about “Green IT” and reporting methods and considerations to utilize less “paper”.  My post today regarding reporting is simply this — I posit that it is reporting which provides the greatest value of any business IT system.  Given this position I further venture to say that in this economic climate there will be an ever increasing demand for reporting which previously was neither desired, requested or dreamed of!  Just within my limited client base I have seen an increased interest in looking at new ways to report the wealth of information contained within client systems.

In addition, I have also seen that in some cases to get the information now being asked for will require additional input - (being of course that if data isn’t available it cannot be reported ).  This presents an interesting mix of work, application maintenance and report building - just what the doctor ordered for an otherwise “slow” time.

Through my years in IT and application development I have experienced many times the frustration of users regarding the presentation of their “data” in meaningful ways, or worse yet, their frustration knowing that data useful to them is simply “inaccessible” - “locked up” within the database somewhere.  During times such as these management becomes more interested in the “finer points” of their operations - “What does this cost us? … How about that? …Can we look at …?”.  During the busier times there seems to be a fear of having “too much” information, and surely “information overload” is easy to accomplish!  But in these tight times the value of specific pin-pointed reporting cannot be overlooked. 

Perhaps this is an appropriate time to be looking at what we can do in reporting to help our companies to succeed during this economic downturn.  Wherever possible it seems appropriate that we application developers “suggest” reporting that will add value.  Heck, it might also mean some job security

This probably isn’t the time, however, to suggest starting on implementing a new BI system, although wouldn’t that be nice?

Lessons to be Learned

This morning I overheard GMA making reference to the case of Julie Amero which was the subject of my blog in December titled “Unsavory Justice - Julie Amero vs Connecticut“.  The ABC News story “Wrong Computer Click Ruined My Life” doesn’t present any new facts in the case, but does present the case before perhaps a much different audience than it might have received previously.  The aforementioned links provide the background which I will not expand upon.

However, what I do want to suggest is that there are lessons to be learned from this:

• The need for training (…over and over again I see training as the number one need!)  Those of us who are computer literate make many false assumptions regarding users — we need to rid ourselves of those assumptions.  I’ve always told users that I was training that “You’re trainable”, but that doesn’t mean they’re trained!  The training MUST be on-going.
• The IT department perhaps was not well enough trained either – OR – the proper precautions that would have prevented the site accesses would have been in place.  (NOTE that this does make the assumption that IF they knew what to do they would have done it - which assumption could be erroneous since there is a cost associated with doing the “right” thing!).
• Leaving a system unattended can lead to mischief or mishaps - whether it’s a school classroom or a business environment, whether it’s a “dumb” use, or an intentional misuse.  If you leave a computer that you’re responsible for, log off!

I’m sure that there are other lessons to gain from this as well, and certainly a variety of actions that can be taken.  I just couldn’t let this sit today, even though I’ve blogged on it before!  The case is unsettling to me!

Application Design for Green Reporting

Perhaps the phrase “Green IT” and various variations of same is over-used, however I suspect that its prevalence does cause us to think “green”, even if we’re sick of doing so.  When I chose “Application Design for Green Reporting” perhaps you wondered why I might have chosen such a title.  Good question

Seriously though, my reasoning is simple - and that is my belief that we as developers can and should do everything we can to ensure an appropriate use of resources - and in those resources I would include not only the obvious paper, but also disk space  (since inefficient disk usage may result in the need for added disks, and thus more energy used). 

Saving paper is certainly the easiest way to design for green, and we as developers have the opportunity to affect paper usage in a dramatic way - those steps may include:

• Offering on-screen views in lieu of paper
• Offering ability to print to a document such as PDF
• Designing reports using minimal header and footer margins
• Working with users to ensure ALL data printed is used
• Working with different font sizes to reduce paper needs
• Reviewing sub-total lines, are they used or just there because…?
• Reviewing page breaks - Are ALL page breaks required?

Additional Notes:

• Printing to PDF documents is great, but … here is an area where disk usage should be considered and an appropriate way to maintain (delete) out-dated or no longer needed “printouts”
• Margins in general require review with a critical eye.  Header and footer areas should contain only information useful to the users.  Use one liners wherever feasible.
• If a report will be going only to a laser printer, pages required for printing a report can sometimes be significantly reduced by going from two-lines per record to a single line per record - all with the same information only by changing to landscape mode
• A proper mix of font sizes can also significantly reduce paper use, while at the same time ooften producing a much more readable report!
• I have found many an opportunity to eliminate sub-total lines which has resulted in great paper savings
• Page break review might be a situation such as providing an alternative to printing the report with a page break at each customer change - or not.  Providing an option rather than forcing the page break can make a hugh difference.  Make the default to the “green” choice of no page-break.

How many pages have you saved today?

2009 Delivered — Now What?

Here we are at the start of a New Year and by all accounts and most crystal balls we can look forward to another year of challenges. Actually though, it may not be a year much different than any other in that there are always challenges with IT technology.  We have much to look forward to — I believe that current economic conditions will accelerate the identification and implementation of systems and processes that will allow us in IT to become more efficient, and provide our users with more effective applications with which to work.  

Earlier this morning I read “A CIO’s Five Rules for Managing Through Tough Times”posted on CIO Insight.  I believe that each of the points made in that brief post are pretty much what I would consider to be generally sound management principles — regardless of the business area.  There has been much written during hard economic times about the risks of not planning for the future, as well as the advantage which can be gained through maintaining some level of work toward the time when the economy improves.  His first point “Maintain focus on key projects that will yield long-term business advantage” is, in my opinion, somewhat of a no-brainer, although I have seen over-and-over again the “long-term business advantage” projects either shelved indefinitely or canceled.  I believe that unless the company started the project for all the wrong reasons that this is a grave mistake.

Particulary in the area of custom applications, timing is everything.  This is a good time!