Using PHP with ROLAP?

Hexxx

My mysql queries are becoming too difficult to perform and I belive I would have to change my project over to using ROLAP (Relational Online Analytical Processing). Does anyone know of any PHP, mysql ROLAP websites or tutorials, because I had already started my project using mysql, apache, and php and can't start over everything from scratch.

tomhath

By difficult to perform, do you mean too complicated? Or take too long to run? OLAP is a technique for aggregating very large amounts of data. It doesn't necessarily simplify things, especially at the server end.

You'll probably have to use an OLAP server; there's Mondrian in the Java world, or Microsoft's Analysis Services.

http://mondrian.pentaho.org/

Hexxx

Well, my my database project is a final year engineering database project, and we have to be able to do everything in it by learning database design implementation etc by ourselves.

My project was based on a previous project for television audience measurement where data came into the database with a specific viewer id (and viewing times) for different channels, and the project was supposed to be able to give different shares, rating and demograhics for each channels. The problem I am getting is the database wasn't really done, and it isn't in any star schema, so nothing is being stored and the same stuff (eg yesterdays hourly ratings) is being generated each time a station wants to view it. Also, because the attributes of the viewer (sex, age, location) are in the viewer table alone (not a dimension table) I have to get this information perfroming alot of queries and grouping each time to get for only speific viewers and then to group for each station.

But the supervisior never said anything about OLAP, or using ROLAP, so I was wondering if it would be something additional like some program I might need. Because all I have done so far is caculate houlry shares for a channel for any day (but not stored it anyway), and wondering that I would have to do the same data extraction and grouping for each different attribute of the viewer.

Is 'ROLAP' something additional I would need (like a program) ? Or is this just a way of doing something?

I did some searching for ROLAP, as I am using and SQL database for the project, but all I came up with (other than definitions of it) was a Visual SQL Designer.

tomhath

I don't claim much experience with cubes, so don't put too much in my opinions here.

A lot depends on the number of rows you're dealing with. Up to several tens of million and a few dozen users, a relational design should be okay. Don't worry about recalculating the statistics each time, a DBMS is very fast at fetching and sorting data. Joining back to the attributes tables shouldn't be a problem with appropriate indexes on the tables.

Updating an OLAP cube when new data comes in is pretty expensive, not something you'd want to do if the data is streaming in constantly. The one we have at work is updated once a quarter; it takes about six weeks to load and scrub the data. But that comes from a several billion row source database. Once you have the cubes ready the end users can explore the data very quickly, but if all you need is a few reports (shares, ratings, and demographics) a relational design will be fine.

You say you're performing a lot of queries and and grouping by viewer attribute and station. It sounds like you need to work on those queries; SQL is very good at that kind of data access if you use all it has to offer.