How to Rebuild the Management Reporter DataMart
Apr 10, 2019
By Kristin Grosz
In Microsoft Dynamics GP, the Management Reporter tool relies on the DataMart to create financial reports. The DataMart is its own, scaled-down database. It aggregates data from GP so that your report queries run faster, and with fewer complications, than if you were querying the live company database.
On occasion, however, certain changes to your Dynamics database could create problems and require you to reset the DataMart. This might happen if you restore a GP database from a backup, Management Reporter services are stopped for a few days, segment names change or you discover bad data/a data corruption issue.
It’s an issue that you can address with a backup, reset and reconfigure process, but there are some key things to look out for. With that in mind, we’ve put together step-by-step directions to recreate the DataMart.
Your Step-by-Step Directions
Note: Make sure all users are out of Management Reporter.
Back up the Management Reporter database.
1. Log in to Microsoft SQL Server Management Studio (you must have credentials that allow you access, typically the ‘sa’ user).
2. Open the Databases dropdown and right click on ManagementReporter. Then go to Tasks>Backup
3. Choose the path you wish to save the back up to, add a .bak extension to the file name and then click OK.
Open the Configuration Console
1. Click on the integration under ERP Integrations (note that the name of your integration will not be the same as you see in the screen shot below, as each company is unique).
2. Click the Disable Integration button. This may take a minute to process.
3. Click the Remove link at the top-right to remove the integration.
4. Stop the MR services.
4. Go back into SQL and delete the ManagementReporterDM database.
IMPORTANT: There are two databases in SQL, ManagementReporter and ManagementReporterDM.
DO NOT DELETE the ManagementReporter database. These two databases are right next to each other in the list. Double check that you are deleting the correct one.
5. Right click on the ManagementReporterDM database and select Delete.
6. Remove checkmark from “Delete backup and restore history information for databases” and “Close existing connections.” Then hit OK.
7. Go back to the Configuration Console, and start the MR services. Then click File>Configure
8. Select Add Microsoft Dynamics GP Data Mart. Select Next.
9. Enter the ‘sa’ user name and password. Choose DYNAMICS database for the top portion. Enter the same credentials on the bottom and make sure the database is ManagementReporterDM.
10. Click Configure. This may take a few minutes. When this step is done, click Close.
11. You will be brought back to the main page that shows the ERP Integrations.
12. Click on your Integration, and then click on Reconfigure Change Tracking on the right side of the screen. Enter the ‘sa’ credentials if prompted.
13. Click on Enable Integration. This integration can take anywhere from 5-10 minutes, up to a couple of hours.
Once this integration is complete, you have successfully rebuilt your DataMart!
Need More Help?
We work with organizations across the country to customize, implement and support their financial and operational solutions. It’s our goal to help clients be more productive, get better information and improve business performance using technology and improved business processes. Contact us for support services or a free consultation.