Sigma enables you to create reports by combining different data sources using several join operations in Studio. For instance, the following screenshot shows a report, Account Details Report created combining Transaction and Address data sources using join operation:
Perform the following steps in Sigma Studio to combine data sources using JOIN option:
- Click Data Builder.
%20-%202.png?version=1&modificationDate=1637494847076&cacheVersion=1&api=v2&width=811)
The Data Builder menu option appears only when the CONNECTIONS on the left navigation is selected.
The Data Builder screen appears.
- Enter the Name and ID of the data source aggregator in the Name and ID fields. Here, ACCOUNT DETAILS is used as the name for data source aggregator.
.png?version=1&modificationDate=1658482283309&cacheVersion=1&api=v2&width=719)
- Select Parent Data source from Data Source ID drop-down list, which appears on the right-hand side. For example, TRANSACTION is chosen as the data source.
.png?version=1&modificationDate=1658809084837&cacheVersion=1&api=v2&width=900)
- Drag and drop another data from the left-hand side and connect the parent data source with this data.
.png?version=1&modificationDate=1658809163933&cacheVersion=1&api=v2&width=605)
Click the data element and select data source ID from the Data Source ID drop-down list. For example, ADDRESS 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.
.png?version=1&modificationDate=1658809295464&cacheVersion=1&api=v2&width=900)
- Click Join. For more details , refer Typed of Joins.
.png?version=1&modificationDate=1658809554271&cacheVersion=1&api=v2&width=900)
- Select any one of the Join options. Here, Left Join is selected.
%20-%202.png?version=1&modificationDate=1658810342305&cacheVersion=1&api=v2&width=900)
- On using the Left Join option in a data source aggregator to configure a report, all entries on the left data source and matching records from the right data source will be displayed based on the provided key column. For example, consider the report shown on the following screenshot. Here, Account Number 5500000852 is the additional entry on TRANSACTION data source. However all entries from the TRANSACTION (left) data source and matching records from the ADDRESS (right) data source are displayed based on the provided key column (Account Number).
- On using the Equi Join option in a data source aggregator to configure a report, only the similar entries on data sources will be displayed based on the parent and the child column selected. For example, consider the report shown on the following screenshot. Here, Account Number 5500000852, the additional entry on TRANSACTION data source will be eliminated and only the matching data from the two data sources will be displayed based on the provided key column (Account Number).
- On using the Right Join option in a data source aggregator to configure a report, all entries on the right data source and matching records from the left data source will be displayed based on the provided key column. For example, consider the report shown on the following screenshot. Here, Account Number 5500000852 is the additional entry on TRANSACTION data source. Thus the additional entry on TRANSACTION (left) data source will be eliminated and all records from the ADDRESS (right) data source are displayed based on the provided key column (Account Number).
In the Join On Keys section, select the Parent and Child columns from the respective Parent Column and Child Column lookup fields.
Note It is mandatory to select the Parent and Child columns from the respective Parent Column and Child Column key fields in the Join On Keys section. In the provided example, ACCOUNT_NO is selected as the Parent and the Child column. The Parent and Child columns can either be the same columns from the different data sources or different columns from different data sources. It must be ensured that the data sets of the parent and child columns match accurately with each other.
.png?version=1&modificationDate=1658810384701&cacheVersion=1&api=v2&width=900)
If required, you can select the Parent Column and Child Column from the respective Parent Column and Child Column lookup fields in the Filter Mapping section.
Note In the provided example, ACCOUNT_NO and ACCOUNT_NO are the respective Parent and Child columns. The Parent Column and Child Column options in the Filter Mapping section are optional fields. You can make use of the Filter Mapping option, only if you want to map the parent and child columns from the different data sources. The Parent and Child columns can either be the same columns from the different data sources or different columns from different data sources. It must be ensured that the data sets of the parent and child columns match accurately with each other. On making use of the filter mapping option, the data filters will get applied for the selected parent and column child columns, thereby showcasing the filtered data sets of the parent and child columns on using the configured data source aggregator in a report. However, it is essential that you select the Parent Column (e.g. ACCOUNT_NO) as a filterable column while configuring the report using the Information Report screen.
.png?version=1&modificationDate=1658811396128&cacheVersion=1&api=v2&width=900)
- Click the Save (
) icon to save the data source aggregator..png?version=1&modificationDate=1658811446866&cacheVersion=1&api=v2&width=900)
A confirmation message appears.
- Click Ok.
- Now, proceed with the configuration and generation of the report. Refer Configuring a Complex Report and Generating Complex Report in Sigma for more information on configuring and generating the report.