Overview

Gtmhub allows you to build insights using SQL queries and then display them using HTML markup. They can be edited in the online editor or in Visual Studio Code using the Offline Insight Editor.

The Gtmhub data warehouse is based on PosgreSQL. Any functions that are supported out-of-the-box by PosgreSQL, are also supported by Gtmhub insight engine.

For more information, see PosgreSQL.

The insight widgets are based on AngularJS. When the insight server responds with the calculated data, the widget binds the response to the user interface.
For more information, see AngularJS.


Create the insight

To create an SQL insight, perform the following:

  1. In the navigation pane, expand Insights and click Insightboards.

  2. In the toolbar, click Manage insights.

  3. Expand Create new insight and click Use SQL expert editor.

  4. Enter a Title for the insight.

  5. In Description, describe what the insight will do.

  6. Click Create insight.
    The SQL editor appears.


The SQL editor

The SQL editor has the following:

  • On the left is an HTML markup editor.
    In the bottom of the HTML editor, you can use predefined HTML templates or insert HTML snippets.
    Using the Automatic UI button, you can display the markup editor or the output that it produces.

  • On the right is an SQL editor.
    You can use the Execute button to check for errors in your SQL syntax.

  • In the right pane, there is a reference of all connected Gtmhub data source entries that you can copy in the editors.

When finished with the insight, click Save and leave.

For more information, see:


Add the insight to an insightboard

Perform the following:

  1. In the navigation pane, expand Insights and click Insightboards.

  2. Click the insightboard where you want to add the created insight.

  3. In the upper-right corner, expand the menu.

  4. Click Add insights.

  5. Select the insight and and click Add selected.

  6. In the toolbar, click Save changes.


Example

Use the following example to understand better the parts of an SQL insight.

Insight algorithm: For the algorithm the example uses a simple SELECT statement that selects a text from the server and returns it as a named column.

Insight user interface: The example uses the following HTML markup for our SQL insight:

<div class='title'>{{ data.message }}</div>

SQL: The example uses the following SQL statement:

SELECT 'Hello, World!' as message

Result: Executing the insight produces the following result:


Key concepts

Each Gtmhub insight has its own Angular scope. The results of the SQL query of the insight are transformed into a JavaScript object and added to the insight scope in the data property.

Single JSON object: When your SQL insight returns a single row of data with an arbitrary number of columns, the data will be returned as a single object.

The following SQL query:

SELECT COUNT(*) AS count
FROM users

Return the data as follows:

{count: 1604}

JSON array: When you are working with collections of data, the result is transformed into an array of objects and returned in the default property of the data object. In the following case, the query result is in data.default. The first row will be in the root of the data object.

The following SQL query:

SELECT
COUNT(*) AS count,
issuetypename AS type
FROM jira_issues
GROUP BY issuetypename

Return the data as follows:

[
{"count":179,"type":"Bug"},
{"count":399,"type":"Story"},
{"count":50,"type":"Task"},
{"count":230,"type":"Sub-task"},
{"count":49,"type":"Epic"}
]


NOTE: When building insights, you can retrieve up to 5000 records at a time from the Gtmhub data warehouse. Retrieving more than 5000 records will result in an error. You can add a LIMIT 5000 to your query or use insightboard parameter filters to avoid this error.


See also

Did this answer your question?