Creating a dataset
Prerequisites
- Access to the Dataset management > Dataset type (DI007) menu.
- Access to the Dataset management > Dataset (DI006) menu.
- Previously configured database data source.
Introduction
A dataset is a collection of data extracted from both internal and external sources, with said collection being built from SQL commands.
Datasets incorporate their own REST API, enabling queries via HTTP requests.
The datasets created in SoftExpert Data integration are associated with forms from the Form component through lists of values that show data located externally to the system. Thus, their SQLs must be configured to display the information needed for the forms. Moreover, they can also be used in attributes recorded in the system.
See how to configure datasets:
Creating a dataset type
Before creating datasets, it is necessary to configure the types that will be used to classify them. Such types make it easier to organize the records in the system and allow for creating a hierarchy among them.
To create a dataset type, take the following steps:
1. Access the Dataset management > Dataset type (DI007) menu.
2. Click on the 
button.
3. Add an ID # and a name for the set.
4. If you wish for the type to be added hierarchically below another dataset type, fill in the Upper level type field.
5. Click on the 
button.
Creating a dataset
Additional information
• In the Construct query step, enter the SQL command that will define the search criteria and the data that will be used in the dataset being configured.
• A data source establishes a connection to the database and must be configured beforehand. If the user has permission, the 
button will be shown next to the field, allowing them to add a new data source.
• After the SQL command is inserted in the Construct query field, some buttons will be made available for use:
| Execute (Ctrl + Enter) | Click on this button to test the query inserted in the Construct query field. If any command errors occur, they will be shown on the screen. |
| Edit parameters | This button will be enabled only when there is a condition in the query whose value is variable. For this reason, use ":" after the statement to identify that the value must be a parameter. For example: SELECT IDLOGIN, NMUSER FROM ADUSER WHERE IDLOGIN =:ParamLogin Where 'ParamLogin' is a form parameter. If there are parameters in the query, when the Execute button is clicked, the system will automatically display the parameter screen, in which the values for the query execution simulation must be entered. |
| Sort data | When this button is clicked, a screen for setting the sorting parameters will be displayed. In the Field section, select the column (of the query) by which the data will be sorted, and, in the Orientation section, define whether the sorting will be ascending or descending. If you need to sort the data using a second column, click on Add new and insert the column and the sorting orientation. Then, click on Apply so that the sorting is applied to the query result. Note: Sorting the data will only be possible through the columns returned in the query (defined in the SELECT statement). If the SQL command is showing all columns (SELECT * FROM TABLE), all columns will be shown in the Field section. |
| Format | Click on this button for the system to apply a format (indentation) to the SQL command, making it more legible for future changes. |
• To add a user, team, department, or position to the security list, simply type the desired name in the available field and select the corresponding option from the list that will be displayed, or press "Enter" on your keyboard. To delete, click on the 
button located next to the desired record.
• Use the 
button, located on the toolbar of the dataset file menu, to simulate the dataset. When this button is clicked, a screen containing the values of the parameters of the dataset will open.
REST API for dataset queries
To query sets of analytical data through HTTP requests, an alternative is to use a REST API.
This application programming interface facilitates the interaction among different systems, which allows for obtaining structured information via endpoints, which are designated access points.
For this API, two verb categories can be used in requests: GET and POST.
Check the published APIs by clicking here.
Conclusion
Once the record is complete, it will be possible to simulate the dataset.