Thread: OLAP versus Materialized Views?

OLAP versus Materialized Views?

From
Jonathan Gardner
Date:
I've just discovered OLAP and it looks like a competing technology with 
materialized views.

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?

(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.

(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?

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.

-- 
Jonathan Gardner
jgardner@jonathangardner.net


Re: OLAP versus Materialized Views?

From
Simon Riggs
Date:
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.



Re: OLAP versus Materialized Views?

From
Tim Larson
Date:
On Wed, Apr 28, 2004 at 12:14:06AM +0100, Simon Riggs wrote:
> Yes. There is Relational OLAP (ROLAP) and Multidimensional OLAP (MOLAP).
> PostgreSQL can be used as a ROLAP server.

For multidimentional on PostgreSQL there is Mondrian: http://sourceforge.net/projects/mondrian/

--Tim Larson