Columnar Tables

Columnar tables use the column store format, which is a data storage format commonly used for data warehousing and analytics. The basic advantages of columnar are a data compression ranging around 98% compared to normal row store data formats, the ability to perform hyper-fast aggregations on the fly, and the ability to insert large batches of data quickly. This makes it useful for data that is immutable or nearly immutable, high volume, and in need of aggregate type operations. The basic disadvantage is a significantly worse ability to create, read, update, and delete records on a single row basis, making it a poor format for data that is frequently changed.

Key Features

In Landmark, if a list or form is set up to use a columnar table and the user interface is either Infor Rich Client or SoHo 4 UX, the following specific features are available.

  • Lists show the number of records found and the last refreshed date.
  • Lists show the grand total on the first line for any "Total" field.
  • Lists can be sorted and filtered by any field included in the replication set, including user fields, related fields, and derived fields.
  • Lists of up to 500 records can be printed to CSV or PDF, and the grand total will be included.
  • Charts are available by default without any configuration.
  • A chart's Dimension (x-axis), Total (y-axis), and Order can be changed dynamically at runtime.
  • Charts can be filtered by any field included in the replication set, including user fields, related fields, and derived fields.
  • Aggregation tables show any number of aggregations (sums, averages, minimums, maximums, and instance counts) broken down by any number of dimensions.
  • Any field included in the replication set, including user fields, related fields, and derived fields can be used as aggregation table dimensions.
  • Aggregation tables can include roll-ups of any subset of dimensions.
  • Aggregation tables can be paged backwards and forwards.
  • Database level joins are used when needed. For more information, see Columnar Joins.
  • REST APIs are supported for columnar tables. For more information, see REST API Support.
  • For business classes that have child or parent hierarchies, the columnar tables created for these can flatten the hierarchies, enabling the tables to be more readily used by Birst.

Columnar Joins

Landmark will only use a database level joins if the related field is not included in the replication set but is used in a filter, an instance selection, a chart, or an aggregation table, or if the related field is a total field or is used as the sort field.

A related field included in the replication set definition for the business class on which the list is defined will not result it a database level join being used. This is because the related field will already be included on the same columnar table as the other fields on the list. Database level joins are also not used if a related field is only included as a display field and is not used in any filtering, sorting, or aggregation. This is because the restricted page size of columnar lists makes it is more efficient to simply individually query the related table for each record included in the result set.

When Landmark decides to use a database join, all fields used in the relation definition of the related field must be included in the replication sets of the related business classes. If any field is missing from the replication set definitions, the user will receive an error identifying the missing field.

Multiple relations (A to B to C) and dependent relations (relations within a relation definition) are supported. However, in the case of multiple relations every intermediate business class must be replicated with all fields used in both the relation from A to B and the relation from B to C. In the case of dependent relations, the dependent related field and all fields used in the dependent relation must be replicated.

REST API Support

Columnar lists, chart data, and aggregation tables can be consumed by REST APIs. Both ldrest (dataview based) and the raw api (business object iterator based) are supported.

The call needs to be structured the same as a call to retrieve data from a normal list. The data that is returned will depend on what view has the "is default" setting in the LPL. If no view is set to "is default," it will return the list view. The list view will return the total row as the first record.

If an aggregation table is set as the default view then the _fields parameter will be ignored. This is because some values returned in an aggregation table are only correct in the context of the full list of dimension fields, and so all fields in an aggregation table will always be returned in a REST API call.

Columnar Table Setup and Requirements

Landmark supports columnar tables without any additional database configuration if you are use Microsoft SQL Server 2016 or higher. If your Landmark database is not Microsoft SQL Server 2016 or higher but you have such a database available> you can configure it so that you can use columnar tables. See Setting Up a Database for Columnar Store.

To use a columnar table, you must use data replication with the Export Format set to Columnar, and the LPL for the list definition must include "implements ColumnarView". For more information, see Implementing Columnar Tables.