Poor Man’s Auditability

One common question in Anchor Modeling is what to do with values that suddenly disappear. Our answer has been that values do not disappear, but may become unknown, and if such occurances are of interest then this fact should be modeled explicitly. The exception is when corrections are made, and if you need to keep track of them the solution is to use concurrent-reliance-temporal modeling. However, there may be scenarios in which you only want to keep some simple auditability of corrections (or some bastardization of disappearing values) while still remaining in unitemporal modeling. This is now possible in the test version of the online Anchor Modeler, thanks to the addition of deletability.

In order not to break compatibility with existing code, new columns have been added in the latest views for deletable attributes. It works as follows with the update trigger on the latest view for an attribute marked as deletable:

update lST_Stage
set
   ST_NAM_Stage_Name = null,
   ST_NAM_ChangedAt = '2018-05-04',
   Deletable_ST_NAM = 1
where
   ST_ID = 42;

Values can be deleted only when Deletable_ST_NAM is set to 1 in an update. Without that row, the update works exactly as before, which is to ignore anything being set to null. The result of the code above is that a new table is created, named ST_NAM_Stage_Name_Deleted, to which all matching rows are moved. Note that this clears out all history. The new table will have one additional column containing the date of the deletion, which is the current date if the attribute is static or no ST_NAM_ChangedAt is present in the update or whatever ST_NAM_ChangedAt is set to if it is present. This should give you a “poor man’s auditability” for disappearing values in unitemporal modeling.

Published by

Lars Rönnbäck

Co-developer of the Anchor Modeling technique. Programmer of the online modeling tool. Site maintainer. Presenter and trainer.

One thought on “Poor Man’s Auditability”

  1. Hi,
    I am a programmer from China. My team is working on an date warehouse project, and my solution is inserting a row instead of replace the data.
    Now we are going to make every column be editable, so I searched your modeling solution.
    I want to translate your content to Chinese, maybe someday we can make a website to share and communicate your solutions.
    May I start to translate and put them on github(or your website/github)? Please send me email to tell the decision. : )

    Best regards from China
    ChanceDoor
    2018/6/24

Leave a Reply

Your email address will not be published. Required fields are marked *