How to populate slice tables for TSV Audit data in Clarity

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

We want to develop our own user-defined reporting to view the 'sliced' value in the ODF_SL_OLD_VALUE & ODF_SL_NEW_VALUE tables. Currently, those tables are not being populated with any values. How do we populate these tables?

Steps to Reproduce:

1. Enable Audit Trail for a Time-Varying Attribute (TSV)
   
   
   • Administration, Studio: Objects, Cost Plan Detail Object, Audit Trail
     
     
   • In the 'Auditing on Updates' section, select some of the TSV attributes, such as planned units, planned cost
     
      
     
     
2. Create a Project detailed Cost Plan and enter in planned units, planned costs for a few periods in the TSV cells
   
   
3. Edit the cost plan TSV values and change the original values to different values
   
   
4. Administration, Audit Trail - see TSV data appears in the Audit Trail
   
   
   
   
5. Query the slice table for the TSV 'sliced' values
   
   
   • SELECT * FROM ODF_SL_AUD_OLD_VALUE
     
     
   • SELECT * FROM ODF_SL_AUD_NEW_VALUE
     
     

Expected Result: We can see the data in the UI, therefore we expected to see data in the slice table

Actual Result: We do not see any sliced data in the tables

Environment

Release: Any

Cause

The data for these tables are not sliced unless a new Time Slice definition has been created.

Resolution

Steps to Setup TSV Audit Slice Data:

1. Login to Clarity as an administrator user 
2. Administration, Time Slices, click 'New' button to create a definition for Old Audit Values 
3. Fill in the required fields, selecting 'Time scaled value audits::Old Value' and save the definition
   
4. Click 'New' button to create another definition for New Audit Values
5. Fill in the required fields, selecting 'Time scaled value audits::New Value' and save the definition

Now the data will appear in the tables:

You will need to tie the ID in this table back to your object instance.

For example,  for a Time Varying Attribute on the Team object which is also audited using the above steps

ODF_SL_AUD_NEW_VALUE.prj_object_id (or OLD_VALUE)  the team records.

Note:  If the values in the time scale are removed/set to 0, the slice then is deleted and will not be available in the TSV audit or the table.  If you need to see who removed the allocation you will have to check the related main allocation audit to see who updated the record as there will be no TSV audit available.

Here is the linking to associate the ODF_SL_AUD_NEW_VALUE to PRTEAM table

ODF_SL_AUD_NEW_VALUE. prj_object_id =  CMN_AUDITS_TSV.id

The CMN_AUDITS_TSV table links back to the PRTEAM.prid

Make sure when querying the CMN_AUDITS_TSV table that you choose the correct object_code as well to make sure you are using the correct object.

 

Example of team

SELECT t.PRPROJECTID,
  tsv.OBJECT_CODE,
  t.PRRESOURCEID,
  t.PRID,
  an.SLICE,
  an.SLICE_DATE,
  an.CREATED_DATE
FROM prteam t
INNER JOIN CMN_AUDITS_TSV tsv
ON t.PRID = tsv.OBJECT_ID
INNER JOIN ODF_SL_AUD_NEW_VALUE an
ON tsv.ID           = an.PRJ_OBJECT_ID
WHERE t.PRPROJECTID       = 5005019
AND tsv.OBJECT_CODE = 'team'