Skip to content
CA PPM - 15.3
Documentation powered by DocOps

Data Warehouse Trending Jobs for Trend Reporting

Last update March 8, 2019

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.


Examples of trending reports you might create for your organization.

Trending Snapshots

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.

Note: The specific tables, views, columns, indexes, and keys can change over time. Changes appear automatically after the trending jobs run.

Trending Jobs

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.

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.

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:

  • DWH_TRD_INV_INVESTMENT
  • DWH_TRD_INV_INVESTMENT_LN
  • DWH_TRD_RES_RESOURCE
  • DWH_TRD_LKP_OBS_UNIT
  • DWH_TRD_INV_OBS_MAPPING
  • DWH_TRD_RES_OBS_MAPPING
  • DWH_TRD_INV_MONTH_FACTS
  • DWH_TRD_RES_MONTH_FACTS
  • DWH_TRD_INV_SUM_FACTS

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
  • dates
  • resource manager
  • booking manager
  • primary role

Tip: 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.

Trending Views

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:

  • ERROR_MESSAGE
  • ERROR_NUMBER
  • SQL_COMMAND
  • TABLE_NAME

The SQL_COMMAND field includes the offending statement that triggered the error.

Was this helpful?

Please log in to post comments.

  1. Velayutham Palaniappan
    2018-02-05 01:50

    is there any Out Of the Box Trend Reports that comes in with CA PPM 15.3 or PMO Accelerator Advanced Reporting Content?

    1. Damon Logiudice
      2018-02-06 10:51

      Yes and No, Velayutham, depends how you look at it. We might say "no" since no new predefined reports purely for trending ship with the last release; however, in another sense, users were seeing trending in their CA PPM reports all along. For example, on the out-of-the-box Financial Budget vs. Forecast by Period report, you can see the change in variance month-by-month. Thus, you would not need to set up a special trending report to track changes in those metrics over time. As users engage in greater use of our new trending features for custom attributes, we might see some good customer examples in the CA PPM Online Community that showcase good trend reporting examples you might also like. Thank you for your comment.