Using Performance Analytics with external data

Performance Analytics on external data sources enables you to perform detailed analysis on data that is not in your ServiceNow instance.

When you collect scores on external data, Performance Analytics stores scores and breakdown elements from the external data on your instance. The raw data being analyzed remains on the external data source and is not copied to your instance. This functionality enables you to analyze and share metrics without duplicating the underlying data.

Performance Analytics external data collection uses three types of configuration records:
  • An external indicator which specifies the JDBC data source and SQL statement used to collect scores.
  • An external breakdown which specifies the JDBC data source and SQL statement used to specify breakdown elements.
  • An external indicator breakdown which defines the relationship between an external indicator, and breakdown, and the SQL statement used to collect scores for each breakdown element for that indicator.

Create an indicator for external data

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

Before you begin

Role required: pa_admin, pa_power_user, or admin

Procedure

  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. If the indicator score is supposed to increase or decrease over time, select Maximize or Minimize, respectively, in the Direction field.
  7. (Optional) Specify any of the remaining indicator properties:
    FieldDescription
    Unit The unit of measurement for the 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 Identifies the indicator as a key indicator. 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.
    FieldDescription
    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 current 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.

What to do next

If you want to collect breakdown scores for the indicator, define an external breakdown and associate it with the indicator. If you are not using breakdowns, test the indicator query.

Create a breakdown using external data

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

Before you begin

Role required: pa_admin, pa_power_user, or admin

About this task

By default an external breakdown can contain a maximum of 5000 elements. This limit is controlled by the property com.snc.pa.dc.max_external_elements.

External breakdown elements are stored on the pa_ext_elements tables.

Procedure

  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.

What to do next

After defining how to collect breakdown elements, associate the external breakdown with one or more external indicators.

Configure an external indicator to use an external breakdown

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

Before you begin

Role required: pa_admin, pa_power_user, or admin

Procedure

  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.

What to do next

After defining how to collect breakdown scores, test the breakdown and indicator to validate that all queries run correctly.

Test external indicators and breakdowns

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

Before you begin

Role required: pa_admin, pa_power_user, or admin

About this task

Test an indicator to test the query for that indicator and the queries for all breakdown elements. Test a breakdown to test the query used to determine available breakdown elements.

No scores or breakdown elements are saved when you test an indicator or breakdown.

Procedure

  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.

What to do next

After confirming that all queries run successfully and return the data you expect, add the external indicator to a data collection job to begin collecting scores.

Filtering external data by date

When using Performance Analytics with external data you must filter SQL statements that collect scores by date.

In indicator and indicator breakdown SQL statements, filter the query by date, such as WHERE DATE(my_date_field) >= DATE(${start_at}). It is not necessary to filter breakdown SQL statements by date as breakdown SQL statements collect only elements and not scores.

The variable ${start_at} contains the date of the period being collected in the format YYYYMMDD. For daily indicators this value is always the date being collected. For indicators with longer collection frequencies, such as weekly or monthly, the date is the first day of the collection period. The date that the data collection job runs on does not affect this variable.

When you test an indicator or breakdown, the ${start_at} variable is always set to the current date.

Limitations when using Performance Analytics with external data

Certain Performance Analytics functionality is not available when you measure external data.

  • You cannot collect snapshots
  • You cannot view real-time scores
  • You cannot apply 2nd-level breakdowns
  • You cannot use widget visualizations that depend on 2nd-level breakdowns with external data. This includes pivot and heatmap visualizations.
  • Because snapshots are not available, you cannot view changes in collected records