One of the joys of using the Data Mart integration with Dynamics GP is that the Data Mart database occasionally needs to be rebuilt. Our helpdesk crew have done a sterling job of taking on client’s Data Mart rebuilds when the GP and MR reports have trouble aligning. However, our consultants have put together a process using PowerShell and SQL Server Agent to make the rebuild process even simpler.
The generic PowerShell script has three lines, as below, with the variables highlighted in blod and italics. The script assumes both the sa login and the Data Mart database name are ManagementReporterDM.
cd ‘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 saPASSWORD -AsPlainText -Force) -MRDatabaseServer SQLSERVERNAME -MRDatabaseName ManagementReporter -MRDatabaseUserName sa -MRDatabaseUserPassword (ConvertTo-SecureString saPASSWORD -AsPlainText -Force) -ErrorAction SilentlyContinue -Confirm:$false
First, test the script in PowerShell (run as admin) and confirm the process runs: check the task manager for the MR and SQL services becoming busy after the script completes. Also, the configuration console will have many lines in the Data Mart integration log with the current time.
Create a SQL Agent job, and configure the owner as sa.
Select PowerShell as the Type, and paste the text from your PowerShell script above.
Save and test.