Technique: Updating a data warehouse.

[Ocean West]

I have a process where I am storing aggregated data in to a table. Which consists of the following data points:

date | staff | data | value

This process will create two records per day for each staff person for the two types of data.

The value is Get(FoundCount) after a find request in a Quotes table for the day / staff and a few other criteria including an omit. I store this in a $quotes variable.

Then I perform a constrain on this founds set to get another Get(FoundCount) which is $approved quotes. 

The process then creates a record in the data warehouse. 

date | staff | data | value
8/28 | Jim | quote | 32
8/28 | Jim | approved | 3

(note: the data actually had full date and staff name - brevity is for this example )

However not all jobs are approved the same day they are quoted. So when I run the update process later I need to be able to update the approved count. But there isn't a 1:1 relationship between this found set and the aggregate record. 

The update technique.

To start this process in the warehouse table I created a new field MD5 which is exactly what it means:  

GetContainerAttribute ( date & staff & data ; "md5" )  

which would become this value for the approved record 959C39DA78749FDEF00AC1EEE4501550

 I then put a global text field in the quotes table I call it UPDATE I related this field to the MD5 field in the warehouse table.

The update will run again go back a few weeks and establish the exact found set of records for the quotes. It also does the constrain for the approved and now at this point in time there is most likely more approved jobs, in this scenario there are now 13 jobs approved.

To update the value by setting the UPDATE field to result of  GetContainerAttribute ( $date & $staff & "approved" ; "md5" )  based on the found set of records this time using variables that are currently set in the running script plus hard coding the data type.

This will create a 1:1 relationship between quotes and the warehouse record where I then set the value across the relationship to 13 which is based on the current Get(FoundCount)

And this is how I update the values, and didn't have to change context while the script is running. 

Hopefully you find it a useful technique.