Data Warehouse Trend Reporting
The data warehouse supports your business intelligence and analytics trend reporting efforts by capturing trending snapshots. 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 for setting up and managing 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.
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.
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.
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?"
- "Is project status trending in the right direction this quarter?"
- 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.
Enable a Custom Attribute for Trending
You can enable custom attributes for trending in the investment, resource, and project objects. Stock non-project investment objects (NPIOs) also support custom attribute trending including applications, assets, products, services, and other work.
To configure trending, enable the object and attribute for the data warehouse and then enable them again, specifically, for trending.
- Open an object that supports trending.TheInclude in the Data Warehouse Trendingcheck box forallobjects is read-only. If the object supports trending, this field is still disabled (grayed-out), but the option is pre-selected. If the object does not support trending, the check box is not selected.
- Click theAttributestab.
- To filter on attributes that are enabled or not enabled for trending, use theData Warehouse Trendingfilter field.
- To add a new custom attribute, clickNew.Trending is limited to the following types of custom attributes: string, number, date, lookup, formula, calculated, aggregated, and Boolean.
- To enable the custom attribute for the data warehouse, selectInclude in the Data Warehouse.
- To enable it for trending, selectInclude in the Data Warehouse Trending.The trending ETL jobs now include any custom attributes that you enable for trending.
When a user disables an attribute that was previously enabled for the data warehouse, the application also clears the trending check box.
Trend Reporting Examples
Project KPI Trends
The Project KPI Trends report is available (introduced in Release 15.5.1) as an example of the applied use of trending snapshots. Trend reporting helps teams and leadership identify overall patterns that shift over time. Use the Project KPI Trends report to monitor key performance indicators and establish priorities. For example, track the quantity and severity of open risks, issues, change requests, and to-do items over time.
Trended Metrics by Project
New! In Release 15.6, the Trended Metrics by Project report serves as a second example of trend reporting. The following example compares key financial data for core investments by fiscal period (in this example, by month).
This report is populated with your pre-defined trending data already configured and extracted to the data warehouse. You can run the report for a single month, up to 12 months, or up to 12 fiscal periods.
When users run or schedule this report, they can select up to five (5) of the following project metrics:
Actual Cost Actual Hours Allocation Cost Allocation Hours Baseline Cost Baseline Hours Budgeted Benefit Budgeted Cost EAC Cost EAC Hours ETC Cost ETC Hours Forecast Cost Planned Benefit Planned Cost
XOG Support for Trending
To support trending in the XOG, a new
dwTrendEnabledBoolean attribute appears in the XML for imported and exported CA PPM objects and attributes. Developers refer to this XML attribute as the
The same business rules apply for both the user interface and XOG. Developers might manually update the trending flag in their XOG export file. Changes could occur that do not adhere to the trending business rules. For example, a XOG import attempts to create or update an attribute that cannot be enabled for trending. In this scenario, the following warning message appears:
Attribute <attribute ID> cannot be enabled for trending.
The XOG import continues but skips that specific change in trending properties for that particular attribute.
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.