Fitting Non-Dimensional Inventory into Dynamics AX’s Dimensional Inventory Model
Development, Tech - November 24, 2009 11:09 pm
In this article I chronicle the business and technical decisions and hurdles encountered in fitting a product that doesn’t fit a finite dimensional model (in this case steel pipe and tube) into Microsoft Dynamics AX’s dimension-driven inventory model. We’ll start off with a quick overview of the Dynamics AX item/dimension model, go into a bit of an overview of pipe and tube manufacturing and stocking and why this material doesn’t fit base AX (or likely any other out-of-the-box ERP) due to a need for ‘variable’ or non-exclusive item dimensions, and finish with the solutions successfully implemented in the marketplace. There will be a mix of business and technical jargon but none of it should be out of reach to a programmer, implementer, or analyst at any level.
AX Item Dimensions
Inventory Management in Dynamics AX is pretty logical on the surface; at the highest level are Items and these Items are sliced and diced by Dimensions. These Inventory Dimensions come in two flavors, Item Dimensions and Storage Dimensions. Item Dimensions describe the attributes of the inventoried item and AX ships with three default Item Dimensions: Size, Color, and Configuration. Storage Dimensions describe the location properties of the item (ie: warehouse, pallet, etc). A number of Storage Dimensions ship with AX to accomdate a wide range of stocking and inventory flow schemes. Item Dimensions, if active for an item, must be attached to every transaction for an item while storage dimensions are optional (ie: backordered items don’t have a pallet on the warehouse floor).
If we look at a trivial product (from an inventory management point of view), let’s say T-Shirts, individual product falls into these dimensional categories very easily. A cotton T-Shirt is an item (vs a sweater which is a different item); T-Shirt colors blue, green, and red can fall into the color dimension; small, medium, and large fit well into the size dimension; and configuration could be used for something like V-neck vs U-neck or screen print design. Microsoft enforces no rules on how these dimensions are used so just because a dimension is called color doesn’t mean it can hold only color values. For instance, if I was selling lightbulbs, physical color would be irrelevant and I could instead store something like stem screw size in color. This is a very powerful and flexible inventory scheme that can handle almost any reasonable sellable item. Microsoft lets the company decide what an item is, which dimensions to use, and what that dimension means to the system. All dimensions are arbitrary and equal and you can even purchase more than those 3 base dimensions if needed.
As noted above, active item dimensions are required on all transactions for that item. There’s no such thing as a sales order for a T-Shirt that doesn’t have a color. You can’t stock light bulbs without a stem screw size. These dimensions also have implications down the road in financial reporting, costing, etc.
Given all of these dimensions, AX can perform some pretty advanced automation. As soon as a sales order is entered AX can go out and reserve the material onhand by matching the sales order dimensions to the inventory and storage dimensions. That same dimension match is done when marking material (reseving material that isn’t available yet but will be coming in on a purchase order or transfer) and just about any other transaction. In fact, these dimension rules are strictly enforced at all times and in all inventory transactions so it would be nearly impossible to send out red T-Shirts when white T-Shirts were ordered. A salesline can order and move a single item and a single set of item dimensions. Those strict dimension rules are the crux of the problem fitting steel pipe and tube into the dimension model. A simplistic analog would be trying to sell a bunch of T-Shirts, any color, any size… a single T-Shirt sales line cannot encompass the multitude of shirts that will be shipped even if the shirts are very similar (ie: pink vs salmon).
Pipe, Tube, and the Infinite Item Dimension
Pipe and Tube are far more unique products than most would imagine. On the face of it, a piece of pipe would seem to be pretty simple. It has a outer diameter, inner diameter, and length. One would imagine there are various physical (ie: bend resistance) and chemical (ie: carbon content) properties that might be attached but still, nothing that wouldn’t slide into a dimensional system without too much aggravation. In the case of standard pieces like plain old PVC, the kind of consistent repeatable stock you’d find at Home Depot, you’d be right but in the industrial pipe and tube market things are a lot more fuzzy.
The first issue is length. Yes, OD and ID remain constant however length is fairly arbitrary and varies from stick to stick. Mills have tolerances, pieces get cut, samples take a foot here and there. Inventorying every single stick would be a solution to this one problem but open a much, much larger operations issue. A requirement of the system is the ability to use a range of lengths in the smallest inventory ‘bucket’; in this case the smallest inventory bucket is called a ‘batch’ (using the InventBatchId storage dimension). I call this the ‘infinite item dimension’ as there are no limits to the number of length ranges the system will be using and for the most part the specifics don’t matter. A customer looking for 300′ of pipe often doesn’t care what the individual lengths are as long as it’s between some mimimum and maximum values and the total footage is pretty darn close to 300′.
The second issue is item substitution. There are many different very-similar-but-not-the-same pieces of pipe. Depending on the application the exact physical and chemical properties aren’t necessarily important as long as some general specifications are met. For instance, if you’re building a roll cage the exact carbon content and tensile strengths probably aren’t going to be important. There would be a very large number of different item/dimension combinations that could fulfill the order. Steel is very heavy and very awkward to move around the storage yard. Often times a sales order may be entered using one set of onhand material however operations may pull a different set of completely compatible material that is more accessible, and may even pull more than one type of material for a single sales line. A lot of freedom is handed to operations at order fulfillment time. The system will have to be able to handle changes to the material at any time from sales order entry up until the truck leaves the plant.
The third issue is item accuracy. While a customer building a roll cage has a very broad range of acceptable pipe and tube, a customer building a power plant is likely to have very specific material requirements. So specific that potentially only a single batch in the entire system can fulfill the order. Every piece of material ever received came with a set of specifications called material test reports. These documents chronicle everything known about the tube including the examples we’ve used above of physical strength and chemical makeup as well as lists upon lists of industry standard specifications. This MTR data must remain ‘attached’ in the system to the physical material it represents as this data is not only used to determine material eligibility for a salesline but also often must be delivered to the customer as proof that the material matches their specifications. The inventory model has to be flexible enough to support a wide range of material substitution for the less picky customer, but precise enough to meet the high demands of a more picky customer, all the while AX’s inventory dimension transactions are enforcing the same set of dimensions on all inventory transactions related to that order.
Let’s Talk Solutions
All of the issues above cannot be addressed in base AX. If that were the case there would be no need for this article apart from a few footnotes on possible item organization. In fact, a good majority of this client’s 3 year implementation was spent on issues and enhancements to the way the inventory model was handled.
First let discuss how items were organized from a purchasing and stocking point of view. An ‘item’ represented the strongest unquieness of a tube; the outer and inner dimensions (there were also some basic chemistry and specialty items that played into item division however for the purposes of this article we can work off the idea that the division is only based on ID/OD). Two pieces of pipe that have the same ID/OD were the same item, two of them that did not were different items. As for the dimensions, early attempts to use the size dimension to represent the tube length were fruitless. There are too many combinations of minimum and maximum length, to much overlap of ranges (resolving demand for 30-35’ when your stock is 32-34’), and the dimension changes far to often. In addition the length is financially irrelevant. Average Item Cost doesn’t change for different lengths of the same tube. Length may have some pricing implications but nothing AX should automate. At the end of the day, average item cost was per foot in a given color. The decision was made to tie minimum and maximum length to the smallest inventory unit’s dimension (batch) rather than trying to force it into an item dimension. This meant AX provided no dimensional enforcement and everything had to be done in custom code. Batches, as the smallest bucket of inventory, held the total footage of material in the batch in decimal feet as well as the minimum (smallest stick in the batch) and maximum (longest stick in the batch) lengths.
Back to item setup… We still have to address all of the material specifications and the broad-vs-specific requirements. Since the material test report is very specific to the material tested, it was also attached to the batch. Receipts were done into a batch so no batch could ever have more than one MTR. This also led to a relatively simple rule than whenever a batch is created it inherits the material test data it was created from (in the case of cutting or splitting), or if it’s a new batch material test report data will be attached. This handled the specific case in that for a picky customer every batch’s MTR data can be searched to find material that matches their exacting specifications and would be entirely sufficient for the less picky customer with broader requirements (just broaden your search parameters) if it weren’t for two hiccups. First, material with different specifications can have dramatically different cost and these costs need to be captured by AX not only for performance reporting but also because cost of goods is an essential factor in tax reporting. Second, batches are a stocking dimension they aren’t assigned until the material hits the warehouse. Those MTRs also take time to receive. An inventory transaction without an MTR (including batch-less material on purchase orders) would be useless in the system as there would be no data to search. Enter the color dimension:
The color dimension was used to represent common groupings of material test data. Since these grouping represented similar material from a composition standpoint they also represented similar material from a cost standpoint (the common unit of inventory use throughout the system was decimal feet but that’s a story for another article). The managers of a product knew what groups of specifications the material they were purchasing was likely to meet so a color could be assigned to material on the PO facilitating inbound-reservations and marking. A salesperson dealing with a less-picky customer had a wider range of colors they knew matched the customers requirements without needing to view MTR data for individual batches. Since costing was at the color level a custom material reservation system could pick the cheapest matching material thus increasing margins. This setup did mean ’searching’ for material got a little more advanced since material sitting on hand had to be searched at the mtr level (attached to the batch storage dimension) and material not yet on hand or material that doesn’t yet have its MTR data available was searched using a set of assumed specifications attached the color item dimension.
So there we have our inventory set up. Items represent basic dimensions (ID/OD). Colors represented sets of MTR specifications. Batches, the most precise storage unit, hold the MTR data and length range. Essentially, storage dimension information (length and MTR tied to the batch) was elevated to the same importance from a purchasing, stocking, and selling point of view as any other item dimension. This had sweeping ramifications across the system.
Implementation
I’m not going to bore everyone with coding specifics but I will give an overview of what needed to be changed when dealing with our new length and MTR sidecar dimensions as well as what was needed to allow for Item Dimension replacement at sales and shipping time.
The big trick was getting length working everywhere. Since it wasn’t a full fledged dimension and not represented in the InventDim table, the min and max length live on most tables that drive inventory transactions (SalesLine, PurchLine, InventTransferLine etc). Just placing the fields was trivial with a bit of legwork done to allow the decimal feet lengths to be entered by the user as feet and inches (and handle the subsequent rounding issues). As indicated above, all of the inventory movement processes had to take these new lengths into account so when batches were created by purchase reception, the purchline lengths drive batch lengths. Batches due to operations against other batches were driven by the parent batch. Frequent inventory audits and material measures ensure batch lengths remain sane and batches are single-use so once a batch is exhausted it’s numbers become meaningless.
Sales order processing was bit trickier. AX reservations and marking could not be used as it would pick material that matched by dimension but not length or MTR data. An inventory search model was installed that gathered all of potential material requirements then produced a list of batches and inbound inventory transactions that were potential matches and sorted them by desirability based on 3 factors: cost (cheaper first), cut waste (less scrap first), and age (oldest first. The managers of that product could give a ‘weight’ to each of the factors influencing the material selection process. This search mechanism was used at sales order entry (driven by a salesperson-friendly form for on-hand and pricing purposes), by reservations (when seeking best-fit material), and at picking time (giving operations a set of alternate picks). The sales order process was also modified from base AX. An order state of finalized vs unfinalized was introduced. An unfinalized order has a sales line with customer-originated search criteria, lengths, pricing, delivery information, etc on it but no material reservations. At finalization time reservations are run attaching the most desirable material to the salesline and changing the order state. The order can then flow down to operations for picking. At pick ticket print, reservations are run again giving a list of alternate material sorted by desirability.
The important thing to note is that the material selected for the salesline was not based on AX dimensions and did not rely on the salesperson to select which specific material to place on the order. It instead relied upon a set of requirements and the system selected the material (possibly along with some alternates). Often, though, the material selected did not match the dimensions on the sales line. At this time the reservation system would update the sales line dimensions to match the selected material. In cases where two or more batches of material are required to fulfill the salesline and the dimensions on the batches did not match, the sales line would be split into two saleslines with the dimensions and quantities from each batch on them (and, of course, reservations was smart enough to ‘re-join’ those broken up lines when sales criteria changed). Not only was reservations smart enough to select material and align dimensions it also made intelligent choices about cut lengths and material waste. For instance, if a sales line calls for 64′ of 8′ pieces, selecting 10′ material would lead to 16′ of waste material where as 16′ or 24′ material would cause no waste at all. Taking that further, 2′ of material would better be cut out of 40′ piece than out of 8′ pieces because material over 8′ is a common requirement in the industry. The custom material selection and reservations system greatly reduced the knowledge barrier-to-entry for sales people and resulted in better inventory movement as fitness and age were determined programmatically and not on the first-come-first-served basis base AX uses.
No Comments »
No comments yet.
RSS feed for comments on this post. TrackBack URI
Leave a comment