After I was able to fix the Management Reporter 2012 Process Service the next step was to reset the data mart. In some certain cases you need to do that to make Financial Reporting deliver the correct data again – one of them and probably the most likely is when you restored a database backup. For some that might be obvious because Management Reporter integrates data incrementally.
I was looking forward to Update 1 (aka the May Update) because in the list of new things that come with it there was the possibility to reset the data mart. Also I found a blog entry, Resetting the Management Reporter data mart, which is quite new but doesn’t relate to the new Microsoft Dynamics AX. Still it indicates the direction to be followed. There are lots of parameters and if you want to provide them you might find that something must have changed. In AX 2012 usually there were two databases. One for the setup data and one for transactions and stuff. In the newest version there’s only one database. So what to do? It’s easier than I thought!
Stop the Management Reporter 2012 Process Service
Go to the Windows Services and stop the Management Reporter 2012 Process Service first.
Use PowerShell to reset the data mart
If you use a single-machine (and right now actually all of them are) you can use the PowerShell command with reduced parameters. Thankfully the first two are enough, everything else is read from the configuration. So (in case of a downloaded or on-prem .vhd – the .dll’s path might be different on Azure-deployed virtual machines) the two commands you need to execute using PowerShell (run as administrator) are:
Import-Module 'C:\FinancialReporting\Server\Console\Microsoft.Dynamics.Performance.Deployment.Commands.Integration.dll';
Reset-DatamartIntegration -Reason BADDATA -ReasonDetail "Restored database from backup"
After the reset you’ll find the provided reason and its detail message in the event log. Navigate to Applications and Services Logs > Microsoft > Dynamics > MR-Logger with the Event Viewer application to see it.
Start the service again
Now you want to start the Management Reporter 2012 Process Service again and let it do its work.
Monitor progress
Depending on the size of your data it might take some time for the process to finish the initial filling of the data mart. If you want to see some status you can use SQL Server Management Studio and query the view Connector.MapsWithDetail of the ManagementReporter database. When all entries in column LastRunTime have a value the intial integration should be finished and Financial Reporting available from AX!
Special thanks go to the FR team at Microsoft who helped me a lot with making this work for the first time on my dev box!
Hello, Thanks for sharing this piece of blog. I have a question, how can we execute this in the Production as we don’t have access to the machines. Should we ask MS to do these steps?
Best regards
Yes, for a production environment you need to open a service request in LCS that goes to the DSEs. Use type “Other” and tell them to reset the data mart. Of course this will take some time to bring the data for financial reporting back to life (depending on how many transactions you have etc.) and delete all customizations and custom reports. Always back them up first!
Hello, Thanks for sharing this piece of blog. When I am trying to do the same steps described as above, I am getting the error as “Reset-DatamartIntegration : Conversion failed when converting from a character string to uniqueidentifier.
At line:1 char:1
+ Reset-DatamartIntegration -Reason OTHER -ReasonDetail “Test reset”
+ CategoryInfo : NotSpecified: (:) [Reset-DatamartIntegration], SqlException
+ FullyQualifiedErrorId : System.Data.SqlClient.SqlException,Microsoft.Dynamics.Performance.Deployment.Commands.In
tegration.ResetDatamartIntegration
Hi Ram,
I’ve seen that before and the solution was to provide all the possible parameters to the PowerShell command (you’ll need to visit the code to look that up). Since the way of resetting the data mart via PowerShell relates to older versions of Financial Reporting I would recommend you to upgrade FR to a new version instead. You can update it independently from Dynamics 365 for Finance and Operations and the newer versions give you the ability to trigger and perform the data mart reset from the Report Designer UI!
Best regards,
Volker
Thanks Volker, for your response.
I am using the Financial report designer version 8 which is downloaded using D365FO platform update 15 (one box environment). When I try to reset the data mart from Report designer, the Report designer crashes and the below message was caught in event viewer.
Resetting the datamart tables.
Machine: XXXXXX
Certificate lookup is using handler of type Microsoft. Dynamics. AX. Configuration. CertificateHandler. LocalStoreCertificateHandler, Microsoft.Dynamics.AX.Configuration.Base, Version=7.0.0.0, Culture=neutral, PublicKeyToken=XXXXXXXXXXXXXXXX
Machine: XXXXX
{“Class”:16,”Number”:8169,”State”:2,”Message”:”Conversion failed when converting from a character string to uniqueidentifier.”,”ErrorCode”:-2146232060,”Procedure”:””,”LineNumber”:2,”Errors”:[{“Class”:16,”Number”:8169,”State”:2,”Message”:”Conversion failed when converting from a character string to uniqueidentifier.”,”Procedure”:””,”LineNumber”:2,”Source”:”.Net SqlClient Data Provider”}],”ConnectionId”:”XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX”,”Source”:”.Net SqlClient Data Provider”}
I have tried both the options using Power-shell and Report designer. Both are result in same error.
Can you please suggest how to resolve this?
Thanks,
Ram
Ugh, downloaded virtual machine… I am not sure if Financial Reporting is supported there actually. Did you check if there are expired certificates? I’ve written about issues with certificates in local machines before: Fix Certificate Issues on Development Machines. Maybe that’s worth checking. Otherwise I’d suggest you to contact Microsoft Support.