Dynamics AX Base Data Model Part II

I ended the previous message on this subject by discussing the inventdim and stating that the introduction of this element had caused quite a lot of heartache in many installations.

In the previous product (XAL) the fields constituting the stock keeping unit as well as the stocking dimensions were found in all the relevant tables, from the sales order lines through the invoice lines down to the inventory transactions themselves.

When Benny was designing the database for the new model some smart Db developer suggested normalising this structure by creating a reference to a new table that contained the information.

This would significantly reduce the amount of data stored in the database, as now the transaction files would only contain a reference id (inventdimid) rather than all the fields (ConfigurationId, SizeID, ColourId, etc). Another thing that this simplified was the ability to add more dimensions to the table seemingly effortlessly, or at least with minimal effort.

So now inventdimid was added to

InventSum (Summary Table containing sum of inventTrans for that dimension combination or inventdimId)

InventSumLogTTS (Used by MRP when doing reduced calculations and also by IMTS)

SalesLine (Sales order lines default values for InventDimId)

PurchLine (Purchase order lines default values for InventDimId)

CustConfirmTrans

CustPickingListTrans

CustPackingSlipTrans

CustInvoiceTrans (Sales order flow lines note could be different InventDimId’s attached to lines in inventTrans that are attached to each document line)

VendPurchOrderTrans

VendReceiptListTrans

VendPackingSlipTrans

VendInvoiceTrans (Similar to above flow except this time purchase related, again same comment applies that the InventdimID of the line could be different on the linked InventTrans)

InventTrans

Are some of the main tables containing our new InventDimId field, I have not mentioned the MRP, Warehouse management, project, requirement planning tables that also include it but you can easily add those your self if you wish.

Note some notable additions in V4

InventSumDelta
InventSumDeltaDim

And

InventSumDateTable
InventSumDateTrans

Because of the nature of inventory valuation reports and their dependence upon the data contained in the above and 2 further tables that are intrinsically linked to the valuation of stock

InventSettlement (Contains all the inventory re-evaluations that have been carried out against any given transaction)

InventTransPosting (Contains the accounts on which valuation and re-valuation has posted values pertaining to a given inventory transaction)

It is given the data structure necessary to create a report using InventTrans joined to Inventdim through the InventDimId joined to InventSettlements in best case and in worst case with a search on the settlements in order to populate a report with correct values.
And if the report is as of a date it is even worse as it has to then first look at inventsum, deduct the inventtrans and or invensettlements that it has done after the date, trusting the user to have carried them out correctly and then present the resulting information as it’s version of the truth.

The Select statement looks like some version of the below taken directly from some of the classes doing this:
select forceplaceholders sum(CostAmountPhysical) from inventTrans
where inventTrans.StatusReceipt == StatusReceipt::None &&
inventTrans.StatusIssue == StatusIssue::Deducted &&
inventTrans.ItemId == itemId
exists join inventTransPosting
where inventTrans.VoucherPhysical == inventTransPosting.Voucher &&
inventTrans.DatePhysical == inventTransPosting.TransDate &&
inventTrans.InventTransId == inventTransPosting.InventTransId &&
inventTransPosting.InventTransPostingType == InventTransPostingType::Physical &&
inventTransPosting.IsPosted == NoYes::Yes
#inventDimExistsJoin(inventTrans.InventDimId,inventDim,inventDimCriteria,inventDimParm);

postedPhysicalValue += inventTrans.CostAmountPhysical;

select forceplaceholders sum(CostAmountAdjustment) from inventSettlement
index hint ItemDateIdx
where inventSettlement.ItemId == itemId &&
inventSettlement.Cancelled == NoYes::No &&
inventSettlement.Posted == NoYes::Yes &&
inventSettlement.TransDate > perDate &&
inventSettlement.SettleModel == InventSettleModel::PhysicalValue
exists join inventTrans
index hint RecId
where inventTrans.RecId == inventSettlement.TransRecId &&
inventTrans.StatusIssue == StatusIssue::Deducted
#inventDimExistsJoin(inventTrans.InventDimId,inventDim2,inventDimCriteria,inventDimParm)
exists join inventTransPostingPhysical
index hint DateVoucherTransIdx
where inventTrans.VoucherPhysical == inventTransPostingPhysical.Voucher &&
inventTrans.DatePhysical == inventTransPostingPhysical.TransDate &&
inventTrans.InventTransId == inventTransPostingPhysical.InventTransId &&
inventTransPostingPhysical.InventTransPostingType == InventTransPostingType::Physical &&
inventTransPostingPhysical.IsPosted == NoYes::Yes;

postedPhysicalValue -= inventSettlement.CostAmountAdjustment;

Regardless of how much you may update / optimise your SQL server given that there are a large number of rows in InventTrans, double that in InventTransPosting and at least double that in InventSettlements (if using average costing) the above query is deadly for the database in terms of performance.

In order to counter this Benny has developed his fast reporting tool which you will find advertised on his web site, and MS have added the InventSumDateTable and the InventSumDateTrans tables which basically do the above queries but store the results in a table which is then consultable.

Unfortunately again the table does not contain a simple date rather they have chosen to have just a parmId reference stipulating the reference number of the calculation having been carried out, and then to use adjustments based on this so again if you want the values as of a date you are stuck doing a subsequent query to get back to a situation back in time.

Enough about InventDim & InventDimId.

InventSum as stated earlier contains a summary of the linked InventTransactions based on the InventdimID associated broken down by the StatusIssue and StatusReceipt fields the transaction is summarized in this table.

This action is carried out based on any change to the inventTrans table, that is the simple fact of doing an update or insert on a record in that table will automatically be reflected in the InventSum table, if it is an update 2 inventsum records could be affected (if the inventdimid changes).

The InventTransPosting table contains the Ledger accounts that have been impacted (posted on) during the update of either a physical transaction or a financial transaction. The reason for having this table is to allow subsequent revaluations of the inventory transactions to be posted in the GL using the same account numbers as the original transaction.

Example:
We introduce a new item in the catalogue, and purchase 100 pieces with an assumed price of 1 units / piece, we receive the items in stock and start selling the items.

When we have sold half the lot of 100 we receive the purchase invoice which is for 90 cents / piece only as we have already had COGS (Cost of Goods Sold) recognized as 50 * 1 unit or 50 Units on the total of 90 (90 cents * 100) there are only 40 units left to share on the remaining 50 units this makes the COGS / piece = 40/50 or 80 cents on the remaining 50 units.

To correct the above Ax has an inventory costing mechanism that allows the system to recalculate a more accurate valuation and correct the COGS of the already sold transactions.

The InventSettlement table will contain the corrections to the existing transactions which would deduct 10 cents / piece for the old invoices and add 10 cents to any new ones.In order to ensure the right accounts and financial dimensions are impacted the account numbers as well as the financial dimensions are stored in the InventTransPosting table.

Sound complicated well not really compared to others it is quite simple but meethinks I will continue later.

/Sven