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

Finding Your Dynamics AX Lead Blocker

Dynamics AX 2009 does have some issues with SQL Server locks and quite often we are finding that people cannot access journal lines due to someone else blocking them. This then just hangs the client and it becomes unresponsive. Here is a quick method of finding the offender. The spid in SQL can be traced back to the user in Dynamics AX 2009 on the online users screen which can be accessed from "Administration". Each user that is working will have a SPID or sometimes several attached to his/her user name. Now you can call the lead blocker as you have a name and ask them to shut down AX.

If you do not have a tool such as Spotlight then create a view on the Master database that will show you the blockers. Here is the code for the view:

Create View vSysProcesses as

select p.spid, case when (p.blocked <> 0) then left(convert(varchar, b.spid) + ' - ' + case when (b.nt_username = '') then rtrim(b.loginame) else rtrim(b.nt_username) end -- UserName, + ' on ' + rtrim(b.hostname) + ', using ' + rtrim(b.program_name), 80) else null end as [BlockedBy], case when (p.nt_username = '') then left(p.loginame, 15) else left(p.nt_username, 15) end as UserName, left(p.hostname, 15) as HostName, left(p.program_name, 35) as Program, left(isnull(d.name, ''), 20) as DB, left(p.cmd, 25) as Command, left(p.status, 15) as Status, p.login_time as LogInTime, p.last_batch as LastBatchTime, left(p.lastwaittype, 20) as [LastWaitType], p.Open_Tran as [OpenTran], p.cpu as [CPU], p.physical_io as [PhysicalIO], p.[memusage] as [MemUsage], p.dbid as [dbId], (((1.0 + p.cpu) / 8500.0) * ((1.0 + p.physical_io) / 1100.0) * ((1.0 + p.[memusage]) / 38.0)) / (datediff(ss, p.login_time, GetDate())) as ResourceUsageFactorfrom master.dbo.sysprocesses pjoin master.dbo.sysdatabases don p.dbid = d.dbidleft join master.dbo.sysprocesses b -- blocking on p.blocked = b.spidwhere p.spid <> @@spid and p.Status <> 'background'

Once you have created the view run this against the view and you will find the blockers:

use master

select * from dbo.vSysProcesses

order by blockedby desc

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

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