implementing dynamic query handler on historical data

Question

Context:

I have historical data about property (house) sales collected from various sources in a centralized/cloud data source (assume info collection is handled by a third party)

Planning to develop an application to query and retrieve data from this centralized data source

Example Queries:

Simple : for given XYZ post code, what is average house price for 3 bed room house?

Complex: What is estimated price for an house at "DD,Some Street,XYZ Post Code" (worked out from average values of historic data filtered by various characteristics of the house: house post code, no of bed rooms, total area, and other deeper insights like house building type, year of built, features)?

In addition to average price, the application should support other property info ** maximum, or minimum price..etc and trend (graph) on a selected property attribute over a period of time**. Hence, the queries should not enforce the search based on a primary key or few fixed fields

In other words, queries can be

What is the change in 3 Bed Room house price (irrespective of location) over last 30 days?

What kind of properties we can get for X price (irrespective of location or house type)

The challenge I have is identifying the domain (BI/ Data Analytical or DB Design or DB Query Interface or DW related or something else) this problem (dynamic query on historic data) belong to, so that I can do further exploration

My findings so far

I could be wrong on the following, so please correct me if you think so

I briefly read about BI/Data Analytics - I think it is heavy weight solution for my problem and has scalability issues.

DB Design - As I understand RDBMS works well if you know Data model at design time. I am expecting attributes about property or other entity (user) that am going to bring in, would evolve quickly. hence maintenance would be an issue. As I am going to have multiple users executing query at same time, performance would be a bottleneck

Other options like Graph DB (http://www.tinkerpop.com/) seems to be bit complex (they are good. but using those tools meant for generic purpose, make me think like assembly programming to solve my problem )

BigData related solution are to analyse data from multiple unrelated domains

So, Any suggestion on the space this problem fit in ? (Especially if you have design/implementation experience of back-end for property listing or similar portals)

Answer 1

From my experience, your main problem is how to let the user specify the queries, rather than the data model, and thus old school relational may well work for you. Here's why.

If you are pulling data from many different sources, you will end up putting them through some sort of interface. As you do this, you will discover an underlying interface, meaning you will find the most appropriate way to present the data from the various sources. (I've actually done this with about 12 different banks). Some of the sources will have extra data that has no counterpart in the other sources, while others will have an idiosyncratic way to show something. But eventually, you will settle upon something that covers most of your use cases. This is of course assuming there's a reason why you need a mixed set of sources.

The querying is the hard bit. If users are not supposed to learn SQL you will need to build something that constrains them but allows the complexity you want to provide.

As for performance, I don't see it being that big a problem. People searching for house price data are just reading some historic rows that won't change, easily scaled in any modern db. One major complication is if you were to try to fill in missing data based on some sort of proxy model. Then all bets are off, and your performance would depend on algo efficiency.

Answer 2

I think that what would be the most useful for you would be Big Data as a "domain" for the problem you're trying to solve.

If you want people to be able to interesting things with the data, on the web, then you're going to want a low-latency system. While there are a lot of approaches, one thing I don't think you'll be able to avoid is pre-calculating at least some level of rollups.

Using something like Hadoop with Hbase and Pig are an approach I've been fond of recently...using something like this you've got the ability to quickly and relatively easily re-calculate things as you refine your requirements from your original source data. If you go in this general direction, you'll want to do some reading on effective row key design in large key/value stores (i.e. Hbase, Cassandra).