Cubes for reporting, good in theory says Report Factory, but……
Multi-dimensional analysis cubes (OLAP) have been around for a while now. They offer high speed analysis of lots of data, and present simple summaries and analytics in complex environments.
So what’s not to love ? Quite a bit as it turns out.
Good tools have their place, but sawing a plank with a chisel perhaps isn’t the ideal. Similarly day to day reporting from cubes has limitations the vendors don’t talk about much. You probably need to know a few key things.
Firstly OLAP (Online analytical processing) cubes are summary data typically calculated in an “update” process. That means the data is not real time. In some cases it’s near real time (like an hour ago, or last night) but it isn’t real time. A lot of the time that’s no big deal, but sometimes it is (like running incremental month end adjustment journals and needing to review the results each time).
It’s worth understanding that OLAP technology developed at a time when compute power was expensive and slow relative to today. It was also a time when live queries on big datasets were frowned upon and far from efficient. Today a lot has changed. Databases are much bigger and much faster. Mass real time OLAP queries are now possible, fast and flexible.
Another issue is that these objects have a definition, that is the things that you’re measuring (measures, like fees billed, write offs, hour production etc.) and dimensions (clients, offices, departments, employees, responsible partner etc.). These measures and dimensions are structural elements (think structure of a house) and changing them is not the work of a moment. Technically the changes are not hard, but the cube typically needs to be rebuilt (re-calculated) after such changes and that usually means for ALL HISTORY. As I said, not the work of a moment if your cube summarises the last 10 financial years.
These are the big ones, but there is quite a bit more to be aware of.
Summary data, by its nature, removes detail and in the case of cube based reporting hides it altogether (ok some hybrid methods exist to get around this, but it’s limited). I’ve previously referred to lies trapped in summary data , or losses hidden inside profits (or more importantly profits hidden in losses !). If we see the summary values, dissected by dimensions, we tend to “believe” they are TRUE. And of course they are true, but true in the way that politicians statements are true, it depends how you ask the question. OK, so it’s still better than a spreadsheet where a single cell formula error can slip through and produce absolute rubbish, because OLAP cubes are built on rules, but the rules make assumptions about the data and assumptions (especially hidden ones) are not the friends of accurate analysis.
Another slight issue is that of missing dimension data (nulls for the technically minded). These can really play havoc with summary analysis if you’re not careful. Most reporting designers are aware of and highlight ‘removed null values’ but I’ve seen plenty of cases where that wasn’t true, and the resulting summary data (which was less than 10% of all the data) was seen as an accurate summary by the reader.
Increasingly OLAP cubes included references and dimensionality form multiple sources (CRM (Client relationship Management), ERP (Enterprise Resource Planning / Practice Management), HRIS (Human Resource Information Systems)). The propensity for “null” or missing values increases exponentially here, especially when dimensionality comes from more than one source and changes over time.
In some cases the cubes may include dimension level security (e..g. you can only see the departments you’re authorised to see). No issue really, except if the presentation leads you to believe you’re looking at the summary for the whole firm, because you’ve selected “all” but the security removes data in the background.
So am I saying Cubes are pointless ? No, but I am saying they’re “not the Messiah”.
Modern databases are fast and deal with massive quantities of data quickly and efficiently. Similarly modern reporting tools are capable of real time analysis of lots of data. These tools are often (but not always) a better solution as they are almost as fast, way more flexible for changes and far less susceptible (though still) to the summary data conundrum. Of course bad design can still produce inaccurate results, but it’s less likely to go through to the keeper without a catch.
As in every part of life, every situation is different and different sets of tools will be the right answer for different firms. There certainly is no perfect recipe for reporting that suits all firms. Recently we’ve been investigating some real time dashboard style reporting tools. Turns out there are a few around and some are reasonably priced, fast and do a good job on the presentation side of things. Of course these too have limitations, but they present an attractive option for medium sized fries who don’t want a monolithic BI development and yet need to the richness that an analysis dashboard or dashboards can bring, together with actual real time data.
If you do invest in and use OLAP type solutions for reporting, be sure you have some real time, transactionally accumulated reports you can run at any time for real time results and cross verification of the apparent truth presented in the OLAP summary data.
Further, and I know this seems obvious, but from what I’ve seen it’s obviously not happening, make sure you train the users (at all levels) how to use whatever reporting solutions you do present from time to time, and do be sure to warn them of the various perils which can befall the blind faith approach often taken when reading summary results.
