BASICS: VDDW Architecture   >   Contribution & Consumption Schemas

• Enlarge VDDW Architecture VDDW Components: Overview Common Dimensions Common Schemas ABC Allocation Activity Dimension Contribution & Consumption OLAP HyperCube What's Left Out

  Along with the Activity Dimension, the VDDW also introduces several new star schemas to the dimension bus.  These new star schemas can be classified into one of two categories:  those which measure activity contribution, and those which measure activity consumption.

• The star schemas which measure contribution show how corporate monies and resources flow so as to enable activities. There are two star schemas involved here. One which captures allocated dollar amounts by GL by Resource by Activity by Time (and possibly also by location if the business keeps multiple sets of books for each location in which it operates). The other captures allocated Resource drivers - like employee hours, head counts and building square footage - by Resource by Activity by Time (and possibly also location). These star schemas are the top two depicted in the middle-right section highlighted above. The contribution schemas, in short, show which resources do how much of what and the performance impact of all this activity.

• It’s important to note that the second star schema, the one which tracks resource drivers by activities, gives rise to the concept of capacity. That is to say, for a given pool of resources, when you sum up the total hours, head count, or square footage for that resource pool, across all activities in which that Resource is engaged, you get the total available capacity of that resource. An enterprise only has so many square feet of space at its disposal, and its employees only work so many hours.  Later on, in examining the OLAP portion of the VDDW, these capacity limits can be divided by the activity consumption results, so as to assess the productive utilization of those resources as well as how much bottom line bang those resources deliver (examples: Net Op Profit per Hour spent Selling, NOP Per Advertising Dollar spent, or NOP per Minute On-Air, etc.)

• The Inventory Performance and Sales Performance star schemas depicted above are examples of schemas which measure activity consumption. These schemas are very simple, though they are very granular with large record counts.  They measure the allocated dollars by activity, by whatever other combination of dimensions define the grain of the original fact / transaction table from which the activity drivers were sourced. Along with the allocated dollars, the consumption schemas also capture allocation units, which represent the calculated weighting factor used for spreading the buckets of activity dollars down to the transactions.  While the ABC allocation engine uses the fact data from the conventional star schemas as its primary inputs for driving allocation, it typically also applies some logic to those data inputs to arrive at the right weighting factors.  Using the example of a fictitious distribution company introduced under the Activity Dimension, a company’s Packing expense is well related to the quantity of that item shipped, a measurement which should be easily culled from the conventional DW.  However, for allocation purposes, simply spreading the Packing activity dollars to transactions on the basis of Quantity shipped isn’t likely to be very accurate. This is because different products may exhibit different levels of handling efficiency based on factors like the following, to name a few:

   

  • Whether the product is shipped on pallets, in individual cases, in bulk, or as individual units

  • Whether the product is fragile

  • Whether the product is oversized

  • Whether the product must be bundled together with other products on to a mixed pallet in support of a specific
  customer's requirements

   

  Upon further analysis, it becomes apparent that it is necessary to somehow factor in variables like those mentioned above, in conjunction with quantity shipped, so as to accurately drive Packing expenses from the activity to the transaction; further, the right unit for expressing what makes one transaction more or less efficient to Pack than another isn’t so much Quantity as it is estimated Labor Time.  This then is what is meant by the Units measure which appears in the above diagram.  Units are the final metrics which the ABC engine uses internally to drive allocation, and they are typically generated by defining algorithmic equations in the ABC modeling tool that combine & compute upon multiple inputs from the data sourced from the DW.

• When the activity units in the consumption schemas line up with the units in the contribution schemas, this then enables the types of capacity and leverage analyses described above.  For example, if we capture the hours worked by a particular department, say warehouse hands, and we know what proportion of those hours are dedicated to a particular activity, like Packing, and if Hours is also the Unit used for driving activity-to-transaction allocation for the Packing activity as reflected in the consumption schemas, then we have a valid basis for computing a meaningful ratio:  activity hours consumed divided by activity hours contributed = utilization.  However, this would not hold true if the units used for allocation in the contribution schema did not align with the Resource drivers. That is, if Quantity Shipped were the transaction driver for the Packing activity, we couldn’t divide a quantity unit by a time unit to arrive at a meaningful ratio for resource utilization.

• The contribution and consumption schemas are key new components that help define the VDDW and make it different than the traditional DW.  Depending on the capabilities of the ABC allocation tool chosen, and the forethought and skill used in designing the allocation model so as to make it align with the corporate dimension bus, these new schemas can be populated directly by the ABC allocation tool with little to no ETL integration.  If the ABC allocation engine is functionally incapable, or the allocation model wasn’t designed with dimensional principles in mind, building these new schemas may require that a fair amount of ETL transformation be applied to the ABC output post allocation.

  
  

•  

• Learn More: Information Supply Chain Basics How VDDW Streamlines the ISC VDDW and Corporate Performance Management How VDDW Delivers Results VDDW Distinctives