Overview
Virtual data sources allow you to develop insights faster and control complex logic from a single point. The virtual data sources are a tool to merge or filter data that you use when creating insights.
If you are familiar with Excel formulas, a good analogy would be: a cell in Worksheet 3 that uses a formula to sum numbers from cells in Worksheet 1 and 2.
Example
In the following diagram, all_tasks merges in one field all task that are done from Asana and Basecamp:
Process
The following is a standard flow of using a virtual data source:
Add new virtual data sources and give it a key.
Afterwards, you will use the key in the SQL of insights.Write the SQL that retrieves, manipulates, filters, and merges in the virtual data source.
When developing new insight, reuse the data from the virtual data source through the key.
Create the key
To define the name and the key of the virtual data source, perform the following:
In the navigation pane, Click Settings then click Data sources.
In the toolbar, click Select data source and click Create virtual data source.
Enter a name of the virtual data source.
Create a description of what this virtual data source will do.
In the input field enter the key. For example, enter all_tasks
You can use lower case Latin letters and underscore.
NOTE: You will use this key in all insights using this virtual data source and you cannot modify it later.
Click Create virtual data source.
Write the SQL
To define the virtual data source, you use the SQL editor.
Perform the following:
Write the SQL using the available data sources.
Test the result by clicking Execute.
Click Save and leave.
Your Virtual data source is available to use
NOTE: After you write the SQL, you can test the virtual data source by writing a query like: SELECT * from all_tasks
Use the virtual data source
You can use the virtual data source in the following ways:
When you create a codeless insight
The virtual data source is listed as an entity in the entities’ selector. Its fields are listed in the fields’ selector.
For more information, see Create codeless insights.When you create an insight with SQL
When you write the SQL of an insight, you use the visual data source as a standard entity.
It is listed in the right pane, together with all connected data entities.
For more information, see Create insights with SQL.
Edit a virtual data source
To edit or delete a virtual data source, perform the following:
In the navigation pane, Click Settings then click Data sources.
Expand the Virtual Datasources section. All created virtual data sources are listed.
Click the ellipses to expand the menu of the data source that you want to edit or delete.
To edit the name and the description, click Edit details.
You cannot edit the key.
To edit the SQL, click Edit SQL.
To delete the virtual data source, click Delete.
Confirm the deletion.