Chapter 54. Data sets authoring

A data set is a collection of related sets of information and can be stored in many ways. For example, in a database, in a Microsoft Excel file, or in memory. A data set definition instructs Business Central methods to access, read, and parse a data set. Business Central does not store data. It enables you to define access to a data set regardless of where the data is stored.

For example, if data is stored in a database, a valid data set can contain the entire database or a subset of the database as a result of an SQL query. In both cases the data is used as input for the reporting components of Business Central which then displays the information.

To access a data set, you must create and register a data set definition. The data set definition specifies the location of the data set, options to access it, read it, and parse it, and the columns that it contains.

Note

The Data Sets page is visible only to users with the admin role.

54.1. Adding data sets

You can create a data set to fetch data from an external data source and use that data for the reporting components.

Procedure

  1. In Business Central, go to AdminData Sets.

    The Data Sets page opens.

  2. Click New Data Set and select one of the following provider types:

    • Bean: Generates a data set from a Java class
    • CSV: Generates a data set from a remote or local CSV file
    • SQL: Generates a data set from an ANSI-SQL compliant database
    • Elastic Search: Generates a data set from Elastic Search nodes
    • Prometheus: Generates a data set using the Prometheus query
    • Execution Server: Generates a data set using the custom query feature of an Execution Server

      Note

      You must configure KIE Server for Execution Server option.

  3. Complete the Data Set Creation Wizard and click Test.

    Note

    The configuration steps differ based on the provider you choose.

  4. Click Save.

54.2. Editing data sets

You can edit existing data sets to ensure that the data fetched to the reporting components is up-to-date.

Procedure

  1. In Business Central, go to AdminData Sets.

    The Data Set Explorer page opens.

  2. In the Data Set Explorer pane, search for the data set you want to edit, select the data set, and click Edit.
  3. In the Data Set Editor pane, use the appropriate tab to edit the data as required. The tabs differ based on the data set provider type you chose.

    For example, the following changes are applicable for editing a CSV data provider:

    • CSV Configuration: Enables you to change the name of the data set definition, the source file, the separator, and other properties.
    • Preview: Enables you to preview the data. After you click Test in the CSV Configuration tab, the system executes the data set lookup call and if the data is available, a preview appears. Note that the Preview tab has two sub-tabs:

      • Data columns: Enables you to specify what columns are part of your data set definition.
      • Filter: Enables you to add a new filter.
    • Advanced: Enables you to manage the following configurations:

      • Caching: See Caching for more information.
      • Cache life-cycle Enables you to specify an interval of time after which a data set (or data) is refreshed. The Refresh on stale data feature refreshes the cached data when the back-end data changes.
  4. After making the required changes, click Validate.
  5. Click Save.

54.3. Data refresh

The data refresh feature enables you to specify an interval of time after which a data set (or data) is refreshed. You can access the Data refresh every feature on the Advanced tab of the data set. The Refresh on stale data feature refreshes the cached data when the back-end data changes.

54.4. Caching data

Business Central provides caching mechanisms for storing data sets and performing data operations using in-memory data. Caching data reduces network traffic, remote system payload, and processing time. To avoid performance issues, configure the cache settings in Business Central.

For any data lookup call that results in a data set, the caching method determines where the data lookup call is executed and where the resulting data set is stored. An example of a data lookup call would be all the mortgage applications whose locale parameter is set as "Urban".

Business Central data set functionality provides two cache levels:

  • Client level
  • Back-end level

You can set the Client Cache and Backend Cache settings on the Advanced tab of the data set.

Client cache

When the cache is turned on, the data set is cached in a web browser during the lookup operation and further lookup operations do not perform requests to the back-end. Data set operations like grouping, aggregations, filtering, and sorting are processed in the web browser. Enable client caching only if the data set size is small, for example, for data sets with less than 10 MB of data. For large data sets, browser issues such as slow performance or intermittent freezing can occur. Client caching reduces the number of back-end requests including requests to the storage system.

Back-end cache

When the cache is enabled, the decision engine caches the data set. This reduces the number of back-end requests to the remote storage system. All data set operations are performed in the decision engine using in-memory data. Enable back-end caching only if the data set size is not updated frequently and it can be stored and processed in memory. Using back-end caching is also useful in cases with low latency connectivity issues with the remote storage.

Note

Back-end cache settings are not always visible in the Advanced tab of the Data Set Editor because Java and CSV data providers rely on back-end caching (data set must be in the memory) in order to resolve any data lookup operation using the in-memory decision engine.