AIF Updated Doc for AX 2009

The documentation surrounding the use of the Application Integration Framework (AIF) with BizTalk has been updated to now include AX 2009. Here is the link. http://www.microsoft.com/downloads/details.aspx?familyid=edc62433-5b21-4f74-b065-b075ba6dc86d&displaylang=en&tm

- Paul Steynberg

TILADAX - Locked in Journal

Dynamics AX has a setting in the chart of accounts that allows one to restrict posting to certain accounts. By using this facility one can ensure that only entries from say a subsidiary ledger can be posted to this account. Now anyone who has tried to reconcile a subsidiary account to the ledger control account will know that this is a good idea. During the setup process one would select certain of the ledger accounts to be control accounts. I will use the Accounts Payable module as an example. Under your setup of the Vendor Posting profiles you can select the summary accounts for each vendor group which will act as the control account in the ledger. The problem that I found was that there is no sure fire way of making sure that what is set up in the posting profiles is in fact then locked in journal in the COA's. By missing this one could easily end up having a reconciliation nightmare later on. To this end I created a whole bunch of scripts that tests all my posting profiles across Accounts Payable, Account Receivable, Bank, Tax and Fixed Assets to the COA to make sure that they are locked in journal.

Using my AP example I found the table that holds the accounts and then joined this to the COA table. Here is the script:

select * from
( select distinct sumaccount as Accounts
from dbo.VENDLEDGERACCOUNTS) as a
left join dbo.LEDGERTABLE b
on a.Accounts=b.AccountNum
where b.BlockedInJournal=0
or b.AccountNum is null

This will test for either the account not being locked in journal or the account missing from the COA. (I did not join on DataAreaId as my COA is shared amongst all the companies).

In order to fully test your system you would also need to write a similar script for the following tables:

BankAccountTable
AssetLedgerAccounts
CustLedgerAccounts
LedgerIntercompany
TaxLedgerAccountGroup

The list is not exhaustive but should get you going.

You will have to use the pivot function on the inter company and tax tables to test all of them.

- Paul Steynberg

Dynamics AX 2009 - Half Way Review

I have spent the past 14 weeks with Dynamics AX 2009. In this time we have installed and configured it with a view to going live in 2 weeks time. I am reasonably happy with the product so far but, like all enterprise software solutions, one only really gets to grips with it once you go live. In these few weeks we have configured General Ledger (GL), Accounts Payable (AP), Accounts Receivable (AR) and Fixed Assets (FA). We have also written interfaces to and from our Stock & POS, Payroll, Bank Reconciliation and PO System. We are also in the process of finalising bespoke integration with Excel for uploading of journals and invoices.

Database Design

Believe me, I have asked the question, but no answer as yet. The design of the database flies in the face of database design principles. Very little attempt has been made to normalise the tables. The biggest sin of all is that the company code (DataAreaId, Char(4)) is stored against EVERY record in the entire database. This would have been the prime candidate for a surrogate integer key. Follow that closely with AccountNum and Dimensions and one can see the HUGE space savings and potential performance improvements. This design, in my experience, has 2 potential sources. One, the database was designed by front end coders. Two, the database was designed by someone who has little experience with large record sets. What is going to happen when my LedgerTrans table gets to 100 million rows? On that note based on all the answers that I have received back from Microsoft it would appear as though Dynamics does not have a stock standard archiving solution shipped with it. So this is what I'm thinking - "I have a database which is not normalised, check. My business processes approximately 40 Million Journal Lines per annum, check. Dynamics does not ship with an archiving solution, check. Question - What will happen to performance and database size over the next 3-5 years?". The answer? Upgrade the DB to SQL Server 2008 with compression set on. Talk about banking on technology improvements.

Something else that really baffles me is the apparent lack of referential integrity. There are also no stored procedures and very limited use of views. I understand that the product is also designed to run on Oracle but one has to ask the question. Why does a company that has developed a database and punted certain principles of design within that product group then write a product that forsakes all of them.

Accounting and Interface Design Principles

I have wrestled hard trying to come to terms with the design principles and how Axapta was conceived. It is both ingenious and ludicrous at the same time. The concept that one needs to get under the belt very early is that you can do just about anything from anywhere when it comes to journals. One is able to debit a supplier in Accounts Payable and credit a customer in Account Receivable directly etc etc. The system setup through it's posting profiles etc from all Subsidiary Ledgers keeps the General Ledger in balance at all times. With such abilities comes the inevitable framework to flummox even some of the most seasoned accountants. When configuring the system you have to keep your wits about you and really focus on the requirements. I cannot help but think that somebody with excellent systems design talent sat down with a bunch of accountants and when all was done and dusted he presented a system, not as the Royal Accounting Society would have done it, but rather as a Technological show piece. I like it, but it will take sometime for the business to fully come to terms with it.

External Interfacing

Dynamics AX is somewhat of a framework and does allow a number of ways to interface to it. The most prominent being through Web Services, the Application Integration Framework (AIF) and natively using X++. If you have read my prior blogs you will know that we hoofed the AIF due to bugs. We have used X++ to interface from our LOB Systems. (When I get a chance I will pen a full article on how we achieved this). After our experience with the AIF and BizTalk we have not endeavoured to test the Web Services.

Reporting

Dynamics AX does not ship with adequate report writing capabilities. Period. 

Frx Reporter is an additional cost and we all know that it is on it's way to the grave. PerformancePoint Management Reporter can be purchased BUT no standard direct data access at this point. My approach to this has been to keep the exact same PPS Financial Model as from our current ERP System and to just add to the data from AX after we go live. This way our Accountants will have one source of reporting data and it will have current data as well as years of history.

Overall Opinion

To date nothing in AX has wowed me. I am impressed with the development framework, disappointed with the reporting and pretty much neutral with everything else. We are not sure how it is going to perform in the wild but time will tell. Both our AOS and SQL Servers are way over spec'd so we do not really think that performance is going to be an issue. We followed the standard guidelines on setting them up. I do believe that we should however move to SQL Server 2008 as soon as we are comfortable with it. (Read- wait for SP1).

What's Bugging the AIF

Well you heard it here first. The Application Integration Framework (AIF) for Dynamics AX 2009 was dropped by us due to its inability to process large volumes of data for journal imports. This issue was raised with Microsoft as a bug and credit must be given to them for a very speedy and concerted effort to find the source of the issue. I had a conference call with Michael Merz and a whole bunch of people from Microsoft land a few weeks ago just after we dropped the AIF. They undertook to do some in-house stress testing and report back as soon as they could. The have upheld the commitment.

I received confirmation this week that there is indeed a bug, not actually in the AIF but with the GL Service that is called in Dynamics. Apparently some sort of validation was being called at a line level that required some caching. This turned out to be less than optimal on larger sets of data.

A hot fix should be available at the end of this week.

- Paul Steynberg

BizTalk Dynamics AX AIF Adapter

A few days back I was blowing off some steam about the Dynamics AX AIF and the lack of documentation and know how. Our BizTalk consultant, Paul Somers, has done an excellent job of working it out and has posted a blog on his site giving the details thereof.

You will find 2 blogs to do with our issues and hopefully more to come.

Happy reading.


- Paul Steynberg

I Promised Myself ........

I promised myself when I started this blog that I would not use it as a soap box to expel the evil spirits of the day, but today I am going to make an exception.

So by now most of you are aware that I am implementing Dynamics AX 2009 and had decided early on in the project to use the Application Integration Framework (AIF) to interface to and from the system. I attended the Microsoft Convergence Conference in Orlando in March this year and after hearing Michael Merz speak, it was in the bag.

Back home during early negotiations with Microsoft and the suggested "Partner" we emphasized this point over and over. At no point does anyone stop me and say "but nobody here in South Africa knows how to use the AIF and certainly not with the BizTalk Adapter". So come time to install the AIF and the BizTalk Adapter our Partner's have no experienced skills base and everyone spins out trying to read the outdated documentation. In short it was not properly configured by the time my BizTalk consultant walked on site to write the interfaces.

It took us several working days to get the BizTalk Adapter to successfully pass XML documents into AX and get a return receipt. How that was done is just another whole story on its own. Once our BizTalk guru has documented the "undocumented" on his blog I will post a reference to it.

So now it works. We then start to pile on the volumes of our daily journals into the test DAX environment and it falls over. We eventually work out that if we chunk the data and send in lots of small journals it works. Our average daily journals into our current system from our LOB systems can vary from about 3000-9000 lines. We had to break them down to roughly 1000 lines each for the AIF to consume without rolling over on its back. To add to this my developer has written a cracker of an interface to bring in our payroll data as journals into DAX. This journal can be up to 6000 lines long and it loads perfectly but when he tried to validate it, it fell over with an out of memory error. In fact quite similar to the error that we were experiencing from the AIF side.

Well if you just imagine there was much wailing and gnashing of teeth. Here we had spent millions on an "Enterprise" solution and it fell over on a few thousand line journal. I sat and contemplated how I was going to tell our Executive Committee on Friday that I had spent millions on the software, had consultants on site who charged like wounded buffalo, and the system was not capable of processing what our current system could. A rusty bread knife and the thought of me slowly perishing in a pool of my own blood suddenly looked appealing. Anyway this passed quickly as I banged out out my plight on the support site with Microsoft.

Well friends it looks like AX has a default install that pretty much matches itself up with something like, say, Quickbooks. ie, it looks good but don't ask it to handle volumes. It would appear that the default size for memory allocation in the AOS is set to 10 MB at installation. To change this default buffer size requires changes in the AOS Server registry and a new entry in the AXC File. Another issue is a restriction on the RPC which also requires a registry setting to increase the MaxRpcSize. One can also increase the maxbuffersize of the client.

Now I have to ask the question, how can one market and sell Enterprise software and then install it with such limitations? I have gone through all the documentation provided with the product and also anything else that I have gleaned from the net and nowhere can I find reference to these settings.

Bad form Microsoft.

- Paul Steynberg

Talking in the Dark

Today has been quite a trying day for my team and I. The Dynamics Consultants have configured the AX2009 AIF as best as they could from the AX4.0 documentation. Now this in itself is quite something as they do have differences. The BizTalk Adapter was also configured and ready for our BizTalk expert who walked on site this morning. The day started of sterling with a thumbs up on our BizTalk installation. The rest of the day was however spent blundering around in the dark trying to get the 2 talking to each other. Have you ever tried to figure out what is going on with outdated documentation. I do not think that anything could possibly be more frustrating.

So at the end of the day we sit with 2 systems that appear to be properly configured but not able to communicate. (Sounds like my wife and I).

Any way here is the error that we get when we drop a document into the BizTalk inbound connector after it has tried to pass this to the AIF.

Event Type: ErrorEvent Source: Microsoft Dynamics AX
2009 adapterEvent Category: NoneEvent
ID: 0Date: 2008/07/16Time: 01:27:07
PMUser: N/AComputer: AUTHENTIXDescription:The adapter is unable
to send the following message to Microsoft Dynamics AX:MessageId:
77802122-69d3-4676-912d-d73d95090e3fError: http://schemas.microsoft.com/dynamics/2008/01/documents/Fault">
Request Failed. See the Exception Log for
details.

--------------------------------------------------------------------------Product
Version : 5.0Assembly Version : 5.0.0.0Class :
AdapterTransmitterEndpointMethod : ProcessMessageSubsystem :
BizTalkDynamicsAdapter

So if anybody has seen this before your comments would be most welcome. When we find a solution I will post it.

- Paul Steynberg

Dynamics AX AIF

If you are looking for some quality information on the Dynamics AX Application Integration Framework I would seriously suggest that you visit Dianne Siebold's blog on the subject. Her blog focuses on the SDK documentation for Dynamics AX.

- Paul Steynberg

The X-Files

So why have I been so quiet for the past few weeks? Well, a lot has transpired. We have made the decision to replace our current ERP system with Microsoft's Dynamics AX. My team and I had exactly 14 weeks to implement counting from the day the first consultant walked onto site which was about 10 days ago. So to say that the deadline is tight would be an understatement.

What does this have to do with the X-Files? Well apart from the new product being called AX, I have also been heavily exposed to XML. I would suggest reading the link to get a good understanding of XML but here is a quick description that sums it up for me. XML is an open standard which promotes self describing data to be transferred between points.

Dynamics AX 2009 is the latest release from Microsoft and we have decided to jump right in and skip V4.0. Most techies reading the blog at this point have probably fainted as we all know what new releases from MS can be like, but know this. My team and I did a lot of homework and also attended the Orlando Convergence Conference. We discussed our concerns with some of the team leaders for AX. The consensus was that as we are using only standard core functionality within the application, the risks would be minimal.

One of the main reasons I wanted to go with AX2009 was the new Application Integration Framework (AIF) provided. This framework allows the flow of data to and from other systems in a robust fashion. I attended the session given by Michael Merz (Program Manager) on the AIF and was extremely impressed with the business capabilities. AX2009 ships with a bunch of new web services for data interchange and we are going to take advantage of this as a lot of data that ends up in our ERP system is generated from home written sub systems. The AIF uses XML files in order to receive and send data.

Reading up on the AIF was quite a task and had me up late at night for a week. This exercise lead me to another product from Microsoft called BizTalk Server. AX2009 ships with the BizTalk connectors required to move data in and out of AX. The great thing about this whole "interface" layer (BizTalk and AIF) is that one never compromises the business logic at any point. We are now at a point where we are seriously considering using BizTalk Server to implement our system interfaces and from what I have seen and heard so far we will not be disappointed. One of the things that BizTalk does is allow sets of data to communicate and be converted into formats that can be read by both sides.

Hopefully BizTalk and the AIF will allow us to meet our deadline with some time to spare. Unlike David Duchovny and his large lunged side-kick Gillian Anderson I have taken a bold step for mankind and hired my people based on talent and not good looks.

- Paul Steynberg