New in Clarity PPM 15.3, the data warehouse now supports your trend reporting efforts by capturing trending snapshots. New tables and jobs are available to help you capture snapshots of important data. You can compare the data over time and make observations about how it is trending. For example, labor costs are trending down while planned ROI is trending up.
This document provides administrators, architects, and report developers with the technical details about the new trending capabilities.
Trending data is dynamically defined through your seed data. Each trend is a series of snapshots of different data warehouse tables. You can run jobs that take snapshots of data to be used in trending reports. The frequency of trending data is flexible.
Trending has been set up to dynamically configure itself by checking the metadata tables and making the database object changes defined in those meta tables. Programmatic changes are not required in order to add tables or columns in future snapshots.
The system uses the following six metadata tables to support trending:
Note: The specific tables, views, columns, indexes, and keys can change over time. Changes appear automatically after the trending jobs run.
To configure your own trending data, use the following three jobs:
Note: The data warehouse creates all the necessary trending tables only after you run the Load Data Warehouse and Create Data Warehouse Trend jobs. If you use the new trending jobs to generate your own custom data, we recommend that you make specific trending data backups. A common troubleshooting technique for on-premise administrators involves dropping the data warehouse schema. If you have used that technique in the past or might in the future, exercise caution. Data warehouse data can be restored; however, without a backup, your custom trending data would be lost. Trending data for SaaS environments is automatically backed up.
The following tables provide the structure for generating and maintaining trending data:
DWH_TRD_trend: This header table stores key information about each trend. It includes the unique trend key, trend name, trend type, the run time start and end dates, and the snapshot year.
DWH_TRD_trend_detail: This table stores details about each snapshot. It includes the trend key, the target table that was refreshed, and the start and end dates.
DWH_TRD_trend_history: Historical information about when the trends were updated. If you update a trend multiple times, the history shows the details including the last run date of the data warehouse at the time of the trend update.
DWH_TRD_trend_deletion: Stores the date and time of trend deletion.
DWH_TRD_gen_facts: Temporary table for processing; stores data structures.
DWH_TRD_tmp_current_key: Temporary table for processing; stores information about the current trend.
The following tables store the trending source data that report developers can use to build trending reports and dashboards. These tables track the slow progressive changes in dimensions for investments, resources, and OBS mappings:
Investment, resource, and investment summary facts are included in the trending snapshots. Snapshots can capture monthly changes to individual attributes. Common examples of popular trending data might include changes to the following attributes:
Tip: Examine trending data to identify answers to popular questions.
Three trending materialized views are available to guide you to the appropriate data in the warehouse:
All three views share the same table column layout.
If SQL errors occur during the execution of a trending job, examine the following details in the DWH_TRD_ERROR_MESSAGES table:
The SQL_COMMAND field includes the offending statement that triggered the error.