How to create a report from multiple data sources?

This document will assist you in joining two tables - one from a Google Sheet file and another from SQL Workspace - to create a comprehensive report.

This is your data table from the SQL Workspace:

This is your data table from Google Sheets:

Google Sheets

Below is a step-by-step guide on how to create a data flow that joins these sources together.

Step 1: Create data sources

To create a data source, please refer to this guide.

Notes: Please proceed to create two distinct data sources - one for the SQL data table and another for the Google Sheets document.

Step 2: Create data destinations

To create a data destination, please refer to this guide.

Notes: To create a data source for a report, choose the corresponding option in the data flow settings, and refrain from creating a data destination for it.

Step 3: Create the data flow

Hover your mouse over Data, choose Batch stream.

Click on the + button to initiate the creation of a new data flow.

Choose 1 of the 2 data sources that you created.

Choose the fields you need in the first data source.

To incorporate additional data sources into your dataflow, choose the Add Data node. Then select the expected data sources.

Notes: Please refer to this guide for working on the data source node.

Also choose the fields you need in the second data source.

Once you have set up the required data sources, utilize the Join action node to combine these sources based on specific conditions.

Notes: Please refer to this guide for working on the 'Join' action node.

Once you have joined the data sources, utilize the 'Branch' function to divide the joined data into separate data destinations.

Add 'Branch' node to break the data destinations

Notes: Please refer to this guide for working on the 'Branch' action node.

To push the joined data for creating a report, utilize the (1) data destination. Alternatively, if you intend to push the joined data to a Business Object, use the (2) data destination.

To add a Business Object destination, click the + button at a branch, and choose the destination.

To filter data and direct it to the appropriate data destination, you can achieve this by writing queries using the Filter node.

Before proceeding to apply filters, it's essential to grasp the context. Your objective is to join data from multiple sources, where unmatched data will be imported to Business Object, and matched data will be used to create the report.

This matching result is achieved by applying an All Row condition, which matches data based on email addresses.

To filter out customers who do not exist in the SQL data table but exist in Google Sheets, apply the filter condition Email is null.

To filter out customers who exist in the SQL data table but do not exist in Google Sheets, apply the filter condition Email address is null.

Notes: Exercise caution while applying filtering conditions using "include" and "exclude" to avoid unintended data omissions.

Result

Navigate to the Data Sources of Insights, and find your Save Data.