1

I am trying to come up with a theoretical solution to an NxN problem for data aggregation and storage. As an example I have a huge amount of data that comes in via a stream. The stream sends the data in points. Each point has 5 dimensions:

  1. Location
  2. Date
  3. Time
  4. Name
  5. Statistics

This data then needs to be aggregated and stored to allow another user to come along and query the data for both location and time. The user should be able to query like the following (pseudo-code):

Show me aggregated statistics for Location 1,2,3,4,....N between Dates 01/01/2011 and 01/03/2011 between times 11am and 4pm

Unfortunately due to the scale of the data it is not possible to aggregate all this data from the points on the fly and so aggregation prior to this needs to be done. As you can see though there are multiple dimensions that the data could be aggregated on.

They can query for any number of days or locations and so finding all the combinations would require huge pre-aggregation:

  • Record for Locations 1 Today
  • Record for Locations 1,2 Today
  • Record for Locations 1,3 Today
  • Record for Locations 1,2,3 Today
  • etc... up to N

Preprocessing all of these combinations prior to querying could result in an amount of precessing that is not viable. If we have 200 different locations then we have 2^200 combinations which would be nearly impossible to precompute in any reasonable amount of time.

I did think about creating records on 1 dimension and then merging could be done on the fly when requested, but this would also take time at scale.

Questions:

  1. How should I go about choosing the right dimension and/or combination of dimensions given that the user is as likely to query on all dimensions?
  2. Are there any case studies I could refer to, books I could read or anything else you can think of that would help?

Thank you for your time.

EDIT 1

When I say aggregating the data together I mean combining the statistics and name (dimensions 4 & 5) for the other dimensions. So for example if I request data for Locations 1,2,3,4..N then I must merge the statistics and counts of name together for those N Locations before serving it up to the user.

Similarly if I request the data for dates 01/01/2015 - 01/12/2015 then I must aggregate all data between those periods (by adding summing name/statistics).

Finally If I ask for data between dates 01/01/2015 - 01/12/2015 for Locations 1,2,3,4..N then I must aggregate all data between those dates for all those locations.

For the sake of this example lets say that going through statistics requires some sort of nested loop and does not scale well especially on the fly.

Improve this question
3
  • How granular does the time need to be? If you have 10 years of data, 200 locations, and store an aggregated row for every location every hour, that is only 17.5 million rows. It should be perfectly feasible to query that on the fly. (It isn't clear what the "Name" dimension is, but I am ignoring it since it didn't seem to factor in your requirements).
    – user82096
    Sep 25, 2015 at 6:38
  • Thanks for your comment @dann1111 - I have edited the question to better reflect what I mean.
    – gimg1
    Sep 25, 2015 at 16:34
  • What are the volumes of data coming in? How users are going to query data? This sound to me like reinventing features of relational database.
    – luboskrnac
    Sep 26, 2015 at 21:46

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.