0

We have a business requirement to show a cost summary for all our projects in a single table.

In order to tabulate these costs we have to query through all the client tasks, regions, job roles, pay rates, cost tables, deliverables, efforts, and hour records (client tasks are in the same table and tasks and regions are in the same table and deliverables, effort, and hours are stored as monthly totals).

Since I have to query all of this before I go for-looping through everything it hits a large number of scripting lines very quickly. Computationally it's like O(m * n * o * p) and some of our projects have all four variables that go up very quickly. My estimates for how to do this have ranged from 90 million lines of code to 600 billion.

Using batch apex we could break this up by task regions into 200 batches, but that would reduce the computational profile to (600 B / 200 ) = 3 billion lines of code (well above the salesforce limit.

We have been playing around with using informatica to do these massive calculations, but we have several problems including (1) our end users can not wait more than five or so minutes, but just transferring the data (90% of all records if all the projects got updated at once) would take 15 minutes over informatica or the web api (2) we have noticed these massive calculations need to happen in several places (changing a deliverable forecast value, creating an initial forecast, etc).

Is there a governor limit work around that will meet our requirements here (massive volume of data with response in 5 or so minutes? Is force.com a good platform for us to use here?

This is the way I've been doing it for a similar calculation:

Loop Overview

micahhoover
  • 2,101
  • 8
  • 33
  • 53
  • I can now get around the astronomical number of script lines executed (I think gov limit was 150k) by creating maps. They're pretty easy: just add another map item from everything that gets returned. For that record make the key something unique (like id or month etc) and make the object the thing getting referenced. Then I can just use a get function to say, "Hey grab me the deliverable record for x project for month index" instead of using a for loop. – micahhoover Jul 27 '12 at 15:46

1 Answers1

0

An ERD would help, but have you considered doing this in smaller pieces and with reports in salesforce instead of custom code? By smaller pieces I mean, use roll-up summary fields to get some totals higher in your tree of objects. Or use apex triggers so as hours are entered the cost * hours is calculated and placed onto the time record, and then rolled-up to the deliverables. Basically get your values calculated at the time the data is entered instead of having to run your calculations every time.

Then you can simply run a report that says show me all my projects and their total cost or total time because those total costs/times are stored/calculated already.

Roll-up summaries only work with master-detail Triggers work anytime, but you'll want to account for insert, update as well as delete and undelete! Aggregate Functions will be your friend assuming that the trigger context has fewer than 50,000 records to aggregate - which I'd hope it does b/c if there are more than 50,000 time entries for a single deliverable that's a BIG deliverable :)

Hope that helps a bit?

caleb
  • 141
  • 1
  • This is a terrible solution, but that is SF's fault not yours. Aggregate functions and formula/rollups won't help because there are so many up/downs with respect to the parents children in the ERD for our business calcs. We have talked with all kinds of SFDC consultants, but no way to break it up any smaller. – micahhoover May 21 '12 at 19:13