(469) 828-3274

Blog Single

30 Jul

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


The Procedure

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.

  1. 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:

erp integrations

  1. Start the integrations wizard by clicking 1. File then 2. Configure:

erp integrations configure

  1. When the wizard starts, 1. Select the appropriate ERP Data Mart option and 2. Click “Next”.

custom configuration

  1. 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.

prerequisite validation

  1. 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.

configure management reporter

  1. 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.

data mart connection information

  1. If the change tracking dialog comes up, give it a dbo or sa login:

enter credentials

  1. If the task list comes up without errors, click configure:

ready to configure

  1. A green check mark says all is well for this phase:

configuration summary

  1. 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:

initial integration

  1. After the initial integration has occurred, similar indications to those below will tell us that we are almost there.

ERP integrations

  1. 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.

data mart integration

  1. 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.

Butch Adams
Conexus SG
Dallas, Texas
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] [su_spacer]

Related Posts


There are 1 comments on this post.

  1. Rebuilding the Management Reporter Data Mart - Microsoft Dynamics SL Community

    July 30, 2014

    […] post Rebuilding the Management Reporter Data Mart appeared first on MS Dynamics GP, SL AX, […]

  2. Comments are closed.