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, these specific features become 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.
- You can sort and filter lists by any field included in the replication set, including user fields, related fields, and derived fields.
- Lists of up to 500 records are printed to CSV or PDF, and the grand total is included.
- Charts are available by default without any configuration.
- You can change dynamically at runtime a chart's Dimension or the x-axis, Total or the y-axis, and Order.
- You can filter charts by any field included in the replication set, including user fields, related fields, and derived fields.
- Aggregation tables show any number of aggregations, such as sums, averages, minimums, maximums, and instance counts, broken down by any number of dimensions.
- Columnar aggregation tables support the show grand total and suppress grand total LPL syntax, allowing them to either display the grand total without subtotals or hide the grand total when one or more subtotals are defined. By default, a grand total is shown only when one or more subtotals are defined.
- Any field included in the replication set, including user fields, related fields, and derived fields, is used as an aggregation table dimension.
- Aggregation tables can include roll-ups of any subset of dimensions.
- You can page aggregation tables backwards and forwards.
- Database level joins are used when needed.
See Columnar Joins.
- REST APIs are supported for columnar tables.
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 only uses 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 does not result in a database level join being used. This is because the related field is already included on the same columnar table as the other fields on the list. Database level joins are not used if a related field is only included as a display field and are 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 uses a database join, you must include all fields used in the relation definition of the related field in the replication sets of the related business classes. If any field is missing from the replication set definitions, the userl receives an error identifying the missing field.
Multiple relations, A to B to C, and dependent relations, relations within a relation definition, are supported. In the case of multiple relations, you must replicate every intermediate business class 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, you must replicate the dependent related field and all fields used in the dependent relation.
REST API Support
Columnar lists, chart data, and aggregation tables are consumed by REST APIs. Both ldrest, which is dataview based, and the raw API, which is business object iterators 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 depends on what view has the setting in the LPL. If no view is set to , it returns the list view. The list view returns the total row as the first record.
If an aggregation table is set as the default view then the _fields parameter is ignored. Some values returned in an aggregation table are only correct in the context of the full list of dimension fields, so all fields in an aggregation table are always returned in a REST API call.
Columnar Table Setup and Requirements
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.