Value Driven Data Warehousing: Activity Based Costing Allocation Engine

BASICS: VDDW Architecture > ABC Allocation Engine

VDDW Components:

The ABC allocation engine is the component which does the heavy lifting of apportioning expenses from the GL down to the individual transactions. In a VDDW, the ABC allocation engine takes individual fact table records as its input and writes its output back out at that grain (technically speaking, it creates new fact tables with the same grain as the incoming facts, but with one additional dimension - the activity dimension).

Furthermore, under the VDDW approach, the ABC allocation engine is deeply integrated into the data warehouse staging area, so as to minimize and preferably eliminate the movement of data between the warehouse and the ABC tool. This is important, especially for company’s that generate millions, hundreds of millions, or even billions of transactional records each period, because the VDDW by design takes as input all or nearly of the company’s transactional data, and spits back as output the product of all those transactions times the number of business activities. This is big data management, and big data is best managed by moving it around as little as possible, so as to save processing time and administrative / infrastructure costs.
The ABC allocation process rests simply upon the assumption that an enterprise (or even a subdivision or department within an enterprise), as a piece of money-making, money-spending machinery, can be decomposed and mechanically described as follows: The enterprise spends money to employ resources that perform activities in support of partners and products. The preceding sentence suggests both the critical dimensional data required by the ABC engine, as well as the way in which the engine flows cost thru the system. That is to say, the ABC engine allocates expenses from source pools of money (typically GL accounts), to the resources those monies pay for, to the activities those resources perform, and finally to all of a company’s transactions via the rate at which each individual transaction drives or consumes business activity.
This basically amounts to an iterative process of weight-averaging money from one node in the allocation network to the next according to the value of drivers shared among the allocation targets. What does this mean? An example: The ABC tool may need to allocate a bucket of GL dollars for Salaries to a collection of company departments. To do so it would rely upon some resource driver that makes business sense. One example might be head count. If there’s $100,000 in the Salaries Account, and 10 people work in Cust Service, 6 in the warehouse, and 4 in Sales – 20 people in total – then each Department would be allocated cost from the Salaries account as follows: Cust Service $50,000 ($100,000 times 10 / 20), Warehouse $30,000 ($100,000 times 6 / 20), Sales $20,000 ($100,000 times 4 / 20). That is to say, each department is allocated cost on the basis of that department’s proportion of the total resource driver value across all departments that receive from the same cost pool.
This same basic logic – rolling up a driver value, and then allocating according to each recipient’s proportion of that total driver value – happens over and over repeatedly all the way down the path of allocation, from GL accounts to Resource Pools to activities and finally to transactions.
The ABC allocation engine then has two key subcomponents: A modeling component and a processing component. The modeling component captures all of the allocation rules and formulae and entity-cost-flow mapping that describes how cost should flow thru the system. The processing component takes the allocation model and executes the allocation rules defined therein. The modeling component is an end-user application. The processing component is a long-running batch process that runs entirely within the database.
Under the VDDW approach, the model defined in the ABC modeling component is built to conform and integrate smoothly with the corporate dimension bus, and accept as input the driver data represented by the various facts in the DW star schemas, and at the level of granularity represented by each star schema. Furthermore, the processing component executes within the DW staging area, ideally without moving or copying the input fact / driver data at all, and writing its output (the new activity contribution / consumption schemas) back out into that same staging area, and with the same granularity (plus activity dimension) as the fact tables from which the ABC driver data was drawn in the first place.