Adding a storage Dimension (Inventory Status)

I have before written about my frustrations as regards Dynamics Ax’s lack of a status inventory qualifier.

In order to somewhat help in this regard I have made an example project with the addition of a new Inventory Status table.

The project is done in a Standard 4.01 SP2, to be precise it is :

The Chinese Localization does not pose any issues as such as no elements touched are touched by the LOS layer where it resides furthermore none have been touched by the SYP either. I used a VPC I had onhand and it happened to have this installed.

I have added the standard sales and purchase status codes in the Inventory Parameters screen

For a full implementation where they are instead added as parameters on the customers brought to sales and on the suppliers brought to purchase, with a group on the item table defining what statuses are allowed for any given item please look to a later release which will be released given time and will include other innovations as well to develop the thoughts into a more useable module. This is more done in the spirit of sharing :-) a work in progress.

On a purchase order you can after importing the project and setting up an item with an Item dimension group that includes the item configure purchases to send items to any inventory status.

I have created a small label file and added labels for French, German, English and US English.

The inventory on hand screen now can show you.

The total on hand of your item or the broken down on hand qty.
And you can now see the main purpose of this modification. It is to allow you to manage stock logically without using the warehouse for the purpose.

We can now create reject stati instead of having to create reject warehouses we can add a status to the return reasons screen and have our returned goods in the normal warehouse but with a flag indicating that they are returns.



Or even a flag indicating that they are unusable (no control of this is done that means you can still sell this stock though you have to choose it to do so)

You can also use the new status for things other than Quality related issues, you can use it to denote stock you have lent out for demonstration purposes f.ex.

Or stock that you have allocated to specific customer categories, or even specific customers.

Giving an on hand picture more like this one.

With the categories being configured as follows

All up to you to configure but it opens up quite a number of possible trade related uses for not a lot of coding changes.

You do however wind up doing a lot of transfer journals when you want to change the status of your inventory, fortunately they are reasonably simple to do.

Another thing you must do, depending on what you want to use the added dimension for, is to configure the inventory dimensions with this as one that has the primary stocking flag set and probably also the physical and Financial inventory flags set.

I will not go into what all the flags do but let you use the standard documentation to help you determine how you want to use the new dimension.

All the standard sales related restrictions immediately apply.

F.ex. the reservation screen knows to limit orders against the not available status, which is quite neat and nifty considering the few lines of code involved.

The above is by no means a complete documentation of the possibilities of this little modification nor is it to be construed as a limiting factor on what you can do with this modification.

Now comes the less amusing parts of this:

Note that by downloading and or by importing the linked code you are accepting the licensing conditions, if you give the code to anyone else you must also apprise them of these licensing conditions and they must agree to them prior to using the code.

The full conditions are the ones given by the OSL http://www.opensource.org/licenses/osl-3.0.php in this link which basically allows you to do anything including use it commercially.

Here is the link to the file for download.

/Sven

In search of Performance (Finance this Time)

I have not noticed when exactly the change was made but when posting to the LedgerBalanceTrans and LedgerBalanceDimTrans the date is no longer always the first or last day of a financial period it is the equivalent of the Transaction date. Validated from V3 onwards.

This has important performance impacts on reporting when you use financial dimensions actively.

Normally you would expect to see a factor of reduction in number of transactions going from LedgerTrans to LedgerBalanceTrans and DimTrans, in practical use I see that most of the implementations I visit the relationship is more like a 10% reduction in DimTrans and 20% in BalanceTrans which is not much and almost makes these tables almost redundant.

In any case the advantage in performance using these tables rather than the LedgerTrans directly for reporting is not very big.

An easy means of seeing whether you have this issue is to configure your chart of accounts to show the current balance, if the display is jerky and slow which is the case very often it means the level of summary provided by the LedgerBalance View on the LedgerBalanceTrans is not sufficient to allow for a speedy summary of the ledgerbalances to date.

Another is to review the number of rows you have in LedgerTrans and in LedgerBalanceTrans & DimTrans respectively if the later are much smaller the optimisation is functional in your case if not you will eventually have a slower system as regards reporting.

What I would suggest is to ensure that it is possible to have a level of consolidation in the Balances so that they are reasonably fast for reporting purposes of course this means that we will slow down posting slightly however I believe the gains in reporting justify the expenditure :-).


/Sven

AX 2009 or V5 goes RTM

Microsoft has recently gone RTM on Ax 2009 or version 5 and in so doing they have removed a few functionalities as opposed to the last TAP version I saw but not much and they are on schedule to make their next fiscal year interesting.

This is the first version of Ax where all of the core development has been done in Redmond, it will be interesting to see where things go, some sources say the intention is to remove Morphx (the internal Ax dev language and dev environment) and replace it with a .Net dev environment. This may proove harder to do than they realise as all the installed base would rebel at having to re-write all their customizations completely, not to say that the job that MS and it's partners would be faced with would be simpler.


What does this mean for customers well it means AX 2009 will be out and sold over the summer as the time frame from RTM to sales is usually very short.


Anyway here's to Ax 2009 hope the launch and the program are sucessfull.

/Sven

Performance and Inventdim PII

I wrote on this subject a few months ago and a comment was made that perhaps a solution could be found by creating appropriate views and stored procedures.

I have in several instances tried the problem is the sheer volume of information that the SQL db is asked to manipulate.

Concrete example, the customer had built up a database of 24 million inventory transactions, there were 2,5 million lines in inventdim, inventsum had 6 million lines, the only active dimensions were the configuration on the inventory side and the warehouse, location and palett on the storage side. No serial numbers or Batch numbers so reasonably limited usage of inventdimid's. They used FIFO costing so inventsettlements were not too bad around 16 Million.

We ran a valuation report as at 3 months in the past. The report was unable to complete after 100 hours of processing time. The server was a quad processor with 32 Gb of RAM and a dedicated 15 K RPM raid 0+1 array with 24 disks in it, no penny pinching there.

The problem lies in the volume of data.

A consultant that helped design the above data model came and installed his solution which basically takes the data from inventtrans + Inventdim + inventsettlement and creates a flat table from the result. 12 hours of processing time. Afterwards the valuation report was printed in less than 10 minutes.

I think the above illustrates my problem with the current data model, having said all this many customers will never notice the issue as they do not have huge amounts of transactions. But as we all know over time systems grow and well this one does not have an archiving tool :-).


/Sven