Overview: Metric Builder for Amazon Redshift

HOW TO

How to access Custom Metrics and Metric Builder for Amazon Redshift

Navigate to Metrics > Custom Metrics to access the Metric Builder for Amazon Redshift. Click the green + New Custom Metric button and select your connected Amazon Redshift Data Source from the Data Source drop-down list.

these steps

How to use the Metric Builder for Amazon Redshift

SQL: Construct query strings to access data from your Database using Structured Query Language (SQL).

Learn more about SQL here.

Data Type: Selecting the appropriate Data Type ensures that your Custom Metrics will be synced correctly in Databox.

Daily Values: This Data Type should be selected when your Metric reflects the most up-to-date Daily Value for the Metric. In Databox, the latest daily entry will be displayed for the Custom Metric.
Total Values: This Data Type should be selected when your Metric reflects the current total Value for the Metric. In Databox, the latest entry will be displayed for the Custom Metric.

For longer Date Ranges, data will be not aggregated and the most recent Value will be displayed for the selected Date Range. This is the main difference between "Total Values" and "Daily Values."
Event Values: This Data Type should be selected when your Metric Value selection reflects individual Value(s) for the Metric at the specified Date/ Timestamp. This Data Type is the default selection because it is appropriate for the majority of use cases.

For longer Date Ranges, individual Metric Values will be aggregated for the entire Date Range and the aggregated Value will be displayed for the Custom Metric.

Custom Metric Name: Enter a name for your Custom Metric. This Custom Metric name will be available in the Designer after saving.

Data Preview: After clicking Run query, a Data Preview of the Custom Metric will be displayed. Daily Metric values are displayed in this section, along with the total Metric values for the Date Range in bold at the bottom. You can view the Data Preview for different Date Ranges by clicking on the tabs at the top of the Data Preview.

How to create a Custom Amazon Redshift Metric [Example]

The Metric Builder for Amazon Redshift is designed so you can directly enter SQL queries. For our example, we will use an Amazon Redshift Data Source and create a Custom Metric that returns a list of authors and their current post count.

Navigate to Metrics > Custom Metrics

Click the green + New Custom Metric button

Select the appropriate Amazon Redshift Data Source

In the SQL field, we will enter the query below. A semicolon at the end of the query is not necessary

SELECT COUNT(p.ID) AS posts, u.display_name FROM users u, posts p WHERE p.post_author=u.ID AND p.post_type='post' GROUP BY u.ID

We used the AS SQL construct in our query (i.e. AS posts). This is not mandatory, but it will set the data as a Metric Key. If you use AS date , this column will represent the Date and Time of the Metric Value ( ISO 8601 Standard Date and Time Format is supported).

We will select Total Values as the Data Type. This selection is based on the way data is stored and updated in our Database

In order to accurately explain what this Custom Metric is reporting on, we'll name the Custom Metric "Authors by Post Count"

Click Preview data to generate a Data Preview of the Custom Metric

If you don’t see any data, double check your SQL query and try it directly in your Database. If no results are displayed there, an error exists in your query. You should also check to confirm that the Amazon Redshift user has the necessary permissions to access the Database from Databox's public IP.

Once we confirm that these are the results we're looking for, we'll click Save to save the Custom Metric

How to add a Custom Amazon Redshift Metric to a Databoard

Learn how to add a Custom Metric to a Databoard here.

Additional Information

Query results are limited to 10,000 rows. If you expect your query to return over 10,000 rows or if you're not sure how many rows will be returned, be sure to LIMIT the query. If you need more than 10,000 rows in order to successfully report on your Amazon Redshift data in Databox, feel free to submit your request on our Roadmap here.
The query must contain a column named "Date." If you need to, use AS in your SELECT statement to satisfy this requirement
One column should always contain the Metric Value
"Null" values will not be recognized. Use a COALESCE function when needed. Learn more here.