The release of Management Reporter 2012 CU15 has delivered a new way to reset (rebuild) the Management Reporter 2012 data mart using Powershell.
Please refer to the steps below as a guideline on how to leverage this new data mart reset process:
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 user with dbo rights to the ManagementReporter and ManagementReporter DM databases. The Powershell script is only supported using SQL Authentication.
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 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 no ERP data will be touched during this process. Only data within the data mart and ManagementReporter databases are impacted.
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.