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
This post has been migrated from a previous version of the PostgreSQL website. We apologise for any formatting issues caused by the migration.