Friday, 22 March 2013

AX2012 : Understanding InventDimID

Hi Friends,
In this post I will describe my view of what is InventDimID and how we can use it in our solutions.

We know there are different inventory dimensions available in Microsoft Dynamics AX like Site, Warehouse, Location, Pallet, Batch number, Serial number etc. These can be active for an item based on the storage dimensions assigned on it.


Let us take an example, we have an Item "BRK6348" on which Site, Warehouse, Location, Pallet ID are active and we did an inventory transaction on this item say on Site '01', Warehouse 'Main', Location 'LOC001' and Pallet Id 'PLT001' and we want to design a table to store all these values on our custom transaction table.

A simple approach is to add fields for all the inventory dimensions in your table. Imagine if there are 10 inventory dimensions in system then we need to add 10 fields in our table and if we have 100 inventory transactions for this item on the set of these inventory dimension combination then we will storing same data in 100 records in all 10 fields. This will bloat the database size and also impact the performance of queries on this table.  A sample of such table would look like below:


Now to simplify this we create a new ID field which refers to a set of combination of these values. For example ID "10001" refers to  Site '01', Warehouse 'Main', Location 'LOC001' and Pallet Id 'PLT001'. We also create a separate table to store values of these 10 fields along with this ID field and add ID field in our transaction table. So in all 100 records we can store data in only one ID field.
We are not required to store data of 10 inventory dimensions fields 100 times, instead we just need to store value of 1 field in 100 records pointing to same combination of these inventory dimensions.  A sample of such tables with some records will look as below:


This ID in Dynamics AX is known as InventDimID and the table which stores all the combinations of inventory dimensions and there ID is InventDim:



This table plays very important role in AX. Any table which stores inventory dimensions in AX like salesLine, purchLine,inventTrans all have relation to inventDim table.




There are already functions to create and find inventDimID for a combination if inventory dimensions . Most commonly used is findOrCreate just initialize a blank instance of inventDim table object, assign all your inventory dimensions and then call this function to assign the inventDimID to your record. If a invnetDimID for the combination exists then system will return it else it'll create a new and return the inventDimId.


So if you are designing new tables in system to store inventory dimensions, then leverage this smart database architecture of AX and give your solution a smart design.



12 comments:

  1. Thanks Priyanka, this is very useful for beginners like me.

    ReplyDelete
  2. Very useful information.Thanks for sharing

    ReplyDelete
  3. Thanks for this useful information. I have a doubt. Based on the Item Id how to find what are all the Dimensions we are having for that particular item id?..

    ReplyDelete
  4. Thanks guys.
    Malleswaran, you can refer to my another post which answers your question.
    http://basicax.blogspot.com.au/2012/08/ax2012-x-code-to-verify-active.html

    Thanks!!

    ReplyDelete
  5. hi priyanka,

    I m facing an issue, hope you could help me,

    The problem is to delete all the batches in inventbatchtable along with its respective dimid's in invent dimtable, which dont have any related transactions(batches dont have any transactions).
    i tried many ways , but faced some or other obstacle. would be great if u can help,
    Thanks in advance

    ReplyDelete
    Replies
    1. Hi Prasad,
      Thanks for reading the blog and discussing your problem.
      Firstly it is quite messy to clean up in this way. You have to be very careful in deleting data from these sensitive tables. If I try to understand your objective of doing this task , I feel you want to clear some junk data from the system, probably the batch numbers which are not referred in the transactions.
      Have you tried running database consistency check process. I hope you can clear such junk data using it.
      If you are using AX2012 then there is a standard periodic process to clean up unused inventory dimension which can be executed from Inventory & warehouse Management -> Periodic -> Cleanup -> Inventory Dimensions cleanup.

      It is always beneficial to use standard AX processes which are available out of the box.

      PS -> Before running any clean up processes it is advisable to take DB backup for safety purpose and also investigate if this fits your purpose and please run such tasks at your own risk.
      Hope this helps.

      Thanks,
      Priyanka

      Delete
  6. Hi Prinka,

    Thanks for sharing knowledge. I would like to import inventory adjustment by code. Is it required to import inventdimid field?

    Thanks..

    ReplyDelete
    Replies
    1. Hi,
      Thanks for the good words.
      You can generate InventDimId during the import process on the basis of combination of the inventory dimension values like site,warehouse,location etc.

      Have a great day.
      Thanks.

      Delete
  7. Hi, I know its a very old post, but I thought someone could help me out with a current problem. During staging purchase orders in ax2009, there is an exemption table which should basically exempt PO's with a particular item for which the size is not available.

    For example, if an item (consider shoes) of size 13 is not available, and only size 7 and 8 are available, this exemption table should not stage Po's with the item of size 13. However, when I add the item of size 13 to the exemption table, it excludes the item itself regardless of the size. all PO's containing that item regardless of the size is excluded. Does anyone have a solution for this.

    ReplyDelete
  8. Running into an issue where queries containing InventDim are causing High CPU. Usually and update stats & update stats fullscan fix the issue. Any ideas of what could be causing this?

    ReplyDelete
  9. Running into an issue where queries containing InventDim are causing High CPU. Usually and update stats & update stats fullscan fix the issue. Any ideas of what could be causing this?

    ReplyDelete