Performance Issues Resolution in Ax Part II

After finishing this work on Ax V4 Ax V2009 was rolled out and my work should really start over again.

However as I am lazy I will simply try to migrate the code and tell you more afterwards.

Noted some interesting changes in V5 as regards multi threaded execution of processes using the new batch service.

Will report back on this later in more detail.

/Sven

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

Dynamics 2009 First Impressions

I have managed to get hold of a TAP2 and TAP3 version of this software and wanted to give my first impressions of some of the new features.

Likes:

The Version control built into Ax

The workflow wizards in almost all modules

The Expense module (finally)


Dislikes:

The Site addition is not really well thought through as it adds another inventory dimension and the companies mostly needing this are heavy inventdim users so this will have a big performance impact on all inventory reporting


The new apparent sub menu in the display window

The new colours in the TAP3 version seem a bit well I hope they are not the final ones



More to come

Take Care
Sven

Performance and InventDim

I find that the impact of the datastructure choosen to implement Inventdimensions is too heavy performance wise and I would really like to see MS - Ax Team in the future revise this structure.

In the product developped before called XAL the inventory dimension fields were present in the tables concerned that is we had the Warehouse as a field on the equivalent to the SalesLine, CustInvoiceTrans, InventTrans etc etc.

Finally this approach allowed for really huge datastructures with simple index based group by type queries and simple index constructs. Compared to the forced joins we have in our current inventsum sphagetti code especially with the huge number of inventdim records when using Batch or Serial numbers.

What would I like to see :

1. Delete Inventdim as a table (whew what a performance boon that would be)

2. Create an Array field like the finance dimensions Called InventSKU with three fields Config, Size and Colour

3. Create an Array field InventStorage with three fields Warehouse, Location, Pallet
( add a fourth one called Site for version 5 compatibility )

4. Create an Array field InventCondition with three fields Batch, SerialNumber, and Condition

I only introduced one new field in the above as compared to V5 and two new fields V3 and V4 (V5 Adds one)

5. Add the above array fields to all tables containing inventdimID, I realise that this means 2 times the fields for the transfer table and will cause a lot of code changes :-)


However the performance improvement in large scale installations !!!!


Also the ease of adding new Inventory dimensions !!!!


The simplification of inventsum logic !!!!



Take Care
Sven

How to test for a methods implementation

I had a little problem and could not find any reference to a way of doing this, I needed to check before hand if a method was implemented in a given class.

After a little bit of searching I came to the sysDictClass which implements a method that enables you to call a method of a class.

This Method is called InvokeObjectMethod it accepts a classid and a method name as a string parameter and invokes the method, if the method does not exist it will return an error.

Here is the complete Method code

public static anytype invokeObjectMethod(Object _object, identifiername _methodName, boolean _tryBaseClass = false)

{
DictClass dictClass = new DictClass(classidget(_object));
DictClass dictClassBase;
DictMethod dictMethod;
int i;
;
for (i=1; i<=dictClass.objectMethodCnt(); i++)
{
if (dictClass.objectMethod(i) == _methodName)
{
dictMethod = dictClass.objectMethodObject(i);
if (dictMethod.parameterCnt() == 0)
{
// invokeObjectMethod is listed as a dangerous API. Just suppress BP error;
// CAS is implemented by DictClass::callObject.
// BP deviation documented
return dictClass.callObject(_methodName, _object);
}
throw error(strfmt("@SYS87800", _methodName));
}
}
if (_tryBaseClass && dictClass.extend())
{
dictClassBase = new DictClass(dictClass.extend());
// BP deviation documented
return SysDictClass::invokeObjectMethod(dictClassBase.makeObject(), _methodName, _tryBaseClass);
}
throw error(strfmt("@SYS60360", _methodName));
}


As you can see the code has pretty much the functionality required to do the job, it cycles through the methods on a given class and tests for the existence of the wanted method by comparing the names.


So the above requires little change to create the below code:

public static boolean xxxObjectMethodExists(Object _object, identifiername _methodName, boolean _tryBaseClass = false)

{
DictClass dictClass = new DictClass(classidget(_object));
DictClass dictClassBase;
DictMethod dictMethod;
int i;
;
for (i=1; i<=dictClass.objectMethodCnt(); i++) { if (dictClass.objectMethod(i) == _methodName) { return true;
}
}
if (_tryBaseClass && dictClass.extend())
{
dictClassBase = new DictClass(dictClass.extend()); // BP deviation documented
return SysDictClass::xxxObjectMethodexists(dictClassBase.makeObject(), _methodName, _tryBaseClass);
}
return false;
}

I have in the above setup indentations etc but they are not working but the code should work if you just copy and paste it in.

Why did I need the above well I am working on a replacement of the Batch processing in Ax as I have too many operational problems with the Batch processing and I have decided it is time to create a Batch processing framework that can be called from the OS through COM and that can be used to execute any class in the system.

So I needed to know whether a class implemented a dialogmake method in order to know whether I can call one. I want to be able to execute also runbase classes not only runbasebatch classes :-).

But if you have another need feel free, plus if you have a better way also feel free to say so

Take care
Sven

Refreshing screen Info PIII

I have a few months back found a solution.

And as is often the case I found the solution in someone else's code :-).

Columbus have developed an integration tool called Galaxy, and this tool imports data into a container and then presents that data using a mask in a form, the presentation top labels have exactly the same requirements as my size / color grid screen.

That is as the user moves down and selects rows the labels at the top should change.

So what is the answer ?

Well it may seem strange but the following works.
Set the first label field to ' ' that is:
QtyGrid1.label(' ');
and then proceed and set the others.

I do not know what flag is activated or set of by the fact of setting the label field to a 32 ascii however it forces a re-draw and thereafter everything works as expected.

As I lifted this from a version of Galaxy intended for V3 I believe the fix works in both I can certainly confirm that it works in V4.

Have fun

Sven

Long Break

I have been busy with a few too many concurrent projects and have therefore been silent for a long time I hope to rectify this now as I am coming up for air again and have a lot of subjects that I wish to get back to / add to the list of subjects covered here.

Take Care
Sven