Example: Merge data with a virtual data source

Overview

In this example, you use two different systems of the same type and you would like to get a unified number or data set around them. To keep track of their everyday tasks:

• The Marketing team is using Basecamp

• The HR team is using Asana.

You want to have a single insight that helps you track the progress from one place.

Approach 1: Merge the tasks from the insight

When you want to merge data from the two systems, you can use the following SQL query in the insight:

SELECT  SUM(tasks) totalTasks
FROM
(
    SELECT COUNT(*) as tasks FROM asana_tasks
    UNION ALL
    SELECT COUNT(*) as tasks FROM basecamp_todos
) 

If you need to apply this logic across multiple insights, for each one of them you have to apply the UNION. If any of the entities change or you incorporate another task management system, you have to go over all the insights and apply the changes.

Approach 2: Merge the tasks with a virtual data source

By creating a virtual data source, you can control the changes from a single place.

Perform the following:

1. Create a virtual data source for the tasks.
   Define its key as all_tasks.
   For more information, see Create virtual data sources.

2. In the SQL editor, enter the same SQL query as in the first approach.
   This way, your virtual data source combines data from the two systems. You can reuse all_tasks in your insights.

3. In the SQL of the insights, use the following:
   SELECT * FROM all_tasks

For more information, see Create insights with SQL.