Use Case: They aimed to aggregate the Annual Recurring Revenue (ARR) for each level by summarizing the figures from subordinate companies up to their parent companies, continuing this process through consecutive levels until reaching the top parent.
Solution: Automating the aggregation of Annual Recurring Revenue (ARR) across four hierarchical levels is not feasible with a single rule/data designer. This is because each level must be aggregated sequentially, starting from the lowest, to accurately calculate the totals for the parent company along with its subordinate entities.
A rule chain had to be created consisting of 3 independent rules to achieve the ARR Rollup.
Rule 1:
Step1: We are fetching the data where the Companies do not have a parent company by using the filter Parent Company GSID is Null.
Step 2: Again, fetching the data where the Companies have a parent company by using the filter Parent Company GSID is not Null.
Step 3: Merging the above data sets to get the 1st set of Parent-Child Companies.
Step 4: Merging the data from the merge (Step 3) to the company data from Step 2 to get 2nd set of Parent-Child Companies.
Step 5: Merging the data from the merge (Step 4) to the company data from Step 2 to get 2nd set of Parent-Child Companies.
Step 6: Transforming the data of the above merge (Step 5) to get the aggregate ARR of the child companies.
Step 7: Creating an action on the transform (Step 5) to load the aggregated ARR to the Company ARR.
Rule 2:
Step1: We are fetching the data where the Companies do not have a parent company by using the filter Parent Company GSID is Null.
Step 2: Again, fetching the data where the Companies have a parent company by using the filter Parent Company GSID is not Null.
Step 3: Merging the above data sets to get the 1st set of Parent-Child Companies.
Step 4: Merging the data from the merge (Step 3) to the company data from Step 2 to get 2nd set of Parent-Child Companies.
Step 5: Transforming the data of the above merge (Step 4) to get the aggregate ARR of the child companies.
Step 6: Creating an action on the transform (Step 5) to load the aggregated ARR to the Company ARR.
Rule 3:
Step1: We are fetching the data where the Companies do not have a parent company by using the filter Parent Company GSID is Null.
Step 2: Again, fetching the data where the Companies have a parent company by using the filter Parent Company GSID is not Null.
Step 3: Merging the above data sets to get the 1st set of Parent-Child Companies.
Step 4: Transforming the data of the above merge (Step 3) to get the aggregate ARR of the child companies.
Step 5: Creating an action on the transform (Step 4) to load the aggregated ARR to the Company ARR.
To accurately populate the aggregated ARR for each respective parent company, all the above-mentioned rules must be executed in the proper sequence.
Conclusion :
The implementation of the ARR roll-up hierarchy has established a structured method for accurately aggregating Annual Recurring Revenue at multiple hierarchical levels. By ensuring that each level is aggregated sequentially, starting from the lowest tier, we maintain precision and integrity in our financial summaries. This organized approach allows parent companies to have a comprehensive view of total revenue, reflecting the contributions from all subordinate companies. The sequential execution of rules is pivotal for this system, and adherence to this process ensures that revenue data is consistently accurate and reliable. Ultimately, this refined hierarchy facilitates enhanced financial oversight and robust strategic decision-making.