Condividi tramite

Management Reporter failing Dynamics AX integration

  • Articolo

Ever had to troubleshoot a problem with Management Reporter (MR) integration failing before? No? You're not alone. For AX folks it’s not historically been super-widely used. This article gives an overview of a particular issue I had, and the tips I used to troubleshoot it, hopefully they are useful for you too.

The problem I had was that changes in the general ledger in AX were not coming through to MR, initially that’s all I had to go on, didn’t know anything more.

Before I get started with the issue itself, I want to give a little overview of the MR integration architecture:

MR integration 5-second-overview:

  • MR has two databases – the data mart database, which it copies data from the AX database into, and the Management Reporter where it keeps report definitions.
  • The DataMart is populated by a process called “Management Report 2012 process service” this is a Windows service running on the MR machine.
  • This process service runs every 5 minutes and uses SQL change management on the AX data database to pick up the latest changes from AX and bring them to the DataMart database.
  • If AX changes are failing to get to MR, then this process of picking up the changes is failing.
  • If you’re looking for more background download the whitepaper here, you want the one called MRforDynERPInstGuide.pdf for details about how the architecture looks and how processing works.

So now on with investigating our problem: First stop – remote desktop onto the MR server, in the configuration console (configuration console, it has a dynamics logo icon, you’ll find it on the start menu) you can look at the logs section. In our case it was the “data mart integration” section showing an error, it was a bit generic but it at least confirmed something was wrong during processing, it was this:

[AX 2012 General Ledger Transactions to Fact] has encountered an error. Processing will be aborted. Error text: One or more errors occurred.

Tip 1: Query error log from the database
The log shown in the configuration console, comes from inside the management reporter database so you can query it to see when a problem started without wading through miles of logs by hand, I queried it like this and found it was erroring since Tuesday at 16:32, and that it has been happening almost exactly every 5 mins since then, so it sounds like the process service has been failing every time since Tuesday afternoon. 

 SELECT * from Scheduling.Message 
 where TEXT like '%Fact%' 
 order by TIME desc

UnfortunatelyI couldn’t find any clues about what had changed at 16:32, so I continued to troubleshoot. The messages continued to come in the log, every 5 minutes or so, each time the process service ran.

Tip 2: Use SQL profiler to monitor process service
Running SQL profiler, using the “tuning” template, with additional events for all errors and warnings turned on and filtered for the data mart database, when creating the trace, go to the "events selection" tab, check the "show all events" box, then right click the errors and warnings category and select all.

 

I watched the process start and try to do its thing. Here you can see two errors being raised: a duplicate key exception but before that this error:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.

It’s worth noting here that most of the processing work that the process service does, is performed by stored procedures in the data mart database – the service itself is largely just a wrapper calling the procedures.

Looking a little before this error in the SQL trace I see that there’s a stored procedure (recalculateBalances in the datamart database) where it should delete some rows in FactAttributeValue, and that bit fails with the collation error, so later when it comes to insert more rows, it tries to insert something which is already there. So I changed the procedure to avoid the error, and then it works. Note that this issue was fixed officially in RU5 for MR.

Tip 3: Enable MR verbose logging
Another useful thing for MR is that there is extra logging you can enable in the server application itself, you can turn this on like this:

1. Go to (C:\Program Files\Microsoft Dynamics ERP\Management Reporter\2.1\Server\Services).
2. Open the "MRServiceHost.exe.config" file in notepad.
3. At the bottom of the file you will see the following line:

 <switches> 
 <add name="Microsoft.Dynamics.Integration.TraceLog" value="0"/> 
 </switches>

4. Change the value from 0 to 4, as shown below:

 <switches> 
 <add name="Microsoft.Dynamics.Integration.TraceLog" value="4"/> 
 </switches>

5. Save the changes. Restart the two MR services.
6. It’ll make a log file in C:\ProgramData\Microsoft Dynamics\Connector for Microsoft Dynamics\Logs

The log file is good, it showed both the errors that we saw in the SQL trace, however here it’s not possible to see which stored procedure it was from, needed SQL trace for that.

Comments

  • Anonymous
    January 30, 2015
    The comment has been removed

  • Anonymous
    February 19, 2015
    Hi Mathieu - Have you looked into the blog posts written by the Management Reporter team? They announce the new releases there and if there is a compatibility requirement with the version of Dynamics AX, it will be included. The blog is located here blogs.msdn.com/.../dynamics_financial_reporting. I have not seen a specific table of versions but will send the team a note and maybe that will appear in a future post on their blog. Regards, Kim

  • Anonymous
    February 19, 2015
    Mathieu - Here is the link to the blog post that provides the compatible versions of MR and Dynamics AX:  blogs.msdn.com/.../management-reporter-feature-and-version-number-summary.aspx Regards, Kim