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

Is Talking Business Cheap?

As part of my Dynamics AX 2009 project we are evaluating the use of BizTalk as a mechanism to integrate data between our LOB systems and DAX. As we are a value retailer (Sounds cooler than calling us cheap-ass), it is in-bred in us to maximize our spend. (Also sounds cooler than milking it).

BizTalk is expensive, you cannot hide from this fact. A 2 processor license for the Standard Edition will set you back $17,000 and for the Enterprise Edition you are in for a stonking $70,00. My reference is here. I am sure that one can argue that this is before discounts etc etc but lets just talk list for this exercises. So what does the Enterprise Edition give you that costs $53,000 extra. You can go from a limit of 5 applications to unlimited. It also allows you to use more than 2 processors and it scales out better with fail over clustering etc etc. Apart from that the functionality is pretty much the same. Now I would imagine that most medium to larger enterprises would probably be OK with the Standard Edition depending on how many disparate systems are lying around.

On one of my late night web blundering, trying to find out more about this animal and potential alternatives I came across something quite exciting. I found a company based in the UK by the name of Quality Componentware. These guys are BizTalk consultants and are Micrososft Gold Certified Partners. If one digs a bit deeper you will also find out that they have written an alternative to BizTalk called SmartsIntegrator. The pricing? $499 per machine!!!! And it does not look that bad. I downloaded a trial version and had it installed and working on my PC within half an hour. Compare this with the day it took us to get a test version of BizTalk up and running.

After installing the product I browsed around and found it relatively intuitive. I created and inbound and outbound file location. Dropped file into one side and a second later it appeared on the other side as expected.

I am in no way endorsing this product and cannot find any customer references on it, BUT at the price surely it is worth investigating? I am certain that BizTalk will club it in terms of volume handling and pure functionality BUT does every business need that?

If you have heard about or worked with this product your comments would be most welcome.

- 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

The Financial System Manager's Toolbox (Part II)

A while back I promised to elaborate on Microsoft Analysis Services as part of a Financial Systems Manager's Toolbox, so here it is. If there was an Accounting God this would be the tool that he would bestow upon his most loved people. SSAS is shipped with certain SQL Server versions. To see if yours is please follow this link:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

I cannot even begin to tell you how incredible this software is for accountants. (Well actually for most people but you get the idea). It allows one to take huge amounts of data and store it in such a way that you can retrieve aggregated data on any combination of dimensions FAST. Two words in that sentence are of importance. The first is dimension and the second is fast.

A dimension in SSAS terms is one of a possible number of ways to describe a number. Here is an example. I give you a number, say 100. That means nothing to you. Now I start to give you some dimensions and dimension members and suddenly the 100 makes sense. Here they are:

Dimensions and Member of the Dimension
Entity=Contoso Head Office
Department=Accounting
Account=Bank Charges
Scenario=Actual
Period=Y2009P03
Currency=USD

Now that you have the dimensions and the dimension member the 100 suddenly makes sense. It is the actual bank charges (as apposed to the budget bank charges) for the accounting department at Contoso Head Office for the accounting period 3 of Year 2009 in dollars. Now imagine having millions of rows of data each having these dimensions describing the number in each row. To try and make sense of this would be madness. Along come SSAS.

SSAS takes the data and builds OLAP cubes which pre-aggregate the data based on internal algorithms in order to allow the user to bring back information at every possible intersection of data with incredible speed.

Here at my organisation I have built a few financial cubes off our underlying ERP system. These cubes allow the rapid slice and dicing of information from a number of delivery mechanisms. I prefer delivering the data in pivot tables in Excel linked directly to the cube. One can also use tools such as ProClarity and PPS Monitoring and Analytics. The uses are endless. PerformancePoint Server uses SSAS as the data point for all its components including Management Reporter, a product that we are rolling out in parallel with our new ERP System, Dynamics AX 2009.

Once you start using something like this you will know that the Accounting Gods are smiling upon you.

- Paul Steynberg

Negotiating in the IT World

Purchasing software is quite an ordeal, especially ERP software. Here are a few tips and notes from some very recent first hand experience.

1. ERP software vendors are no better than second hand car salesmen. Listen well but make sure that you get any promises in writing or at least confirmation via e-mail. This has just saved me $45,000 and certain embarrassment. During the negotiation period they will promise everything and often state that "that functionality is standard". Be certain to check everything as often things don't add up when you get to sign the contract.
2. The RFP and negotiating always takes months. Find out when each of the ERP vendor's financial year end is. Aim to sign the final deal within a few weeks of this date. They will throw in the CEO's grandmother for free, if it means making target.
3. Plan your capacity with care. It often does not pay to purchase double the required licenses now at a great discounted price if you are not going to hit the numbers for quite some time.
4. A large portion of a major ERP project requires no software so do not buy the software up front as it will sit on your desk starring at you while your annual license fees for a gazillion concurrent users remains unused. So when you do go live you suddenly find yourself having to pay the annual fee again a few days later. Use a test environment until you really have to install the live one.
5. When one of the ERP vendors comes to visit during the negotiation stages always subtly remind them that this is a competition. For example when Oracle comes to visit make sure the pen you remove from your top pocket to make notes is from SAP. Don't show the SAP people out of the lobby with the Oracle bunch waiting for you. This may sound effective but they all know each other and you don't want them to start talking to each other.

Above all else, as mentioned in one of my other blogs, DO NOT believe all the white papers. Do your homework diligently and if you are offered site visits try and do them without vendor presence as you can really get some honest answers.

- Paul Steynberg

Management Reporter - Scheduling Reports Act II

Sometime back I requested the ability to schedule reports from PPS Management Reporter, a feature that was missing from V1. I put together a quick spec for Microsoft of what would pacify us until they put a proper full scheduler into future releases. This is what I requested as a minimum:

The command – ManagementReporter.exe

Flags/Parameters

-Generate (action to be done)
-Connection:DefaultConnection (connection created to the MR database.)
-Logon:”Mr Price Group” (The entity that you wish to log into)
-BuildingBlockGroup:Default (building block within logged on install that you wish to use)
-Report: “\Mr Price\SSMP1 Income Statement with PMO Monthly” (The report name and the subdirectory stored in)
-Output:”Management Reporter” (Could be “Management Reporter” or “Reporting Definition Language” or “Printer”

So what did we get? In short we get to use the same ReportGenerator.exe that is in the program files directory. We also get 3 of the 6 switches/parameters requested. These are:

-Generate
-BuildingBlockGroup
-Report

The report flag does not however require any sub directory as although the reports are stored in directories they MUST be unique in name.

Here is an example of the command syntax.

ReportDesigner.exe -Generate -BuildingBlockGroup:Default -Report:"SSAS4 Expenses Actual vs Budget Monthly"

We are extremely grateful for what we have received but it does come with some prerequisites as the Report Designer is actually opened and then closed once the report has completed.

1. Remember to tick the "save my credentials" box on the logon screen for Report Designer on the machine that you are going to run the scheduled reports. Also make sure that the appropriate default connection and entity are selected. The scheduled reports will use these credentials, connection and entity.
2. Any reports run should not have "Select units at run time" ticked on the Settings Tab under "Reporting unit selection".
3. You can only output to Management Reporter and not to file or RDL. Even if you select these they report will only be sent to the Report Viewer.
4. You cannot run more than one report at a time. If one report is running any subsequent attempt will realize that the Report Designer is already open and report a success message even though it has done nothing.

A list of error messages that you can encounter are:

0=Success
1=UnknownBuildingBlockGroup
2=UnknownReport
3=ReportViewerExportError
4=ReportFailedToLoad
5=ReportAlreadyGenerating
6=ReportingServicesGenerateError
7=UseRowDefinitionFromTree
8=ReportDesignerBadRepositoryPermissions
9=UnknownRepositoryError
10=FailedToConnectToReportingServices
11=BeforeGenerateValidationError
12=AcceptSecurityWarningError
13=ValidateDataError
14=BuildingBlocksNotValidated
15=UnableToCreateTree
16=CompanyCredentials
17=EnsureCompleteCompanyCredentialsFailed
18=EngineException
19=FailedToOpenReport
255=UnknownError

Reports can be run from the command line, task scheduler and from SQL Server Agent. Although this is not comparable to the Frx Reporter Scheduling it is a step in the right direction.

- Paul Steynberg

Management Reporter - Some Feedback

Thanks to Jill Carter from the Management Report Team for a prompt reply to my questions posed to them yesterday. Refer to my last post.

It seems that the team tried to "group" the entities together and fire of fewer queries but found that is was slower. Who would have thought?

There are apparently some improvements on SQL2008 which we can look forward to. I have also suggested that they multi-thread the queries but at this point it cannot be done as a hotfix as it my destabilize the product. Watch this space.

- Paul Steynberg

Management Reporter - Fixed or Not Fixed, That is the Question

For those of you following my Management Reporter performance tragedy here is Act II. I received my QFE . Now it sounds ironic that I would be experiencing a performance issue with something called PerformancePoint, but what is more ironic is that the fix did not really fix the problem. I have seen a marked improvement in the report generation times BUT it is still not good enough.

Most of the reports halved in the time taken to run. A report with a large reporting tree did not return in the previous version (canned after 8 hours) but took 23 minutes in the "fixed" version. So the gains are excellent but how does this stack up to the data. Not well, the 23 minute report only needed to fetch 3310 rows of data from the underlying PPS cube. I ran the report a number of times in succession, hoping that the trick of "warming" the cache might be my salvation. Performance increases were seen and this report took 12 minutes to run. So from not being rendered at all to just 12 minutes means that the people at Microsoft did some serious work. Now from a technical perspective one has to admire the effort. From an end users perspective I still say "toooooooo sloooooooowwwww".

Surely it cannot take 12 minutes to return 3310 rows of data from a technology such as OLAP which was designed for fast and efficient retrieval. So what is the problem? In order to make sense of this I profiled the cube being connected to by MR and what I found is quite interesting.

Reports are made up of a combination of rows, columns and then reporting trees. It seems that for every row in the report an MDX query is built to return the required data and then fired off to the server for each entity in the entity reporting tree. Here is an example of the MDX query. I have replaced the parameters with the actual member names. They follow the query in the profiler in XML format. Now take note of the red code which is the entity reference.

WITH
-- Column Sets and Members
-- Columns General: Define a set and calculated member for each unique filter criteria across all columns.
SET [Criteria480369GLScenarioCriteria] AS
{
([Scenario].[All Members].[Actual])
}
SET [Criteria480370GLTimeDataViewCriteria] AS
{
([TimeDataView].[All Members].[YTD])
}
SET [Criteria480371GLTimeCriteria] AS
{ [Time].[Financial Calendar].&[772007013] }
MEMBER [Time].[Financial Calendar].[Criteria480371GLTimeCriteria] AS SUM ([Criteria480371GLTimeCriteria])
SET [Criteria480372GLCurrencyCriteria] AS
{
([Currency].[All Members].[ZAR])
}
SET [Criteria480377GLScenarioCriteria] AS
{
([Scenario].[All Members].[Budget])
}
SET [Criteria480378GLTimeCriteria] AS
{ [Time].[Financial Calendar].&[772008025] }
MEMBER [Time].[Financial Calendar].[Criteria480378GLTimeCriteria] AS SUM ([Criteria480378GLTimeCriteria])
-- Column46363
SET [Column46363] AS
{
( Criteria480369GLScenarioCriteria, Criteria480370GLTimeDataViewCriteria, [Time].[Financial Calendar].[Criteria480371GLTimeCriteria], [Account].[DebitCreditMemberId].[All], Criteria480372GLCurrencyCriteria )
}
-- Column46364
SET [Column46364] AS
{
( Criteria480377GLScenarioCriteria, Criteria480370GLTimeDataViewCriteria, [Time].[Financial Calendar].[Criteria480378GLTimeCriteria], [Account].[DebitCreditMemberId].[All], Criteria480372GLCurrencyCriteria )
}
-- Column46365
SET [Column46365] AS
{
( Criteria480369GLScenarioCriteria, Criteria480370GLTimeDataViewCriteria, [Time].[Financial Calendar].[Criteria480378GLTimeCriteria], [Account].[DebitCreditMemberId].[All], Criteria480372GLCurrencyCriteria )
}
-- Row Sets and Members
MEMBER [ValueMember] AS [Measures].[Value]
MEMBER [ValueMemberWithSignReversal] AS [Measures].[Value] * IIF([Account].[MemberId].CurrentMember.Properties("DebitCreditMemberId") = "Credit", -1, 1), NON_EMPTY_BEHAVIOR=[Measures].[Value]
-- Row 61640
MEMBER [61640] AS
( [ValueMemberWithSignReversal],
([Account].[Masterpiece].[7XXXXXXX]),
([BusinessProcess].[Standard].[INPUT]),
([Flow].[All Members].[NONE]),
([Entity].[Entity Hierarchy].[SSMP1AA46])
)
-- Main Select Statement
SELECT
{ [Column46363], [Column46364], [Column46365] }
PROPERTIES MEMBER_CAPTION ON COLUMNS,
NON EMPTY
{ [61640] }
PROPERTIES MEMBER_CAPTION ON ROWS
FROM [FinancialBudgets]

This code returns 1 row of data.

So if you have 60 entities in your Reporting Tree Definition this code is going to run exactly as it is 60 times with only the row number and entity changing. So why not modify the MDX and send only 1 query through per line and modify the row set to include ALL entities? I tested my theory and modified the code with 3 additional entities. The run time was the same but it returned 3 rows of data.

Here is the part that is amended:

-- Row 38186
MEMBER [38186] AS
( [ValueMemberWithSignReversal],
([Account].[Masterpiece].[3XXXXXXX]),
([BusinessProcess].[Standard].[INPUT]),
([Flow].[All Members].[NONE]),
([Entity].[Entity Hierarchy].[SSMP1AA62])
)
MEMBER [38187] AS
( [ValueMemberWithSignReversal],
([Account].[Masterpiece].[3XXXXXXX]),
([BusinessProcess].[Standard].[INPUT]),
([Flow].[All Members].[NONE]),
([Entity].[Entity Hierarchy].[SSMP1AA63])
)
MEMBER [38188] AS
( [ValueMemberWithSignReversal],
([Account].[Masterpiece].[3XXXXXXX]),
([BusinessProcess].[Standard].[INPUT]),
([Flow].[All Members].[NONE]),
([Entity].[Entity Hierarchy].[SSMP1AA64])
)
-- Main Select Statement
SELECT
{ [Column19387], [Column19388], [Column19389] }
PROPERTIES MEMBER_CAPTION ON COLUMNS,
NON EMPTY
{ [38186],[38187],[38188] }
PROPERTIES MEMBER_CAPTION ON ROWS
FROM [FinancialBudgets]

I am curious as to why MS has elected to fire 1 MDX query per report row for each entity and have put the question to them.

I did notice one additional feature with the hotfix. The "number of records processed" counter on the "Generate Report" screen now shows the count in 100 increments as the rows are retrieved. In the previous version you would have to wait until the end of the report for the total only so you had no idea of the progress.

I will report back once I have more information from Microsoft.

- Paul Steynberg