Management Reporter/Frx Roadmap

The big question has finally been answered, what is the future of Management Reporter, FRx Reporter, FRx Forecaster and Enterprise Reporting? Well MS has released this roadmap.

2010 - MR to Replace FRx Reporter as the reporting tool of choice for Dynamics AX. This will coincide with the release of AX2010 (6.0) and it will be called MR V2.
2012 - MR to be released as V3 with AX2012 (7.0) and will now include Forecaster.
2014 - MR to be released as V4 with AX2014 (8.0) and will include functionality from Enterprise Reporting.

Personally I would not move from FRx Reporter until 2012 having learned a very hard lesson over the last year or so with the current version of MR.

Regards

Paul

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

Dynamics AX 2009 - Inconsistent Data When Exporting to Excel from Grid

Sometime back I listed some bugs that we had found in Dynamics AX 2009. One of the bugs was exporting large record sets from the journal lines grids into Excel. See here.

Although we have not managed to get this fixed here is a workaround until Microsoft releases the fix.

1) Go to AOT – Forms - LedgerTransAccount [this is the form that corresponds to Chart of Account Details - Transactions ]

2) Expand the node to view the datasources. Select the datasource ‘LedgerTrans’

3) Click on the ‘Properties’ icon or alternatively, press ‘Alt+Enter’, to open up the Properties window.

4) The property ‘StartPosition’ by default is marked ‘Last. This results in the cursor being at the last whenever the particular form opens up. Change this property to ‘First’ so that the cursor will always be at the top of the grid for the mentioned form

So whenever one opens this grid it will automatically navigate to the first record and will thus export correctly to Excel.

- Paul Steynberg

Interfaces to Dynamics AX 2009

A while back I penned about our dismal interlude into the BizTalk to Dynamics Application Interface Framework. I am led to believe that the problem was not with the AIF but with Dynamics and that it has been resolved. We however did not have any slack time in our very ambitious timetable to wait for it so had to take a different route.

I spent countless hours talking to people and browsing the Internet trying to find a robust way of creating interfaces and found absolutely nothing worth reading. Here is the route that I took.

I created a new Database specifically for interfaces into Dynamics AX. This Database is housed on the same server as our Dynamics Database. Within this database we then created a number of tables that are populated by our other systems within the organization. Based on our install I had 2 distinct types of interfaces. Invoices for Accounts Payable and Journals for the General Ledger. Most of the fields for both are shared anyway. Each system that was being integrated was assigned a "Source" code so that we would never lose track of where the data originated from. In a more complex environment I would suggest that you create a schema within the database for each source system. This will help with control and security.

Within Dynamics AX (DAX) we then created a new setup table and created a form called "External Interface Import Parameters". This table/form held the following information:

• Import Type – A short identifier of the interface
  
• Name – A full description of the external interface
  
• External Server Name – The name of the server that housed the Interface Database being called.
  
• Database Name – The name of the interface database mentioned above.
  
• Journal Name – We set up a different journal name for each interface so that they could easily be identified when looking at the transactions in an account.
  
• One Voucher Number Only – A bit switch. If set on this will create one voucher number for the entire interface run. If set off it will generate a new voucher number when it reaches a point of balance.
  
• Stored Procedure – This is the name of the stored procedure within the database to call in order to get the data.
  
• Write Back Table Name – The table that will be used to stamp back information once the interface has successfully run.
  
• ODBC Source Name – We had to use ODBC connections due to the way the systems run from the client or from the batch server. (Some history here but it was necessary).
  
• Batch Class Name – If this job was going to be called by the Batch server this is the name of the job to call.
  
• Auto Post – If set on it will load and auto post the interface journal.
  

This information is then used repeatedly throughout the interfaces. Here are the steps that all interfaces go through:

1. An interface call is made either from a menu item by an operator or from the batch manager in DAX.
   
2. Based on the Import Parameters mentioned above the job will then call a Stored Procedure on the Interface database and will pass it the Company code as the only parameter. This is often referred to as the DataAreaId in DAX.
   
3. The Stored Procedure fires on the Server and passes back to the job all the records for the interface.
   
4. DAX then creates the Journal based on the Journal Name and will either auto post or leave it as unposted.
   
5. When complete it will the write back to the table the Journal Number and Date/Time Completed.
   
6. The batch job will keep firing for the company until the stored procedure returns no records back.
   

So what do the Stored Procedures do?

All records that are new have a TransactionStatus of "NEW". Those in the process of being posted are stamped "WIP" and those completed are stamped "FIN". The stored procedures finds records that are "NEW" and then selects the Top 1 record summarized by your extraction criteria into a table variable. As an example our POS Journals are summarized by Company, Trading Date, Currency and ExtractID from the LOB System. This table variable is then used to join back to the table to return the result set. As part of this procedure we also stamp the records as "WIP" with the date/time it was sent to Dynamics.

Each interface has been specifically written to meet the LOB criteria. I am busy working on a more generic system that will completely future proof the business. Once the framework is in place no additional development will be required. The only work required will be set up a record in the Import Parameters Table, set up a Journal Name, Number Sequence and just populate the interface tables.

I would be very interested to hear from other parties how they tackled this.

- Paul Steynberg

Dynamics AX Implementation Hours and Costs

When I started this Dynamics AX 2009 implementation process I hunted the net looking for a graph detailing the costs and cash outflows for the duration of the project. I could not find one so had to rely on my past experiences and the vendor to budget. As we stand, just a few weeks away from the consultants walking off site, I predict that we will be bang on target. We should spend about 95% of our allocated budget. Having gone through this process I thought that I should share my weekly cost and hours graph with you. To read the graph is quite simple. The red line represents that weekly cumulative cost of the project relative to the total cost of the project. I have actuals to the week ending 24th October 2008 and budgeted figures to the end. The graph starts at 61% which is the total cost of the software, training for my team and half a weeks consulting time. The final costs are expressed as a percentage of the total cost. (Values excluded to protect innocent bystanders).

Cost of Software (including first years maintenance) 58.36%
Training of myself and my team 1.93%
Consultants Time 39.71%

I have not included hidden costs such as training lunches, my staff time, material printing etc etc.

The blue line represents consultants hours by week.



- Paul Steynberg

Spotlight on.....

At the beginning of my blogging career I penned the Financial System Manager’s Toolbox Series. This series covered amongst other essential products the likes of Speed SQL IDE and LiteSpeed from Quest Software. I have to add another product to this list called Spotlight also by Quest Software. We have found this product to be brilliant. Although specifically purchased to monitor our SQL instances it also has a wonderful Windows Operating System Dashboard. If it were not for Spotlight I am sure that we would have taken many more hours to find our Dynamics AX 2009 issue.

Spotlight gives you a visual interface into your systems inner workings and you can always see what is going on. The ability to also track back in time to see what was happening at a point in time is priceless. Here is an example of the dashboard for a SQL instance.

Other products are capable of doing this type of monitoring but we have just found this one to be easy and it looks really great.

- Paul Steynberg

Is Your Dynamics AX 2009 Client Freezing?

Picture the scene. 16 weeks of hard work and implementation time behind you. 1000's of hours with users, management and consultants. We finally go live and...... The clients hang for no apparent reason. No detectable patterns whatsoever. The peasants were getting restless. Imagine Marie Antoinette standing in the middle of Paris with a piece of cake in her hand. Things were looking a bit bumpy.

So we got under the hood with the spanners and gauges trying to find the problem. We eventually narrowed it down to open cursors locking a table "SysUtilElementsLog". This table is new to AX 2009 and is updated when the client is closed or every 15 minutes. If you are experiencing the client hanging I would suggest that you look for Database Locks. If they are locking the above table here is the work around from Microsoft. Backup the table prior to running this as it will be dropped. In essence one merely changes the table from being permanent to temporary.

1. Open the Application Object Tree (AOT).
2. Locate the Data Dictionary node in the AOT.
3. Expand the Data Dictionary node by clicking on the 'plus sign' located in front of Data Dictionary.
4. Locate the Tables node that exists within the Data Dictionary.
5. Expand the Tables node by clicking on the 'plus sign'
6. Scroll through the tables until you locate the SysUtilElementsLog table.
7. Right click on the SysUtilElementsLog table, and choose properties from the menu that is displayed.
8. Locate the property Temporary in the property window.
9. Change the Temporary property from No to Yes.
10. Save the changes by clicking on the disk icon in the AOT window.

- Paul Steynberg

Dynamics AX 2009 - ETL for PPS

Unlike our old system Dynamics AX is more suited to the PerformancePoint
Server Planning data requirements. Our old system stored the account movements in buckets by period in a single line for a year. In order to make this PPS friendly I had to use the SQL Server 2005 function "unpivot". We also had to hard-code certain things into the extract scripts in order to make sense of it in PPS. As an example here is the code I wrote. (It was beautifully structured and formated until a pasted it into the blog).

select case when code_fscs_dsg='A' then 'ACTUAL' when
code_fscs_dsg='B' then 'BUDGET'end as ScenarioLabel,case when
Period='Amt_Perd_01' then 'P1'+' FY'+cnt_yr_fscs when Period='Amt_Perd_02'
then
'P2'+' FY'+cnt_yr_fscs when Period='Amt_Perd_03' then 'P3'+'
FY'+cnt_yr_fscs
when Period='Amt_Perd_04' then 'P4'+' FY'+cnt_yr_fscs when
Period='Amt_Perd_05'
then 'P5'+' FY'+cnt_yr_fscs when Period='Amt_Perd_06'
then 'P6'+'
FY'+cnt_yr_fscs when Period='Amt_Perd_07' then 'P7'+'
FY'+cnt_yr_fscs when
Period='Amt_Perd_08' then 'P8'+' FY'+cnt_yr_fscs when
Period='Amt_Perd_09' then
'P9'+' FY'+cnt_yr_fscs when Period='Amt_Perd_10'
then 'P10'+' FY'+cnt_yr_fscs
when Period='Amt_Perd_11' then 'P11'+'
FY'+cnt_yr_fscs when Period='Amt_Perd_12'
then 'P12'+' FY'+cnt_yr_fscs when
Period='Amt_Perd_14' then 'P12'+'
FY'+cnt_yr_fscsend as Time_MonthLabel,
right(rtrim(left(id_acct,21)),8) AS
ACCOUNTLABEL,case when
Period='Amt_Perd_14' then 'MANADJ' else 'INPUT' end as
BusinessProcessLabel,left(ID_ACCT,13) as EntityLabel,'PERIODIC' as
TimeDateView,CODE_CURN_FSCS as CurrencyLabel,case when
substring(id_acct,14,1)
>= '1' and substring(id_acct,14,1)<='2' then 'CLO' else 'NONE' end as FlowLabel,substring(id_acct,6,2) as Country,Value,null as RowId, '' as RuleID, '' as ContextID, '' as AssignmentID, GETDATE() as CreateDateTime, GETDATE() as ChangeDateTime, 0 as LoadingControlID, 200 AS [BizSystemFlag],'' as BizValidationStatus, '' as BizSystemErrorDetailsfrom (Select Id_Acct,code_fscs_dsg,cnt_yr_fscs,CODE_CURN_FSCS,fscs_curn_type, Amt_Perd_01,Amt_Perd_02,Amt_Perd_03,Amt_Perd_04,Amt_Perd_05,Amt_Perd_06,Amt_Perd_07,Amt_Perd_08,Amt_Perd_09,Amt_Perd_10,Amt_Perd_11,Amt_Perd_12,Amt_Perd_14 from iMp30_afs)punpivot (Value for Period in (Amt_Perd_01,Amt_Perd_02,Amt_Perd_03,Amt_Perd_04,Amt_Perd_05,Amt_Perd_06,Amt_Perd_07,Amt_Perd_08,Amt_Perd_09,Amt_Perd_10,Amt_Perd_11,Amt_Perd_12,Amt_Perd_14)) as Unpvt

As mentioned Dynamics is better suited as it stores the balances by company, dimensions and accounts per day. So having to unpivot the data is now unnecessary. The information is now stored in a table called LedgerBalancesDimTrans. I have had a cursory look at the AX to PPS Wizard and have it on good authority that it will take across your transactions by day. Now I hate duplicating data so have written our extract to summarize the data by Ledger Period before loading it into PPS. In order for all this to make sense one has to make sure that the descriptions captured in the Ledger Period table match those that you are going to use when setting up the PPS Calendar series. For example I have used the format P1 FY2009 in PPS and must type this into the Period description on the table LedgerPeriod in Dynamics. Now my extract will bring across movement by period in the format that I require for PPS. There is subsequent conversions to id's etc but I will not get into that now. Below is the much shortened and simpler extract.

SELECT A.DATAAREAID AS DATAAREAID, A.DIMENSION AS DIMENSION, A.ACCOUNTNUM AS ACCOUNTNUM, P.COMMENTARIES AS PERIOD, SUM(A.DEBITMST+A.CREDITMST) AS VALUEFROM LEDGERBALANCESDIMTRANS A JOIN DBO.LEDGERPERIOD PON A.DATAAREAID=P.DATAAREAID AND A.PERIODCODE=P.PERIODCODE AND A.TRANSDATE BETWEEN P.PERIODSTART AND P.PERIODEND WHERE P.PERIODSTART>='2008-03-30'GROUP BY A.DATAAREAID, A.DIMENSION, A.ACCOUNTNUM, P.COMMENTARIESORDER BY A.DATAAREAID, A.DIMENSION, A.ACCOUNTNUM, P.COMMENTARIES

This was also laid out in an orderly fashion but it appears that the blog editor does not respect my line feeds and tabs. I think you will agree that Dynamics AX 2009 lends itself to easy integration with PPS Planning.

- Paul Steynberg

TILADAX Series

TILADAX - Things I learned about Dynamics AX, the Series. Over the past few months I have spent countless hours trying to find information on Dynamics AX. There is surprisingly less information than one would expect. Most of the information found tended towards either installation or development. A lot of what I have learned would have been helpful if it were published and although it may now seem trivial I am sure that someone else is looking for similar information.

To this end I am going to publish a whole bunch of TILADAX blogs. Some may appear ho-hum to the seasoned DAX Consultant but if it can help at least one person then it was worth it. The first blog will about the concept of "locked in journal" from the Chart of Accounts. I will try and bang it out tomorrow with a script that I used to check that the  accounts that should be locked in journal are in fact locked.

- Paul Steynberg

Setting the MaxBufferSize for Dynamics AX 2009

A while back I was bleating on about some changes to the MaxBufferSize to help out in some performance issues between the client and the AOS in Dynamics AX 2009. Someone pointed out to me yesterday that it may have been a tad helpful had I posted the procedures. My apologies and here they are. Changes must be made to both the AOS and the Client.

DISCLAIMER: I have made these changes on our system BUT I do not endorse them or suggest you do them without first speaking to MS. (That should keep the legal chaps happy). And as per MS please backup your registry before attempting any changes.

AOS Registry
Key name: [HKLM\SYSTEM\CurrentControlSet\Services\Dynamics Server\5.0\\]
Value name: maxbuffersize
Value type: REG_SZ
Value:

Client Registry
Key name: [HKCU\Software\Microsoft\Dynamics\5.0\Configuration\]
Value name: maxbuffersize
Value type: REG_SZ
Value:

AXC-File Add the following line to the AXC-File manually:

maxbuffersize, text,0

Use Notepad to edit the AXC file. Do not change the format of this file. These changes will disappear if you change the configuration using the ConfigUtility.

Another source of restriction may also be the MaxRpcSize. I would suggest taking a look at this on the client and the AOS. You can find the setting on the AOS here:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Rpc

I would suggest reading up on this before making any changes.

I did notice an improvement when loading very large journals from our Excel templates.

- 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).

Dynamics AX 2009 - Bug List 1

For those of you who are looking to move to Dynamics AX 2009, here are some bugs/issues that you might find worth knowing about before you start.

Journal Grid Export to Excel

We found that if one did an inquiry which returned a large number of records for an account (say 10,000 records) they would be returned to the grid, BUT exporting them to Excel produced erroneous records. This will only happen if you DO NOT navigate the grid prior to exporting the data to Excel. You can easily see the problem as it stamps every record with exactly the same journal number, being the first one. If you navigate the grid first you will get the correct records in Excel. One only has to hit Cntrl-Home and then export. So there is a problem but also a very easy work around.

Fixed Assets Movements Report

When running this report you will get an exception thrown as seen below:

The reason, Variable fieldPrintFAInfo_FR is only initialized under the French configuration key, but is accessed under all configurations.

The solution, Add check for French configuration key in getFromDialog() method.

Grid Sort Order

Now Microsoft are not totally convinced that this is a bug but I am going to stick to my guns on this one. Every single software product in the market place that uses grids and allows sorting by clicking on the heading, sorts in ascending order first. All the grids in AX 2009 default to sort in descending order first. MS inform me that this can be changed but I am not convinced that out of the box sorting should be descending.

Unsaved Changes in the AOT Mark Objects as Changed

This is just downright annoying. If you go into the AOT and say for example right click on Addresses under forms and select edit, the edit screen will appear. Now do nothing but close down the edit screen. You will notice that a red vertical strip will appear next to the object and if you close and open the AOT it has been marked as changed with (usr) layer. This is valid for all objects in the AOT that we have looked at.

As we come across any others (I am sure we will), I will post them.

- Paul Steynberg