Blog: Neil Raden Subscribe to this blog's RSS feed!

Neil Raden

I hope that you will engage with me with your comments as we explore the future of business intelligence (BI), particularly its expanding role in the actual process of making decisions and running an organization. BI is poised for a great leap forward, but that will leave a lot of people and solutions behind so expect a bumpy ride. I also expect there will be a flurry of advice and methodologies for moving BI into a more active role, one that will widen the audience as BI meets more needs. But a lot of that advice will be thin and gratuitous, so hold on while we put it under the microscope. You can reach me directly if you prefer at nraden@hiredbrains.com.

About the author >

Neil Raden is an "industry influencer" – followed by technology providers, consultants and even industry analysts. His skill at devising information assets and decision services from mountains of data is the result of thirty years of intensive work. He is the founder of Hired Brains, a provider of consulting and implementation services in business intelligence and analytics to many Global 2000 companies. He began his career as a casualty actuary with AIG in New York before moving into predictive modeling services, software engineering and consulting, with experience in delivering environments for decision making in fields as diverse as health care to nuclear waste management to cosmetics marketing and many others in between. He is the co-author of the book Smart (Enough) Systems and is widely published in magazines and online media. He can be reached at nraden@hiredbrains.com.


It's pretty clear that the term "OLAP" is losing its mojo. In a series of exchanges on Twitter, it was suggested that pivoting is the same as OLAP. I don't agree. Pivot tables were first introduced by Microsoft in Excel over ten years ago (I'm sure someone else used the term prior to that, probably that guy from IBM who invented Business Intelligence in the fifties) J. Other spreadsheet vendors have followed suit. Oracle introduced PIVOT to their non-standard brand of SQL, SQL*Plus, too, but more on that in a minute.

 

Essentially, a pivot in a spreadsheet is a little more than an a crosstab - transposing columns and rows, with some smarts added in to figure out the unique elements of each identifying "dimension" and performing some rudimentary calculations such as aggregation. It is essentially a linked report, in that changes to the original data are reflected in the pivot table, and this can go both ways.

 

OLAP, on the other hand, is quite a bit different. It includes:

 

Multidimensional model at its core: dimensions, hierarchies and attributes

Pivot: the ability to rearrange the display of same without code or script

Drill: at least to drill into detail and drill back up if not across dimensions

Cross-dimensional calculations

Filters

Collapsible browsing

Flexible definitions of time

Sparse matrix

Read &WRITE, though many OLAP tools cannot do the later

Query generation, no coding

FAST

 

This is no the clear definition of pivot tables or "analytics," but Oracle's SQL pivot fails on almost every criterion, especially on no code and fast. That isn't to say it isn't a useful feature in a database, but it's not OLAP and it isn't really pivot, either, which I assume is an interactive process. SQL is not, as far as I'm concerned, interactive.

 

OLAP as a term may be a little old-fashioned, but it is not the same as pivot tables. However, pivot tables do seem to be on a path to provide all or most of OLAP's functionality.

 

Interestingly, the energetic wars of ROLAP versus MOLAP of the 90's may have subsided, but OLAP has clearly settled into a cold peace between the two. The true MOLAP's, Essbase. Microsoft, TM1 for example, have settled in for the long haul while the ROLAP's of Microstrategy, SAP Business Objects and Oracle BI have their own adherents and are still viable. Not many people actually use OLAP, perhaps fewer than 10% of the workforce in organizations that own OLAP tools, but it would be reckless to say that OLAP is no longer relevant. If anything, its ascendant thanks to the acquisition of BI vendors by enterprise software companies.

 

One last thing to consider. Predictive analytics are the topic du jour, but before an analyst builds a predictive model, he/she spends a lot of time profiling the data. A great of this work is done on OLAP tools. In addition, the output from OLAP manipulations is not terribly different from applying models - analyzing data to derive some understanding. And lastly, after predictive models are run, there is a continuing need to examine the results. Fertile ground for OLAP.

 

 


Posted December 1, 2009 8:59 PM
Permalink | 3 Comments |

3 Comments

Neil,

I've been following the Twitter discussion. I wholeheartedly agree that pivot [tables] does not provide the exact same functions as OLAP.

For me, the most interesting piece of your post is at the end. "Fertile ground for OLAP" [from ongoing analysis of predictives].

I first had the joy of seeing my Bayesian algorithm for predicting the reliability of the Star48 solid propellant rocket motor and risk associated with the Payload Assist Module, developed as a FORTRAN program in 1979. More recently, I've been looking at wats to combine OLAP and Bayesian predictives with tools such as LucidDB, Mondrian and R.

It seems to me that OLAP will be for the "a priori" or historical data, more than for the "a posteriori" and resulting inference or predictive analysis.

Fertile ground for OLAP indeed, though perhaps not quite as you intended.

When I focus on pivoting rather than OLAP, the reason is the pivoting is the user-facing capability that covers (making up numbers) 90% of the OLAP capabilities that 90% of OLAP users use. Pivot is also a somewhat clearer term.

So tell me, why is it important to have a "multidimensional model at its core" -- rather than running off a dynamic restructuring of a flat, 2D table -- or to have special handling of sparse data if the user experience is adequate?

Seems to me that the more useful distinction here would be pivot -- well, OK, pivot+ with some richer features -- on the client side with OLAP referring to the server-side technology... not that I'm the terminology police or anything.

Seth,

Why call it a relational database or a server if the users don't really perceive that functionality? Anyway, OLAP isn't all about users, it's about the capability it delivers. And if pivot = 90% of what users need, as you say, then I disagree entirely. They need what they need whether they perceive it or not. Do they perceive a correlated subquery? Do they perceive an optimizer plan?

Why have a multidimensional model at its core? Maybe I wasn't cleear, or should have used the word core, but I don't care if the data is written on a roll of toilet paper, unless the tool understands all or most of the primitive of multidimensional analysis, it isn't OLAP.

So, OLAP tools pivot, but pivot tools are not OLAP,

Leave a comment