# Approach to perform a calculation over a large dataset and calculate mean of scores

I have a table/collection called scores and it has 3 relevant identifiers based on which calculation need to be done.

sample

``````{
score : Number,
company : String,
zone : String,
unit : String,
timestamp : Number
}
``````

Everyday we get huge amount of data in this scores table which has 3 identifiers. On client side dashboard we are bound to show progression for company, zone and unit separately for past six weeks.

And weeks are not fixed - they are dynamic instead. For e.g if you are checking the dashboard on 15th of march then past 6 weeks data from x date to 14th March is shown. Progression in terms of weeks.

The plan is to create a system which at midnight will take scores from records for past 42 days from that time and calculate weekly score and then save it for every company, zone and unit separately.

I don't know if this approach is correct or not, probably not. Let's say there are 500 records coming in the system everyday so for 42 days I will have roughly 21000 records. Now calculation has to be done for all three entities separately.

Plus the records that I am going to fetch based on identifier(one from set of three) & timestamp, I will end up with 42 days data. How do I churn them into weeks format? I should fetch 42days data in one go and then distribute via timestamp using conditions or I should just call for 1 week data in one go.

This approach seems really confusing given that the output that I am going to get from this is not that significant. Is this the right approach to solve this issue?

I want to make a system that'll scale in future for more data and not just 21k records.

• An OLAP cube is perfect for this. They can handle orders of magnitude greater row counts. Hold each day separately and sum at run time. Mar 15, 2017 at 11:49
• Well... the DB system I am using is Mongo DB. Mar 16, 2017 at 7:11
• What kind of a calculation are you doing? Sum, averages, something more complex? Could you take the data from each day and do some sort of partial calculation on it, store the partial results and then just compile the last X days into a result set? (IE if you are averaging scores, make a table to store the date, identifiers, total score and total number of scores for the day. Then when you want to compile the average over the last 42 days, you sum the total score and number of scores and do the math. Far easier to compute using 42 records vs. a lot more.) Mar 16, 2017 at 19:49
• Also, why is 21000 records such a prohibitively large number of records? Is the calculation complex? Or is it just written in with a poor algorithm, etc? That doesn't seem like a huge number to me. Mar 16, 2017 at 19:51

Everyday we get huge amount of data in this scores table which has 3 identifiers. On client side dashboard we are bound to show progression for company, zone and unit separately for past six weeks.

The plan is to create a system which at midnight will take scores from records for past 42 days from that time and calculate weekly score and then save it for every company, zone and unit separately.

Here is a jsbin of a working solution with sample data:

https://jsbin.com/beyetof/27/edit?js,output

If you have a dataset of your own handy, just `main(YOUR_DATA_HERE);` assuming that it all follows the sample you provided.

if you are checking the dashboard on 15th of march then past 6 weeks data from x date to 14th March is shown.

Try:

``````var userEnteredStartTime = new Date('2017.05.15').getTime(); // 1489536000
const WEEK_IN_SECONDS = 604800;
var startTime = userEnteredStartTime;
var endTime = startTime + WEEK_IN_SECONDS;
var endTimeOfSixWeeks = (startTime + WEEK_IN_SECONDS) * 6;
var endTimeFortyTwoDaysLater = startTime + 3628800
``````

I want to make a system that'll scale in future for more data and not just 21k records.

As you can see above, 38912 data points takes 152.072ms (~0.152s) You would have to have 275,000 records to process before this operation breaks 1 second.