Author Topic: Summary Figures and De-Normalization Issues  (Read 2431 times)

Offline Dean Wooldridge, Jr.

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 494
  • Karma: +9/-0
  • Gender: Male
    • View Profile
Summary Figures and De-Normalization Issues
« on: May 30, 2007, 09:38:30 AM »
I'm looking for a "this is how we approached the problem" kind of answer.

Issue #1 deals with the summarization of data.

For example, the OOTB system has support incidents & support steps.  Pivotal goes through some hoops to summarize time and step counts on to the support incident record.  OK, no problem.  We want to add all the minutes on the steps by account executive and store that total on the employoee record.  Account executive is determined by the individual assigned to the company.  Support incidents belong to companies in our world so the process is not tough on the surface.  We could do what Pivotal does and incrementally add when steps change.  But here's the gotcha - sometimes the Account Exec assigned to the company changes (naw...).  When this happens, all of the times for the old and new account exec have to be recalculated.  That can be done but what a delicate balance and a coding mess especially since you know something will get out of kilter and we will have to recalculate everything.  I've considered just setting up a SQL job that runs every few hours and recalculate the world.  It violates the "Do it inside of Pivotal" rule but it's fast.  I've considered a Pivotal agent that runs every once in a while but Agents are slow... So my question is:  How have you addressed something like this?

Issue #2 deals with denormalization

This is very much like the earlier issue and again it's a How did you do it? question.  We have some record where we have copied the Account Exec off the company to this record.  We did this with a foreign key formula in the field.  What appears to be the problem however has to do with View Filters.  If the Acct Exec is changed on the Company record, the child record does not reflect the new Acct Exec unless we retrieve it and that new value is not saved unless we save the child record.  It appears to me that view filters just get the record and don't actually force the record to "recalculate" the new Acct Exec.  So we are left with child records that are not up to date with the records they get their values from.  This is nothing new, Pivotal discusses the issue of record tickling to bring records up to date.  Generally I have addressed this with a Pivotal Agent that runs periodically to retreive and re-write record after record.  My question is:  Anyone have any better ideas or great insights?