Sometimes people have troubles with their Management Reporter integration with AX 2012. I also encountered some problems in the past. If the synchronization is not working, or not working correctly, it could be related to data issues in your Microsoft Dynamics AX environment. In this post I will share some ways to find and solve some problems.
Is your integration service running?
When you notice amounts are incorrect you can first have a look if the integration service is running and the last date/time when it has been updated. You can do this using the Management Reporter 2012 configuration console.
When this is not a very recent date/time the integration is not running. This should run every 45 seconds.
Find errors in the log files
In the Management Reporter 2012 configuration console you can view the log files created by e.g. the data mart integration. Check if there are no errors. If there are errors they need to be resolved. There are multiple causes. Some of them are described below.
Problem synchronizing users
If the error details mentions you have an issue synchronizing users due to the license, you have installed Management Reporter, but not registered your license code. To solve this you need to get your license code. Then open the Report Designer application. Go to Tools and select the option Registration. The registration form opens. You can paste the License key in this form. Validate if it is correct and the click OK. If you have not installed the license, Management reporter is running in a trial/demo mode.
“Object reference not set to an instance of an object”
If you have this error, there is a page on Microsoft support to help you. You can click here to go to this page. The provided solution did not work for me in a particular scenario, but it guided me to solve it. The error is related having a value of an enumeration that does not exists. The resolution provided guides you to solve a problem related to the field Posting type. In my case due to a conversion of main accounts from a previous version a wrong value was used in the Main account type. You can use the resolution provided by Microsoft Support also with other enumeration fields.
Other database issues
If there are some other issues in the error log, you can try to find and solve them using SQL scripts. In one particular scenario I got some scripts from Microsoft Support with SQL queries which can find data which causes the synchronization errors and also even could create inconsistent data mart data. You can find the scripts below.
You need to have access to the database to run these queries. SQL Management studio is commonly used to run the scripts. In the Management studio you can select the Dynamics AX database and then click New Query. An empty Query form will be opened.
Now copy the scripts (can be found below) and paste it in the Query window. Then click the button Execute and wait for the results. If there are problems data will be shown in the results window. In this particular example there are some accounting entry records where the header is missing. You then need to investigate why the header is missing. This could be related to a wrong customization or manual data correction. If you know what caused it, you can also solve the error by e.g. recreating the header record.
Per query, at least a header is shown. So to know which table causes the error we have to find (in this case) the fourth query and try to understand what the query is showing. In this example it will show records from the GeneralJournalAccountEntry table where no record in the table GeneralJournalEntry exists.
SQL scripts
--budgets assigned to invalid company
select * from BUDGETTRANSACTIONLINE l with (nolock)
join BUDGETTRANSACTIONHEADER h with (nolock) on l.BUDGETTRANSACTIONHEADER = h.RECID
where h.PRIMARYLEDGER not in (select recid from ledger)
--bad category assigned to an account that has transactions
--Script start
select MA.MAINACCOUNTID, MA.NAME, L.NAME as CompanyID
from MAINACCOUNT MA with (nolock)
join DIMENSIONATTRIBUTEVALUE DAV with (nolock) on DAV.ENTITYINSTANCE = MA.RECID
join LEDGER L with (nolock) on L.CHARTOFACCOUNTS = MA.LEDGERCHARTOFACCOUNTS
where MA.ACCOUNTCATEGORYREF not in (select ACCOUNTCATEGORYREF from MAINACCOUNTCATEGORY)
and DAV.ISTOTAL = 0
and MA.ACCOUNTCATEGORYREF <> 0
and MA.MAINACCOUNTID in
(
select DISTINCT MA.MAINACCOUNTID from GENERALJOURNALACCOUNTENTRY GJAE with (nolock)
join DIMENSIONATTRIBUTEVALUECOMBINATION DAVC with (nolock) on DAVC.RECID = GJAE.LEDGERDIMENSION
join MAINACCOUNT MA with (nolock) on DAVC.MAINACCOUNT = MA.RECID
)
order by MA.MAINACCOUNTID
--Script end
--transactions with an invalid company
select * from GeneralJournalEntry with (nolock)
where ledger not in (select distinct recid from ledger)
--transaction details with no associated transaction header information
select * from GeneralJournalAccountEntry with (nolock)
where GeneralJournalentry not in (select distinct RECID from GeneralJournalEntry with (nolock))
--invalid dimension combinations
select * from GENERALJOURNALACCOUNTENTRY GJAE with (nolock)
where GJAE.LEDGERDIMENSION not in (select distinct VALUECOMBINATIONRECID from DIMENSIONATTRIBUTELEVELVALUEVIEW with (nolock))
--Custom list financial dimension values
select * from DIMENSIONFINANCIALTAG with (nolock) where VALUE is NULL
--loops through all companies to identify null system dimensions
--Script start
DECLARE @TableName nvarchar(40)
DECLARE @SQL nvarchar(max)
DECLARE tables_cursor CURSOR FAST_FORWARD
FOR
select distinct DA.VIEWNAME
from DIMENSIONATTRIBUTE DA
where DA.TYPE <> 3 and viewname <> 'DimensionFinancialTag'
order by DA.VIEWNAME
OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Checking ' + @TableName + ' table...'
SELECT @SQL =
'SELECT VALUE, NAME, ''' + @TableName + ''' as TableName FROM ' + @TableName + ' WHERE NAME='''''
EXEC (@SQL)
FETCH NEXT FROM tables_cursor INTO @TableName
END
CLOSE tables_cursor
DEALLOCATE tables_cursor
--Script end
That’s all for now. Till next time!
Image by DarkWorkX from Pixabay
Hi Andre,
Thanks for posting this.
We have a problem where MR integration seems to run forever. We checked the log, it seems MR is looping.
We tried so many solutions recommended by other blogs but no success. Then we found your blog, run your script above and found one record from your script. After we “fix” the problematic record, rerun your script, the result is blank.
However, when we re-run MR integration again, the result is still the same, the integration process seems to be looping.
Have you encounter such issues? Any idea what else we should check?
Thanks.
Best regards,
Fredy
Hi Fredy,
What do you mean with “looping”? MR runs every 45 seconds to update transactional data between the AX database and the DataMart. In addition every 5 minutes more static data like users and security is synchronized.
Has anyone had an issue where the users in AX do not sync properly to management reporter? We can’t seem to add a new user. The user synchronization between management reporter and AX seems to stop working. The symptom is that new user added to AX with accounting-related roles have not been added to management reporter automatically. There is a blog about this problem or one very similar. We followed the instructions on this blog and removed all possible AD obsolete users from MR database last week. But the user sync problem still persists. Any suggestions?
Hi Ed,
Thanks for reading the blog. Have you checked if there are any synchronization errors? If not, check the security role(s) of this particular user. You can get further assistance on e.g. the Dynamics community.
Hi,
Is there a way to see if MR is still updating after a massive data load (for example a consolidation in AX) and MR is not yet fully in sync with AX?
Thank you
JP
Hi JP,
Thanks for reading the blog. You can monitor if you continue to see new events coming in the Data Mart integration log. It is not possible to see what it is doing exactly on a certain point in time.
Hi Andre,
The script (loops through all companies to identify null system dimensions) gave me some result.
Honestly, i have no idea to fix it? Deleting it would cause any other problem? Is there anyway to fix it?
Thank you very much
Mark
The screenshot of result is added in URL, please click to my name to see the picture
Hi Mark,
If you have created a question on the Dynamics community, it would be better to continue on the community forum where additional information can be provided. From these blog comments I can see you referred to the community website, but cannot see any details.
Hi Mark,
After installing management reporter CU15 for AX 2012 I am not able to see the AX companies. Refresh companies do not work. What do you reckon is the issue?
Hi RC,
You can try to recreate the datamart database in this scenario.
Hi,
In my MR 2012 cu13 it is reporting following error in DataMart Integration Log :
:System.AggregateException: One or more errors occurred. —> System.Data.SqlClient.SqlException: Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.SqlCommandExtensions.c__DisplayClass1.b__0()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
at Microsoft.Dynamics.Performance.Core.SqlCommandWrapper.RunCommand[TResult](SqlCommand command, Func`1 execute)
at Microsoft.Dynamics.Performance.DDM.SqlUtility.ExecuteSql(String sql)
at System.Threading.Tasks.Parallel.c__DisplayClass17_0`1.b__1()
at System.Threading.Tasks.Task.InnerInvokeWithArg(Task childTask)
at System.Threading.Tasks.Task.c__DisplayClass176_0.b__0(Object )
— End of inner exception stack trace —
at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
at System.Threading.Tasks.Parallel.ForWorker[TLocal](Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Action`1 body, Action`2 bodyWithState, Func`4 bodyWithLocal, Func`1 localInit, Action`1 localFinally)
at System.Threading.Tasks.Parallel.ForEachWorker[TSource,TLocal](IEnumerable`1 source, ParallelOptions parallelOptions, Action`1 body, Action`2 bodyWithState, Action`3 bodyWithStateAndIndex, Func`4 bodyWithStateAndLocal, Func`5 bodyWithEverything, Func`1 localInit, Action`1 localFinally)
at System.Threading.Tasks.Parallel.ForEach[TSource](IEnumerable`1 source, Action`1 body)
at Microsoft.Dynamics.Performance.DDM.SqlUtility.ExecuteSqlInParallel(IEnumerable`1 parallelSql)
at Microsoft.Dynamics.Performance.DDM.FactLoader.ProcessStagedData(String connectionString)
at Microsoft.Dynamics.Integration.Adapters.DimensionalDataMart.DataMartAdapter.RunMaintenance()
at Microsoft.Dynamics.Integration.Service.Tasks.MaintenanceTask.RunImplementation()
—> (Inner Exception #0) System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.SqlCommandExtensions.c__DisplayClass1.b__0()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
at Microsoft.Dynamics.Performance.Core.SqlCommandWrapper.RunCommand[TResult](SqlCommand command, Func`1 execute)
at Microsoft.Dynamics.Performance.DDM.SqlUtility.ExecuteSql(String sql)
at System.Threading.Tasks.Parallel.c__DisplayClass17_0`1.b__1()
at System.Threading.Tasks.Task.InnerInvokeWithArg(Task childTask)
at System.Threading.Tasks.Task.c__DisplayClass176_0.b__0(Object )
ClientConnectionId:d258c278-49fe-48ec-9cfe-51c40ca82dd5
Error Number:1205,State:18,Class:13<—
Hi mazher,
Thanks for reading my blog. I don’t know the purpose why you posted the event log as reply on the blog. If you have a question about this, please create a question on the Microsoft Dynamics community.
Hi,
I have an issue with some transactions that are not appearing in Management Reporter. I am using AX 2012 R3 CU-8 and MR-CU-16. I need to check what transactions (Vouchers) not appearing in Management Reporter. I run the above scripts and in last script, I found some vouchers that belongs to discontinued company. In the Project table, it is showing 2 Project where Name is Empty
Hi Ashan,
The scrips provided are intended to help you finding causes for not correct synchronization of data to the data mart database or wrong figures. You have to fix the data or find someone who can help you fix the data correctly without crippling other transactions.
Andre,
This script is not giving any result therefore raised query
Hi Ashan,
First you mention that the last script gave results in a discontinued company. Now you are stating that the script is not returning anything. You are confusing me now. Possibly you can better create a question on the Dynamics community where you can explain more. On the forum there are more volunteers who might be able to help you.
Hello Ashan,
I periodically receive errors from MR. The 1st error happens once every month or so and I haven’t been able to track down the issue. 2&3 have happened only once.
What’s steps should I take when I receive these errors? The 1st error says to ‘rerun the transaction’. How would I do that?
Is there a list of errors and the action you should take after each one?
1. Transaction (Process ID 98) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2, Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
3. Insert bulk failed due to a schema change of the target table.
Hi Andre,
My Management Reporter was working, but last few days, some transaction is missing, i checked your script, i got table name in the last, when i check those table, i got blank in corresponding rows have null or blank value in column.
Is this a cause of not syncing full data from Microsoft Dynamics into Management Reporter. Please guide me, if it is case what is solution of this problem.
Thankns
Hi Mohammad,
You have to check if all integrations are still running correctly. If not, try to find the culprit. Eventually it would be an option to rebuild the datamart from scratch.
hi experts .
With Management Reporter 2012 CU13, we have also removed the licensing
When i create a report and run generate report i get a output error as below
MANAGEMENT REPORTER
This report view contains no data.Make sure that the report was generated with the following settings:
A valid report date
Valid dimensions and accounts
A valid reporting tree unit
Change your report options or work with your report designer or generator to make sure the report is using the right settings.
What could be the reason for this issue
Thanks in advance
Hi Sukumar,
Like the error details is saying, you need to see if another selection or report design would give results. I cannot help you on the report designer or generation part. If you don’t get data on all reports or as of a certain date, you can use some options provided in the blog to find out if the integration is running correctly or not.
Hello Andre,
I periodically receive errors from MR. The 1st error happens once every month or so and I haven’t been able to track down the issue. 2&3 have happened only once.
What’s steps should I take when I receive these errors? The 1st error says to ‘rerun the transaction’. How would I do that?
Is there a list of errors and the action you should take after each one?
1. Transaction (Process ID 98) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2, Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
3. Insert bulk failed due to a schema change of the target table.
Hi Michael,
If you have deadlocks, you first have to determine if you have other applications using the same MR databases; probably not. In that case, you can update to the latest version and/or contact Microsoft support for assistance. Time-out can be caused by or insufficient hardware resources or a very large query. For this, you have to start monitoring if you can do something yourself or maybe it is also something for Microsoft Support.
Related to the schema change, do you know if a SQL maintenance job was running which e.g. could also adjust the fill factor for indexes?
good morning,
my client have a problem, ax 2012 create double entry in Trialbalance (havecheck thats is double), sudenly she delete each one in tabel and she was run the report MR (management report) and the value diffrent with TB, what have i do? Please i need suggest
Hi Gaya,
If you still have an issue, please create a question on the Dynamics community with some additional information.
Hi Andre.
When i try to re create data mart the process gives me this error. “:Divide by zero error encountered.”..
—
:Divide by zero error encountered.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning transaction.
Committing transaction.
0 dimensions updated with organization id
2023-04-03 16:04:10.7528892
Beginning transaction.
Full balance calculation NOT required.
Full translation NOT required.
Starting [UpdateBalancesForPeriods]
2023-04-03 16:04:10.7808874
Starting [UpdateBalancesForAccount]
2023-04-03 16:04:10.7808874
Starting [UpdateBalancesForScenario]
2023-04-03 16:04:10.7808874
Committing transaction.
Beginning transaction.
Moving staging tables to archive 3 at 2023-04-03 16:04:10.78688
Caution: Changing any part of an object name could break scripts and stored procedures.
Committing transaction.
Beginning transaction.
Caution: Changing any part of an object name could break scripts and stored procedures.
Committing transaction.
Staging tables archive process completed at 2023-04-03 16:04:10.81788
Beginning processing generation 1 of archive 3 at 2023-04-03 16:04:10.81788
Beginning transaction.
Caution: Changing any part of an object name could break scripts and stored procedures.
Caution: Changing any part of an object name could break scripts and stored procedures.
Committing transaction.
Beginning transaction.
insert into [Datamart].Fact with(TABLOCK) (
Id, DimensionsId, PeriodId, FactDate, OriginalAmount,
OriginalUnitOfMeasureId, PrimaryAmount, ScenarioId, IsPosted,
PostingLayerId, FactType, IsDebit, OrganizationId, ShouldBeTombstoned,
AcquisitionDate, SourceKey, [Description], VersionStart, FactTypeQualifierId)
output inserted.Id into #newlyInsertedFactIds
select
f.Id, dc.Id, p.PeriodId, f.FactDate, f.OriginalAmount,
f.OriginalUnitOfMeasureId, f.PrimaryAmount, s.Id, f.IsPosted,
f.PostingLayerId, f.FactType, f.IsDebit, o.Id, f.ShouldBeTombstoned,
f.AcquisitionDate, f.SourceKey, f.[Description], ‘2023-04-03 16:04:10.9408944’,
ftq.Id
from [Datamart].FactProcessing f
inner hash join [Datamart].Organization o on f.OrganizationKey = o.SourceKey
inner hash join [Datamart].DimensionCombination dc on f.DimensionsKey = dc.SourceKey and dc.OrganizationId = o.Id
inner hash join [Datamart].Scenario s on f.ScenarioKey = s.SourceKey and s.OrganizationId = o.Id
inner hash join [Datamart].PeriodsByDate p on CAST(f.FactDate AS date) = p.IncludedDate and p.OrganizationId = o.Id
left join [Datamart].FactTypeQualifier ftq on f.FactTypeQualifierKey = ftq.SourceKey and ftq.OrganizationId = o.Id
where (ftq.Id IS NOT NULL or f.FactTypeQualifierKey IS NULL)
Warning: The join order has been enforced because a local join hint is used.
insert into [Datamart].Fact with(TABLOCK) (
Id, DimensionsId, PeriodId, FactDate, OriginalAmount,
OriginalUnitOfMeasureId, PrimaryAmount, ScenarioId, IsPosted,
PostingLayerId, FactType, IsDebit, OrganizationId, ShouldBeTombstoned,
AcquisitionDate, SourceKey, [Description], VersionStart, FactTypeQualifierId)
output inserted.DimensionsId into #newlyReferencedCombinations (Id)
select
f.Id, dc.Id, p.PeriodId, f.FactDate, f.OriginalAmount,
f.OriginalUnitOfMeasureId, f.PrimaryAmount, s.Id, f.IsPosted,
f.PostingLayerId, f.FactType, f.IsDebit, o.Id, f.ShouldBeTombstoned,
f.AcquisitionDate, f.SourceKey, f.[Description], ‘2023-04-03 16:04:10.9408944’,
ftq.Id
from [Datamart].FactProcessing f
inner hash join [Datamart].Organization o on f.OrganizationKey = o.SourceKey
inner hash join [Datamart].DimensionCombinationUnreferenced dc on f.DimensionsKey = dc.SourceKey and dc.OrganizationId = o.Id
inner hash join [Datamart].Scenario s on f.ScenarioKey = s.SourceKey and s.OrganizationId = o.Id
inner hash join [Datamart].PeriodsByDate p on CAST(f.FactDate AS date) = p.IncludedDate and p.OrganizationId = o.Id
left join [Datamart].FactTypeQualifier ftq on f.FactTypeQualifierKey = ftq.SourceKey and ftq.OrganizationId = o.Id
where (ftq.Id IS NOT NULL or f.FactTypeQualifierKey IS NULL)
and f.Id not in (select Id from #newlyInsertedFactIds)
Hi Martin,
Have you checked with the provided scripts in this blog if there is an issue with your data? If it doesn’t give a possible data inconsistency as result, I would suggest you log an incident at Microsoft Support.