For the given sample SWIFT MT940 report, we need to create the datasources. Let's assume there are two datasources in Studio for the STATEMENT MASTER and STATEMENT TRANSACTION tables. We need to aggregate these two datasources using the primary key-foreign key relationship. Here, the two tables are related by the ACC_IDENTIFICATION field.
Perform the following steps to build data source aggregator for combining the two datasources in Sigma Studio:
Click Data Builder.
Info The Data Builder menu option appears only when the CONNECTIONS on the left navigation is selected.
.png?version=1&modificationDate=1635416729099&cacheVersion=1&api=v2&width=1225)
A page appears where the new data source aggregator can be created easily through drag-and-drop..png?version=1&modificationDate=1635416729578&cacheVersion=1&api=v2&width=1225)
- Enter a unique ID and Name for the data source aggregator. Here, DB_BUILDER is used as the ID and Name for the data source aggregator.
.png?version=1&modificationDate=1654249172515&cacheVersion=1&api=v2&width=900)
- Select Parent Data source from Data Source ID drop-down list, which appears on the right-hand side of the page, e.g.
STMT_TRANS_MASTER..png?version=1&modificationDate=1654249419184&cacheVersion=1&api=v2&width=1139)
- Drag and drop another data from the left-hand side of the page and connect the parent data source with this data.
.png?version=1&modificationDate=1654249552085&cacheVersion=1&api=v2&width=709)
Click the data element and select data source ID from the Data Source ID drop-down list. For example, Statement Transaction is STMT_TRANS_DETAIL is chosen as the data source.
Note The Data Source ID selected can either be a database or a web service data source made for the report.
Select how the data source should be attached and resulted in accordance with the parent data source.
Info - Merge combines the data sources, where the duplicate data items are eliminated.
- Join combines data based on the following selection:
- If you click Equi Join, only the similar entries on datasources will be displayed based on the provided key column.
- If you click Left Join, all entries on the left datasource and matching records from the right datasouce will be displayed based on the provided key column.
- If you click Right Join, all entries on the right datasource and matching records from the left datasouce will be displayed based on the provided key column.
- Union combines the data into new rows.
Similarly,
- Record displays the selected data source same as the parent data source is displayed.
- Children displays the attached data source as a child for the parent data source.
- Object displays the attached data source just as an object.
.png?version=1&modificationDate=1654250045241&cacheVersion=1&api=v2&width=985)
Select Parent Column and Child Column from the drop-down list. For example, ACC_IDENTIFICATION is chosen as the parent and the child column in this data builder.
Info These will be identified as unique key columns between the two data sources.
.png?version=2&modificationDate=1654250172680&cacheVersion=1&api=v2&width=1019)
Click the Save
%20-%202.png?version=1&modificationDate=1635416727442&cacheVersion=1&api=v2)
icon to save the data aggregator.Warning When you try to exit the Data Builder screen, before saving the configured data source aggregator, a warning pop-up message appears, as shown in the following screen shot:
%20-%202.png?version=1&modificationDate=1648757145113&cacheVersion=1&api=v2&width=296)
- Click Cancel, if you do not want to exit the Data Builder screen.
- Click Ok, if you want to exit the Data Builder screen.
.png?version=1&modificationDate=1654250224480&cacheVersion=1&api=v2&width=1225)
A confirmation message appears..png?version=1&modificationDate=1635416726965&cacheVersion=1&api=v2&width=363)
- Click Ok for the message.
...