Do You Really Have to Rebuild the Management Reporter Datamart?

Microsoft Dynamics Management Reporter (MR) is a nearly real-time financial reporting application that can empower users to quickly and easily design, generate, secure and publish financial statements. It is “shipped with” and used alongside several Microsoft Dynamics ERPs, including GP, SL and AX. If your organization is using Management Reporter, this blog may be a great tip for you or your team.  

DID YOU KNOW THAT YOU DON’T ALWAYS HAVE TO REBUILD THE DYNAMICS MANAGEMENT REPORTER DATAMART?  

So many believe that if there is any missing data in the Datamart that one must throw whole thing out and rebuild again… but that is not the case! Understanding more about what makes the Management Reporter ERP Integration work, can illuminate a much easier and faster alternative! 

First things first, how does Management Reporter know what data it needs to integrate? MSSQL Change Tracking!   

WHAT IS SQL CHANGE TRACKING?  

It is what Management Reporter uses to identify new records and changes in the ERP Database that need to be integrated into its Datamart. By default, Change Tracking remains viable on a record for 3 days, then it will expire. As such, there can be outside influences that may interfere with this automated integration process at times. 

For example:

• MR Services stopped or never started/restarted for an extended period. 
• ERP Data Validation errors affected integration processing. 
• Disaster recovery down time or restorations took extended time to complete. 
• SQL Change Tracking was globally disabled on the SQL Host. 

In many cases, these “overlooked” records can be easily “re-triggered” for the Integration rather than doing a full rebuild. You might ask, what’s the difference? Well, as you may know, a rebuild is just that, a complete rebuild from the ground up. The re-trigger approach allows you to target a specific set of data, so the process does not require any downtime to complete. 

QUICKER AND EASIER THAN A REBUILD

Let’s explore this example:  In Dynamics SL, the Key tables referenced by the Integration are GLTran, Account, SubAcct, AcctHist and CuryAcct. So, for this exploration of concept, if I were to target an Update Statement on an unused field, on records in the afore mentioned tables, where Created or Last Updated date is from a certain date forward; Change Tracking would be “re-triggered” on those records. At which point, the Integration would “see” and pull those missed records over to the Datamart. The best part of this is you do not have to know the exact date or worry about duplication or redundancy of data – because any already existing iteration of said records would simply get retired in the Datamart and replaced with the new iteration. 

I have seen this work so many times and it’s far quicker, easier and less disruptive than a rebuild. There are of course going to be times when it won’t work but I recommend trying it, as I have found that often, it’s the perfect solution! 

If you have any questions about this Management Reporter Datamart tip or others regarding your Dynamics solution, please reach out to our team of experts.