Resetting the Management Reporter data mart
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.