RSMUS.comYou’ve just closed the month or year for one or more of your entities in Dynamics GP and you’re now finding incorrect figures and/or missing information when generating financial reports in Management Reporter. Often, we assume that a rebuild of the Data mart is required, but that is not always the case. There are a variety of causes for the data between GP and Management Reporter to go out of sync. Below are some suggestions for things you can easily check to determine when you need to rebuild the data mart or if the problem is related to something else such as the MR building blocks, or just a timing issue.
1. If any one of the following conditions exist in your ERP system, you will mostly need to rebuild the data mart to correct the issue:
You have damaged data in Dynamics GP
- You restored a database backup or copied live data to a test company.
- You recently used the PSTL Account Modifier/Combiner Tools
- You made changes to Analytical Accounting in MR reporting
- You’ve modified Account Categories or Account Segment Descriptions in GP
- Management Reporter Services have not been running for 3 or more days
- You just completed a Year End Close
- You’ve recently inactivated or deleted one or more accounts.
Always start with verifying the data in GP. To verify the data in GP, use a combination of summary and detail trial balance reports, Smartlists, and Financial Inquiry windows to confirm your account balances. If you determine that there is damage to your data, correct the data prior to rebuilding the data mart.
Note: Sometimes restarting the Management Reporter Services is all that is needed to resolve the issue quickly. Restarting the Services is quick and easy and should always be done first before attempting to rebuild the DataMart.
You can restart the services by going to the server where Management Reporter is installed and opening the Management Reporter Configuration Console. In the Configuration Console window, first click to Restart the Application Services and then Restart the Process Services as seen in the window below.
If restarting the Management Reporter Services does not resolve the issue, you should proceed to rebuild the data mart. Please review the link below for steps to rebuild the DataMart.
Rebuilding Management Reporter 2012 DataMart
Note: Make sure that you are logged on to the MR server with the MR Services Active Directory account OR with an Active Directory account that is setup in MR as an Administrator before you proceed with these steps.
2. Once you’ve ruled out all of the conditions in Item 1, check to make sure the account or accounts in question are setup with the correct posting type of either Balance Sheet or Profit and Loss. Accounts setup with the wrong posting type will not update properly on your financials.
3. Make sure you’re comparing the same period for the reports and windows in GP with reports in MR.
4. Check to see if there is damage to one or more of the building blocks used in your report. Test running the report with and without a reporting tree, creating a new row, column or tree, or reporting definition, running other similar reports, or switching the report definition to use other rows, columns, or trees. If the data appears correctly on another report in MR, the problem is with one or more of the building blocks in MR.
5. Print the report using the Legacy Provider. If the Legacy report is incorrect, the problem is not with the DataMart but more than likely with damaged data in GP.
6. If you’re using Multicurrency in Management Reporter, check to make sure you have both Functional and Reporting currencies setup for each company. (Tools > Setup > Financial > Multicurrency) Also, check to make sure any currencies you have setup are assigned the appropriate ISO code. (Tools > Setup > System > Currency). If any of these fields are not populated in GP, it can cause problems with your MR reporting.
7. If you’re running consolidated reports, are the names of your GL Account Segments consistent across all companies?
Some other points to consider when experiencing data inconsistencies between GP and MR and delays in refreshing the DataMart:
- What is the normal and expected refresh/sync time between GP and the DataMart?
Management Reporter uses the Fact task that runs every 60 seconds to search for new transaction information. The transaction information is then added to temporary staging tables in the data mart where the values are confirmed before moving them to the “finished” tables. The next task is the Maintenance task which runs every 60 seconds. This task validates the values in the staging tables and if the values are correct, moves them to the “finished” tables where they are read by the Report Designer. All other MR tasks run on a 5 minute interval. A transaction may remain in the staging tables until the other required information is retrieved for it, such as a brand new account number which has never been used before. Please refer to the articles below for more detailed information on the process. Although the articles reference AX, they also apply to GP and SL.
Management Reporter AX 2012 Integration Overview
Troubleshooting Management Reporter Data mart Staging Tables
- At what point should we be concerned that balances are not updating correctly?
Assuming your system is not experiencing any performance issues that would delay the required tasks, new transaction details should be available in Management Reporter within approximately 2 minutes from the time the change was made in GP. For transactions on new accounts that have no prior usage, it may take up to 5 minutes to run the account task and then another few minutes for the transactions to be validated. Transactions for new accounts will be validated a second time since the first validation will fail if the account has not yet been added to MR. Refer to the “Management Reporter AX 2012 Integration Overview” blog for the retry attempt table.
- What should we look for in the DataMart Integration logs regarding delays or an issue with a particular account?
The data mart integration logs do not contain status messages for individual or ranges of accounts. You can use the email notification setup option in the MR Configuration Console to ensure that any errors with the data mart are sent to the appropriate resource so they can be addressed. See setup options below:
Review the data mart integration log to verify the tasks are running and completing within a reasonable timeframe.
Each task should generally complete in a minute or less for normal updates. Depending on the volume of data that was recently added to GP and overall server performance, there could be some delays in processing the tasks. Once the bulk of the new data has been validated and synced with MR, processing times should return to the usual and customary wait time.
You should also review the Data mart Integration logs for any specific errors related to issues such as permissions, tables, stored procedures, disk space, and so forth.
More information about errors can also be found in the Event Viewer. Go to the MR server, and open the Control Panel > Administrative Tools > Event Viewer > Windows Logs > Application.
- Besides checking the Data mart Integration logs and the Event Logs, how can we determine if it’s just a timing issue or if the DM needs to be rebuilt?
If none of these troubleshooting suggestions resolve the issue or you are concerned that the items are not updating correctly in the data mart, check the version of Management Reporter you’re running. The latest release is Management Reporter 2012 CU15 which contains many fixes and performance enhancements.
There is currently no simple way to determine the status of the processing that is happening in the data mart. The next step would be to investigate the sql tables for more information. If you are experiencing inconsistencies with your Microsoft Dynamics GP data and Management Reporter reports, RSM offers access to Certified Microsoft Professionals, help desk and phone support, knowledge and experience with third party products and dedicated account management. Please contact our professionals for more information at erp@rsmus.com or by phone at 855.437.7201
by Nancy Hogan for RSM
