SQL Analytics (Beta)

SQL Analytics allows you to carry out advanced manipulation of data using a SQL query before you visualize it.

For example:

• You might ask 'What is the average time difference between a ticket created and resolved?' You can use SQL to calculate the difference between the ticket created and resolved time, then you can visualize the Mean Time To Resolution (MTTR).

• For AWS CloudWatch data you might want to know 'What is the 95% percentile response time of my microservice?' use SQL to query the data, and then show the Service Level Indicator (SLI).

• You can also combine entry points and query them before visualizing the data. You might combine your Azure Cost data with your Azure Monitor data and ask 'What is the potential cost saving of turning off my unused VMs?'

Switch to the Analytics Editor

When editing a tile click on Switch to Analytics at the top of the screen. This changes your view from the simple tile editor into the more advanced Analytics Editor. It's best to decide whether you want to stick with the SQL Analytics editor for a particular tile, or with the simple editor, rather than switching between the two.

If you exit out of the Analytics Editor, choosing to revert to using the simple editor for this tile, then you lose your SQL queries and tables, as only the first table is saved.

Instead of the Scope - Data Stream - Visualization tile editing view that you're used to in the simple tile editor, in the Analytics Editor you have Tables - SQL Query - Visualization down the left-hand side.

As with the simple editor, you can still add Monitoring or KPIs, or view the Raw (raw data) or Code (code editor).

Tables

A table is the scope and the data stream combined. If you had already selected a scope and data stream in the simple tile editor this will be shown as your first table, which you can rename if you wish. For example, if you are looking at Zendesk tickets you might rename table1 to tickets.

You can add new tables, which allows you to add in further data. For a new table configure the scope and data stream to show the data you want.

Each table can be configured to use a different timeframe, if required and it makes sense for that data, or use the dashboard timeframe.

What is the dashboard timeframe?

The dashboard timeframe is the current timeframe setting for a dashboard. Users can change the dashboard timeframe to see data for a different time span, for example, instead of showing data from "the last 12 hours" it can be changed to show data from "the last 7 days".

Tiles can be configured to:

• Use dashboard timeframe (default). For these tiles the data shown will change when the user changes the dashboard timeframe.

• Use a fixed timeframe from the options available. These tiles show a clock icon and hovering shows the fixed timeframe configured. The data will not change when the dashboard timeframe is changed.

Tip: Indicate with the name of a tile if the tile's timeframe can be changed. For example, naming a tile "Performance during the last week" tells users that this tile always shows data for the last week. Naming a tile just "Performance" indicates to users that changing the dashboard timeframe will change the data.

SQL Query

This uses standard Structured Query Language (SQL).

When you first go into the SQL Analytics editor the SQL query will show everything from the first table, as a simple query:

Select * FROM tickets

You can edit this query to be something more complex. For example, to find the average difference between two different times you can use DATEDIFF and display this it as 'MTTR':

SELECT AVG(DATEDIFF(HOUR, created_at, updated_at)) AS MTTR FROM tickets

You can also use SQL to query data from more than one table, or combine data with a JOIN etc.

Visualization

Select the visualization for your tile. Which visualizations are offered to you depends on the data available, for example Line Graph will only be offered if there is time series data.

Visualization Advanced Settings

Bar Chart settings

X-axis data	Choose which data to show on the x-axis. Auto <column> shows the column which was selected automatically for you, usually a time field for the x-axis.
Y-axis data	Choose which data to show on the y-axis. Auto <column> shows the column which was selected automatically for you, usually a numerical field for the y-axis.
Y-axis range	Auto - graph is fitted to the data automatically Percentage - shows 0-100 Fit to data from zero - shows from 0 to the data maximum Custom - allows you to specify the min and max
X-axis label	Allows you to override the default and enter a label, for example Date or Time.
Y-axis label	Allows you to override the default and enter a label, for example Tickets or ms.

Blocks settings

Sublabel	Choose the sublabel to be shown beneath the main block label
Columns	Set the number of columns the blocks are displayed in

Reset - reverts to the default settings.

Gauge settings

The Gauge visualization shows a single value, often a percentage, in relation to minimum and maximum values. Monitoring can be added so the gauge color changes based on your configured parameters.

Data	Choose which column or count to use as the value for the gauge. Auto <column> shows the column which was selected automatically for you.
Range	Specify the min and max of the gauge
Label	Add a label to be shown beneath the gauge
Monitoring	You can configure Monitoring (from the Monitoring tab) and then the colors configured for the conditions will show on the gauge. Check that the Data chosen in the Visualization (e.g. count) and the Value (and Column where applicable) that you are Monitoring (e.g. count) are as intended.

Stacked Bar settings

Type	Specify how to group bars, either stacked in a single bar or grouped as separate bars.
Mode	Percentage mode displays a series as a percentage of the overall bar, only available for Grouped charts.
Layout	Vertical or horizontal
Y-axis range	Auto - graph is fitted to the data automatically Percentage - shows 0-100 Fit to data from zero - shows from 0 to the data maximum Custom - allows you to specify the min and max
X-axis label	Allows you to override the default and enter a label, for example Date or Time.
Y-axis label	Allows you to override the default and enter a label, for example Tickets or ms.
X-axis data	Choose which data to show on the x-axis. Auto <column> shows the column which was selected automatically for you, usually a time field for the x-axis.
X-axis grouping	Choose how the stack is split into segments, usually a label or a string.
Y-axis data	Choose which data to show on the y-axis. Auto <column> shows the column which was selected automatically for you, usually a numerical field for the y-axis.

Line Graph settings

X-axis data	Choose which data to show on the x-axis. Auto <column> shows the column which was selected automatically for you, usually a time field for the x-axis.
Y-axis data	Choose which data to show on the y-axis. Auto <column> shows the column which was selected automatically for you, usually a numerical field for the y-axis.
Y-axis range	Auto - graph is fitted to the data automatically Percentage - shows 0-100 Fit to data from zero - shows from 0 to the data maximum Custom - allows you to specify the min and max
Data points	This shows where the data points are on the line. Useful to identify missing points, or detail for changing data.
Shading	Adds shading below each line.
X-axis label	Allows you to override the default and enter a label, for example Date or Time.
Y-axis label	Allows you to override the default and enter a label, for example Tickets or ms.

Scalar settings

Source Column	Choose the column to be used. The default is auto, where the column is chosen by the visualization.
Label	Enter a label, for example Tickets or ms.
Show formatted value	This shows a simplified value. Toggle to Off to show the raw value. (On by default)

Reset - reverts to the default settings.

Table settings

Sort order	Click and drag a column name to change their order.
Hide/show	Click on the Toggle visibility eye button to hide or show a column.
Resizing columns	Use the handle on each column of the table to change the width. Changes will be saved while in the tile editor or in dashboard edit mode.

Filtering, grouping and sorting of the data can be configured in the data stream advanced settings.

Reset - reverts to the default settings.

If you exit out of the Analytics Editor, choosing to revert to using the simple editor for this tile, then you lose your SQL queries and tables, as only the first table is saved.