Chapter 55. Data sets authoring

A data set is a collection of related sets of information and can be stored 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.

55.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
    • Kafka: Generates a data set using metrics from Kafka broker, consumer, or producer

      Note

      You must configure KIE Server for Prometheus, Kafka, and Execution Server options.

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

    Note

    The configuration steps differ based on the provider you choose.

  4. Click Save.

55.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 data 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.

55.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.

55.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.

55.5. KIE Server data sets with Dashbuilder Runtime and Dashbuilder Standalone

A data set is a collection of related information. If you have a KIE Server that contains imported data sets, you can use Dashbuilder Runtime or Dashbuilder Standalone and the KIE Server REST API to run queries on imported data sets.

Because KIE Server uses Business Central as a controller, KIE Server containers are created in Business Central. Data sets are also created in Business Central. The KIE Server configuration is a template that you can refer to when you create data sets or install containers.

Other services, such as Dashbuilder Runtime and Dashbuilder Standalone, use the KIE Server REST API to retrieve KIE Server information. Dashbuilder Runtime and Dashbuilder Standalone access the KIE Server REST API to run queries from data sets.

When a KIE Server data set is created in Business Central, the server template information is provided and it is used by Dashbuilder Runtime and Dashbuilder Standalone to look for the KIE Server information. For example:

dashbuilder.kieserver.serverTemplate.{SERVER_TEMPLATE}.location={LOCATION}
dashbuilder.kieserver.serverTemplate.{SERVER_TEMPLATE}.user={USER}
dashbuilder.kieserver.serverTemplate.{SERVER_TEMPLATE}.password={PASSWORD}
dashbuilder.kieserver.serverTemplate.{SERVER_TEMPLATE}.token={TOKEN}

You can also setup KIE Server for each data set. For example:

dashbuilder.kieserver.dataset.{DATA_SET_NAME}.location={LOCATION}
dashbuilder.kieserver.dataset.{DATA_SET_NAME}.user={USER}
dashbuilder.kieserver.dataset.{DATA_SET_NAME}.password={PASSWORD}
dashbuilder.kieserver.dataset.{DATA_SET_NAME}.token={TOKEN}
Note

Token authentication is not used if credentials are provided.

You might want to run the dashboard against another KIE Server installation. When data sets are created on a KIE Server in a development environment, the data sets queries are created on the development KIE Server, for example DEV. If a dashboard is exported to a production environment, for example PROD, with a different KIE Server, the queries that you created in DEV are not available, so an error is thrown. In this case it is possible to port queries from a data set to another KIE Server by using the replace query functionality, either through a server template or a data set:

  • Server template example:

    dashbuilder.kieserver.serverTemplate.{SERVER_TEMPLATE}.replace_query=true
  • Data set example:

    dashbuilder.kieserver.dataset.{DATA_SET_NAME}.replace_query=true

The replace_query=true property only needs to be set once so that Dashbuilder Runtime or Dashbuilder Standalone creates the queries. After the queries are created you can remove this system property.