Monday, 27 October 2008
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
Thursday, 23 October 2008
Monday, 20 October 2008
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
Thursday, 16 October 2008
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
Wednesday, 15 October 2008
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'
'P2'+' FY'+cnt_yr_fscs when Period='Amt_Perd_03' then 'P3'+'
when Period='Amt_Perd_04' then 'P4'+' FY'+cnt_yr_fscs when
then 'P5'+' FY'+cnt_yr_fscs when Period='Amt_Perd_06'
FY'+cnt_yr_fscs when Period='Amt_Perd_07' then 'P7'+'
Period='Amt_Perd_08' then 'P8'+' FY'+cnt_yr_fscs when
'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,
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
>= '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
Monday, 13 October 2008
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
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:
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