Data Warehouse Trending Jobs for Trend Reporting
New in cappm 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.
Clarity PPM15.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 its own set of jobs that depend on the Load Data Warehouse job.
- The jobs do not depend on the Data Warehouse Load Access Rights job.
- Trending references only the data warehouse tables; it does not use the PPM transactional database.
- The data warehouse harvests your trending data from its other existing tables. Those tables are already aggregated and summarized. When updating and connecting to trending data in the data warehouse, performance is already optimized by design.
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:
- DWH_TRD_META_tables: Contains information about the tables that are included in the trending snapshot.
- DWH_TRD_META_odf_tables: Contains ODF information about the tables that are included in the trending snapshot. (Same as DWH_TRD_META_tables.) You cannot make changes to this table.
- DWH_TRD_META_columns: Contains information about the table columns that are included in the trending snapshot.
- DWH_TRD_META_odf_columns: Contains ODF information about the table columns that are included in the trending snapshot. (Same as DWH_TRD_META_columns.)
- DWH_TRD_META_idx_pk_fk: Contains information about the table indexes, primary keys and foreign keys.
- DWH_TRD_META_odf_idx_pk_fk: Contains ODF information about the table indexes, primary keys and foreign keys. (Same as DWH_TRD_META_idx_pk_fk.) You cannot make changes to this table.
: 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:
- Create Data Warehouse Trend: To define parameters that establish your new trend, run this initial job. For example, you could create a trend to analyze monthly changes in spending for a particular year.
- Update Data Warehouse Trend: To refresh an existing snapshot with new data, run or schedule this job.
- Delete Data Warehouse Trend: This job deletes your previous snapshots. You can delete snapshots by specifying a named trend or by entering a specific date or relative date. The job deletes all snapshots in that trend or all snapshots from all trends prior to your selected date.
: 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.
Trending Structural Tables
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.
Trending Data Tables
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:
- investment OBS
- resource OBS
- OBS mapping
- investment manager
- status and KPI values
- actual hours
- resource manager
- booking manager
- primary role
: Examine trending data to identify answers to popular questions.
- "What locations or departments have added the most staff in the last six months?"
- "Are actuals trending up or down, and on which investments?"
- "How are forecasts trending against budgets over the most recent four quarters?"
- Human Resources might want to know when the number of resources in the primary role of Chief Data Scientist drops below three to refresh its staffing efforts.
Three trending materialized views are available to guide you to the appropriate data in the warehouse:
- DWH_TRD_PER_TREND_BY_F_MV: Fiscal period data.
- DWH_TRD_PER_TREND_BY_M_MV: Monthly calendar data.
- DWH_TRD_PER_TREND_BY_W_MV: Weekly data.
All three views share the same table column layout.
Troubleshooting SQL Errors in Trending Jobs
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.