Introduction
Firms collect and store data in very large quantities and at various levels of granularity. This data is then used to perform high quality analytics to support various business decisions. A well conducted analysis could have significant impact on any firm's bottom line and in this article we highlight the importance of choosing the right data rollup to perform the analysis. While we illustrate the issue in the context of a forecasting problem, the learning's are universal.

A good forecast will depend on (a) The choice of an appropriate statistical technique (b) The choice of an appropriate data rollup at which to perform the analysis (actually this holds true for all types of analysis). While (a) is a very important component of a successful forecast, we will leave a detailed discussion on it for another day and focus on (b) for this article.

Top Down or Bottoms Up?
Let us begin with the following scenario:
A grocery chain which operates in the North Eastern United States wants to incorporate data analytics into its business decision making process. In particular, it is interested in setting up a modeling system which will forecast sales performance. Sales data from all its stores have been collected. Key users have been identified and methodology appropriate for their needs is being formulated. Some of the pressing issues involve not only decisions regarding the sophistication of modeling and implementation, but also the types of data to use and the level of data rollup.

This brings us to the central point in our discussion: What is the right data rollup at which to perform the forecast?
We could roll the data up to the highest level and perform analysis at that level. If lower level numbers were ever required, we would arrive at that number by using some kind of splitting rules. This is referred to as the Top Down Approach.

Alternatively, we could generate the forecast at the lowest level and higher level numbers could be obtained by rolling up the lower level numbers. This is called the Bottoms Up Approach.

Conventional datamining approach would suggest starting at the lowest level and building up to the top. While this might be appropriate for some cases, it is not always the case. There are times when a top down approach is clearly superior.

In an analytic environment where the primary focus is on top level numbers (ex. The numbers are used by the CEO to guide his business strategy), the top down approach is clearly more appropriate. Not only are the data and computational requirements of the top down analysis less demanding, but the accuracy is also likely to be higher. To understand this take the case where we are comparing the accuracy of the annual sales forecast of the grocery chain, with that of one of its stores in New Providence. The New Providence store will be subject to a lot of local level fluctuations. There could be unexpected weather situations, power outages or other factors that would influence the sales. These are very hard to predict. However at the aggregate level a lot of these uncertainties are reduced. In our example, a lot of these unexpected happenings cancel out across the stores and the aggregate number is more stable. In addition, the accuracy of a forecast can be often enhanced by identifying leading indicators like macro economic variables. These variables might be more easily available at the aggregate than the granular level, helping make top down approach more accurate.

However often the focus of analysis might be at the lower level. For example, the managers of the grocery stores might be interested in predicting sales at their stores to better manage the operations and inventories. A top down forecast which focuses on the top level numbers and distributes to lower levels based on some rules will be inadequate here as it will miss out on the local level nuances. A bottoms up approach would be more appropriate in this situation.

The table below summarizes the two approaches:

Approach Top Down Bottoms Up Data Aggregate level data of the metric to be predicted External Macro Economic Data (if appropriate) Rules/Historical Proportions Data granular to the desired level of analysis External data (if available and appropriate) Advantages More stable and accurate at the aggregate level Higher accuracy at the granular level Drawbacks The accuracy at the lower levels are off due to the lower level nuances which are not captured in rolled up data Adding up lower level forecasts leads to less accurate top level forecasts Often misses out onopportunities to incorporate external data which is available at the aggregate level

Hybrid Approach
However, there are often situations where an organization might need forecasts at multiple levels. For example, the CEO of the grocery chain might want to look at firm level sales forecast, whereas store managers might be interested in store level predictions. A common forecast which uses the top down or the bottoms up approach will always be less accurate at one of the levels. The firm could use independent forecasts at the top and lower levels to make them both accurate. However, a problem with this approach is that the sum of lower level forecasts will generally not add up to top level forecasts and lead to internal inconsistencies. A Hybrid Approach can help resolve this problem. Here are the specifics of the hybrid approach:

• Generate lower level forecast using granular data
• Generate top level forecast with aggregated data
• Use the lower level forecasts to generate proportions which are then used to split the top level number