Dynamics Ax Base Data model Part I

I often get asked by customers for a basic data model of the Dynamics Ax system and I always have to answer a bit shamefacedly that it does not as of yet exist.

But then I manage to show the Visual Morphx explorer or the Visio tool in version 4 and explain that with 1,500+ tables a chart would not be a chart but a panoramic wall chart requiring the deforestation of Finland to create.

Nevertheless it is instructive when getting introduced to Ax to start by considering some basic elements of the data model.

Before starting one should consider that Ax is a fully integrated ERP system, that is transactions actually cross module boundaries. This implies that the data-model very quickly grows quite complicated, or rather perhaps rather than saying complicated I should say it is quickly heavily populated with tables.

As with most ERP’s one can view Dynamics Ax as being about controlling the flow of goods through the company, the stock control view or approach, or one can view the product as being about controlling the financial information in the company, the nominal/general ledger view or approach.

In my book both are correct, sooner or later most of the modules in Ax will have an effect on the inventory, and or the ledgers.

So let us take a look at the basic data-model surrounding these modules :

I choose to take the more complicated inventory model first J

The main table here is the InventTable which contains the basic information about products in Ax and which notably of course contains the ItemId field which is the identifier by which the product is known.

The list of items controlled in a company when presented to a user also includes 3 instances of a table called InventTableModule all linked through the ItemId to InventTable, and at least one instance of InventItemLocation.

Why are the above elements stored in separate tables ?

In order to allow more flexibility as to configuring in multi company scenarios where the information concerned is held, F’ex the default warehouse for inflow, outflow, and stocking is held in the InventTableModule, this allows us to configure the system in such a manner that 2 or more companies can share the InventTable whilst at the same time having separate configurations of default warehouses, tax codes, unit sizes etc all of which are found in the InventTableModule.

InventItemLocation is another story as it has been split in order to accommodate being able to based on a users configuration needs have different stock flows in the replenishment planning area or net requirement calculation. Of course this also means that we can have a different setup in a multi company situation as well.

Enough about the base table to which everything is tied there are a large number of further tables to explore in this area to do with the configuration of the behaviour of the item but we will get back to that later.

Let us look at the basic transactional model as regards inventory, the information about stock inflows and outflows in Ax are stored in a table called InventTrans, this table contains some key fields, the first basic one is the ItemId field of course which ties the transactions to an item ;-).

Thereafter not in order of importance the InventTransId or what one could call the internal lot number, which identifies the source of this / these transactions as the model permits the existence of multiple InventTrans with a common InventTransId.

Then the StatusInflow, and StatusOutflow fields which are used to identify that the transaction is an inflow or outflow transaction and to state at what stage the transaction is currently, that is if f’ex StatusInflow is 1 (Purchased) it means this item is considered as a fully purchased or costed inflow depending upon it’s provenance, if it is 2 it means it has been delivered but not invoice updated.

The model so far is exactly the same as the one that Concorde XAL had which was created by mostly Benny Olesen who is linked / referenced on the side together with Bjorn Moeller Pedersen, Erik Damgaard, Jesper Theil Hansen and the rest of the core developers at the time.

The next key field is new / a departure from the XAL model, it is the inventdimid field which contains a reference to a combination of inventory dimension fields ( The Warehouse, Lot number of an item as well as it's configurationId ). This Innovation in the data model does make it easier to add new / more dimension fields however it also renders all direct lookups impossible and with heavy data traffic in the inventory transactions renders the system hard to use.

I will carry on from here later as I have been busy these last few days.

/Sven