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

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

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

Management Reporter Considerations

When using Management Reporter (MR) with PerformancePoint Server Planning you must take certain things into consideration. To start with you must have a Financial Model as your source for MR. This will give the entity wizard a few things that are required such as currency and a calendar. It is also crucial that the calendar view selected for MR must have Years and Months.

How you set up your dimensions is also very important especially if have specific reporting requirements. None of the dimension attributes are exposed to MR so if you require them then create separate dimensions. For example you may have set Country as an attribute of your Entities and you will not see these in MR. Actually create a new dimension called Country and suddenly it will pop up in MR for use.

Another consideration of labeling your dimensions is that when exposed in MR, the hierarchies are not visible. It may be a good idea to identify your dimensions in some manner so that one can determine parents vs leaf level members. You may consider having an alternate hierarchy dimension for accounts that only show posting level accounts. This way you do not have the risk of adding already totalled accounts more than once.

By just keeping these few things in mind when creating models for reporting through MR will make life a lot easier.

- Paul Steynberg

Performance Issues with PPS Management Reporter - Update

After months of testing PerformancePoint Server Management Reporter in it's current state, I have concluded that it will not handle the volumes that our company requires. It is way to slow and will just frustrate the business. The problem lies with the whole design of the system. I did a full analysis of the MDX code and how the system operates and found that the client (on your machine) sends an MDX code string to the Analysis Server for every combination of lines in your report to the entities specified in your reporting tree. So if you have a 50 line report and you are running it for 10 entities you will send out 500 MDX queries, one at a time. As you can imagine this makes my ZX81 (with the 64Kb Ram Pack) look like Usain Bolt pumped up on steroids.

Do not despair, all is not lost. I had a very positive conversation last night with some members of the team working on this issue. I suspect that we could see two positives in the near future. The first being a MASSIVE improvement on SQL Server 2008. PPS should be certified to work on SQL Server 2008 by SP2 which is planned for December 2008. Another idea kicked around is changing the architecture to a service type environment on the server and use multi-threading and the power available. I would guess that this type of change would only be done for V2, so let's wait and see. As soon as I get my grubby paws on a copy of SP2 to test I will post an item on performance improvements.

- Paul Steynberg

Should You Throw Apples at Windows?

I found myself looking around for a new laptop a few month ago. Around the same time I purchased a Canon DSLR and started a photography course. Everything pointed to a Mac being the choice for my hobby but Windows was required for my work. I found an article on running Windows on a Mac using Parallels and tested it out on a Mac that I borrowed from my company.

Well hold the phone people, we have a winner. I can say from first hand experience that purchasing a MacBook Pro is the best thing I have done all year. I can switch between Windows and Mac OS without even thinking about it. All my Windows tools are still available and I can benefit from Aperture 2 on Mac for my photograpy.

From what I can gather there are 3 products that allow you to run Windows on your Mac. They are Parallels, Fusion and Macs own Boot Camp. I chose Parallels and have been very happy with the results. It has a mode called Cohesion which presents Windows in such a way that it looks like your Windows applications are running natively within Mac OS. In other words you do not get the Vista desktop in a traditional sense of the word, you get the task bar loaded with your Mac task bar and you cannot tell that it is running on a virtual machine.

If you decide to go this route I suggest that you purchase a 4Gb memory kit from Kingston at around $100 and replace the 2x1Gb modules that comes standard with the Mac. The performance increases are well worth the price.

Another good idea is to share your documents with Mac outside the virtual machine. Also once you have installed Vista and all your applications take a snapshot. This way if you mess something up in the future you can restore the snapshot and still access your documents.

I have tested my system with no problems as yet. I can even terminal on to my PC at the office via a VPN.

The two biggest pleasant surprises for me have been the following:

• If you have a number of applications open and say for example you have done some work on a new Excel spreadsheet but have not yet saved it, you can just quite Parallels and when you bring it back up again in the future the spreadsheet is waiting for you just as you left it.
  
• If you receive e-mail attachments via your Mac, say a Word doc, and you double click it on your Mac, it associates it with Word in your VM and opens up your Vista session and presents the document in Word.

So to answer the original question, YES, you should throw Apples at Windows.

- Paul Steynberg