Blog Posts Process Management

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.

Job Properties - Reset Datamart

Select PowerShell as the Type, and paste the text from your PowerShell script above.

Job Step Properties - Shell Script

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

Get the BPI Web Feed

Using the HTML code below, you can display this Business Process Incubator page content with the current filter and sorting inside your web site for FREE.

Copy/Paste this code in your website html code:

<iframe src="" frameborder="0" scrolling="auto" width="100%" height="700">

Customizing your BPI Web Feed

You can click on the Get the BPI Web Feed link on any of our page to create the best possible feed for your site. Here are a few tips to customize your BPI Web Feed.

Customizing the Content Filter
On any page, you can add filter criteria using the MORE FILTERS interface:

Customizing the Content Filter

Customizing the Content Sorting
Clicking on the sorting options will also change the way your BPI Web Feed will be ordered on your site:

Get the BPI Web Feed

Some integration examples