Virtual OLAP slicing and dicing

Ever since Dr Ted Codd, the inventor of the relational database management system, defined the characteristics of on-line analytical processing (OLAP) in a 1993 white paper, OLAP has been the de facto term for multi-dimensional data analysis software.

He chose that term carefully: the IBM research scientist wanted it to resonate with on-line transaction processing (OLTP), the description for the operational side of business systems, while standing apart as the clearly analytical side of the data processing coin that supports decision-making.

That separation of transaction processing data and decision-making data has never gone away. Almost all analysis today is conducted on historical snapshots of business data rather than on live, operational data. And for some very good reasons.

System performance, certainly until recently, has been incapable of coping with the simultaneous demands of OLTP and most types of analysis. At the same time, OLAP by its very nature involves the extraction of data and summary of that data, its invention a response to the weakness of the relational databases in handling multi-dimensional analysis.

The downside, though, is obvious: by the time data is extracted from operational systems and then built into summary ‘cubes’ within an OLAP database, it is often out of date and held in a relatively inflexible structure.

Now a group of business intelligence (BI) companies and influential analysts and consultants are arguing that the separation of operational and analytical is not just artificial, but dangerous. In an era when competitive advantage can be gained or lost overnight and when regulatory pressures are intense, it is important that decisions are based on accurate, trusted information – and that means current data.

Their suggestion is not that all business intelligence should be applied directly against operational data, but in plenty of situations – especially in financial departments – there is a clear and pressing requirement. And in such cases the shortcomings of traditional OLAP are very evident.

Unfit for purpose

“Without wanting to denigrate [traditional BI tools] it cannot be denied that they possess certain characteristics that make them unsuitable for the more controlled environments [such as] providing accurate financial information,” says Mike Thompson, an analyst at IT sector watcher, the Butler Group.

Others have fewer problems identifying the weaknesses of current BI offerings.

“The problem with traditional OLAP is that you have to set where you are going to go with the data right at the beginning,” says Nick Gomersall, senior VP of sales and marketing at The GL Company, a pioneer of the real-time BI movement. “Users need to say how many dimensions – company, division, area, and so on – they want to measure. But later when they want to add a seventh or eighth dimension, IT needs to redevelop the OLAP cube, redevelop all the links, change the data extraction and transformation mechanism to get that new information into the OLAP database.”

The GL Company has built a real-time enquiry and consolidation tool, to date targeted at finance, that uses a ‘virtual OLAP' engine to provide greater control over management reporting processes. “We have turned the [operational] database into an OLAP database and this provides customers with real-time access to summary management information, with full drill down to the transactions and without having the need to transfer the data into another database,” says Richard Crotty, North America business development manager at GL.

This revolutionary approach has impressed some big names: even with its initial limited targeting of Oracle’s JD Edwards enterprise resource planning (ERP) applications, the company has built up over 200 customers, including Johnson & Johnson, Electrolux and Fosters. Oracle’s own E-Business Applications users are the next target ERP base.

Building links directly into the ERP systems also removes the ceiling on the number of dimensions that traditional OLAP can practically deal with. Indeed, with most OLAP products, users talk of more than 12 dimensions being unmanageable.

“What virtual OLAP is giving you is the ability to build cubes on the fly over the total amount of dimensions that are available to you in the ERP system,” says Gomersall. In the case of JD Edwards, that is 2,069 tables.

Similar claims have made over the years by vendors of relational OLAP (ROLAP) products. But the difference here is that there is no extraction.

“Virtual OLAP is similar to a ROLAP tool in that we are working from relational, transactional data and building things on the fly, but the thing with ROLAP is that you actually have to copy the transactions into relational tables or data marts,” says Gomersall.

One-sided entry

The problem of analysing second-hand data is most obvious in one very common set of business circumstances: the financial closing period.

“How do you close your books in the last few days of the month when a journal entry takes 24 hours to appear [in the financial summary],” says Gomersall. “Accountants hate to post a journal entry and then not see it in the P&L account straight away.”

In the absence of that visibility, many financial controllers keep a separate Excel spreadsheet of new entries towards the end of the month or quarter, just to know where the company stands. “Because they can’t get that visibility, they extract it to Excel and start doing one-sided journal entries. After numerous changes they need to start working off the spreadsheet and they daren’t go back to the general ledger because they have made all these changes in the spreadsheet for which there aren’t journal entries.”

Serious charge

Analysts such as Butler’s Thompson are not impressed: “The use of [traditional] BI tools on top of ERP systems has created a situation which calls into question the validity of financial information presented at period ends. The checks and controls inherent within accounting systems (or accounting modules within later ERP systems) are being bypassed and this is leading to incorrect and non-valid data being used for period-end reporting.” That is a serious charge that could have profound implications for those who lose control of the consolidation process.

Access to live data has another important implication: users can drill down into actual transactions, says Paul Yarwood, general manager of GL’s US operations. The OLAP-created reports that are typically sent out to end users typically generate lots of follow-up questions as users try to figure what is behind individual figures or why there is a variance between the situation as they know it and the figures. “With real-time software they can just double click and see the transaction themselves immediately,” he says.

GL is not the only company attacking such requirements for zero-latency reporting. Mainstream ROLAP specialist MicroStrategy offers a product that provides “real-time data through direct access to OLTP systems”, and Germany-based start-up InstantOLAP makes claims echoing those of GL, of real-time BI, unlimited dimensions and virtual warehousing. But the pursuit is the same: addressing users’ needs for speed and accuracy.

When the legitimacy of an organisation’s financials are being called into question, those are not insignificant needs.

Pete Swabey

Pete Swabey

Pete was Editor of Information Age and head of technology research for Vitesse Media (now Bonhill Group plc) from 2005 to 2013, before moving on to be Senior Editor and then Editorial Director at The...

Related Topics