Crosstabs
Most database systems are able to evaluate statistical functions, produce frequency distributions and execute Group By queries. Whilst Engine supports this functionality via its NSQL Query language, it also offers a powerful alternative via its Crosstabulation Engine.
Introduction
The Crosstab object is one of Engine's key analytical objects. It allows rapid calculation of counts and statistics functions, as well as easy and flexible extraction and interpretation of results.
The Crosstab object is used when examining the distributions and intersections of data and provides a grid-like result set. The diagram below shows the result of a crosstab analyzing the gender distribution of data for the BRISTOL region (the data used is for demonstration purposes only).
Over-indexing
Engine makes use of over-indexing techniques to improve crosstab performance. This is where more than one index is built for the same set of data. The use of these techniques makes crosstab resolution one of the key strengths of Engine. Engine is much faster at producing crosstab results than standard RDBMS systems.
Types of Crosstab
Crosstabs can be Standard or Multi-dimensional. Each of those types can be the following sub-types:
- Count
- Comparative (Index/Difference)
- Functional
Operational Elements of a Crosstab
The following table lists the Analytic Elements associated with a crosstab.
| Element | Description |
|---|---|
| Owner Table | The crosstab is created from this table, and the table at which the crosstab is counted or summarized. |
| Axis | Column to be analyzed in the crosstab. Engine allows up to 16 Axes to be added to a multi-dimensional crosstab. Can be a domain, but domain must be permanent. Columns used as an X Axis can have a maximum of 512 discrete values. |
| Function Column | Numeric column to be summarized in the crosstab. Available functions are MIN, MEAN, MAX, SUM, RMEAN. Column must come from the ONE side of a link with the Owner Table. |
| Source Table(s) | The owner table(s) of the Function column and Axes. Note: a crosstab may have multiple source tables. |
| Target Filter | Domain to apply as a filter to the crosstab results. Up to 32 target filters can be applied. Target filters are used when calculating Index and Difference scores. All filters must be linked to the Owner Table. |
| Axis Filter | Domain to apply to an axis before it crosstabulated. Removes records from the Crosstab count before the crosstab is calculated. Must be linked to the column's Source Table. Must be specified as an NSQL string. |
| Base Filter | Domain to apply as a filter to all results. Determines the Universe for any Index and Difference calculations that are performed. Must be linked to the Owner Table of the crosstab. Can be specified as an NSQL string or an IDomain. |
