One of many great new features in CA PPM 15.3 is that Data Warehouse now includes trending data. With this feature, we can now capture historical data, offer many reporting capabilities, capture changes over time and leverage new auditing dimensions.
What data is captured?
- Resource data: Resource manager, booking manager, and primary role.
- Investment data: Dates, status, and manager information.
- Resource facts: Costs, hours, and timesheet data.
- Investment facts: Costs, hours, totals, and variances.
- Investment summary facts: Costs, percentages, variances, NPV, ROI, and other metrics.
- OBS data: OBS level, type, and mapping.
What and where are the trending tables?
Data is captured in the Data Warehouse trending tables. You can easily recognize them because trending tables start with dwh_trd_. More details are in DocOps: Data Warehouse Trending Jobs for Trend Reporting
How does trending work?
This feature has a dependency with the Load Data Warehouse job (but not with the Load Data Warehouse Access Rights job). You always need to keep in mind the order of the jobs:
- First run the Load Data Warehouse job to snapshot current data from PPM transactional tables into the Data Warehouse.
- Once completed, run the Create Data Warehouse Trend
If you run the Load Data Warehouse job again:
- The ETL process will overwrite existing data into Data Warehouse tables, but not into trending tables. Trending tables will be updated only if you run a Create Data Warehouse Trend
- If you need to capture a new trend on the latest updated data in Data Warehouse, run a Create Data Warehouse Trend job again. This will aggregate data in trending tables instead of overwriting, so you can keep historical data.
Your reporting needs will dictate the way you use historical data. Therefore:
- Create new trend reports based on trending historical data and KPIs.
- Create new auditing reports, especially OBS reports for resources and investments.
- Create custom domains, ad hoc views and reports.
- Create, update, and delete trends as required.
The software does not provide out-of-the-box domains or reports using trend data.
What is a representative use case?
As you may know, it’s not possible to audit OBS changes. However, CA PPM 15.3 enables us to use trending to capture OBS historical data about investments and resources.
Let’s say a company wants to audit and track all changes in its resource OBS pool.
- Project manager Paul Martin is assigned to /All Groups/Internal/Operations.
- You run a Load Data Warehouse Once that is completed, run a Create Data Warehouse Trend job.
- When Paul Martin is reallocated to a different department, his OBS is updated to /All Groups/Agile Teams/Shopping Team.
- Run a Load Data Warehouse job again and then another Create Data Warehouse Trend
- Explore the content of the table using a simple query such as:
Note: Adjust the query as needed based on your environment’s specific details.
Note: The ETL process was run on 27/09 and 28/09 for a demo. Other dwh_trd_ tables can be joined in the query to use trend details such name, id, created date, period, etc.
This new out-of-the-box feature provides auditing and historical data—that means we no longer need to rely on custom scripts, tables, triggers or other customizations. With these new trending jobs, housekeeping will be a piece of cake, so unleash your imagination and explore the unlimited types of trending reports.
For readers interested in more detail, check out DocOps. I encourage you to participate in the best-in-class site, where you have access to your peers, events and support. You can also reach out to CA Services for information about CA PPM release 15.3 upgrades/implementations and individualized business outcome references and analysis. Feel free to post in the comments section of this blog or contact me directly via email and Twitter @janetulrich.