SEO

#1 (**permalink**) 02-27-2008, 09:32 PM

Anyone here uses OLAP, running in MySQL? Any OpenSOurce OLAP query
tools?
How does one goes about doing OLAP? Is there any documentation w/ MySQL
anywhere. (I'm currently looking at Pentaho and Mondrian etc but the
Pre-configured demo didn't work as it should, meaning it doesn't run for
X reasons)

What about doing views? eg: I take a snapshot of the data, every 1
hours, and plug them into a view or another table and use that eg:

select
Avg (A),
Min(A),
Max(A),
AVG(B)
min(B),
max(B)
from table1
left join table2
on table1.field = table2.field
etc..
etc..

Will that work? Then when I query, I query this newly created aggregrate
table and I'm thinking of something along these lines.

DATE | Month | Day | Hour | AVG | Min | Max| some other field
2006 | | | | 10 | 0 | 100 |
AAA
-->2006| Oct | | | 10.5| 1 | 101 | BBB
-->2006| Oct | 10 | |10.2 | 3 | 98 | CCC

etc..etc..

Will something like this work for a So called "moving average"??

#2 (**permalink**) 02-27-2008, 09:32 PM

You'd have to use another table. I don't believe mysql views will keep your
'moving average' values.

If you're using 5.1, you can automate the select/insert with an event --
it's a cron like tool built into mysql.

If you have a datetime field in either of the tables that represents the
'action' time of each piece of data, you could do it as:

select
DATE(datetime_field) AS period,
Avg (A),
Min(A),
Max(A),
AVG(B)
min(B),
max(B)
from table1
left join table2
on table1.field = table2.field
GROUP BY period

On 10/11/06, Ow Mun Heng <Ow.Mun.Heng@wdc.com> wrote:
>
> Anyone here uses OLAP, running in MySQL? Any OpenSOurce OLAP query
> tools?
> How does one goes about doing OLAP? Is there any documentation w/ MySQL
> anywhere. (I'm currently looking at Pentaho and Mondrian etc but the
> Pre-configured demo didn't work as it should, meaning it doesn't run for
> X reasons)
>
> What about doing views? eg: I take a snapshot of the data, every 1
> hours, and plug them into a view or another table and use that eg:
>
> select
> Avg (A),
> Min(A),
> Max(A),
> AVG(B)
> min(B),
> max(B)
> from table1
> left join table2
> on table1.field = table2.field
> etc..
> etc..
>
> Will that work? Then when I query, I query this newly created aggregrate
> table and I'm thinking of something along these lines.
>
> DATE | Month | Day | Hour | AVG | Min | Max| some other field
> 2006 | | | | 10 | 0 | 100 |
> AAA
> -->2006| Oct | | | 10.5| 1 | 101 | BBB
> -->2006| Oct | 10 | |10.2 | 3 | 98 | CCC
>
> etc..etc..
>
> Will something like this work for a So called "moving average"??
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=g...dson@gmail.com
>
>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

OLAP for MySQL or an Aggregrate Table