Leading out-of-home advertising agency cuts costs and reporting time by 50%
Leading out-of-home advertising agency cuts costs and reporting time by 50%
As part of its IT roadmap to support the expanding and increasingly complex UK operation, JCDecaux UK decided to develop a companywide business intelligence (BI) solution.
Its aim was to ensure that the UK management team had the information it needed to make better informed decisions – at a strategic and operational level. Essential to its success was producing detailed, accurate weekly reports on the UK’s activities: as well as being able to provide detailed, up-to-date information to JCDecaux’s head office in France. This information would enable the UK and French management teams to predict sales, plan ahead and maximise opportunities more effectively.
JCDecaux UK stored its assets and sales data on three separate databases – one for each large format advertising, street furniture and airports - its three key business areas. “All the management and operational information had to be extracted using Business Objects or produced in Excel and Access,” according to Raj Rajkuman, JCDecaux financial systems and BI manager.
“As we had no common information bank to draw on, we had to spend hours gathering information from scattered data sources. Reports were often produced in different formats using different data sources, processes and methods. Over time, we’d seen our users developing their own complex reporting options to provide the data they needed. Reporting had become very inefficient, it took too long and didn’t meet our business needs.”
“We recognised that we needed to integrate all our corporate data into the one solution” recalled Stephan Lavollee, IT manager at JCDecaux UK. “But given the complexity of our business and the sheer volume of data, it was obvious that our existing solutions just weren’t a viable option. We had millions and millions of lines of data, so making any changes was difficult, time-consuming and took too long.”
Evaluating open source
As an existing Oracle customer, JCDecaux’s IT team considered this option for developing the new solution, but discounted it: “It would have been a logical, but expensive choice,” continued Lavollee. “We were already using all our Oracle licenses, so we needed to buy new ones adding considerably to the project’s cost”.
Early in 2009, Lavollee and his team started developing the new BI solution using open source solutions, having identified MySQL and Pentaho’s Kettle as the most suitable database and extract, transform and load (ETL) tools. As well as offering a 50% saving over Oracle, the team were familiar with these solutions, having used them on previous projects.
“Initially, we only loaded new sales data from our new booking system into the solution, commented Lavollee: “During the first weeks, it performed very well, but as we added more data, the load times increased from 20 minutes to an hour and then two hours. At the outset, the reports were excellent; they contained all the information the business needed, but they began taking longer to produce and started to time out. When we then tried to add two years’ legacy data for one business unit, it took 36 hours to load. It simply couldn’t handle the volume of data and produce reports inside an acceptable timeframe.”
Lavollee and his team considered reverting to Oracle, but the cost encouraged them to explore other open source options, which led them to identify Infobright as a possible solution. Designed specifically for BI, its column-orientated database, data compression and optimised load capabilities were particularly suitable for JCDecaux’s high data volume needs.
Keen to find out whether it was as fast and effective as he hoped, Lavollee contacted Infobright who introduced their partner, Open Business Associates (OBA), an open source management consultancy. After an initial discussion, OBA recommended a short pilot project using JCDecaux’s own data. “Several colleagues were not convinced that open source would deliver given how much data we were using,” said Lavollee. “So we deliberately gave OBA only three days to do the pilot project. We wanted to see just how quickly they could load so much data and how rapidly they could produce a useful report.”
Taking data in its stride
During the proof of concept, OBA’s team reviewed all the data which had taken 36 hours to load, identified and addressed a number of issues concerning the data’s layout. They then loaded the 4 million records into Infobright’s solution in less than two minutes from MySQL tables, without applying any business rules. “I was amazed with the results,” recalled Rajkuman. “Queries which had taken more than 15 minutes using MySQL only took a couple of seconds using Infobright. It handled all our data, we could generate reports easily and it was very obvious, even from this short trial, that it was the solution we needed.”
Based on the results of the pilot, OBA began implementing Infobright in December 2009. During the three month project, OBA recommended that JCDecaux use Talend as its ETL tool; partly due to its ability to handle complex data transformation logic and also because the product already had a data connector for Infobright.
“OBA really understood our issues and the problems we were facing. They brought the technical knowledge and flexibility to the project and from day one, explained what they could offer – and then delivered it” said Lavollee. “I’m delighted with the outcome: data loads that used to take two hours are now happening in 20 minutes. We can now produce the essential reports in less than three minutes, when originally we couldn’t produce them at all. Our users are very happy because they have the information they need within the timescales and can meet their deadlines.”
This project is the first phase of JDecaux’s three-year plan to implement a complete BI solution throughout its UK operation. In the coming months, JCDecaux aims to integrate all its historical data into the solution and then plans for a further three data warehouses. Within the next two years, all the UK operation’s departments will benefit from a dedicated BI solution, with the eventual goal of producing companywide information through a single reporting tool.
