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.

Supported database formats for external data

Performance Analytics supports only the database formats that ServiceNow supports by default. If a customer adds a JDBC driver for an unsupported database format, Performance Analytics does not support that database format as an external data source.

Table 1. Supported database formats
SQL Server Type Port
MySQL 3306
Microsoft SQL Server 1433
Oracle 1521

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 All > 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.
    Important: The aggregate alias must be value.
    For a simple indicator, the SQL statement will be something like SELECT count(*) AS value FROM... WHERE DATE(date_field) >= DATE(${start_at}).

    For more complex indicators, you may also need a condition that DATE is earlier than the date that DATEADD returns. Here a monthly indicator with complex data has the SQL statement SELECT count(*) AS value FROM... WHERE DATE(date_field) >= DATE(${start_at}) AND DATE(date_field) < DATEADD(month, 1, ${start_at}).

  6. If you prefer that the score of this indicator increases or decreases over time, select Maximize or Minimize in the Direction field.
    Analytical tools and graphic displays use this Direction with this indicator.
    Tip: Set a direction whenever you can. All key indicators should be set to Maximize or Minimize.
    ValueUse 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 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 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 these indicator properties:
  8. In the Access control tab, set whether to save this indicator to the library and whether to limit the visibility of the indicator by user, group, or role.
    Activating Share to library means that the indicator will be available in the following places:
    • The list of KPIs in the Platform Analytics experience library
    • KPI Details
    • Analytics Hub, but only on upgraded instances that have not fully migrated to Platform Analytics
  9. (Optional) In the Other tab, set various miscellaneous properties.
    For example, in the Other tab you can configure an indicator as follows:
    • By default, it shows a 30-day running sum of scores in widgets. You can override this default in the Analytics Hub, KPI Details, dashboard widgets, or workspace data visualization.
    • The IT Live Group is notified of changes to the indicator.
    • The indicator is listed third in the Analytics Hub list of indicators. All other indicators also have their position specified. This setting does not affect the order of indicators on KPI Details.
    • The default chart type is Spline on the Analytics Hub and KPI Details.
    • Data lines for the indicator are shown unbroken, even when data is missing.
    • The Analytics Hub and KPI Details can show the score of this indicator in real time.

    The Other tab on an indicator with settings previously listed

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

What to do next

If you want to collect breakdown scores for the indicator, define an external breakdown and associate it with the indicator.

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 All > 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.

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 All > Performance Analytics > External Indicators.
  2. Select an indicator.
  3. In the Breakdowns related list, click New.
  4. Select the external Breakdown to apply to this indicator.
  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.

What to do next

Repeat the previous steps to add additional breakdowns to the indicator. Test the SQL statement after adding each one.

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 All > 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