Real-World Business Intelligence

The RAC has been through many changes in recent years, having been acquired by Lex Service in 1999, and then insurance giant Aviva in March this year, but behind the scenes the biggest change has been the introduction of a People P&L strategy to better understand its employees.

Based on the finding of the Department of Trade and Industry's Accounting for People Task Force, the People P&L strategy was designed to measure employees as a tangible resource on the balance sheet and accurately measure employee performance.

As Graham Pritchard, human resources data warehouse manager, explains, there was also a requirement to bring together disparate HR systems and identify potential problems with the RAC's workforce, which serves as its first point of contact with customers via roadside assistance or phone-based services.

"In 2000, we introduced a People P&L strategy because we had different HR payroll systems and we thought we had problems with absenteeism and turnover, but we couldn't trace it," he says. A balanced scorecard approach was implemented with key metrics including stability, retention, and colleague satisfaction, as well as absenteeism and turnover.

Introduced via a pull strategy that encouraged line managers to see the potential benefits of understanding the value of this data, the new initiative was to some extent a victim of its own success, as by 2003 the company had between 300 and 400 business units producing 10 reports a month.

The manual report process was also causing data migration and input problems, meaning that there were questions over data credibility, while the fact that all the reports were being run off transactional HR and payroll systems, led to systems performance problems.

"By the end of 2003 the HR board was pleased by the take-up but concerned by the overheads and the demand on HR administrators," says Pritchard. "We were asked to automate the HR process.

"I think the sticking-plaster solution would have done, but we weren't happy it would have delivered long-term return on investment. We decided we wanted something to report, with the increased ability to benchmark."

The answer was to implement a data warehouse, and the RAC turned to the specialist Strata HR Analyser solution from Strata Systems, which makes use of Cognos Impromptu, PowerPlay, and Metric Manager software and was built to match the conclusions of the Accounting for People Task Force.

With board approval granted in August 2004, the system was installed in November of last year and piloted in January 2005 after some customisation, before going live in February, with immediate business benefits.

"There is effectively now one truth," says Pritchard. "The quality of the data has got better. In the warehouse it's easier to see if you've got a dirty data issue." Extracting the data from live systems into a data warehouse has also improved transactional system performance, while automating the generation of reports via Cognos ReportNet has produced an estimated saving of £50,000 ($88,000) a year in man days.

The RAC is also now better able to analyse the data it is producing for more meaningful business results. "Fundamentally it gives us a key to understanding our people by cutting the data to understand what's driving those figures," he says.

The company is also looking to cut voluntary turnover to produce savings of £500,000, and Pritchard states that it is already down by 0.5%, which equates to 80 people, or £350,000 a year. "When we come back to the benefit of having this system, there is the potential of £1m or £1.5m a year, at a cost of around £150,000 a year," he adds.

While the RAC's business intelligence was focused on cutting down employee turnover, for Caudwell Communications the requirement was to capture and retain more customers. Entering the UK fixed-line telephony business in 2000, Caudwell knew that it had to differentiate itself from a multitude of incumbent and emerging suppliers.

"The original motivation was the problem with the tariffs," says data architect Graham Mossman. "In order to distinguish ourselves we needed some pretty smart pricing. Telephony pricing is quite an art, and we needed to be able to come up with tariffs that undercut the competition while still making us money."

Like the RAC, Caudwell found that running reports against its business systems was causing performance problems, and a decision was taken to implement a data warehouse. The problem was that Caudwell wanted the warehouse up and running within four months, and its start-up level budget meant it could not simply throw money at the problem.

The answer emerged in the form of data warehouse appliance specialist Netezza, and its Netezza Performance Server system. "It's a nice piece of kit and it solved two problems for us," says Mossman. "We had very imminent problems we wanted to solve but knew that one day we could evolve it into a grown-up data warehouse."

The company has taken a three-phase approach to data ware-housing, started by implementing two NPS 8100 systems in late 2003 to support production queries and reporting using Business Objects reporting tools, as well as back-up and recovery.

The second phase saw the introduction of a Tactical Data Model, which created a common data format for all the Caudwell business systems, hiding the application complexity from the data-user and creating what the company refers to as "Lego bricks" of enterprise data that can be fixed together for individual reports.

This approach enabled the reporting capabilities to be quickly rolled out across the business, with different departments using different Lego bricks for their own reporting needs, while ensuring the consistency of the source data.

"What we've managed to do is to tidy it up so we have a single version of the truth," says Mossman. "The words Lego bricks are something I use a lot: we give people objects they can slot together and then map over that what it means in their language. It allows us this level of abstraction, and the ability to have this data in a format that is different from the source application," he adds.

While the reporting tools are used across the business, one of the key areas of benefit for Caudwell has been in studying customer usage trends, analysing over one billion call data records (CDRs) at an average rate of 1,000 queries a day, enabling it to keep up to date with its customer usage trends.

The warehouse has also enabled the company to respond quickly to competitive threats, such as when a competitor announced a new pricing model. Using Netezza, Caudwell was able to assemble 40 million CDRs and apply hypothetical call pricing models to them to find the most effective rate, introducing its response before the competition had been able to introduce its new pricing.

The project is currently in its third phase, with the implementation of Extract, Transform and Load technology from Sunopsis, and an expansion of the TDM to include data from Caudwell's new Customer Care and Billing system.

While it is early days to put a value on the benefit of the data warehouse to Caudwell, Mossman does not underestimate its value in helping the company get through the precarious start-up phase of its development. "The fact is that the survival of the company was dependent on providing the right price to customers," he says.

The company has not only survived but exceeded the expectations of its creator, Caudwell Group founder, John Caudwell, who announced as CBR went to press that he was putting the fixed-line phone business up for sale to focus on his mobile interests, such as Phones4U. Caudwell Communications is valued at up to £100m.

While the creation of a data warehouse is often a long and drawn-out affair, Caudwell's experience indicates that the sooner a business can create a meaningful report, the sooner it will get business and board-level buy-in. "A lot of the literature seems to be involved with building big data palaces," says Mossman. "A good first step is to get comfortable with the technology and just start running reports."

A similar approach was taken at South East Water, which has implemented an Oracle-based data warehouse using Business Objects' Data Integrator ETL tool and WebIntelligence reporting application.

Consolidated information

"With any programme of work the big mistake people used to make was to develop, and develop, and develop, and then announce a data warehouse was available, but nobody knew what to do with it," says Brain Calcott, South East Water programme manager for information services.

Having purchased the elements to create the data warehouse at the end of 2003, South East Water immediately began its first proof of concept project at the start of 2004, proving to the finance department that it could improve OPEX and CAPEX reporting, and gaining the support of the business.

South East Water's project was driven by a requirement to consolidate information from a variety of best-of-breed applications across the business and meet Ofwat's regulatory demands for increased efficiency.

"We considered application-level integration, but really what we wanted was consolidated information, so we decided to integrate at the information level." While the company already had single-system reporting tools, their use was limited and was becoming a problem.

Calcott adds that by running a proof of concept with the finance department, the use of the tools was quickly applied to other areas of the business where it could provide immediate savings. "If you look at this sort of integrated reporting, the time you can save from automating these reports provides the cost benefit straight away," he says.

"Another key area is the operations department, which manages the supply and distribution of water, monitoring sites and underground pipes for supply levels and flow pressure." Like all water suppliers, South East Water has a telemetry system to keep track of its network of pipes and facilities. It was previously not able to run query and analysis on the state of that network, however, but is now able to run WebIntelligence reports and send them out to staff on site via GPRS.

The company has also been able to make direct savings on electricity costs by analysing the optimal time to be running its pumping stations based on electricity billing costs. "We were looking to reduce between 3% and 5% of the £4m we spend a year, which we've achieved," says Calcott.

Having spent an estimated £400,000 or £500,000 on the data warehouse, South East water is confident that the results are already enabling it to make savings. "We expect to show efficiencies in running a programme that costs £50m a year, so even a 1% improvement is a major return for us," he says. "We've already had our ROI."

Groundwork importance

The water company is now looking to expand its use of the data warehouse technology still further, enabling it to analyse its network to better match supply and demand and mitigate against the increase in dry summers, while there is also a plan to introduce dashboard technology at the turn of the year to give executives a snap-shot view of company performance.

While dashboards are the hot new topic in business intelligence, Calcott is quick to warn potential users that without the groundwork, a dashboard is of limited use. "It's the icing on the cake, a dashboard," he says.

"The dashboard costs about £5,000, but to get the information into the dashboard to make any sense it costs a lot more than that. It's like an iceberg. What you see is just the tip, but there's a lot going on underneath that needs to be in place and verifiable before you can do that."

CBR Opinion

The decision to invest in business intelligence and data warehousing technology is more often than not driven by the particular business imperatives of the company in question. For the RAC there was a need to identify perceived problems with staff absenteeism and turnover; for Caudwell Communications there was a need to differentiate itself from the competition with call tariffs and customer services; while for South East Water the issue was consolidating information from various application sources to establish whether it was meeting key performance regulations. While the projects varied in terms of their business drivers and goals, the benefits that have been accrued are broadly similar: a single version of the truth, consolidated information, improved live system performance, and improved operational efficiency.