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:

  1. Add new virtual data sources and give it a key.
    Afterwards, you will use the key in the SQL of insights.
  2. Write the SQL that retrieves, manipulates, filters, and merges in the virtual data source.
  3. 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:

  1. In the navigation pane, expand Insights and click Data sources.
  2. In the toolbar, expand Add new data source and click Add new virtual data source.
  3. Enter a name of the virtual data source.
  4. Create a description of what this virtual data source will do.
  5. 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.
  6. Click Create virtual data source.

Write the SQL

To define the virtual data source, you use the SQL editor.

Perform the following:

  1. Write the SQL using the available data sources.
  2. Test the result by clicking Execute.
  3. 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 virtual data source, perform the following:

  1. In the navigation pane, expand Insights and click Data sources.
  2. Expand the Virtual Datasources section.
    All created virtual data sources are listed.
  3. Expand the menu of the data source that you want to edit or delete.
  4. To edit the name and the description, click Edit details.
    You cannot edit the key.
  5. To edit the SQL, click Edit SQL.
  6. To delete the virtual data source, click Delete.
    Confirm the deletion.

See also


Did this answer your question?