Re: OLAP versus Materialized Views?

On Tue, 2004-04-27 at 23:47, Jonathan Gardner wrote:
> I've just discovered OLAP and it looks like a competing technology with
> materialized views.

Yes. Read up some more, but don't get sucked in by the marketing.

> In a nutshell, OLAP seems to be "pre-storing" the results of potential
> queries. When queries are made with those conditions, then the pre-stored
> results are used. It seems most common for join conditions or aggregates
> with conditions. (Corrections welcome...)
>
> This seems like a generally useful tool. I expect PostgreSQL should one day
> have a feature like this.
>
> OLAP usage would basically be describing how the data fits together and
> predicting what kinds of queries will be made. Then, internally the
> implementation would decide what data should be cached. Perhaps it can do
> some analysis of the data and the queries being run to come up with a fair
> compromise.
>
> Materialized Views are a simpler concept, but of limited usefulness. Having
> a bunch of materialized views that are very similar would be less efficient
> overall than having OLAP configured properly.
>
> Questions:
> (1) Has anyone been doing any work or serious thought on anything like this?
>

Yes. There is Relational OLAP (ROLAP) and Multidimensional OLAP (MOLAP).
PostgreSQL can be used as a ROLAP server.

> (2) Should I be focusing on OLAP rather than materialized views? With
> materialized views, a few specific queries work really well, but related
> queries get no benefit. Apparently, with OLAP, a whole class of queries
> will get a huge boost. I'm not certain that OLAP and materialized views are
> entirely different.

No, your work is well received.

>
> (3) I can't seem to find the paper on the twelve laws of OLAP, but it gets a
> lot of mentioning. Does anyone know where I can obtain it?

Written by Ted Codd, but not nearly as interesting as his earlier work.

> Future Thoughts:
> OLAP relies on knowing how the data fits together and what kinds of queries
> are being done. It could be possible, just by analyzing the queries being
> run, to have OLAP automatically kick in without user intervention.