Mysql vs postgres with Mondrian

[usenet]

I'm just about to try building a version of our OLAP database with
mondrian. As a back end, I'll either use postgresql or mysql. I have a
lot of experience with postgres and will probably use it, but I wanted to
hear from others if there was any significant advantage of mysql over
postgres when used in this role.

Thanks!

[usenet]

Hi, Mysql is better for small datamarts and works fine with
mondrian.But its performance degrades if the fact table more than half
a milion rows.with mysql 4 the picture might be better but there are
liencing issues(So i didnt use that) and limitations of mysql 3.2 might
make you crazy during ETL.
With postgres stored procedures,views will be useful and will be better
for large databases.For performance you can use indexing .You can also
hack mondrian to use materialzed views on postgresql generated with
triggers.If that works then nothing like it.

[Andrey Eliseev]

Hey. I am tring to use Mondrian + MySQL. My fact table contains over 50 million records and data base is supposed to grow in the future.

In order to improve performance:

#1. I flatted fact table, that means I have spacial script which populate foreign key defendant fields in fact table.
For example I have field userid in fact table and I need to aggregate or select records which have users from the same state. In order to achive this I created special extra fields _userstate, _usercity, _userzipcode and so on. This allows me to improve selection speed and aggrigation by this fields. But at the same time it requires to run extra update scripts in order to keep fact table and dimension tables in sync.

#2 I have a lot of dimension having small distinct values like: Gender, State, Country, Status, etc. Total qty is about 10.
I cannot build all passable Index combinations, since it takes forever to update table. So I built indexes for every dimension and created another one index having almost all fields in it: Gender, State, Country, Status
If you try to select by where State='BLA' and Country='BLA' and status = 'BLA' that index would not be called since first column is Gender in it.
So when you create your selection query you can fill out all possable values for gender. where (Gender='M' or Gender='F') and State='BLA' and Country='BLA' and status = 'BLA'
In this case your index would be used and it is equivalent to UNION of two queries for Gender='F' and for gender = 'M'
By doing this way you don't need to create index for State, Country, Status combination and it saves a lot of time for updating indexes.
Again this approach works fine when you have a lot of small foreign keys having few distinct values.

I am still investigating Mondrian features but it is obvious that it uses MySQL for aggregation fact table. General performance is good for running batch scripts against Cube but it is not On-line for sure. I cannot wait up to 5 minutes when MySQL finishes select query having group by on my table.

What I expect would further improve performance is usage of Bitmap indexes. Which are not available in MySQL. That is why PostgreSQL is very interesting for me from this stand point.

I know Oracle very well but non-profit nature of my project makes it usage imposable.