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:
- Create a virtual data source for the tasks.
Define its key asall_tasks
.
For more information, see Create virtual data sources. - 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 reuseall_tasks
in your insights. - In the SQL of the insights, use the following:
SELECT * FROM all_tasks
For more information, see Create insights with SQL.