Management Reporter DataMart Rebuild
In this post we will go over a basic Management Reporter Data Mart rebuild for Microsoft Dynamics GP (GP). In my experience a Data Mart (DM) rebuild is a quick way to solve many problems experienced with Management Reporter (MR) and is often my first step. Officially, Microsoft does not recommend a DM rebuild as the first step in the troubleshooting/support process for Management Reporter.
For some simple background information, the DM sits between GP and MR. The DM takes the financial information in GP and organizes it in a way that optimizes report running and prevents frequent calls to the GP databases and tables. The DM requires change tracking to be configured for the DYNAMICS database and all GP company databases that want to be reported. The DM watches for changes to the financial tables and updates itself when it sees changes are made. In some cases the DM can get out of sync with the GP databases, these cases will require a DM rebuild. As an example, a client ran out of space on their SQL server which was preventing anything to write to the DM database. Since nothing was writing to the DM it became out of sync with the GP databases. Once more space was added the DM began pulling in new transactions but any transactions that were entered while the drive was full were missing, you could actually pinpoint the exact times the drive was out of space based on the missing transactions. Once we rebuild the DM everything was back in sync and the reports were once again accurate.
The below walkthrough may not be the best way to do a rebuild but it is how I do it based on years of experience. Follow these instructions at your own risk, I do not hold any responsibility for any damage done to your GP/MR/SQL environment if you follow the below steps.
Step 1- Open the Management Reporter Configuration Console, go to the server integration, click 'Disable Integration'
Step 2 - Click 'Remove' in the top right corner then confirm the removal by selecting 'Yes'
Step 3 - Go to the 'Management Reporter Services' in the top left corner then select Stop for both services.
Step 4 - Open SQL Server Management Studio and delete the datamart database. In the below screenshot the database is named ManagementReporterDM
Step 5 - Return to the Management Reporter Configuration Console and selecting 'Start' for both services.
Step 6 - Select 'File' in the top left corner then select 'Configure'
Step 7 - Select 'Add Microsoft Dynamics GP Data Mart' then select 'Next'
Step 8 - Select 'Configure' to check the server configuration
Step 9 - Once the configuration is done you will be taken to the 'Settings' window. Under Dynamics GP connection Information in 'Database server:' enter the SQL server name, in 'User name:' enter a user to run the queries with, in 'Password:' enter the users password. In the screenshot I am using sa to keep it simple but you can use any GP account as long as it has access to the companies you want to report on. Under 'Database:' click the drop down and select the 'DYNAMICS' database. Under 'Data mart connection information' in 'Database server:' enter the SQL server name, in 'User name:' enter an account with the sysadmin role, in 'Password:' enter the SQL account password. In the below screenshot I am using the sa account but you can use any SQL account with sysadmin. Click 'Next' when done.
Step 10 - After clicking 'Next' you will be taken back to the main Configuration Console window. In the left column go to the ERP Integrations drop down and select your SQL server connection. In the integration screen select 'Enable Integration' this will start pulling the GL data into the DM and may take minutes or hours depending on the number of companies, the size of the companies, the speed of the server, and many other factors. When the integration is complete the status will read "Initial integration is completed. Period integrations will occur' Run some reports in Report Designer to ensure the numbers are now correct.