The report demonstrate the use of Cube
package to analyze data. The raw data is pulled from CSV file containing customer name, product category and the sale amount. Those data after is piped through Cube
process will be turn to 2 dimension table in which row will be group by customer name, column is grouped by product category and data cell is the amount that customer purchased a specific product category.
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 "SalesCustomersProducts.php";
$SalesCustomersProducts = new SalesCustomersProducts;
$SalesCustomersProducts->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\cube\processes\Cube;
class SalesCustomersProducts 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(
"row" => "customerName",
"column" => "productLine",
"sum" => "dollar_sales"
)))
->pipe(new Sort(array(
'{{all}}' => 'desc'
)))
->pipe(new Limit(array(
5, 0
)))
->pipe(new ColumnMeta(array(
"{{all}}"=>array(
"label"=>"Total",
),
"customerName"=>array(
"label"=>"Customers",
),
)))->saveTo($node2);
$node2->pipe($this->dataStore('salesCustomerProductLine'));
$node2->pipe(new RemoveColumn(array(
"{{all}}"
)))->pipe($this->dataStore('salesCustomerProductLineNoAll'));
$node2->pipe(new OnlyColumn(array(
'customerName', "{{all}}"
)))->pipe($this->dataStore('salesCustomerProductLineAll'));
}
}
<?php
use \koolreport\widgets\koolphp\Table;
use \koolreport\widgets\google;
?>
<div class="report-content">
<div class="text-center">
<h1>Customers vs Categories Cube</h1>
<p class="lead">
Top 5 customers with sales by categories
</p>
</div>
<?php
Table::create(array(
"dataStore" => $this->dataStore('salesCustomerProductLine'),
'cssClass' => array(
'table' => 'table-striped'
)
));
?>
<?php
google\BarChart::create(array(
"dataStore"=>$this->dataStore('salesCustomerProductLineAll'),
"options"=>array(
'title' => 'Top 5 Customers\' Total Sales',
'legend' => 'bottom',
'isStacked' => true
),
'width' => '100%%',
));
?>
<?php
google\ColumnChart::create(array(
"dataStore"=>$this->dataStore('salesCustomerProductLineNoAll'),
"options"=>array(
'title' => 'Top 5 Customers\' Sales by Categories',
),
"width"=>'100%',
// 'height'=>'300px',
));
?>
</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 |