Help & DocumentationDatasets Creating Elements from DatasetsSourcing Reports / Metrics from "Existing Datasets - SQL"

Sourcing Reports / Metrics from "Existing Datasets - SQL"

All Datasets created in the system are saved as SQL tables which can be used to source data for creating new Elements: Reports and Metrics.

The method described in this article represents an alternative to: Create a Report from a Dataset View

POWER USERS NOTE:

In order to grant Power users access to this feature (creating Reports / Metrics from the "Existing Datasets - SQL" Data Source), a Privilege has been added to User and Group Editors: Create Content Using Existing Datasets.

It can be added at Admin > Group or User > Group or User Editor > Info tab > Privileges

Sourcing a Report (Introduced in Version 5.0.5)

1. Access New > Report
  1. Name the Report: Define a unique descriptive name of your element
  2. Reported: choose the measurement interval from the drop-down list
  3. Category: define a category this element belongs to
  4. To move on to defining data collection details, click Next: Define Report
2. Full Editor displays the Data Collection tab
  1. Data Source: select "Existing Datasets - SQL"
  2. Click the Source of Reports icon to view the Datasets that have been saved as source tables
  3. Open the Available Datasets drop-down list to select a  source Report and view its associated fields.
  4. In the SQL Statement using report names and columns text box, define the fetch command required to extract data from existing Dataset's table
  5. Alternatively,  Use a SQL Builder tool to select report(s)

NOTE:  Approach constructing the fetch command in the same manner as sourcing a Metric from an Existing Report  (see Source a Metric From an Existing Report).

3. Verify your Statement

Click Show data below the SQL statement box. If your command is valid, the command box is green and the Report Columns are shown in the table below; if there are any errors, the box is colored in red and errors are explained below the statement box.

At the upper right corner of the screen click Enable and Publish.

Sourcing a Metric (Introduced in Version 5.1)

1. Access New > Metric

Provide the basic information required for creating a new metric:

  1. Select the Measurement Interval that applies to your element. Your Metric Insights instance comes with a standard set of Measurement Intervals, each of which has a series of settings that control such behavior as display of values, alert parameters, default naming conventions, and chart labels.
  2. Give the element a unique Name
  3. Optionally, assign a Category
  4. Click Next: define details to proceed with data collection
2. Full Editor displays the Data Collection tab
  1. Data Source: select "Existing Datasets - SQL"
  2. Click the Source of Reports icon to view the Datasets that have been saved as source tables
  3. Open the Available Datasets drop-down list to select a  source Report and view its associated fields.
  4. In the SQL Statement using Dataset names and columns text box, define the fetch command required to extract data from existing Dataset's table

NOTE:  Approach constructing the fetch command in the same manner as sourcing a Metric from an Existing Report  (see Source a Metric From an Existing Report).

3. Validate your SQL statement and Collect data
  1. Click Validate below the SQL statement box. If your command is valid, the command box is green and the number of records available for collecting is shown below; if there are any errors, the box is colored in red and errors are explained below the statement box.
  2. Collect data

Once the data is collected, in the upper right corner fo the screen click Enable & Publish.

To learn about other settings in the Metric Editor, refer to: Metric Editor (Data, Stoplights, Alerting, Charting, Associations)

0 Comments

Add your comment

E-Mail me when someone replies to this comment