Chapter 5. Data sets authoring
A data set is a collection of related sets of information. It can be stored in many ways, such as 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 could 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. This data set definition specifies the location of the data set, the ways to access it, read it, and parse it, and the columns that it contains.
The Data Sets page is visible only to users with the admin role.
5.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
-
In Business Central, go to Admin
Data Sets. The Data Set Explorer page opens. Click New Data Set and select one of the following provider types:
- Bean: Use to generate a data set from a Java class
- CSV: Use to generate a data set from a remote or local CSV file
- SQL: Use to generate a data set from an ANSI-SQL compliant database
- Elastic Search: Use to generate a data set from Elastic Search nodes
Execution Server: Use to generate a data set using the custom query feature of an Execution Server
NoteKIE Server must be configured with this option.
Complete the Data Set Creation Wizard and click Test.
NoteThe configuration steps differ based on the provider you chose.
- Click Save.
5.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
-
In Business Central, go to Admin
Data Sets. The Data Set Explorer page opens. - In the Data Set Explorer pane, search for the data set you want to edit and click Edit.
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 Section 5.4, “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.
- After making the required changes, click Validate.
- Click Save.
5.3. Data refresh
The data refresh feature 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.
5.4. Caching
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
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.
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.