Skip to content

Using the API

Jump to bottom
Yeray Alonso edited this page Jun 27, 2017 · 4 revisions

Using TeeBI at low level

In this mode, its not necessary to compile and install any package. Just add TeeBI source code folders to your project (Project->Options->Delphi directories->Search Path), or to global environment Tools->Options, and start using TeeBI units.

The paths of TeeBI source code are:

Common base sources: ..\Sources

VCL specific sources: ..\Sources\VCL

Firemonkey specific: ..\Sources\FMX

Optionally, folder containing algorithm-related sources: ..\Sources\Algorithms

Simple demo

The following low-level features are included in a test project located inside the folder that contains this document:

Docs\Starting Guide\Samples\BI_Tests.dpr

Before opening this sample project, packages should be recompiled and installed using the TeeBIRecompile.exe tool that comes with TeeBI source code.

The TeeBIRecompile.exe tool adds a registry key to declare a data "Store" (see more about Stores later) named "BISamples", pointing to the folder "Sample Data" that comes with TeeBI installation.

The "Sample Data" folder contains a collection of popular datasets in different formats, already imported and persisted into native binary TeeBI data files.

These datasets are used by TeeBI demos and examples for many different purposes.

Class structure

The main class in TeeBI is called: "TDataItem"

TDataItem is simply a container for data, and can be configured in three different ways:

-As a "column" of simple data, like numbers, text, dates, etc (equivalent to a dataset "field").

-As a "grid" that has one or more "columns" (equivalent to a dataset "table").

-As a "list" of TDataItem (equivalent to a "database" that has a group of "tables").

The interesting thing here is that any "column" can also contain child columns, being each one of any of the above kinds. This makes possible to configure simple structures like plain tables, and also "unstructured" or hierarchical, like for example data coming from JSON, XML or NoSQL contents.

In some way, a TDataItem class resembles Python lists or Numpy ndarrays, or R DataFrames.

Example

An example project showing the different kind of structures that TDataItem can do, including master-detail 1:N relationships:

Creating TDataItem by code

First sample

Creating a column (field)

uses BI.Data;  // <-- main unit
  var Numbers : TDataItem;   // <-- instance
  Numbers:= TDataItem.Create;

  Numbers.Name:= 'Quantity';
  Numbers.Kind:= TDataKind.dkInt32;   // <-- type of data
  Numbers.Resize(1000);    // <-- number of elements (this simply resizes an internal array)

Filling data

Data inside TDataItem lives in simple arrays, one array for each different data kind.

var t : Integer;
for t:= 0 to Numbers.Count-1 do
      Numbers.Int32Data[t] := Round(123*Random);

Note: It is currently not possible to use language "generics" to reduce all data kinds to a single generic array.

This is the reason of having several XXXData array fields instead of just one Data array of generic type "T".

Creating a "grid" (table)

var Customers : TDataItem;
Customers:= TDataItem.Create(True);   // <-- True means it is a table
Customers.Name:= 'Customers';

// Add columns
Customers.Items.Add( 'CustomerID', TDataKind.dkInt32 );
Customers.Items.Add( 'Name', TDataKind.dkText );
Customers.Items.Add( 'BirthDate', TDataKind.dkDateTime );
Customers.Items.Add( 'City', TDataKind.dkText );
Customers.Items.Add( 'Salary', TDataKind.Single );

// Fill one row
Customers.Resize(1);

// Row zero values:
Customers['CustomerID'].Int32Data[0] := 1;
Customers['Name'].TextData[0] := 'Acme Inc';
Customers['BirthDate'].DateTimeData[0] :=EncodeDate(1967,4,13);
Customers['City'].TextData[0] :='Barcelona';
Customers['Salary'].SingleData[0] := 123.45;

Creating a "list" (group of data)

var Demo : TDataItem;
Demo:= TDataItem.Create;

Demo.Items.Add(Numbers);
Demo.Items.Add(Customers);

// This is equivalent also to do:
// Demo:= TDataItem.Create( [Numbers,Customers] );

Accessing Items (columns)

Using the default property of data, chaining consecutive names:

var D : TDataItem;
D:= Demo['Customers'] ['City'];

This is equivalent also to:

D:= Customers['City'];

The above syntax will raise an exception if a given name cannot be found in the items array.

If you wish to check the existence of a name without raising the exception, use the Find method instead:

if Demo['Customers'].Items.Find( 'City' ) then
...

Persisting data

The BI.Persist.pas unit contains classes to save and load TDataItem instances to any TStream, or to a disk file.

Data is divided in two sections:

  1. Structure (data name, data kind, children data, basic statistics etc)

  2. Data array contents (the real data in the arrays) and data "maps" (indexes)

These two sections can be saved to a single stream:

uses BI.Persist;
var M : TMemoryStream;
M:= TMemoryStream.Create;
TDataItemPersistence.Save( Demo, M );

...and then reloaded it later:

Demo.Free; // <-- forget existing data and release memory
M.Position:= 0;
Demo:= TDataItemPersistence.Load( M );

// Get variables again :
Customers:= Demo['Customers'];
Numbers:= Demo['Numbers'];

In a later part of this document (when describing the "High-Level" mode), data is always saved in two streams instead of a single one. One stream for the first "structure" part of data, and another stream for the data arrays.

The reason is: speed. The data arrays, which can be potentially huge, can be loaded into memory in a special "delay load" mode, that only reads data from disk at the time individual items are needed (just-in-time), instead of loading the whole (potentially huge) stream at once.

This delay-load mechanism also applies to data loaded from a BIWeb server (explained later).

Data relationships

A very important feature in TDataItem is the ability to define "links" between data.

These links information is also persisted when data is saved.

Links enable queries (explained later, see "Summary") that can automatically group data from independent "tables", without needing to care about "indexes", which are automatically created and maintained.

Links are expressed as "Master->Detail" relations. For example in the above code we can add a new table:

var Orders : TDataItem;
Orders:= TDataItem.Create(True);
Orders.Items.Add('Quantity', TDataKind.dkSingle);
Orders.Items.Add('Customer', TDataKind.dkInt32);

And then define a master->detail relationship (from detail Orders to master Customer), to indicate that any given Customer might have zero or more Orders:

Orders['Customer'].Master:= Customers['CustomerID'];

When importing data from databases or json/xml formats (see "Importing" later), any existing master-detail links are automatically detected and setup.

An special case of master-detail relationship is when a data in "table" mode is a detail of its parent. This allows embedding a detail dataset inside the same master table, instead of being the detail a separate table, something that happens for example when importing JSON array objects.

Missing data

TDataItem maintains a list of "missing" values ("null" data) in the Missing property:

var b : Boolean;
b:= Numbers.Missing[ 42 ];  // <-- Is Numbers column value at position 42 missing ?

// Set Numbers at position 123 to null:
Numbers.Missing[ 123 ]:= True;

Missing data will show as empty text in Grid cells, as empty content when exporting them, and it can optionally participate in summary aggregations (nulls considered as zeroes).

When importing data, missing values are automatically detected (when there is a way to detect them, depending on the import source format).

Expressions

The BI.Expression.pas unit implements a general-purpose expression parser and evaluator. Several small classes represent basic expression operators. This unit can be used outside TeeBI projects, as it has only few dependencies to Delphi RTL units.

The TExpression base class can be created from a text string, like:

ShowMessage( TExpression.Evaluate( ' (3 * 4) + ( 5 * 2 )' ).ToString );

Outputs: 22

or it can be directly created by (advanced) code if necessary, chaining each part of the expression to form a "expression tree":

uses BI.Expression;
var Sum : TArithmeticExpression;
Sum:= TArithmeticExpression.Create;
Sum.Operand:= TArithmeticOperand.Add;
Sum.Left:= TIntegerExpression.Create( 123 );
Sum.Right:= TFloatExpression.Create( 45.67 );

//... etc

ShowMessage( Sum.Calculate.ToString );

Operators supported in expressions include logical (=, <>, <, >, >=, <=, and, or, not, in), mathematical (+, -, *, /, mod, power, sqrt, log, exp, sin, cos, round, trunc, etc), text (lower, upper, trim, length, etc), date-time part extraction (Year, Month, Weekday, Hour, Minute, etc) and so on.

Custom expression functions and operators can be easily created deriving small classes from the existing ones.

Beta note: TExpression support for multiple-parameters in functions is not yet available.

TeeBI uses this expression engine to support TDataItem data as variables inside expressions, thus allowing data columns or tables to participate in the expression calculations.

These TDataItem-enabled expressions can be used in several places:

  1. Calculating extra "columns" (calculated fields)

Orders.Items.Add('Total', 'Quantity * Price');

  1. Using a expression as the values to Sort a table or column by:

Orders.SortBy( 'Quantity * Price' );

  1. Using a logical (true / false) expression to Filter rows of a column or table:

Cursor.Filter:=TDataExpression.FromString(Products, '(Price>5) and (Price<=8)');

  1. Using an expression to calculate GroupBy summaries, and Measure aggregations (see "Summary" later).

Sorting data

The TDataItem SortBy method allows sorting data (a single column or all rows in a table), by a single data item, like:

Orders.SortBy( Orders['Quantity'] , True );   // <-- ascending order

or using an expression like explained above:

Orders.SortBy( 'Quantity * Price' );

Sorting effectively reorders the underlying data arrays.

Using a TDataCursor (more about it later), it is possible to sort data using more than one column and/or expressions, where each sort item can be defined as ascending or descending.

Using a TDataCursor to sort data, does not alter the internal data arrays order.

Filtering data

TDataCursor provides a Filter property, which is of type TExpression and must be a "logical" expression (an expression that returns True or False).

When evaluating the expression for each row in the data to filter, all rows that return "True" will be included in the final output.

For example:

uses BI.DataSource, BI.DataSet;

BIDataSet1.Data:= Orders;
BIDataSet1.Cursor.Filter:=TDataExpression.FromString(BIDataSet1.Data,'(Quantity>5) and (Price<=100)');

TeeBI web site

Installing

Introduction

Using TeeBI

Importing Data

Clone this wiki locally