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:LocationDate TimeNameStatisticsThis 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 4pmUnfortunately 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 TodayRecord for Locations 1,2 TodayRecord for Locations 1,3 TodayRecord for Locations 1,2,3 Todayetc... up to NPreprocessing 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: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?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 1When 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.
Data Store Design for NxN Data Aggregation
architecture;database design;data;architectural patterns;aggregate