Multi-Table Query Source provides the functionality to retrieve data from a data model or a virtual database. Additionally, it allows you to design Filters, specify Where Clause, and set Sort Order to control the result set.
Using Multi-Table Query Source
You can use a Multi-Table Query Source to fetch data from a data model or a virtual database. To call a data model, you will have to provide the path of the folder where the data model is saved. In case of a virtual database, you will have to provide the connection of the database where the virtual data model is deployed, and the root element which is the starting point for the mapping tree, Filter criteria, Where Clause, and Sort Order.
To add a Multi-Table Query Source in your integration flow, locate the element in the toolbox under the Sources group, and drag-and-drop it into your flow.
To configure a Multi-Table Query Source object, right-click on it and select Properties from the context menu. The following properties are available:
Data Model is selected as the default Model Type. If you want to continue with it, then provide the Model Path by clicking on the Browse Files icon at the right side of the Model Path box.
If you want to provide a database connection different than the one used in the data model, then put a check on Override Database Connection in the Model below the Model Path.
Click on the dotted box to configure the database connection.
This will open the Database Connection window, where you can specify the database with which you want to override the connection.
Next, you have to select the Root element from the entities that you have in your data model. In the data model that we have specified in the screenshot, there are 2 entities: Products and Suppliers. Therefore, you can see both in the drop-down menu of Root. You can select either of these depending on which entity you want to see in the mapping tree and use it for Filter, Where Clause, and Sort Order.
To use a virtual database in the Multi-Table Query Source, select Virtual Database in the Properties window.
Now, click on the dotted box to specify where the virtual data model is deployed.
You can either specify a recently used connection or a new one. Select Astera Data Model as the Data Provider, the Server on which the virtual data model is deployed, and finally the Virtual Database that you want to use in the Multi-Table Query Source. Once you have specified the connection, test it, and click Ok after the test is successful.
Now, select the Root element from the drop-down menu. In the screenshot below, the virtual data model ‘VDM_Test’ contains 2 entities that you can see in the drop-down of Root. For now, we will select ‘Orders’.
Now, click on Next to go to the Query Source Layout window. On the left, you can see the mapping tree, and on the right, you can apply Filter, write Where Clause and set Sort Order.
You can filter the records by clicking on Add a Filter to the Query icon.
Provide the Path of the field on which the filter will be applied. For this example, we have selected ‘EmployeeID’.
Now, provide the criteria based on which the filter will work. The available options are Equals to, less than and Equals to, and greater than and Equals to. Apart from these, the Other option is also available for the Filter criteria.
Let’s select equals to 5 for now. You can preview the output of Multi-Table Query Source to view the filtered record set.
In the Where Clause screen, you can write queries on any field in the table as shown in the screenshot below.
To sort the record set of a field, click on the Add a Field to Sort By to the Query icon in the Sort Order window.
Now, select a field in Path and apply the ascending or descending order in Sort Order. You can preview the output of Multi-Table Query Source to view the sorted data.