Overview

Gtmhub allows you to build insights using SQL queries and then display them using HTML markup.

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

The Gtmhub insight engine serializes the result from your SQL queries to a JSON and binds it to the data object in the scope of each insight.

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 JSON 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 serialized to a JSON array and returned as a child element of data object. In the following case, the query result is in data.default.

The following SQL query:

SELECT
COUNT(*) as "count",
issuetypename as "type"
FROM jira_issues

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 1000 records at a time from the Gtmhub data warehouse.


See also

Did this answer your question?