Creating reports from external data

At times, it might become necessary to create reports that include data not present in the data sources defined for the extract, transform, and load (ETL) process. This topic contains the structure of the tables and the workbooks needed for loading the external data.

Microsoft® Excel workbook

The workbook must have at least two worksheets:
  • A worksheet named TABLES for the control table.

    Each row in the control table represents one source data table.

  • A worksheet for each source data table.

    In the sample catalog, the name of the data source worksheet is assumed to be SAMPLE_TABLE.

The columns of the control table are:
  • TABLE_NAME
  • DATASOURCE_ID
  • PROJECT_KEY
  • COMPONENT_KEY
  • PRODUCT_KEY
  • RELEASE_KEY
  • TEAM_KEY
The columns of the tables for the data sources are:
  • DATE
  • PROJECT_ID
  • COMPONENT_ID
  • CUSTOMER_PRIORITY
  • PRIORITY
  • PRODUCT_ID
  • RELEASE_ID
  • RESOLUTION
  • SEVERITY
  • STATE
  • STATUS
  • TEAM_ID
  • VERDICT
  • METRIC_LABEL
  • LOWER_LIMIT
  • UPPER_LIMIT
  • TARGET

Fact table

Table 1. Columns of the F_REPORT_PLANNED_DATA fact table
Column name Attribute type
TARGET Decimal (18, 2)
UPPER_LIMIT Decimal (18, 2)
LOWER_LIMIT Decimal (18, 2)
METRIC_LABEL Varchar (255)
DATE_ID Integer
PROJECT_ID Integer
COMPONENT_ID Integer
CUSTOMER_PRIORITY_ID Integer
PRIORITY_ID Integer
PRODUCT_ID Integer
RELEASE_ID Integer
RESOLUTION_ID Integer
SEVERITY_ID Integer
STATE_ID Integer
STATUS_ID Integer
TEAM_ID Integer
VERDICT_ID Integer

The values for METRIC_LABEL, TARGET, UPPER_LIMIT and LOWER_LIMIT of the measure can be specified.


Feedback