Rebuilding the Management Reporter Data Mart
Microsoft Dynamics Management Reporter 2012 (MR) has matured very rapidly from its original RTM release. It has gone from a weak replacement for FRx to the essential reporting platform for MS Dynamics. The feature set is getting richer and richer with every quarterly release and performance has become very stable and reliable.
Why the Data Mart?
In order to enjoy the latest greatest features of MR, The Dynamics Data Mart (DDM) is required. As far as I can tell, there will be no more development on the legacy data connector and there hasn’t been for a few updates now. For any installation that is still on the legacy connector, I recommend migration as soon as practical for the organization. Some cool features are missing for those installations that are being held back. I would also be glad to discuss it with anyone that disagrees or is confused by conflicting recommendations.
When the Data Mart Needs Attention
That said, there is still a bit of care and feeding of the database that needs to be done occasionally. As of this writing, version update CU9 is available and should be considered the minimum level for an installation. At the very least, CU8 is the earliest very stable offering. Anything earlier should be upgraded as soon as organizational policy would allow it.
Some reasons we need to consider completing this task are:
- The MR reports do not agree with the ERP data
- We are making an extensive change to the GL data (Keychange or Professional Tools activity or other mass data changes, Yearend closing and deleting detail, etc.)
- Restoring a backup of the ERP database
- The MR processes are discovered to have been offline for more than three days
This procedure can take a lot of time to complete, so that should be weighed into the decision to do it. Make sure the accounting folks agree that they can be offline for a few hours. Once that is decided, it is a rather straightforward procedure, and the risk is minimal.
- Remove the current integrations by opening the Configuration Console and 1. Clicking on the ERP Integrations tree on the left and 2. Click “Remove” in the gray box on the left side:
- Start the integrations wizard by clicking 1. File then 2. Configure:
- When the wizard starts, 1. Select the appropriate ERP Data Mart option and 2. Click “Next”.
- If the Access Database Engine has not been installed on your MR server then you will see this warning. This is okay if none of the company’s reports are importing spreadsheets. Click Next.
- Enter Login info for the ERP System. The details of what to enter here change depending on your ERP. In SL we want the System database. In GP, We want the DYNAMICS database.
- Scroll down a bit and enter Login info for the Data Mart. Again, the details vary slightly depending on your ERP. I like to use Windows Authentication in SL and SQL Authentication for GP using the sa password.
- If the change tracking dialog comes up, give it a dbo or sa login:
- If the task list comes up without errors, click configure:
- A green check mark says all is well for this phase:
- By clicking on the link in the lower left, the correct panel will load and the next phase can begin. Click the Enable Integration button. And watch the status area for an indication of progress:
- After the initial integration has occurred, similar indications to those below will tell us that we are almost there.
- Click on the 1. Data Mart Integration log and 2. Click refresh. Scroll through the log entries and look for signs of trouble. The types of errors possible are beyond the scope of this document, but every entry should have “0 records failed.” We are especially interested in the “G\L Transaction to Fact” entry to make sure our integration actually has GL data in it.
- It doesn’t hurt to also look in the System Event log and make sure nothing related to MR is being logged there. There will be a lot of detail there is there are errors, especially if any reports are failing to run.
Follow me! @Butch_Adams
Want to learn more about Management Reporter?
If you have more specific questions about how Conexus SG can help you make the most out of your reporting in Microsoft Dynamics please call us or fill out our contact form.
[su_button url=”https://conexussg.com/contact-us/” style=”soft” background=”#FF6A1C” size=”7″]Contact us[/su_button]