How to Rebuild DataMart (DDM) for Management Reporter 2012 in Microsoft Dynamics ERP
Every now and then, you may find that the reports generated from Management Reporter with DataMart sources are missing amounts from an entire period, or they are simply not up to date. To refresh all data in DataMart, you can simply rebuild the DataMart Integration using Management Reporter Configuration Console.
Checklist before you proceed to rebuild DataMart:
- To rebuild DataMart, you will first need to check what credentials are being used for Microsoft Dynamicsand DataMart connections. These credentials will be used to connect to DYNAMICS and DataMart databases. Unless you have specific user setup for these connections, you can use GP and SQLs sa user. Take note of these credentials as we will need them when recreating the Data Mart.
- Make sure you have admin rights to Management Reporter server i.e. where Management Reporters services are hosted. When in doubt, have your domain administrator perform these tasks.
- You will also need sufficient access rights on SQL to backup and delete a database via SQL Server Management Studio.
Note: Depending on the size of your databases and the number of Microsoft Dynamics GP companies you have, it may take up to several hours for DataMart to complete its initial refresh after a rebuild. Please allow ample time before generating reports. You can check the progress in Configuration Console >> ERP Integrations >> DataMart integration. When completed, you should see Last time everything was fully integrated: <recent timestamp>.
Once all the items in the above list are sorted out, follow the below instructions to rebuild DataMart.
- Run Management Reporter Configuration Console as an administrator. (Right click >> Run as administrator)
- Select the Microsoft Dynamics GP to DDM integration under ERP integrations. The give-away here is you should see an option to Update DataMart credentials on the screen.
- Click the Disable Integration button.
- Click Remove in the upper-right corner to get rid of the integration.
- Close the Configuration Console.
- Open SQL Server Management Studio and login as sa.
- Double-check and make sure that no one is using the DDM database.
- Backup the DDM database.
- Take note of the DDM databases name and delete the DDM database (Note: You must delete the database. Renaming the database may cause errors).
- Run the Configuration Console as an administrator again (Same as the second step).
- Stop the Management Reporter 2012 Process Service.
- Stop the Management Reporter 2012 Application Service.
- Start the Management Reporter 2012 Application Service.
- Start the Management Reporter 2012 Process Service.
- Go to File >> Configure
- Choose “Add Microsoft Dynamics GP DataMart” and click Next.
- Enter your GP database server name and the credentials DataMart will be using to connect to GP databases. Select DYNAMICS from the Database drop-down menu.
- Enter your Management Reporter database server name and the credentials DataMart will be using to connect to the DataMart database. Type the DataMart database name in the Database text field. This should be the same name as the one you deleted in earlierstepabove.
- Click Next, then click Configure.
- When finished, be sure to click the “Enable Integration” button in the Configuration Console if it was not automatically enabled.
** Information provided in this article is for reference purposes only and the decision to implement it solely yours, UXC Eclipse accepts no responsibility if you implement the following suggested procedure. Please beware that the information is provided as is and has been neither tested nor verified in your environment. **