Configure SQL Server Agent to Rebuild Management Reporter Data Mart
Blog: Professional advantage - BPM blog
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 FilesMicrosoft Dynamics ERPManagement Reporter2.1ServerConsole’
Import-Module .Microsoft.Dynamics.Performance.Deployment.Commands.Integration.dll
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.
- If you would like to know more, complete the form below and one of our experts will contact you.
-
-
We would like to send you occasional news and relevant marketing communications. To confirm you would like to receive these communications please check the box below. You can unsubscribe at any time.
- Your information will never be shared or sold to a 3rd party. Please see our privacy policy to learn more about how we use your data.
The post Configure SQL Server Agent to Rebuild Management Reporter Data Mart appeared first on Enterprise Software Blog – Professional Advantage.
Leave a Comment
You must be logged in to post a comment.
