Creating database-driven web applications

Getting fast answers to tough business questions need not involve a time-consuming, costly IT project.

By David Ricketts

Where do you go to get fast answers to tough business questions? If your organisation is looking to make efficiencies and to maximise the return it gets on its investments – and what organisation isn’t? – managers will need to know how they can ‘sweat’ the company’s assets, making existing resources work harder and go further. Finding answers, meanwhile, depends on the ability of managers to analyse and report on the performance of business operations.

In doing so, they gain insights into performance against targets and key performance indicators (KPIs) that not only support effective internal management, but can also be communicated to customers and partners. It is not just backward reporting on past achievements that is required. Companies also want ‘forward reporting’, to help with business planning for the short and medium term.

Mine field

This is where data mining comes in – extracting raw data and making sense of it in order to find out why events have unfolded in the way they have and how company resources should be aligned to meet future targets. In some companies, key business information is found in data ‘silos’ scattered across the organisation, from customer relationship management (CRM) and other transactional systems to content management systems and intranets. In others, the most critical information is housed in one or two core business systems. In either case, it’s all about weeding out the specific information that managers really need to know in order to make good business decisions.

Where the data mine consists of a small number of systems – usually structured information sources, such as relational databases – the real question is this: How does the business persuade its IT team to perform the heavy lifting, extraction and presentation of data in a way that will be meaningful and valuable to management and other end users? This is a pressing issue for most organisations, but particular in a number of specialised companies with very specific business intelligence (BI) requirements operating in such sectors as logistics, warehousing and retail.

Companies in these industries typically have a known number of key assets (for example, goods, shipping containers, trucks) of which they must keep track. In such companies, data about these assets is kept in a relational database and the company’s management and/or customers need instant, up-to-date access to in-depth reports on that data – and they are not prepared to wait. To meet the short timescales of these reporting challenges, and the range of technical experience and query-framing abilities of all possible users, the only real option is to put a web-based GUI [graphical user interface] and reporting tools on the front end of the database, which can handle specific queries and present the results in the desired format.

Driving web apps

This means using the relational database as the driver for a form-based web application. The use of a web front end makes data access and sharing, whether locally or remotely, easy to deliver. But how do you approach creating a web reporting front end? Should it be done from scratch by bespoke development, or by using a rapid application development (RAD) tool that can short-cut the process?

Many companies are finding that use of these tools can cut development times and costs – if the right tool is chosen. Here’s a checklist of attributes that a successful development tool should have.

Dynamic data access

The tool you choose should enable you to access data from almost any resource. This enables developers to take any existing database as the starting point for front-end development. Look for built-in modules to enable access to widely used databases, such as Microsoft SQL Server, Oracle, Sybase, Informix and others.

Reporting

Does the development tool feature wizards and built-in report objects and templates to help you create graphical data and charts in a range of formats, including HTML? This should be considered essential when choosing the tool, as for these types of applications, customising reports according to users’ needs is almost as important as database access.

SQL set-up

In many cases, you’ll be using the SQL database query language to interrogate the database. So the development tool should include pre-formatted SQL requests, enabling you to get up and running with manipulating data fast, even if you have only a working knowledge of SQL.

Debugging

You need to ensure the tool you choose lets you test your work as you proceed with the development – so look for a set of debugging functions in the solution. Ideally, it will enable you to switch between design and runtime modes to give fast and easy checks.

Delivery matters

This real-life example is a data mining and SQL database-driven web application deployment for a UK logistics and supply-chain specialist. The company has a network of 30 depots in the UK with a fleet of 500 vehicles and its services to customers range from handling home deliveries to providing dedicated and shared warehousing.

In its role as the logistics supplier to a well-known cake and confectionery maker, the logistics company has to schedule and make hundreds of deliveries daily, all within allotted time periods specified by supermarkets, to comply with local delivery and access restrictions.

Furthermore, as part of its overall service to customers, the company needs to demonstrate that it has met its contractual key performance indicators (KPIs) and targets agreed with that customer, and be able to identify the reasons for any departure from the planned schedules, whether days, weeks or months past.

These are challenging demands. It means having accurate, in-depth reports of time records for movements of all trailers in use from their respective depots and loading points, and time stamps for arrivals and departures from supermarkets. The logistics company needs this data to be presented to managers and customers in an easy-to-understand reporting format, using visuals, such as graphs and charts.

As well as providing evidence of delivery and timekeeping to customers, the company also wanted managers to be able to quickly ascertain the location and availability of all its trailers. That would help them to respond quickly to new instructions and to determine the overall utilisation of its trailer fleet across the available trailer types – which vary from full-size articulated trailers and multiple-deck trailers to box vans, according to the size of the cargo and physical delivery restrictions.

That kind of visibility into operations, the logistics company felt, would enable it to make the most of its assets and to avoid unnecessary charges for new trailer rentals where inactive because compatible trailers sitting idle could be identified and brought into use quickly.

Trailer tracking via GPS

To help it meet this challenge, the logistics company needed to devise a method for gathering the evidence of movements of trailers and times of loading and delivery. For this, it deployed a trailer tracking system based on GPS [global positioning system] technology for the 140-plus branded trailer units it operates on behalf of the cake and confectionery company.

The commercial trailer tracking system takes information from a GPS transmitter installed on each trailer, which can even signal when the trailer doors open or close (signalling loading or off-loading), and when the trailer is coupled and uncoupled from the driver’s cab (indicating whether that trailer is in use or inactive).

The transmitter also sends a signal when the trailer arrives at and departs a specified ‘geofence’ – a virtual radio-frequency perimeter around a supermarket or depot. When the trailer enters or exits the geofence, the GPS transmitter is triggered, sending a signal that enables accurate movement timings.

Each GPS tracker sends a unique, identifiable GPRS mobile signal which is received at the haulage company’s Midlands headquarters. The signal is then converted and fed into a bespoke central trailer-tracking database created using Microsoft SQL Server technology and running on Hewlett-Packard servers.

Piecing together the BI pie

Although the trailer tracking system was effective in reporting trailer movements, location and utilisation; the key issue faced by the company was the ability to mine the SQL data from the database and to produce detailed reports on movements and deliveries, both for performance reporting to the customer and for its own trailer fleet management purposes.

A further issue was taking planning and scheduling data from the logistics company’s planning system, a bespoke application created using Microsoft Access, so that actual delivery times could be compared with plans in order to gauge performance. The customer had very specific reporting requirements and needs for presentation in a format which made the information easy to assess. The chosen method of presentation was via a web front-end, making reports accessible to all authorised personnel (both internal and external) on any browser-equipped PC. This would enable users to simply point their browser at the reporting web server and, by entering a user name and password, gain access to reports.

At this point, the logistics company approached RETP, its long-term IT services partner, to come up with a solution to its data dilemma. RETP worked with business intelligence software specialist Ardentia, with whom it has collaborated on a number of similar BI projects, to devise a reporting system to meet the specified requirements. To enable data mining from the GPS database, Ardentia used its rapid application development product NetForms, which can query and access structured data and deliver the required sets to a web front-end for easy access and reporting using a range of pre-built components, ‘applets’ – small, Java-based applications that can run in web browsers – and client-side scripting.

The first step in the process was to integrate the logistics company’s planning system into the SQL-based tracking database, to enable data to be compared from the same source. In this case, planning data from the Microsoft Access database is output to a standard CSV file, which is then easily inserted into the tracking database.

Location, location, location

However, compiling reports from the GPS data in the SQL database presented a further challenge. Imperfect network coverage in the UK, or poor reception in a given area, can cut the ‘real-time’ nature of reporting to ‘semi-real-time’, leading to gaps in data and making accurate time-stamps difficult to identify in many cases.

For example, if a trailer is in an area of poor mobile network reception, no signal may be received from it for 30 minutes or more. However, if the signal reception is good, the tracker may transmit several times during a 30 minute period. In which case, which time-stamp should be taken as the accurate one for a delivery or location confirmation?

The initial approach taken was to enable users of the tracking report application to pick a date and time (which would default to the current date and time) and the reporting tool would scan an interval of plus or minus 15 minutes each side of the chosen time to get the GPS location. However, when it became apparent that the 30-minute range was sometimes too inflexible due to network coverage or communication issues, a second date/time picker field was added. This new field defaulted to picking half of a working day of GPS data. From this data interval, the end user could choose the narrower ‘time slice’ that they wished to focus on.

This overcame the issue of intermittent network coverage and had the added benefit of providing a useful analytical tool for investigating individual or multiple trailer movements during a given period. An example of the ‘date picker’ report screen is shown in figure one. Here, the user selects the date and time interval they require and can then choose a range of pre-defined report options.

Internal affairs

For the company’s internal purposes, such as investigating which trailers are inactive over a period, the tool can be used to see periods of inactivity during a given day or number of days. Alternatively, in the event of needing to deploy a trailer quickly for an immediate operational requirement, it can identify the trailers that are inactive at the required time and at which location, for the fastest and most efficient possible usage. An example of this type of web report is show in figure two, with a chart giving a quick overview of trailer utilisation by type.

The system can also be used to schedule tracker unit maintenance, such as replacing tracker unit batteries – an important consideration as the tracker units deliver the data which is critical to the overall logistics operation. Any unit which has a battery failure is effectively lost to the tracking system, which in turn means any excursions from planned routes and deliveries could incur penalties – cutting efficiency and risking the customer relationship.

The logistics company is able to use NetForms to deliver the required reports to its customer both online and printed, for current periods and covering historic trending. It reports that the system has been well received by external customers and internal managers alike. So when business demands quick answers from core resources, database-driven web applications can deliver.

David Ricketts is a IT consultant and director of RETP Solutions. He can be contacted at david.ricketts@retp.net