Share via

Resetting the Management Reporter data mart

  • Article

There have been a few questions around troubleshooting the data mart and how to rebuild or reset the data mart when required. In this post, We'd like to clarify when resetting the data mart is required, and hopefully clear up some misunderstandings around the process.

We’ve been hearing, pretty consistently, that customers are rebuilding the data mart on a frequent basis. This shouldn't be the case. There are specific scenarios where a rebuild is required (we’ll talk about them in a minute), but generally speaking, rebuilding the data mart shouldn't be required in most scenarios. In fact, rebuilding the data mart without talking with our support team means if there was an issue, we can’t fix it to make sure it doesn’t happen again.

So, when do you need to rebuild the data mart?
You will need to rebuild the data mart if:

• Your Microsoft Dynamics ERP database is restored from a backup
In this situation, the data mart will have transactions that no longer exist in the ERP database and may report incorrect balances.

• Copying your production ERP and MR databases into a test environment
In this situation, the integration configured for production is hard-coded into the Management Reporter database. If the databases are restored elsewhere, the connections will still be pointing to the test environment. Instead of copying the MR databases into test, you can copy over any reports via a .tdbx export, and can reset the data mart for up-to-date numbers.

• Microsoft Dynamics GP - Activating or De-activating Analytical Accounting for reporting in MR
If Management Reporter needs to change the type of integration, a reset is required to stop looking at the GL tables and start looking at the AA tables

• Microsoft Dynamics GP - Account Category change
If an account is moved to a different account category, the account category change won't occur until a transaction is processed for that account combination. It could be an actual or budget transaction, but all related combinations would need to see an update.

• Microsoft Dynamics GP - Account Description change
If an account description is changed, the new description will not be available until the data mart is reset. The descriptions entered into the row definitions for the account will require a manual update for changes to be reflected.

• Microsoft Dynamics GP - PSTL Account tools
If any tools are run to change segments or accounts, a reset may be required.

Note: I will add more information here once we have additional clarification

 

Please feel free to leave comments on what you're seeing, but the preferred course of action would be to update to the latest version of Management Reporter, and submit a support ticket to review what issues you are seeing. I will be moderating comments to make sure the discussion stays on track, and to ensure correct and validated information is being shared.

If additional issues are found, we'd love to fix those issues. There have been several fixes to issues that previously required a data mart reset to fix. If you are told to rebuild the data mart in the future, please consider this post for additional guidance.

 

How do I rebuild the data mart (CU15+)?
1. Before starting, make a backup of the ManagementReporter and data mart (ManagementReporterDM) databases before starting. Backups can be helpful in the case of diagnosing why the issue occurred after the rebuild occurs.

This process will require a SQL authenticated user (not Windows login user in SQL) and dbo rights to the ManagementReporter and ManagementReporter DM databases.  This process will delete all of data in the data mart tables and remove the integration logs, remove the data mart connection information, and fix the dates for previous generated reports to be able to properly include transaction detail with the ManagementReporter database.

2. Log onto the machine where the MR Server components are installed and open Powershell as Administrator

3. Navigate to the Console folder

PS C:\> cd '.\Program Files\Microsoft Dynamics ERP\Management Reporter\2.1\Server\Console\'

4. Import the module
PS C:\Program Files\Microsoft Dynamics ERP\Management Reporter\2.1\Server\Console> Import-Module .\Microsoft.Dynamics.Performance.Deployment.Commands.Integration.dll
Note: You can get additional help for each of the parameters by running Get-Help Reset-DatamartIntegration -Full

5. Run the below command. You can run get-help Reset-DatamartIntegration for more information. If credentials aren't supplied, you will be prompted
PS C:\Program Files\Microsoft Dynamics ERP\Management Reporter\2.1\Server\Console> Reset-DatamartIntegration -Reason BADDATA -ReasonDetail "Restored database from backup" -DatamartDatabaseServer sqlservername -DatamartDatabaseName ManagementReporterDM -DatamartDatabaseUserName sa -DatamartDatabaseUserPassword (ConvertTo-SecureString Thisisapass@word -AsPlainText -Force) -MRDatabaseServer sqlservername -MRDatabaseName ManagementReporter -MRDatabaseUserName sa -MRDatabaseUserPassword (ConvertTo-SecureString Thisisapass@word -AsPlainText -Force)

6. Agree to the below confirmation. It is recommended the process service is stopped, but not required in most cases.
WARNING: Resetting the financial reporting data mart will delete all data in the data mart as well as any supporting data in the financial reporting and ERP databases. The Management Reporter 2012 Process Service must be stopped on all machines that it is installed on before continuing.

Note: For Microsoft Dynamics AX 2012 customers, GP, and SL customers, no ERP data will be touched during this process. Only data within the data mart and ManagementReporter databases are impacted.

Confirm
Are you sure you want to perform this action?
Performing operation "Reset-DatamartIntegration" on target databases.
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "Y"): Y

When the process starts, the Configuration Console will display a message that the initial integration is in progress. Once complete, you will see the message change to initial integration is complete.

You can review the log of previous reset activity in the Event Viewer with the following steps:
1. Open the Event Viewer
2. Expand Applications and Services Logs
3. Expand Microsoft
4. Expand Dynamics
5. Expand MR-Logger
6. Select Operational

The log will contain the date/time, username who requested the reset, and the reason details entered.

Comments

  • Anonymous
    June 28, 2016
    The comment has been removed
    • Anonymous
      June 30, 2016
      Good to hear that new versions do well with upgrading. We are still on GP2010. So, my experience is little behind.
  • Anonymous
    June 28, 2016
    [Edit from Ryan] - Ravi points out you can reset the data mart from the Config Console rather than PowerShell. The three major downsides here is that it requires deleting the database first, and that existing MR reports with transaction detail will no longer include transaction detail. Lastly, no audit trail is logged that a data mart reset was performed. The PS script resolves these specific issues. Hi,Resetting Data mart from configuration Console is a bit easier, if you are not much comfort with PowerShell. You can follow below mentioned steps to recreate/refresh datamartNote : Before starting data mart recreation, take backup of MR and MRDM databases.1. First, stop the process service from MR Configuration console (or) services.msc 2. Use configuration console to remove the existing AX integration3. Start the process service in Configuration console4. Delete the DataMart database from SQL Management Studio (ManagementReporterDM) 5. Use the wizard in configuration console (File menu) to configure the AX integration again (File Configure Select ‘ Add Microsoft Dynamics AX 2012 Data Mart’)6. Provide required details in Configuration console form7. Once Ax Integration created, enable integration.Thanks.Ravi Theja Madisettyhttps://techguns.wordpress.com
    • Anonymous
      June 30, 2016
      Ryan,Any chance that the config console will be updated with the UI to do a proper data mart reset to match the capability of the PowerShell script?
  • Anonymous
    June 29, 2016
    Hey Ryan. Thanks for this post. I agree with the bolded statement above - that shouldn't be the case. It's near impossible to sell someone on the benefits of a financial statement reporting tool if I can't even guarantee the numbers are consistently right each month. I've had experience with customers on every version of MR since it replaced FRx. Ever since the legacy connector was ditched in favor of the data mart, I've seen discrepecies with the data. Customer after customer has needed their database reset. Missing transactions, duplicate transactions, budget lines not showing up, and various other issues have popped up.I also agree with the need to report these issues to the product team so that they can be resolved quickly. And I must say, your team is by far the most accessible and responsive team that I've gotten to deal with at Microsoft. I also must say, though, that a number of times I've seen issues, we've reported them, they were resolved, but then very similar behaviors have popped up in the next release. After reaching out to resolve the new issues, we've been instructed that it's being caused by some new issue.All in all, I keep recommending MR to my customers. To be honest, though, it gets harder and harder each time I see them run into data consistency problems and have to suggest an upgrade to the newest version to resolve it. Thanks for listening!
  • Anonymous
    September 08, 2016
    Hi, Is there a way to fix MR if its been down more then three days without resetting the datamart? That is a quite common scenario that MR is down for a couple of days before anyone notice and because of change tracking we have to reset the datamart.[Ryan edit] - Yes, as of recent CUs, MR should auto-recover in this case and re-integrate all data. When integrations start again it should detect this and kick off a full rebuild on its own. Let us know if you don't see this happening, but it was recently verified.
    • Anonymous
      September 16, 2016
      Perfect, then we have to upgrade to latest CU.From what version is that fixed?