Tracking Data Changes over time

[Scott_Hall]

I'm wrestling with how best to implement change tracking in a smooth way and thought I would see how other people have approached it.

I am tracking budget data and the change actions that occur over time, with approval state and the needed to see prior configurations, not just creating a static log of all data changes like an audit trail. I'd like to know what people see as best practice.


The database currently has 3 tables-
-tblBudgets - [BID] is PK, other metadata
-tblBudgetLines - [BLID] is PK, [BIDfk] is FK,[Event],[StartDate],[EndDate],[Cost], one to many with tblBudgets
-tblChangeControls - [CCID] is PK, other metadata


I need to be able to:

1. Record the changes to a budget over time, but changes may or may not get approved (someone could ask for time or money and be rejected, keeping the original value).
2. Pull up a budget and see the current approved dates/dollars
3. Look back at each stage between approved changes, tracking cost growth and date shifts, etc. i.e. it was initially $1000, then changed to $1500, then $2000.

My issue is that I'm not sure the optimal way to manage this data.

• I could make a standard audit trail table. Each time a line is updated in tblBudgetLines I write the original value into the audit log. Maybe the CCID is tied in this log so that we know a specific line changed by request X. What does this mean when it is not an updated line, but a new or deleted line? There is either a new entire line in the budget (but not the audit trail- how can I tell it is new), or the line is gone from the BudgetLines (how do I know it was removed).
• I could keep all of the line variations in the main tblBudgetLines table but add a column to represent state...maybe need the CCID for each line as well.
• Maybe junction table that has line (BLID) and "Added","Modded","Removed"

Anyway, I'm curious as to what has worked best for folks in various scenarios.

Thank you,
Scott

 

[The_Doc_Man]

I never had one of the situations you have described so have no anecdotal guidelines to offer on general structure.

Here is the problem you (and therefore we) have. In your bulleted list you show three different approaches - but the question will always be "what did you intend to do with these things" and THAT is going to govern what & how you you store. Asking for an approach is pointless unless we know WHAT you are approaching.

It is clear that you need to have info about past budgets vs. current budgets. No question there. But what do you need to do about the unapproved stuff? In general, the ideal case is that function guides form and method. Reality has a way of stepping in, but the goal is to design something that you want/need to do the job. The more goals you can define specifically, the better off you will be.

 

[Pat Hartman]

It is not clear to me how what you want is different from an audit trail. To clarify this in your mind, you might want to make the reports you want to see with sample data. That should help you clarify your direction.

If you don't care about unapproved changes, maybe you shouldn't log them. To make reporting easier if what you want to see is each version of the budget, perhaps, you should not log individual changes at all. When the total budget is approved, copy ALL the data to the history table.

When I do logging, I use the form's AfterUpdate event because I don't want to log changes that didn't actually happen. If you turn on this functionality AFTER data already exists in the table, you need to copy the existing data and add it to the log table. When you are doing the logging, you can distinguish between adds and changes by keeping an internal flag so when you get to the AfterUpdate event, you can check the flag to determine whether the record that was just committed was new or changed. Delete is logged as a delete and the only fields filled in are the ID and the ActionDate. OR, don't allow deletes at all. Use an IsActive flag and set it to No. Of course if you use this method, all your queries must ignore inactive records except perhaps for the maintenance form.

 

[Scott_Hall]

Apologies Doc, let me see if I can clarify the situation.

This is our workflow:

A project is scoped out and a budget is drafted. There could be several budgets drafted per project, exploring different methods of achieving the project goal…but ultimately only one “approved” budget when the project is given the nod. The budget is tracking scheduled bookend dates and the cost.

A simple budget (in reality they can be hundreds of lines):

• Planning ............................. 01/03/2018.....05/15/2018............$1000
• Environmental Assessment.....05/01/2018.....07/01/2018...........$20000
• Realty Review.......................05/01/2018.....08/15/2018.............$1000
• Legal Compliance..................09/01/2018.....10/01/2018.............$5000
• Architectural Drawings...........10/01/2018.....03/20/2019...........$25000
• Construction ........................04/01/2019.....09/30/2021.......$1500000

So at “Approval”, we have a scheduled start of January 2018 and scheduled end of September 2021. The project is expected to cost $1,552,000

A Project Manager can submit revisions to multiple lines on a Change Request. The request goes through an approval process, so managers to need to see what the project is currently planned to be as well as what the requested budget would be.

Work begins…

Change Request 1:

The start of the Environmental Assessment gets delayed 90 days. The duration doesn’t shift (it is still 60 days), but the dependent events are now pushed back as well. The Legal Review…and the Architectural work… and the actual Construction. So changing one line ended up pushing the additional others. Cost is not impacted.

Change Request 2:

The architectural work is done within schedule, but the cost is now going to be $35,000 instead of $25,000.

Change Request 3:

The construction work gets weather delays and takes an additional month and costs $10,000 more.

If a change(s) were to be rejected, we would want to keep the request and the values, but not apply it to the approved budget. In this case I will assume that none of the changes get rejected.



The idea is that management can approved or reject each change request, and then look back at the end of a project to see how the costs and schedule changed over time…what drove the project off the rails.

 

[The_Doc_Man]

Pat and I would probably agree that this looks much like a budget audit trail. Close enough that the old rule applies: Looks like a duck? Check. Waddles like a duck? Check.... get the idea?

However, it seems that you REALLY wanted something more customized than a simple audit trail. I can understand that, since simple change-audits tend to be disorganized in appearance. It can be hard to "dig out" and reassemble the details.

My thought is perhaps you should treat ALL budget elements as change requests starting from an empty project. I.e. the initial estimate is comprised of changes from doing nothing. Every line item would need some kind of description, cost-change estimate (which would be a positive or negative effect), time-change estimate (again, positive or negative) , and a date of when proposed (or discovered). You would need each change to have a "who proposed" and a "who approved" field, or perhaps a list thereof if proposed by committee and approved by committee, if that is what you need. Plus, of course, each line item has the status "Proposed" or "Under review" or "Approved" or "Rejected" or such as that.

Then of course your current budget is the roll-up of all approved items. Your history is the roll-up of all items. Other selective roll-ups can exist, such as "everything not rejected" or "everything that made it through the review stage" or similar.

That ISN'T quite an audit, but you could call it a specialized audit. Then your low-level change auditing would only track details like "changed from 'proposed' to 'under review' on 'thus-and-such' date" or "changed to 'Approved' by 'Joe Schmuckatelli' on 'this happy date'" - and so on.

Here's a variant thought. Before I retired, the U.S. Navy bought in on a commercial off-the-shelf (COTS) package called "Solar Winds" that did project change management for us. It was pre-built but capable of modification to customize how we viewed things.

It sounds like you folks are trying to "roll your own" but I wonder if you might save some time & bucks by searching for a COTS solution that has some local customization capability. I neither recommend nor denigrate Solar Winds because I was only an end user thereof. I have no idea about what it took to manage and customize it.

I did a web search for "Change Management Software" and got a page full of hits. Perhaps you might wish to look into some COTS offerings before taking on a really complex package to be created from scratch. The "make/buy" decision is complex when the thing in question is complex. I can tell you that one big hit either way would be the time for this thing to be ready for prime time. "Home-grown" has a long lead time based on complexity but COTS has a shorter lead time based on customization before you are sure it is ready. Both home-grown and COTS each have a user's learning curve, but for the COTS package they will sell documentation. For home-grown, you have to roll your own documentation, too. Not a trivial problem.

One last thought: Looking into COTS packages in some depth might give you a better viewpoint on how to approach the problem even if you later decide to roll your own package. Seeing how it is done by COTS packages oriented directly to your problem is a better source of knowledge than hoping that some of us have worked on such a detailed package.

 

[Scott_Hall]

Guys,

Thanks for the information, there is some good stuff here and I like where you were going Doc.

Unfortunately I am working for the government and in a less visible department. Buying off the shelf software is...hard. My group is still on the waiting list for a CMMS package. Ten. years. later. Anytime a bigger fish comes to IT asking for resources, our project gets kicked off the docket since we are small.

I've done quite a bit of research on different CM packages, and specifically looked at SolarWinds. I got some extra ideas but at the end of the day I'm going to be in this boat for awhile.

 

[The_Doc_Man]

Scott, I understand. I was a USN/DoD for 28+ years myself. But here is something to consider. If the government has contracted with a vendor, it is a LOT easier to find an appropriate contracting vehicle to extend one product to another site by just adding seats to a license. (Sometimes, it is, anyway.) If the COTS vs. home-grown issue leans towards COTS, extending an extant contract might be a good strategy.

On the other hand, home-grown products are known to be more focused. "Less visible department" eh? Any less visible than CNRF Code 10 or NAVRESINFOSYSOFF? Though I ended up with NEDC NO, which was high visibility within the Navy because it became a regional data center.

 

[Scott_Hall]

"Less visible department" eh? Any less visible than CNRF Code 10 or NAVRESINFOSYSOFF?

Okay you won that round.

I work for the DOE in a Facilities team. When people hear that "F" word they automatically think "broken toilet", but we are spending our money doing seismic bracing, bridge installs, etc. My team is viewed as somewhat optional since we don't produce or transmit power (we just keep buildings and bridges from collapsing on people).

You know, typical corporate style politics. People don't understand that when bad things don't happen it is because someone is working on that. That perception always puts us high on the funding-chopping block, and low on the list for resources.

 

[The_Doc_Man]

As a system admin, I was ALWAYS considered as overhead, part of the maintenance cost center, rather than a member of a profit center (when in the private sector) or a funded project (when with the DoD). Contractors ALWAYS worry about becoming redundant or superfluous. However, after a couple of disasters caused by cutting a little TOO close to the bone and thus having inadequate support during Hurricane Katrina's aftermath, the Navy brass learned the value of overhead personnel. So I fully sympathize. Different structure within DOE but same concept. When your charges are indirect, you ALWAYS get the short end of the stick.