HOW TO
- Access Custom Metrics and Metric Builder for Microsoft SQL
- Use the Metric Builder for Microsoft SQL
- Create a Custom Microsoft SQL Metric [Example]
- Add a Custom Microsoft SQL Metric to a Databoard
- Additional Information
How to access Custom Metrics and Metric Builder for Microsoft SQL
Navigate to Metrics > Custom Metrics. Click the green + New Custom Metric button and select your connected Microsoft SQL Data Source from the Data Source drop-down list.
Metric Builder is available on the Professional and higher plans. Request a trial of Metric Builder by following these steps.
How to use the Metric Builder for Microsoft SQL
- 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.
How to create a Custom Microsoft SQL Metric [Example]
The Metric Builder for Microsoft SQL is designed, so you can directly enter SQL queries. For our example, we will create a Custom Metric that returns a list of Product Names and their associated Order Quantities.
- Navigate to Metrics > Custom Metrics
- Click the green + New Custom Metric button
- Select the appropriate Microsoft SQL Data Source
- In order to accurately explain what this Custom Metric is reporting on, we'll name the Custom Metric "Product Name by Order Quantity"
- In the SQL field, we will enter the query below. A semicolon at the end of the query is not necessary
SELECT TOP 20
p.ModifiedDate AS date,
p.Name, sd.OrderQty
FROM Sales.SalesOrderDetail sd
INNER JOIN Production.Product p
ON sd.ProductID = p.ProductID
ORDER BY sd.OrderQty DESC
If you don’t see any data, double-check your 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 Microsoft SQL user has the necessary permissions to access the Database from Databox's public IP.
How to add a Custom Microsoft SQL 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 Microsoft SQL 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.