Views in Dynamics Ax

Views in Dynamics Ax are a great tool to improve performance, simplify code as well as allow easier reporting.

Historically views were introduced in version 3 in order to circumvent a scalability issue in the standard product as regards the updating of ledger transactions.

Ledger transactions are as in any ERP a type of transaction generated by many processes in the product, f'ex. the update of a production order costing, the update of a purchase invoice, in certain cases the update of a goods receipt or delivery note.

As the accounts involved are limited, what was happening was that the tables containing the sum of the ledger transactions by financial period were functioning as a block to the number of processes being able to be carried out simultaneously. In database speak we had a classical serialising bottle neck.

In order to reduce this the tables involved (LedgerBalances and LedgerBalancesDim) were changed to being views and the views showed the totals contained in LedgerBalancesTrans and LedgereBalancesDimTrans. These tables then had an index field added to them which was selected based on the modulo value of the Dynamics Ax session id number. Now we still have serialisation but strongly reduced by the fact that there are more entries on which we can act.

Just for completeness sake in V3 we use a modulo 10 and in V4 we use a modulo 20 which should allow for even more concurrent updates of the related information.

In order for this to work in V3 the developpment team had to add views in the AOT tree, and to make these the very close relative of tables, which has of course happened and is the reason we have views in the product.

Now there are several things which are missing from our implementation of views as opposed to the ones that you can define in SQL directly, chief amongst these are the fact of the generated code that creates the view depending upon the sequence with which one adds the tables in the AOT. This can create some phenomally badly performing views whereas if one re-creates the view in the right order the performance is much better.

This calls for a means of viewing the code generated directly in Dynamics Ax in order to better control what is generated and how it is generated in the SQL database.

The second and perhaps biggest issue I have with views is the inability in DynamicsAx to create unions, that is to have several final transactions steps. The reason why this is so is simply because queries for views are like queries for Olap cubes they can only have one base transaction table. That is if you want to have a view that lists all SalesLines and all PurchLines at the same level in order to do an analysis of the whole you cannot do so directly on those two tables in one view. In this instance there is a solution as both these tables translate downto entries in InvenTrans so you can build from this table upwards and get a global view :-).

However in many instances this is not possible as there is no federating Ax table, an example is the wish for a view federating all project transactions and budget transactions in one entity, or the ledger transactions with the ledger budget.

These are to me normal (as well as common) customer requests and Ax does not allow any easy means of satisfying them, in some instances just adding the ability to create views that are unions would allow a simple means of reporting what in Ax often becomes a very complicated task requiring the creation of additional tables federating information that is already availeable in the product.

Of course you can simplistically, and I have done so, add a view and then change it's code at syncronize time so that the query that is done behind the scenes is one that you wish. But the whole point of this is of course to get MS to add the tools in the system that we need in order to do a better job and a quicker job of implementing Ax.

I would suggest that once Unions are supported in Ax views a few example views should be created and based on these reporting cubes could be built that would really do justice to the system.

Allowing simple comparisons of Budget / Realised (commited) etc.

This would really reduce the time spent building reports / extracting information from the system, and that would be a great way of improving partner productivity and reducing development and deployment time.

Best Regards,

Sven