Microsoft’s Management Reporter makes use of a Data Mart to store general ledger data in a structure that is more efficient for financial reporting. On large datasets, this approach makes Management Reporter considerably faster than FRx.
Because this Data Mart is simply a copy of existing data, it is possible for it to get out of sync with the original data. When this happens, it is necessary to rebuild the Management Reporter Data Mart.
Fortunately, this is not a particularly common problem with later versions of Management Reporter. For earlier versions, the steps are simple provided you are prepared for the process.
In this article, you’ll learn a step-by-step process for rebuilding the Management Reporter Data Mart.
THINGS NEEDED BEFORE GETTING STARTED
Generally speaking, a technical resource performs these steps. Administrative access and an understanding of SQL Server are required. Here is a specific list of the requirements:
- Access to the server where Management Reporter is installed
- Dynamics GP username and password for Management Reporter
- Administrative SQL Login to the SQL Server where the Management Reporter Data Mart is stored to enable change tracking
- Domain User with Administrative Privileges to Management Reporter
- Administrative access to SQL Server where the Management Reporter Data Mart is stored to drop the existing Data Mart database
Did You Know?
Management Reporter Cumulative Updates are worth installing. Each patch fixes significant issues, including multiple scenarios that previously required rebuilding the Data Mart.
Although Management Reporter can have various components installed on separate servers, it is not very common. Therefore, for simplicity’s sake, this article will assume that everything related to Management Reporter and Dynamics GP is on a single server.
STEP 1: Open the Management Reporter Configuration Console
The first step is to open the Management Reporter Configuration Console. This can vary based on the operating system, but in general, it can be found in the Start Menu under Microsoft Dynamics:
If you do not have permission to connect to Management Reporter, it will be necessary to log in as a user that does.
STEP 2: Remove the Existing ERP Integration
Within the Configuration Console, select the ERP Integration, and click Remove:
From the Management Reporter Configuration Console, select the current ERP Integration and click Remove.
Step 3: Stop the Management Reporter Services
Use the Configuration Console to stop both Management Reporter Services:
From the Configuration Console, select Stop on both Management Reporter Services.
It will take a few seconds to stop each service. Wait patiently for both to stop.
Step 4: Delete the Data Mart Database
The next step involves deleting the ManagementReporterDM database. If you are unfamiliar with, or are uncomfortable with deleting a database, please contact your support resource for SQL Server or Dynamics GP before continuing. Permanent data loss can occur if the wrong database is deleted.
Using the Microsoft SQL Server Management Studio, find the ManagementReporterDM database, right-click, and choose Delete:
Begin the process of deleting the ManagementReporterDM database in Microsoft SQL Server Management Studio.
On the “Delete Object” window, triple-check that you have the correct database selected. It is easy to delete the other ManagementReporter database unintentionally, resulting in permanent data loss. Additionally, check the box for “Close existing connections” to force SQL Server to delete the database. This option bypasses all built-in checks provided by SQL Server to prevent data loss. Use at your own risk.
After triple-checking that you are deleting the “DM” database, complete the process of deleting the ManagementReporterDM database. Use the “Close existing connections” option to force SQL Server to delete the database even if it is still open.
Step 5: Start the Management Reporter Services
We can now restart the Management Reporter Services and begin the process of rebuilding the Data Mart. It will take a few seconds to start each service; wait patiently for both services to start successfully.
Return to the Configuration Console and Start both Management Reporter Services.
Step 6: Rebuild the ERP Integration
Using the Configuration Console, create a new Integration to Dynamics GP using the Data Mart option.
Click on File→ Configure to open the “Configure Management Reporter” window:
To add the new ERP integration, choose “Configure” under the “File” menu.
In the “Configure Management Reporter” window, check the box for Add Microsoft Dynamics GP Data Mart, and click Next:
Select the checkbox to Add Microsoft Dynamics GP Data Mart and then click Next to continue:
If you receive a warning message about missing software, click Next to continue:
Click Next to safely skip the “Prerequisite Validation Error Message” shown.
Step 7: Setup Data Mart Configuration Settings
The “Settings” window requires a lot of detailed information. The upper section is asking the user how they want Management Reporter to connect to Dynamics GP, and the lower section is asking how to connect to the new Data Mart database that is being added.
The Dynamics GP connection should be done with a dedicated account in Dynamics GP with minimal security. This is generally a user called “MRUser” or similar that has access to all companies, but does not have any roles assigned. While not being recommended, it is also possible to use ‘sa’ in this context. The ‘sa’ user should be reserved for the handful of tasks that absolutely require it.
Complete the required fields on the “Settings” window including the username and password.
Click Next to continue to the next screen. Then click Configure to create the Integration:
Click Configure to continue.
This process takes several minutes, and is dependent on the performance of the SQL Server and the number of companies to process.
Click Close on the final confirmation window:
Click Close on the Configuration Summary window.
Step 8: Reconfigure Change Tracking
The data synchronization process makes use of a SQL Server technology called Change Tracking. We recommend resetting Change Tracking during the rebuild to ensure data consistency.
From the Configuration Console, select Reconfigure Change Tracking and enter the appropriate credentials. We recommend using ‘sa’ for this step, as it is typically the only login with sufficient permissions.
Select Reconfigure change tracking to ensure future data consistency.
Step 9: Enable the Integration
The final step is to Enable the Integration and monitor the progress. From the Configuration Console, select Enable Integration and wait for the “Integration Complete” message to be displayed. The time to rebuild is dependent on multiple factors; it can take a few minutes or a few hours.
Click Enalble Integration as the final step to synchronize the data.
When the Initial Integration is complete, run a sample report to confirm proper operation.
Confirm that the “Initial integration is complete” message appears begore running any reports.
Wrap Up
Congratulations if you have made it this far. With practice, rebuilding a Data Mart is straightforward, but the first few times are daunting.
If you find that you need to rebuild very often, please contact your support resource. With the newest cumulative updates, Management Reporter is very stable and does not get out of sync with Dynamics GP very often.
If you are struggling with Management Reporter or the Data Mart Integration, please Contact Us here at Telmar. We have extensive experience with Management Reporter and all other aspects of Dynamics GP, and we would love to help you get the most out of your software.
NEED HELP MAKING YOUR DYNAMICS GP ENVIRONMENT SIMPLER TO MANAGE?
Learn how the Telmar Team can help!
Telmar Computer Solutions, Inc. is providing the information in this publication as a courtesy, free of charge, to share information with the general public. Nothing in this publication is intended to be, nor should be, interpreted as advice or consultation on any matter, including taxes, accounting, business management, system configuration or operation, or any other similar topic. Please engage the services of an appropriate professional for assistance in these areas. Telmar Computer Solutions, Inc. is not responsible for any loss, including downtime, lost files/data, rework, or other expenses sustained by you, your company, or any person who relies on this publication.
Other Posts You May Be Interested In…
How to See Who is Logged into Dynamics GP
As you may know, Dynamics GP licensing is based on concurrent user. For example, if you have five user licenses, only five users can be logged in at a time. Because of this, you may have received the "You reached the maximum number of users logged into the system" message. Trying to figure out who is logged in can be frustrating and time-consuming. Also, from time to time, a user’s session can become "stranded" due to power outages, frozen workstations, or being disconnected from the network.
MAPI, SIMPLE MAPI AND OUTLOOK SECURITY
A Client recently deployed a new RDS Server farm to run a variety of applications, including Dynamics GP. About a week after going live the client ran a monthly process that involves generating emails from an add-on software package intended to simplify AR collections. Unfortunately, on the new server, when the user triggered the email, the following prompt showed up: "A program is trying to send an email message on your behalf. If this is unexpected, click Deny and verify your antivirus software is up-to-date."
A Guide to Successful Project Management
A simple guide to successful project management including a preparation checklist, what to look for in a project manager and tips for minimizing risk.
0 Comments