Inclusion of 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 is an overview of the process for including in reports such external data.
The process for including external data in reports is:
  1. The external data is loaded from a Microsoft® Excel workbook, which has specific columns, to a generic fact table (F_REPORT_PLANNED_DATA) with a specific structure.

    For information on the structure of the workbook and the fact table, see the related links at the bottom of the page.

  2. The fact table is transformed through the builds and jobs defined in the Others/ReportPlannedData folder of the sample catalog.

    In the data warehouse, each dimension is defined to store the surrogate key of the dimension record. You can specify the data source ID and natural key of the artifacts in the source data. During the ETL process, the specified values are loaded and transformed to the surrogate key of the records in the data warehouse. You must specify the natural key for dimensions that represent an independent artifact, and in the control table, you must specify the column name of the table in the operational data source that corresponds to the natural key of the artifact. For dimensions representing simple values such as state and priority, you can specify the values directly.

  3. The reports are generated through queries that get data directly from the data warehouse.

Feedback