Develop an Aggregation Engine in Salesforce 

Overview

We need to develop a “smart analytic snapshot” utility to provide schedulable processes to aggregate source data and save / update aggregate results in a destination table.

Challenge Requirements

The user should be able to define aggregation rules and then Execute it immediately or schedule them

This is the break up of tasks:

  A. Create a custom object, Aggregation_Rule__c, that will store the following aspects of the aggregation rule in following fields :

            1. Aggregation Name (Text)
            2. Status : Picklist(Draft,Active.Inactive)
            3. Overwrite_Existing_data: Checkbox - If this checked, overite/update existing snapshot else Insert new rows
            4. Target object: Name of table intgo which the aggregated data will be loaded. 
            5. SourceTableName : (Text) This field stores Name of the source Data Table/object on which the query will be fired. This will make the FROM clause in the query
            6. WhereClause: (Text)  - This field stores the entire where clause 
            
                    
B. Create a custom object Aggregate_Field__c to store the aggregate fields and group by fields . The Select Clause and Grouping will be create from this related list
            1. Field Name (Text)
            2. Aggregation Rule : Master -Detail Lookup(Aggregation_Rule__c)
            3. isGroupByField (Check box)
            4. AggregateFunction : (Picklist- SUM) . For now we will only provide SUM
            5. isUnique (Check box)- This will work is identity field in case of update operation
            6. Target Field (Text) - Name of field in target object this filled is mapped to
             
                                        
C. After the Aggregation Rule is defined , the user should be able to run it as a scheduled job or nudge job
            1. Add a button on the Aggregate Rule details page to schedule the job. Show a popup VF page to enter the Date time and frequncy when the job has to be run
            2. Add a button on the Aggregate Rule details page to run the job immediately.                      

D. Aggregate Rules Engine: This is the controller class that will Excute the Aggregation rule. This will be initiated from the Buttons on Aggregate Rule details page
              These are the basic steps to execute the Aggregate Rule:
              1. Build the  query using data from the Aggregate_Rule__c class and Aggregate_Field__c class
              2. Initiate a Aggregator batch job and pass the query in a batch class 
              3. The aggregator batch job will aggregate the results from all the batches grouped by the group fields defined in Aggregate_Field__c class
              3. Save the aggregated result data into the target table using the Target  field mapping defined in the Aggregate_Field__c class. 
                  Follow these rules when Uploading data:
                    - If the Overwrite_Existing_data is set to true, Always Upsert data. Update existing data using the Unique Fields defined in Aggregate_Field__c
                    - If the Overwrite_Existing_data is set to false, Always insert data.

E. Stale Data Cleanup: The user should be able delete old data.
            Add a button on the Aggregate Rule details page to Delete Stale data in the target table. Show a popup VF page to enter a Date. all Date older than the date entered should be deleted

 

  Note: Since you wont be able to pass Aggregate Queries to the Batch Process, You will have to first create normal SOQL and then create the queryLocator from that query
             For aggregation you will have to loop over all data rows and sum up the data.
             The Aggregator Batch Class should be Batchable and Schedulable
            Volume Requirements. The aggregation query definitions could result in 100K  + records, and the aggregation logic needs to be mindful of such volumes and be able to manage aggregating such source data creatively without causing governor limit issues or exceptions.

Provide 100% test coverage

** As an example, see how some of the DataLoaders works. The difference being, we want to tranfer aggregated data between different objects within the same org
    
Example Use Case 1 - Transaction Line Summary

We have installed our Accounting product (managed package). This includes a Transaction Line Item object which for some customers may have many millions of records.

Transaction Line Items have many lookup fields to other objects used to provide analysis of the Transaction Lines - e.g. standard objects such as Account and Product and other custom packaged objects such as “General Ledger Account”, “Dimension 1”, “Dimension 2”, “Dimension 3”, “Dimension 4”

To make it easier to report on Transaction Lines, we want to aggregate the transaction line data and store this in a new Transaction Summary object.

We want for the user to be able to decide which Transaction Line fields (or fields on the related object in the case of lookups) should be used to select the records, group the records, and which fields should be summed.

For example:

I want to select data from:
c2g__codaTransactionLineItem__c

where:
c2g__LineType__c = “Account”
c2g__Transaction__r.c2g__Period__r.c2g__YearName__r.Name = “2014”

group by:
c2g__GeneralLedgerAccount__r.c2g__TrialBalance1__c
c2g__GeneralLedgerAccount__r.c2g__TrialBalance2__c
c2g__Account__r.Industry

summing:
c2g__HomeValue__c
c2g__DualValue__c

And insert the aggregate results into a new object (TransactionLineSummary__c):

Period - lookup to c2g__Period__c - from c2g__Transaction__r.c2g__Period__c
Trial Balance 1 - text - from c2g__GeneralLedgerAccount__r.c2g__TrialBalance1__c
Trial Balance 2 - text - from c2g__GeneralLedgerAccount__r.c2g__TrialBalance2__c
Industry - text - from c2g__Account__r.Industry

Example Use Case 2: Account-Defect

The standard Account object is used to represent customers.
The standard Case object is used to represent customer issues, which can relate to a specific product defect.
A custom object called “Story” can represent either new features to be added to a product, or defects which need to be fixed in a product.  Story__c uses record types to differentiate between features and defects.
Case has a lookup to Story__c to relate a Case to a specific defect.

Account --< Case >-- Story

(each relationship is a lookup field)

We want to be able to report on how many customers are affected by each defect (Story) and how many defects each customer is suffering from. We would like to use rollup summary fields to provide this information on the relevant records.

However, without master-detail relationships we cannot use rollup summary fields to provide this at the Account or Story level.

Also, there may be several Cases joining the same Case to Story.

We therefore want to aggregate the Cases into a new “Account-Defect” object:

Account |--< AccountDefect >--| Story

(each relationship is master-detail field)

The only fields needed on the Account-Defect object are lookups to Account and Story. Rollup summaries can now be added to the Account and Story objects. 

 

Final Submission Guidelines

Architecture Considerations and Requirement:

*** Code Best Practices that we expect you to follow:
Unit and System Testing. Unit tests must be developed as part of the challenge, 90% to 100% (coverage for all new classes written is required.
Code Aesthetics. Code must be clean (no commented code fragments, unused variables etc), tidy (4 space indents) and well commented. Use of Contest/Challenge names in comments instead of developer names. Utilising good clear and purposeful naming for variables, classes etc. Employ the use of Constants or enumerations where ever required. API methods should reside in the Service layer and not in the controller.
Scalability. 

Volume Requirements. The aggregation query definitions could result in 100K  + records, and the aggregation logic needs to be mindful of such volumes and be able to manage aggregating such source data creatively without causing governor limit issues or exceptions.

Submission Details
This Solution will need to be a Production quality asset.
Your Submission should Include all Code elements for the Salesforce code and metadata elements. Please provide an unmanaged package of the same.
Demo should present the application functionality end to end and overview of the application codeblocks.
Demo the Submission with :
Featuring the Install and Config steps
Organized Code walkthrough
Demonstrating the Test execution (positive and negative tests)
Demonstrating all functionality
A Final Documentation that Covers Deploy/Install, Configure for the solution.
Your submission will be judged by a Community review board.