The report demonstrate the use of Cube
package to analyze data. The raw data is pulled from CSV file containing sale amount, orderDay, orderMonth and orderYear. The time information will act as dimension. Those data after is piped through Cube
process will be turn to 2 dimension table in which row will be group by month, column is grouped by year and data cell is sale amount purchase in particular year and month.
Cube can be considered a simple version of Pivot Tables that you see in Excel or any Speadsheet application. The different between Cube and Pivot is the number of dimension they handle. While Pivot can handle more than 2 dimension and support hierarchical dimension, Cube
support only 2 dimensions and single level of dimension. Although it sounds simple but according to our observation, 70% cases Cube
is enough.
Because of the simplicity, the power of Cube
process lie on its speed to handle data compared to Pivot.
<?php
require_once "SalesMonthsYears.php";
$SalesMonthsYears = new SalesMonthsYears;
$SalesMonthsYears->run()->render();
<?php
require_once "../../../load.koolreport.php";
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\Limit;
use \koolreport\processes\RemoveColumn;
use \koolreport\processes\OnlyColumn;
use \koolreport\processes\Sort;
use \koolreport\processes\ColumnsSort;
use \koolreport\processes\ValueMap;
use \koolreport\cube\processes\Cube;
class SalesMonthsYears extends koolreport\KoolReport
{
function settings()
{
return array(
"dataSources" => array(
"dollarsales"=>array(
'filePath' => '../../../databases/customer_product_dollarsales2.csv',
'fieldSeparator' => ';',
'class' => "\koolreport\datasources\CSVDataSource"
),
)
);
}
function setup()
{
$node = $this->src('dollarsales')
->pipe(new ColumnMeta(array(
"dollar_sales"=>array(
'type' => 'number',
"prefix" => "$",
),
)));
$node->pipe(new Cube(array(
"column" => "orderYear",
"sum" => "dollar_sales"
)))->pipe(new ColumnMeta(array(
"{{all}}"=>array(
"label"=>"Total",
),
"orderMonth"=>array(
"label"=>"Month",
),
)))->saveTo($node2);
$node2->pipe($this->dataStore('salesYear'));
$node2->pipe(new RemoveColumn(array(
"{{all}}"
)))->pipe($this->dataStore('salesYearNoAll'));
$node->pipe(new Cube(array(
"row" => "orderMonth",
"column" => "orderYear",
"sum" => "dollar_sales"
)))
->pipe(new ColumnMeta(array(
"{{all}}"=>array(
"label"=>"Total",
),
"orderMonth"=>array(
"label"=>"Month",
)
)))
->pipe(new Sort(array(
'orderMonth' => 'asc'
)))
->pipe(new ColumnsSort(array(
'{name}' => 'asc',
'fixedColumns' => array(
0 => 0,
'{{all}}' => 10
),
)))
->pipe(new ValueMap(array(
"orderMonth"=>array(
1 => "January",
2 => "February",
3 => "March",
4 => "April",
5 => "May",
6 => "June",
7 => "July",
8 => "August",
9 => "September",
10 => "October",
11 => "November",
12 => "December",
)
)))
->saveTo($node2);
$node2->pipe($this->dataStore('salesYearMonth'));
$node2->pipe(new RemoveColumn(array(
"{{all}}"
)))->pipe($this->dataStore('salesYearMonthNoAll'));
}
}
<?php
use \koolreport\widgets\koolphp\Table;
use \koolreport\widgets\google;
?>
<div class="report-content">
<div class="text-center">
<h1>Months vs Years Cube</h1>
<p class="lead">
Sales across months and year
</p>
</div>
<?php
Table::create(array(
"dataStore" => $this->dataStore('salesYearMonth'),
'cssClass' => array('table' => 'table-condensed')
));
?>
<div class='row'>
<div class="col-md-4">
<?php
google\ColumnChart::create(array(
"dataStore"=>$this->dataStore('salesYearNoAll'),
"options"=>array(
'title' => 'Yearly Sales',
'isStacked' => true,
'legend' => 'bottom'
),
'width' => '100%',
));
?>
</div>
<div class="col-md-8">
<?php
google\AreaChart::create(array(
"dataStore"=>$this->dataStore('salesYearMonthNoAll'),
"options"=>array(
'title' => 'Monthly Sales by Years',
),
"width"=>'100%',
// 'height'=>'300px',
));
?>
</div>
</div>
</div>
customerName | productName | productLine | orderDate | orderDay | orderMonth | orderYear | orderQuarter | dollar_sales |
Vitachrome Inc. |
1937 Lincoln Berline |
Vintage Cars |
2003-01-10 00:00:00 |
10 |
1 |
2003 |
1 |
3726.45 |
Vitachrome Inc. |
1936 Mercedes-Benz 500K Special Roadster |
Vintage Cars |
2003-01-10 00:00:00 |
10 |
1 |
2003 |
1 |
1768.33 |
Baane Mini Imports |
1952 Alpine Renault 1300 |
Classic Cars |
2003-01-29 00:00:00 |
29 |
1 |
2003 |
1 |
5571.8 |
Baane Mini Imports |
1962 LanciaA Delta 16V |
Classic Cars |
2003-01-29 00:00:00 |
29 |
1 |
2003 |
1 |
5026.14 |
Baane Mini Imports |
1958 Setra Bus |
Trucks and Buses |
2003-01-29 00:00:00 |
29 |
1 |
2003 |
1 |
3284.28 |
Baane Mini Imports |
1940 Ford Pickup Truck |
Trucks and Buses |
2003-01-29 00:00:00 |
29 |
1 |
2003 |
1 |
3307.5 |
Baane Mini Imports |
1926 Ford Fire Engine |
Trucks and Buses |
2003-01-29 00:00:00 |
29 |
1 |
2003 |
1 |
1283.48 |
Baane Mini Imports |
1913 Ford Model T Speedster |
Vintage Cars |
2003-01-29 00:00:00 |
29 |
1 |
2003 |
1 |
2489.13 |
Baane Mini Imports |
1934 Ford V8 Coupe |
Vintage Cars |
2003-01-29 00:00:00 |
29 |
1 |
2003 |
1 |
2164.4 |
Baane Mini Imports |
18th Century Vintage Horse Carriage |
Vintage Cars |
2003-01-29 00:00:00 |
29 |
1 |
2003 |
1 |
2173 |