OLAP cube - PHP and MongoDB

5
votes

I need to create an analytics system. I already built the system using MognoDB and PHP but without using OLAP. Now my queries are really the best I can get, but the system is really slow because no cube. It can take a minute to load a report for the last 7 days. I really need the options of the cube - slice & dice.

So what would be the solution for me? Is there a good cube system build with MongoDB and that can insert & view data via PHP? Maybe MongoDB won't be good for me? Should I use another database and start all the system from 0? What OLAP solutions there are using PHP?

Edit: More info -- Well, the system is like google analytics. Need to be able to know how much views in every day, need to be able to report from only a specified traffic source and country. The system needs to handle 1,000,000 unique view each day. But not only count of views, there needs to be able to see how much users are returning, what is the average time for every user, etc.

Thanks.

2
phpmongodbanalyticsolapolap-cube
You haven't said anything about the type of data, qty of records, master data, what the source of the data is, what the reporting requirements might be, what aggregation you use. How can we answer this without it being guess? you need to provide a whole lot more information, and I doubt very much that the answer is just cube + olap engine. – T9b
Thanls. Sorry, added. Can you explain a bit more about this cube + olap engine? How can I do it using PHP? What about my mongodb? Should I use another DB? – Eli_Rozen
I'm not sure what your obsession is with PHP and mongoDB. All of what you need can be done in any database well enough. Sounds like you are either not loading your data appropriately or your SQL is not good enough. Please supply your code, DB structures etc... that you say is not performing well. – T9b
Let's try to focus a little here: you have 1 minute loadtime for 7 millions of records to analyze. I don't know if a simple mysql setup could do better, but I believe a simple test for that use case can be written in 4 to 6 hours + installing mysql time. Are you low on RAM? RAM could be an issue with both MySQL and Mongo. Have you considered caching stats on a day by day/hour by hour/visit by visit basis? (and base the 7 day stats on those sub stats?) – ZJR
@Eli_Rozen did u figure it out? – Sibelius Seraphini

2 Answers

2
votes

MongoDB isn't built for OLAP cube-type applications. I can think of two approaches:

1) Determine ahead of time what queries you'll need to do, and store your data in the optimal format ahead of time. E.g., if you want to know visits per country per day, then as each visit occurs do something like:

db.visits.update({'country':country, 'day':current_day()}, {$inc:{visits:1}})

Repeat for each metric. Fire-and-forget updates from your application to the analytics DB will add minimal overhead to serving a request to the visitor. Then your queries will be largely precomputed.

2) Try JasperSoft's MongoDB backend.

2
votes

To update a bit the responses, icCube is now offering access to MongoDB. You should be able to setup some MongoDB query to create tables that icCube is going to use for reporting and analytics.