KPI Adjustment Table

Question

Hello,

I have a table with monthly summary of KPIs. The customer now wants to be able to make adjustments to the original data. I have to allow for adjustments as well as preserving the original data. The report I generate will show original data + or minus any adjustments.

Here is an example of what I need to be able to do.

Table A Report_Month 7/31 KPI_1_QTY 88

Customer requests to decrement KPI_1_QTY by 5. This results in KPI_1_QTY being 83. I need to report 83 but have to keep the original value as well.

Any ideas on how to code this?

Answer 1

There are a number of ways of addressing the general problem of keeping audit trails of original data. The simplest is to timestamp the row and include an "IsHead" bit field.

For example:

FactID Timestamp Attribute Value IsHead
2      8/1       KPI_1_QTY 83    true
1      7/31      KPI_1_QTY 88    false

If you want the "head" branch of the data, you filter on IsHead = true (usually you create a view for this).

If you want the change log, you display all the rows in descending order by timestamp.

If you want the data as it exists at a specific point in time you reconstruct the data as it existed on that date (left as an exercise to the reader).

If there are properties associated with the adjustment, create another table with the fields you need to describe the change. For example...

FactID ModifiedBy ModifiedOn ValueDelta Description
2      Fred       8/1       -5          Adjusted KPI_1_QTY per customer request

Answer 2

Thank you very much!! :)