Best Practices in Account Details Breakdown

Introduction

This topic first appeared as my answer to a question posted in Oracle's OTN Financial Consolidation forums. Since then I have included several improvements to clear-up the presented information. The description of the design issue goes like this:

Suppose we have a Sales account which needs to be detailed by TotalProducts hierarchy in a custom dimension. Therefore TotalProducts is the sum of its children Product1, Product2, Product3 etc. Usually we get the total sales figure as part of the company's General Ledger trial balance. However, in order to have the sales figure both participate on the company's income statement and provide with the desired product analysis, we need to wait until the individual product sales figures become available. This prevents us from previewing calculated and consolidated early drafts of our financial statements.

The concept of the Data Stream

The pattern described above is all too common in consolidation applications. Different pieces of data, arrive at different points in time each providing different information or different aspects of the same information. This is roughly what we call Data Streams. We may identify two or more streams of data in a more formal fashion by analyzing the following characteristics:

1. We get them from different sources (systems, people, departments etc)
2. They become available at different points in time
3. They are of different levels of detail or use different aspects of analysis
4. They come in different frequencies (monthly, quarterly, annually)
5. They may address to different audiences (internal management, external authorities, auditors etc)

It is imperative that we hold data from the different streams at different locations. We should never overwrite or delete information, even if it is redundant as in our example Sales account. All information used must be present and auditable at any time. And more often than not, redundant information among different streams must reconcile.

Let's return to our setting. We receive a sales figure from the trial balance stream and several sales figures broken down by product in a subsequent stream. Keeping in mind the above imperative, we can choose from the following solutions, all equally applied in typical HFM applications:

1st solution: use supplementary accounts
With this method we don't keep a single account for both sales figures. We use account Sales as a target of the trial balance and account SupplSales as a target for the product stream. The account SupplSales attribute Custom1TopMember is set to TotalProducts to provide with product analysis. Here is how the product hierarchy in Custom1 dimension looks like (indented members are children):

In addition to the two accounts Sales and SupplSales, we shall also need a validation account for instance VSales to hold the difference between Sales and SupplSales. To calculate VSales we have to write a validation rule like this:

While VSales equals Sales when the trial balance stream is submitted, as soon as the product stream is also submitted VSales will zero out. We can also connect VSales to the application validation accounts, to control the process flow.

2nd solution: submit the product stream as a journal entry
In this case we need a single sales account and use a slightly modified product hierarchy in Custom1:

When loading the trial balance, the total sales figure will be targeted to the TB_AllProducts member. From there the sales figure will roll-up to TotalProducts and the total sales figure will enable us preview the financial statements before all submissions of different streams are made available.

At a later time when the products stream comes in, we shall load it as a balanced journal entry. This journal entry will credit product sales figures to Product1, Product2 and Product3 and debit the total to TB_AllProducts, effectively countering the previously loaded amount, without overwriting it. The result will look like this:

Note that when we select <Entity Curr Total>, we get both the trial balance figure at the summary level, and the individual product sales at the detail level. Also note that if the individual product sales submitted, do not meet the summary loaded with the trial balance, a balancing figure will appear in the cell identified by the combination A#Sales.C1#TB_AllProducts.V#<Entity Curr Total>, instead of zero. This data cell can be considered as the validation, though we have neither provided with a validation account, nor a validation calculation rule. Once more, we can connect the above cell to the application validation accounts, to control the process flow.

3rd solution: use an automatically calculated balancing entry
This is a more advanced technique, whereby you keep a single Sales account and support with product analysis in a custom dimension as before, with the following changes:

Note that in this case we must also set the attribute AggrWeight of TB_AllProducts to zero, so that we ensure that there is no double counting when both the trial balance and the product streams are loaded. In order for this to work we also need to write the following calculation rule:

As soon as we load the trial balance stream and execute the calculations, the picture will be like this:

Note that the whole trial balance amount is captured from the calculation and rolls all the way up to the total, while the product sales cells are still blank.

Subsequently, the product stream comes in, we calculate again and the above picture changes as follows:

Note that in the above example the balancing entry is zero, while the TotalProducts value has not changed since the initial load of the trial balance.

Now, interesting things happen when the product values do not meet the trial balance total. In that case, the balancing figure will fill the gap between the two totals. We can view the balancing member as the validation between the two streams. Like the previous solutions, we can connect the balancing member to the application's validation accounts to control the process.

Conclusion
We presented three techniques that we can use for addressing the agreement of redundant information coming in different streams. There is no single answer to the question which is best. One has to examine each individual account and the related data streams that correspond to the account and decide what fits best. I have cases where all three patterns have been applied to the same application for different accounts and/or streams. Possibly, you could come up with a hybrid solution, it is all fine.