Using Performance Analytics with external data

Create an external indicator to define what data to evaluate and the SQL statement used to determine the indicator score.

1. Navigate to Performance Analytics > External Indicators and click New.
2. Give the indicator a descriptive Name.
3. In the Frequency field, specify the frequency of data points for the indicator, such as Daily, Weekly, or Monthly.
4. In the Source tab, select a Data Source to collect scores from.
   Only JDBC type data sources are supported by Performance Analytics. Refer to the data sources documentation for information on setting up data sources.

   Note: The SQL Statement and Import set table defined in the data source are not used by Performance Analytics.
5. Specify the SQL Statement to use to calculate the score value.
   The SQL statement must return an aggregate value with the alias value, and should filter data based on a date field, such as SELECT count(*) AS value FROM... WHERE DATE(date_field) >= DATE(${start_at}).

   Important: The aggregate alias must be value.
6. (Optional) If you prefer that the score of this indicator increases or decreases over time, select Maximize or Minimize in the Direction field.
   Many analytical tools and graphic displays use this Direction with this indicator.

   Value	Use case
   Maximize	Select if an increase in this indicator score is desired. For example, consider selecting Maximize for an indicator that shows revenue. Analytic tools and graphic elements, such as those in scorecards, reflect that an increase in this indicator score is good and a decrease is bad.
   Minimize	Select if a decrease in this indicator score is desired. For example, consider selecting Minimize for an indicator that shows costs. Analytic tools and graphic elements, such as those in scorecards, reflect that a decrease in this indicator score is good and an increase is bad.
   None	Select if the direction of change in this score does not matter to your business.

7. (Optional) Specify any of the remaining indicator properties:

   Table 1. Other property fields

   Field	Description
   Unit	The unit of measurement for the indicator score, such as number, days, or percentages.
   Precision	The number of digits behind the decimal separator. For indicator scores in the thousands and millions, the score is displayed as the number of thousands or millions with a k or an M, respectively. For example, a score of 612,875 with a precision of 0 is rendered as 613K. A score of 8,546,937 with a precision of 1 is rendered as 8.5M. For more information, see Rounding and precision in indicators.
   Key	Check box to indicate if the indicator is a key metric for the process being monitored. Used only to filter the list of scorecards in Performance Analytics > Scorecards.

8. In the Access control tab, set whether to publish this indicator to a scorecard, and whether to limit the visibility of the indicator by user, group, or role.
   The scorecard enables analysis of the indicator through graphics.
9. (Optional) In the Other tab, set various miscellaneous properties.

   Table 2. "Other" tab fields

   Field	Description
   Default time series	A predefined analytical function, like a 7-days running average, to display the indicator instead of showing the actual values of the indicator.
   Live group profile	Live group profile that indicates the live group where the indicator scores are published.
   Order	Number indicating the order in which scorecards are displayed. Indicators with the lowest value are displayed at the top of the scorecard list. If no values are provided in the Order field, scorecards are displayed from a to z using the Name field. To use the order field, you must enter order numbers for all indicators. If you put in numbers for only a few indicators, the order in which scorecards are displayed reverts to a to z.
   Default chart type	Set a default chart type (line, column, spline, or area) for this indicator. When opening the detailed scorecard for the first time, the default chart type is used. If the chart type is changed in the detailed scorecard, that preference is remembered.
   Render continuous lines	When selected, scorecards displaying this indicator show unbroken data lines, even when there is no data for a specific date. This behavior may be useful when displaying data sets with varied starting dates or data that is not regularly updated, such as stock information.
   Show real-time score	When selected, scorecards displaying this indicator show the score in real time, as well as the current state of associated records. Clear this check box when indicator data is not available in real time, such as in an integration that uses data from a third-party source.
   Show delta	When selected, enables reporting of historic records when viewing this indicator on a detailed scorecard. You can filter the data to display only the currently data, only the historical data, or the data shared between both sets.

10. (Optional) In the Forecasting tab, set the forecast method, the number of data collection periods to forecast, and the amount of historical data to base the forecast on.
    For more information, see Forecasting Performance Analytics data.
11. Save the indicator.
12. Press Test Collection.
    This action tests the main query of the indicator.

Create an external breakdown to define what elements are available to break down external indicator scores.

1. Navigate to Performance Analytics > External Breakdowns.
2. Click New.
3. Select a Data Source that contains the records you want to use as breakdown elements.
   Only JDBC type data sources are supported by Performance Analytics. Refer to the data sources documentation for information on setting up data sources.

   Note: The SQL Statement and Import set table defined in the data source are not used by Performance Analytics.
4. Specify a SQL Statement to select the breakdown elements.
   The SQL Statement must return the unique key for each breakdown element with the alias id and the element display name with the alias name,such as SELECT guid AS id, user_name AS name FROM....

   Important: The unique key alias must be id and the display name alias must be name.
5. Save the breakdown.
6. Press Test Collection.
   This action tests the query that selects the breakdown elements.

Associate an external indicator and external breakdown to define how to collect breakdown scores for the indicator.

1. Navigate to Performance Analytics > External Indicators.
2. Select an indicator.
3. In the Breakdowns related list, click New.
4. Select the Breakdown to apply to this indicator.
   You do not need to select a data source. The indicator data source is used.
5. In the SQL statement field, enter a SQL statement that calculates the score value for each breakdown element.
   The SQL statement should use the same aggregate function as the indicator SQL statement, such as COUNT. The SQL statement must return the aggregate value with the alias value and the breakdown element unique key with the alias id. The SQL statement must also group the data by the column that contains the breakdown element values.

   For example, SELECT count(*) AS value, guid as id FROM... WHERE DATE(date_field) >= DATE({$start_at}) GROUP BY guid

   Important: The aggregate alias must be value and the breakdown unique key alias must be id.
6. Save your changes.
7. Press Test Collection.
   This action tests the SQL statement that calculates the score value for the breakdown element.

Test your external indicators and breakdowns to ensure you can connect to the external data source and collect the data you expect.

1. Navigate to Performance Analytics > External Indicators or Performance Analytics > External Breakdowns.
2. Select the indicator or breakdown that you want to test.
3. Click the Test button.
   If the query runs successfully, the number of scores or breakdown elements that would be collected appears. If an error occurs during testing, the error message appears.