Analytic SQL Server - next generation analytic Data Warehouse with OLAP support

I am pleased to announce first commercial use of Analytic SQL Server
(ASQL) based on PostgreSQL.


Analytic SQL Server is implementation of the next generation Data
Warehouse Model (for now available only for PostgreSQL server), with
full OLAP functionality and analytical processing support
(Mathematical Models, Statistics Models etc.) on the SQL server-site.

The main feature of ASQL is building very large scale BI (Business
Intelligence) and AI (Analytic Intelligence) solutions, forgetting the
known limitations of today's systems, widely used in:
* Financial Sector (Controlling, Budgeting, Forecasting; MiFID);
* Banking (BASEL II, credit scoring, forecasting, frauds, money laundering);
* Insurance (Solvency II);
* Healthcare Industry;
* General Purpose Data Analysis;

First commercial use of Analytic SQL Server is the application called
SART (System for Transaction Registry and Analysis) - used in
data-gathering and advanced analysis of bank transactions (including
cash-flow chains), in terms of the prevention of the use of the
financial system for the purpose of money laundering and terrorist
financing (EU directive implementation: 2006/60/EC).

Full directive text at:
http://europa.eu.int/eur-lex/lex/LexUriServ/site/en/oj/2005/l_309/l_30920051125en00150036.pdf).

After 15 December 2007 most of the financial institutions (including
banks) in EU have to implement this directive.


System implementation is being performed by company TETA SA in one of
the polish banks. (at this point, Data Warehouse deployment phase has
been finished).

Business Contact:
   Wojciech Merchelski
   e-mail: w.merchelski(at)teta(dot)com(dot)pl
   www.teta.com.pl

For any questions regarding technical background of ASQL, please contact me:
   Blazej Oleszkiewicz
   e-mail: blajan(at)poczta(dot)fm



Below there is a short description of Data Warehouse module and OLAP
functionality in Analytic SQL Server.

o ROLAP storage models in Analytic SQL Server
Data Warehouse module in ASQL supports all known schemas:
 * star schema
 * snowflake schema
 * fact-constellation schema

The existing ROLAP data structures in ASQL may be easily integrated in
a database-driven applications (for example: use of dimensions as
dictionaries in application).

o ROLAP dimensions in Analytic SQL Server
In ASQL dimensions are based on Hierarchical Tables (SQL data
structures enabling tabular or hierarchical data processing, in
addition hierarchical data may be processed recursively or
iteratively).

Dimensions in Analytic SQL Server
 * Full compatibility with classic OLAP dimension model (in ASQL these
are uniform dimension);
 * Defining non-uniform dimension;
 * Any element in dimension is identifiable (ASQL provides full
dimension granularity).

Differences between uniform and non-uniform dimensions are explained
by the following example:

Organization structure in company (uniform dimension):
* Company
 * Division
  * Branch
   * Section
    * Group
     * Employees
Every employee has particular role in organization structure [Company,
Division, Branch, Section, Group].

Organization structure in company (non-uniform dimension):
* Company
 * Division
  * Employees
  * Branch
   * Employees
   * Section
    * Employees
    * Group
     * Employees
* Branch of a company 1 (in other Country)
 * Branch
  * Employees
  * Section
   * Employees
* Branch of a company 2 (in other Country)
 * Branch
  * Employees

In this example every employee may belong to any node in organization
structure, for example:
[Company, Division],
[Company, Division, Branch, Section, Group],
[Branch of a company, Branch, Section].

Important fact is: in ASQL every element in hierarchy is identifiable,
so data agregation in ROLAP cubes works on every dimension level.

o Fact tables in Analytic SQL Server
Fact tables in ASQL are the same as the other ROLAP Data Warehouse
systems and may by partitioned using native PostgreSQL interface.

o ROLAP Cubes in Analytic SQL Server
 * Full ROLAP Cubes;
 * Partial ROLAP Cubes;

Full ROLAP Cubes represents the Cartesian product of all the cube
dimensions with the fact-based measure.
Partial ROLAP Cubes represents the Cartesian product of all the facts,
from the fact table, with their sub dimensions from dimensions, and
the fact-based measure.

ROLAP cubes stores data aggregated from all sub-dimensions making the
cube. (feature offered by MOLAP systems).

Defining ROLAP cubes on any dimension level - example: for time
dimension [Year, Quarter, Month, Day, Hour] it is possible to define
the cube aggregating on months level [Year, Quarter, Month], with no
need to redefine the dimension.

Partitioning ROLAP cube tables is supported, using native PostgreSQL interface.

Rebuilding the cubes is done in the background, full or incremental
and do not requires stopping the database service.

o Additional DataMart ROLAP structures in Analytic SQL Server
 * Vector aggregates;
 * Hierarchical vector aggregates;
 * Chains aggregates.

Vector aggregates are used for:
 * Subject oriented Data Mart Cubes;
 * Grouping data sets (customers groups, products grups).

Hierarchical vector aggregates are used for grouping data sets in
hierarchical contex.
Chains aggregates are data structures allow storing data chains, for
example cash-flow chains, supply chains and logistics, WorkFlow
state-change chains.

o Analytic SQL Server support OLAP operation on the database server:
 * Pivoting;
 * Drill Down;
 * Roll Up;
 * Slicing;
 * Dicing;
 * Drill Across, Drill Through;
 * Unroll vector to table/tree;
 * Rollup table/tree into vector;
 * Bulk vector operation.

Very important fact is, Drill Down/ Roll Up operations, are based on
the native PostgreSQL SQL language syntax, and don't require any
additional SQL clause like CUBE, ROLLUP (in ASQL there is no need to
implement these SQL statements in the form ORACLE does, they are even
undesirable).

Regards,
Blazej Oleszkiewicz