Financial Reporting – Reset The Data Mart

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"

Financial Reporting Reset Data Mart PowerShell

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!

Financial Reporting Reset Data Mart Monitoring SQL Server Management Studio

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!

6 thoughts on “Financial Reporting – Reset The Data Mart”

  1. 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!

  2. 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

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.