Tee.Data

Multi-dimensional queries and charting with JavaScript.

v0.82 Beta, November 2012

Contents



Data --> "Sum Hours by Sport" --> Chart

(This browser does not support HTML5 Canvas)

1. Introduction

 Tee.Data is a lightweight javascript (teechart-data.js, 15KB minified) to create pivot-tables and optionally chart results using TeeChart HTML5 for JavaScript.

The client-side script runs on any browser and can also be used at server-side using node.js or any JScript machine like Google's V8.

Input data can be any JavaScript object, or array, or a JSON string, or XML datasets, or an html <table>, embedded in scripts or loaded from urls, or any of the above returned from a REST or Ajax server.

Jump directly to demo examples.

2. Basic steps

 Create a Tee.Data object: 
  var MyData = new Tee.Data();
Then provide your own data, using the addDataSet function:
  var Data = [ { id: 1, name: "Acme Inc" }, { id: 2, name: "Google" } ];
  var Customers = MyData.addDataSet( "Customers", Data );
For JSON data, call the addJSON function.
In this case the JSON string will be internally converted to a JavaScript object using the browser "JSON.parse" method.
  var customerData = "{ [ { 'id': 1, 'name': 'Acme Inc' },
                          { 'id': 2, 'name': 'Google'   }  ] }";

  var Customers = MyData.addJSON( "Customers", customerData );

XML datasets can be loaded with the following code:
  var xml = MyData.loadXMLDoc( 'temperatures.xml' ),
      Temps = MyData.addDataSet( 'Temperatures', xml );

Data contained in an html <table> elements can also be used, using a helper tableToObject method. (See example).

Multiple datasets are supported, and links between them can be optionally created.

3. Defining Dimensions

 Queries are defined by Dimensions, Metrics and Filter selections, and the output can be optionally sorted.

After adding one or more datasets, you can define how is the data structured inside them.
A Dimension is an object or object property that will be used to group results, and a Metric is a property that can be "aggregated" (summed, averaged, etc).

Like for example, if your data consists of "Products", "Sales amount" and "Units sold", the dimension will be "Products" and the metrics will be "Sales amount" and "Units sold".

Any dimension can also have sub-dimensions to define hierarchies of data.

To define a dimension, use the addDimension method:

  var SalesData = MyData.addDataSet( "Sales",
                    { "items": [
                       { "product": 1, "amount": 123, "units": 45 },
                       { "product": 2, "amount": 321, "units": 22 }, ...
                      ]
                    } );

  var Products = SalesData.addDimension( "Products", "items", "product" );

The firsts parameter ("Products") is the dimension title, used to decorate the chart and output tables.

If data is an array like in the above example, the second parameter ("items") is the property name that contains the array, and the third parameter ("product") is the dimension items ID (identifier).

Dimensions in grid-like data

When data is just an array of records (like a grid or table), dimensions can be defined calling the same addDimension method without passing the third parameter: 
  var Friends = MyData.addDataSet( "Friends",
                 [
                  { id: 1, name: 'Mike',  birth: '4/18/1994', gender: 'M' ... },
                  { id: 2, name: 'Mary',  birth: '3/22/1980', gender: 'F' ... },
                  { id: 3, name: 'Carol', birth: '11/6/2001', gender: 'F' ... },
                  ...
                 ]);

  var ID     = MyData.addDimension( "People", "id" ),
      Birth  = MyData.addDimension( "Birth Date", "birth" ),
      Gender = MyData.addDimension( "Gender", "gender" );

Adding sub-dimensions

If the data contains sub-arrays (arrays inside arrays), simply call the dimension addDimension function instead of the dataset one:

  var Products = SalesData.addDimension( "Products", "items", "product" ),
         Store = Products.addDimension( "Store", "", "store" ),
             ...

Datetime dimensions

Dimensions are usually identified by string or numeric "ID" values, such as Product id, or Person FullName.
Another important kind of dimensions are Datetime values.

When a dimension is declared as Datetime, data can be aggregated and filtered using datetime intervals, like for example "sum of sales grouped by sale date month", or "by weekday", etc.
After adding a dimension, it can be marked as to contain datetime values in the following way:

  var People = CustomerData.addDimension( "Customers", "persons", "id" );
      BirthDate = People.addDimension( "Birth Date", "", "birthDate" );

      BirthDate.datetime = { month:true, year:true, decade:true, selected:'y' };
The above "datetime" object values determine which kind of datetime aggregations are valid for that specific datetime values, and the "selected" field determines how queries will use the BirthDate dimension to create groups.
In the above example, the "y" value means "group data by BirthDate Year".

Including empty or null values

When executing a query, dimension items that have no output are included by default as "zero" values.

Missing items can be included or not in query output, by setting the nulls property: 
  var Products = SalesData.addDimension( "Products", "items", "product" );

  // Do not show Products that have no data to measure:
  Products.nulls = false;

4. Adding Metrics

 Dimensions provide an "addMetric" function to specify which fields can be aggregated:

  var Products = SalesData.addDimension( "Products", "items", "product" ),
        Amount = Products.addMetric( "Sales Amount", "amount" ),
        Units  = Products.addMetric( "Units Sold", "units", "average" );

The first parameter is the metric title (used to decorate the chart), and the second parameter is optional and defines the field inside the dimension, if any.
A third parameter is optional, and indicates the Metric "measure" that is "sum" by default.
Valid measure values are: "sum", "average", "high", "low" and "count".

Fields should be of type numeric to be valid when calculating sums and averages, but they can be of any type when the metric "measure" is to "count" occurrences.

Calculated Metrics

Metrics can also be declared as functions to return values that have no corresponding data.

For example, the following metric defines "Fahrenheit" degrees that are calculated on-the-fly from Celsius data:
  var Fahrenheit  = Temperatures.addMetric( "Fahrenheit",
                          function(data) {
                             return 32 + ( data.celsius * 9/5 );
                          });

5. Creating Queries and Filling a Chart

 After you have defined the datasets and their dimensions and metrics, queries are executed with this code:

  var result = MyData.query(Products, Amount);

The first parameter is a dimension, and the second is a metric. The above query will return the sum of Amount grouped by Product.
To query multiple metrics, you can pass an array of metrics:

  var result = MyData.query( Products, [ Amount, Units ] );

The "result" object returns an array of results, and each item contains a sub-array of values.
Each value is an object that includes the result "value", the "code" and the "label" text.

Charting a query

Creating a chart and filling it with a query results:

  var Chart1 = MyData.Chart( "canvas1", "Product Sales" );

  Chart1.fill( Products, Amount );

See demo: Basic simple example

The first parameter "canvas1" is the HTML5 canvas element that will display the chart.
The second parameter is an optional chart title string.

By default the "fill" function will add query results to the chart, and then it will just draw the chart to show it at the canvas.
You might want to set additional chart properties after the query is executed, and draw the chart afterwards.
In this case its better to fill the chart without redrawing it, to avoid painting it twice.

The "fill" method accepts an additional optional parameter to do this:

  var Chart1 = MyData.Chart( "canvas1", "Product Sales" );

  Chart1.fill( Products, Amount, true );  // <-- "true" means "not repaint the chart"
  Chart1.panel.transparent = true;
  Chart1.draw(); // <-- the chart is painted here

Different metrics

The default metric calculation is the sum of values, and its controlled with the "measure" property.
Available calculations are:

  var Amount = Products.addMetric( "Sales Amount", "amount" );

  Amount.measure = "sum";  // <-- by default
  Amount.measure = "average";
  Amount.measure = "high";
  Amount.measure = "low";
  Amount.measure = "count";


6. Linking Dimensions and Datasets

 To allow querying across multiple datasets, they need to be "linked".
Once they are linked, you can then use dimensions that belong to one dataset and metrics from another.
Linking is defined by calling a dimension "addLink" method, passing another dimension that is at another (or same) dataset.

First dataset: 
  var Products = MyData.addDataSet( "Products",
                        "{ 'items': [ { 'product':1,
                                        'amount': 33 ... }, ...
                                    ] }" ),
         Sales = Products.addDimension( "Sales", "items", "product" );

  var Amounts = Sales.addMetric( "Sales Amount", "amount" );
Second dataset: 
  var Names = MyData.addDataSet( "Names", [ { code: 45, name: "Scissors" }, ... ] ),
        Codes = Names.addDimension( "Codes", "", "name" );
Linking the two datasets: 
  Sales.addLink( "product", Codes, "code" );
Now we can chart the sum of "sales" (first dataset) by "product name" (second dataset): 
  Chart1.fill( Codes, Amounts );

Links can be specified using a single field or multiple fields:

  // Link the two datasets using multiple fields:
  Dimension1.addLink( [ "field1", "field2" ] , Dimension2, [ "field3", "field4" ] );

Or using a custom "search" function that will called when the engine needs to access one dataset record from another dataset:

  var link1 = Dimension1.addLink( null , Dimension2, null );
  link1.search = function( data1, data2 ) { return data1.field1 == data2.field2; };

To navigate from a Dimension to another Dimension that are located at different datasets,
dimensions have a method that returns an array of "links" that configure a "chain of jumps",
from one dimension dataset to other datasets until arriving at the other dimension. 

  var links = Dimension1.getLinksTo( Dimension2 );

7. Additional Features

Selecting dimension elements (filtering)

Before executing a query, you can select specific items of a dimension to restrict the output results.
For example if you have a Products dimension, the following code will consider only the selected products:

  Products.selected = [ productsData[3].id, productsData[5].id ];
  Chart1.fill( Products, Sales );

Setting the "selected" property to "null" or empty array ( [] ) will clear the selection:
  Products.selected = null;

Inverting the selection (to consider all values except the selected ones) is defined by setting the selectedInclude property:
  // Exclude selected product 123, and include all the rest:
  Products.selectedInclude = false;
  Products.selected = 123;

Selection applies to any dimension and to any dataset, not only to the dimension being queried: 

  Products.selected = [ "23", "67", "89" ];   // filter one dimension
  Countries.selected = "Australia";           // filter another dimension
  Chart1.fill( Products, Sales );

Custom filtering can be done with a "select" function:
  Products.select=function(data) {
    return (data.product === 23) || (data.product === 45);
  }

Sorting output

Query results can be optionally sorted:


  // Sort by numeric values:
  Chart1.sort.sortBy = "values";
  Chart1.sort.order = "ascending";

  // Or sort by text labels:
  Chart1.sort.sortBy = "labels";

  // Execute query, sort data, and repaint the chart:
  Chart1.fill( Products, Sales);
For bi-dimensional queries, the second dimension items can also be sorted by name:
  Chart1.sort.series = "ascending";

Configuring Chart options

Charts are fully customizable, either before or after queries are executed.
Tee.Data extends standard TeeChart with several new properties and methods:

Before or after filling the chart, set the default series type:
  Chart1.setSeriesStyle( Tee.HorizBar );  // default is "auto"
  Chart1.fill( Products, Sales );
Event to perform additional changes when the engine creates a new series:
  Chart1.onnewseries=function(chart, series) { series.format.gradient.visible=true; }
Drill-down when clicking a series item:
  Chart1.onclickseries=function(series,index) {

    // Filter dimension by the clicked series point id:
    Products.selected = series.data.code[index];

    // Fill another chart using a sub-dimension (drill-down):
    Chart2.fill(Countries, Amount);
  }

Meta-data

Unique Id's of a dimension can be obtained as an array of strings or numbers:
  var productsIds = Products.getIds();
Object values of a dimension can be obtained as an array:
  var productsData = Products.getValues();
Finding an object with an specific Id in a dimension:
  var product123 = Products.get("123");
Tee.Data instances include an array of datasets:
  var dataset = MyData.datasets[3];
DataSets have an array of dimensions:
  var dim = MyData.datasets[3].dimensions[2];
Dimensions have an array of metrics:
  var metric = MyData.datasets[3].dimensions[2].metrics[0];
Dimensions also have an array of "sub-dimensions":
  var dim2 = MyData.datasets[3].dimensions[2].subdimensions[0];
And an array of links:
  var link1 = MyData.datasets[3].dimensions[2].links[3];

Other utility functions

There are several functions included in another script ( "tee-data-utils.js" ), used by this tutorial demos.

The fillTable function adds query results to an html <table> element:
  var result = MyData.query( Products, Amount );
      MyData.fillTable( "table1", result[0], true );

The tableToObject function converts an html <table> to a JavaScript object to use it as a dataset:
  var MyData = new Tee.Data(),
      personsTable = document.getElementById( "table1" ),
      Persons = MyData.addDataSet( "Persons", MyData.tableToObject(personsTable) );

8. All Examples

9. Unit Testing

 Automated testing is done using this scripted page: Unit Tests