Crossfilter against large volume of data
s.sind...@gmail.com
CustomerOrderid SKU Each Cases OrderQuantity OrderDate
13516 129209 1.2 2 15 01/05/2015
13516 129210 1 7.5 10 01/12/2015
13516 129211 1.3 1.2 2 01/20/2015
13516 129212 1.2 2.3 4 01/28/2015
13516 129213 1.6 1.6 6 02/09/2015
13516 129214 2.4 2.4 8 02/17/2015
13516 129215 2.1 2.8 11 02/18/2015
13516 129216 2.2 2.9 7 03/23/2015
13516 129217 2.3 2.4 1 03/24/2015
13516 129218 2.4 2.4 4 03/25/2015
13516 129219 1 1.9 6 03/30/2015
13516 129220 1.1 2.7 4 04/06/2015
13516 129221 1.5 2.8 1 04/13/2015
13516 129222 1.6 2.4 6 04/22/2015
13516 129223 1 1.5 1 04/28/2015This table contains the historic data, it contains the past 5 years data. We need to display the chart for Average units of Each and Average units of cases based on Day of Week. If user selects the “Each”, then we need to display Average of Each, if user selects the “Cases”, then we need to display the Average of Cases.
We have done the above item using D3 Bar chart and Google big query. This is working fine.
Every time we just call the db and fetch the data based on user selection.
Expected Output
DAYOFWEEK AVG_Units(Cases) AVG_Units(Each)
Monday 3.05714 1.37142
Tuesday 1.875 1.75
Wednesday 2.475 1.825we have implemented the cross filter to display the chart details by Year. We just download the 105 million data text file and kept it in our local environment, where the d3 chart is running, and then we just loading the distinct OrderDate years in pie chart using cross filter library, if user clicks the year as 2015 in pie chart, then the report should displays Average of (Each\Cases) for which, OrderDate falling under 2010.
I have used the following code to achive this, if we have 10L records in the text file it is working fine, if we have 105Million record in the file then my browser is getting crashed. Please advise to resolve this.
<script>
var j = jQuery.noConflict();
j(document).ready(function () {
d3.csv("SmallDB.txt", function (error, data) {
var dayOfWeekChart = dc.barChart("#Canvas");
var chart = dc.pieChart("#test");
var numberFormat = d3.format('.2f');
data.forEach(function (d) {
d.dd = new Date(d.OrderDate);
d.Year = d.dd.getFullYear();
});
//Pie chart Start
var index = -1;
var ndx = crossfilter(data),
runDimension = ndx.dimension(function (d) {
var a = parseInt(d.Year);
return "Year-" + a;
});
speedSumGroup = runDimension.group().reduceSum(
function (d, i) {
var b = parseInt(d.Year);
return b;
});
chart
.width(600)
.height(300)
.transitionDuration(1000)
.slicesCap(4)
.innerRadius(70)
.dimension(runDimension)
.group(speedSumGroup)
.legend(dc.legend());
chart.render();
chart.filter = function (d) {
OrderDateRange(d);
};
//Pie chart End
//Bar Chart Start
function OrderDateRange(data) {
var dayOfWeek = ndx.dimension(function (d) {
return d.dd.getDay();
});
var AVGUnitsDimGroup = fluctuation.group().reduce(
//add
function (p, v) {
++p.count;
p.sum += v.Each / 1;
p.avg = p.sum / p.count;
return p;
},
//remove
function (p, v) {
--p.count;
p.sum += v.Each / 1;
p.avg = p.sum / p.count;
return p;
},
//init
function (p, v) {
return {
count: 0,
sum: 0,
avg: 0
};
});
dayOfWeekChart.width(400)
.height(300)
.margins({ top: 20, left: 50, right: 10, bottom: 20 })
.transitionDuration(1500)
.dimension(dayOfWeek)
.group(AVGUnitsDimGroup)
.valueAccessor(function (p) {
return p.value.avg;
})
.x(d3.scale.ordinal().domain([1, 2, 3, 4, 5, 6, 7]))
.xUnits(dc.units.ordinal)
.title(function (d) {
return d.value.avg;
})
.xAxisPadding(600)
.elasticX(true)
.elasticY(true)
dayOfWeekChart.render();
}
})
})
</script>