Asynchronous rollup numbers forecasting methods and systems

• US 9,009,709 B2
• Filed: 03/16/2011
• Issued: 04/14/2015
• Est. Priority Date: 03/16/2010
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Long, Martin, Chou, Chien-Lang
• Primary Examiner(s): Wai, Eric C
• Application Number:
  US13/049,596
• Publication Number:
  US 20110231848A1
• Time in Patent Office: 1,490 Days
• Field of Search: None
• US Class Current: 718/100
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/2435 Active constructs
• CPC Class Codes: G06F 9/546 Message passing systems or structures, e.g. queues

Abstract

Improved methods and systems are provided for asynchronously updating forecast rollup numbers. The asynchronousity is achieved by decoupling the source data change from further manipulations of the source data, for example in calculating and updating forecast rollup numbers by user role hierarchy, layer by layer. An event message queue implementation can be used for asynchronous processing. The process works by dequeuing a batch of event messages and then deduping and sorting them before applying forecast logic. Forecast numbers are updated based on target data and then rolled up the user role levels by aggregating forecast numbers for all subordinate forecast data entries.

Claims

1. A computer-implemented method of asynchronously updating forecast rollup numbers:

receiving, by a processor system, a data update, including a source data or an override data update;

adding, by the processor system, one or more events to an event queue based on the data update;

dequeuing, by the processor system, a batch of one or more events from the event queue; and

processing, by the processor system, the batch of events to update forecast rollup numbers, the processing including;

retrieving one or more source data based on the batch of events being processed,

retrieving one or more target data based on the batch of events being processed, each target data being associated with a corresponding source data,

determining differences in value between each target data and the corresponding source data,

updating each target data based on the corresponding difference in value,

retrieving forecast data based on the batch of events being processed,

aggregating the differences in value associated with users at a first level of an organizational hierarchy to provide a first aggregated data, and

summing the first aggregated data with forecast data corresponding to a user at a second level of the organizational hierarchy to provide a second aggregated data, the second level being at a higher level of the organizational hierarchy than the first level.

2. The method of claim 1 wherein the step of batch processing of events includes:

deduping the events;

sorting the events by event type; and

sorting the events by user role layer.

3. The method of claim 2 wherein the event type includes at least two of the following event types:

entity event, entity event being either an insert or an update to source data;

override event, override event being either an insert or an update to forecast override data; and

rollup event, a rollup event being one that computes a forecast rollup.

4. The method of claim 3 wherein the processing of events includes:

processing all entity type events, one at a time;

processing all override events, one at a time;

processing all rollup events, one at a time, layer-by-layer.

5. The method of claim 1 wherein the processing of events includes:

processing the events one at a time;

obtaining a lock on forecast data based on the event being processed;

obtaining a lock on target data based on the event being processed;

re-queuing the event being processed where a lock could not be obtained;

otherwise updating target data and forecast data; and

adding a rollup event to the event queue where a user role layer above the current processing layer exists and where the event is not a duplicate of another event in the batch being processed.

6. The method of claim 5 wherein the step of updating includes:

retrieving a source data object based on the event being processed;

comparing one or more dimension values of the event being processed, source data, and target data;

updating the target data with the source data based on the comparison; and

updating forecast data based on the comparison.

7. The method of claim 6 wherein measure value of the target data is updated with the measure value of the source data, where the event, source, and target dimension value all match each other.

8. The method of claim 6 wherein the measure value of the target data is updated to zero where the event and target dimension value match but the event and source dimension value do not match.

9. The method of claim 6 wherein measure and dimension values of the target data are updated, when the source and event dimension value match, but the event and target dimension value do not match, and the target measure values are zero.

10. The method of claim 6 wherein no update is required to the target data when dimension value of the source and target data match but dimension value of the target and event do not match.

11. The method of claim 6 wherein no update is required to the target data where dimension value of the event, source, and target data do not match each other.

12. The method of claim 1 further comprising:

adding one event to the event queue where a user has updated only the measure values of the source data; and

adding two events to the event queue where a user has updated both dimension and measure values of the source data.

13. The method of claim 12 wherein the event added to the queue includes a forecast data identifier and source data identifier.

14. A non-transitory computer-readable storage medium that stores code, which when executed by a processor, causes the processor to:

receive a data update, including a source data or an override data update;

add one or more events to an event queue based on the data update;

dequeue a batch of one or more events from the event queue; and

process the batch of events to update forecast rollup numbers, the process causing the processor to;

retrieve one or more source data based on the batch of events being processed,

retrieve one or more target data based on the batch of events being processed, each target data being associated with a corresponding source data,

determine differences in value between each target data and the corresponding source data,

update each target data based on the corresponding difference in value,

retrieve forecast data based on the batch of events being processed,

aggregate the differences in value associated with users at a first level of an organizational hierarchy to provide a first aggregated data, and

sum the first aggregated data with forecast data corresponding to a user at a second level of the organizational hierarchy to provide a second aggregated data, the second level being at a higher level of the organizational hierarchy than the first level.

15. The non-transitory computer-readable storage medium claim 14 wherein the storage medium consists of two or more storage units.

16. A database system that asynchronously updates forecast rollup numbers, the system comprising:

a database configured to store source data or override data, target data, and forecast data; and

a processor configured to;

receive a data update, including a source data or an override data update;

add one or more events to an event queue based on the data update;

dequeue a batch of one or more events from the event queue, and

process the batch of events to update forecast rollup numbers, the process including the processor configured to;

retrieve one or more source data based on the batch of events being processed,

retrieve one or more target data based on the batch of events being processed, each target data being associated with a corresponding source data,

determine differences in value between each target data and the corresponding source data,

update each target data based on the corresponding difference in value,

retrieve forecast data based on the batch of events being processed,

aggregate the differences in value associated with users at a first level of an organizational hierarchy to provide a first aggregated data, and

sum the first aggregated data with forecast data corresponding to a user at a second level of the organizational hierarchy to provide a second aggregated data, the second level being at a higher level of the organizational hierarchy than the first level.

17. The system of claim 16, wherein the processor is implemented in a computer system coupled with the database.