Product dimensions

The other day a colleague invited me to sit in on workshop session with a customer to discuss changes to an attribute of product. On the face of it it sounds a bit of overkill to spend two hours discussing the meaning of just one attribute, but when that attribute is cost and the impact of the change is reflected in the cost of sales and ultimately reported profit it all seems worthwhile.

However, for this customer, product is not simple, they have three ways to view a product depending on whether they are buying, selling or just counting it. Let me explain.

Assume my customer runs a burger bar (which of course they don't) then they sell things such as double cheese burger which is two burger patties, lettuce, tomato, relish, mayo, cheese, dill pickle in a bun. They also buy from their supplier disks of ground meat, the burger patty; these are delivered in boxes of 50, 100, or 250. And finally the bar manager counts his stock in terms of individual burgers. In BI terms this sort of thing does not easily map into conventional hierarchies; for one thing there is a many to many relationship between counted stock items and items in a burger recipe - do we have one burger patty or two? and what about all of the other ingredients: this is not exactly the classic parent child rollup that is so easy to model in a data warehouse.

The relationship between goods supplied and counted stock is much more simple, each case size could be considered the child of the counted item. That is, 50 patty boxes and 250 patty boxes all rollup to "burger patty". But this rollup also has an impact on the attribute stock cost - the cost of a single patty; obviously it is the cost of 1/50th of a carton of 50 patties, but what if the supplier discounts the larger boxes? what value is assigned to the item cost. And then, by extension, we try to calculate the cost of a cheese burger as the sum of the individual recipe items (plus the prep costs, fuel, staff etc) but what if we used burger patties from the big carton? Do our costs go down and profits increase?

This type of discussion takes a long time to work though, loads of "what ifs" and usually ends up with the accountants having to think things through. But the end result is that the reporting is more accurate than before, or at least I hope so