Case Study - Management Reporting

The Situation

This client recycles and remanufactures inkjet and toner cartridges, often on behalf of charities,m whereby the user registers to support a particular charity, and then send cartridges in using a pre-paid envelope.

Each cartridge had to be processed according to its condition, the degree of demand for it as a remanufactured item, and also in respect of the royalties due to the selected charity.

This involved complex reporting requirements, most of which were being handled manually using spreadsheets.  If a specific report was required, queries would be run against data, and then manually fed into MS Excel pivot tables.  This was a time-consuming and, as business levels increased, unsustainable situation.

Our Approach

It was evident that a data warehouse was required, to act as a repository for the raw data.  This was to be populated from other business systems via an automated process, so that on any goven daya full details of transactions to the close of the previous business day were available.

We then needed a tool to enable the MS Excel pivot tables to be replaced, or generated much more quickly, via an automated process, so it was necessary to develop a reporting system on top of the data warehouse.

The Solution

We selected an open source business intelligence tool called Pentaho.  This enabled us to create so-called ‘cubes’ – a common and powerful data mining methodology enabling multi-dimensional analysis beyond the normal columns and rows:

In this example, you can see Hat sales (values are called measures in this scenarion) represented in a standard grid, with locations down the left, and periods across the top.  With a cube, however, you can also have additional ‘dimensions’ – in this case the same information for (say) Coat and Shoe sales.

The user interface for this data structure provides a simple way of selecting the dimensions and measures you wish to view, so in effect it is a single report with multiple outputs, depending on the user choices.  You can also drill down to see how sales numbers or quantities are built up, should you wish:

Pentaho User Interface

The Outcome

This solution delivered significant time-savings in terms of generating both regular statements for specific charities or customers, as well as the creation of ad hoc reports.

There is no need for users to request information – they can access this system through their own web browser and obtain whatever they need.  There is improved accuracy of information, and customer response times.

The client feedback on this project was: “the difference in our ability to service our customers is night and day.”