The problem: Slow running of retail sales reports
Early this year one of our new retail clients mentioned that they were experiencing problems with the performance of some of their existing sales reports. They’re a major non-food retailer with a large high street shop presence and they’re also one of the UK’s biggest on-line retailers. They’d been considering investments in SAP HANA, Oracle Exalytics and an IBM solution including Netezza, but the sheer scale of investment meant it was proving very difficult to progress. So we started looking for some alternatives for them. The criteria were:
- At least an order of magnitude faster than the current solution, to address the immediate issues.
- Preferably provided as an on-premise analytics appliance to avoid the procurement and delivery complexities of multiple different third party service providers of hardware, database, application…
- A SAP HANA proof of concept had shown that it offered an acceleration of the order of 10,000 times, which offered business transformative options for new ways of doing business, but a 1,000 times acceleration would be similarly business transformative. So the challenge became: could a 1,000 times acceleration in query results be achieved at about a tenth of the cost of implementing SAP HANA?
The solution: 900-times acceleration through inexpensive in-memory analytics
We put together a solution based on Informix Warehouse Accelerator (IWA), a database platform designed for fast responses to complex, ad-hoc queries – in other words, for analytics and data warehouse queries. It brings together a number of current technologies (in-memory columnar database, huge parallelism via a co-ordinator / multiple worker node architecture, multi-core optimisation…) at an attractive price point.
We worked with one of our longstanding technology partners, to carry out some performance benchmarks on a representative data-set based on the TPC-H standard. The tests were performed on relatively modest IBM hardware (for the more technically minded, 2*E7520 QC nodes running at 1.86 GHz, each with 512 GB of memory) against a data set of around 600 million line items; the data model structure and the number of records in various elements of it are shown in these diagrams. Eight different query types were run; the queries selected were typical business queries for a retail environment, for example the amount of business that had been shipped, billed and returned in various given periods; the top “x” unshipped orders with the highest value; forecasting queries such as the potential revenue increase if certain discounts had been eliminated, and so on.
We thought the results were impressive, and so did the client: using IWA on this relatively inexpensive hardware for in-memory analytics we were able to produce query acceleration of around 900 times what could be achieved using a disk-based solution. We haven’t shared all the details here but if you want to know more please contact us, we’d be happy to share more with you. We think this type of solution offers real potential for businesses who aren’t getting the speed they need from their current data warehouse but can’t afford to look at some of the enterprise-scale solutions such as SAP HANA, and we’d be happy to speak with you about helping you construct your internal business case and demonstrating this type of speed with your own data.
