RSMUS.comIf you use Management Reporter, and you have probably heard of the Data Mart. The Data Mart is a way for Management Reporter to access the data required to create your financial reports for Microsoft Dynamics GP. Most people may not know too much about the data mart itself, but I would bet that most people have had to get their data mart recreated. That begs the question though… What does that mean and why do I have to do it?
First, we need to look at what the Management Reporter data mart is, and why we would use it. The data mart is a component of Management Reporter which aggregates data for financial reporting into its own database rather than running queries on the live company databases themselves. This gives the benefit of speed over the legacy company, as it should only have relevant information that needs to be sorted through. It collects this information using SQL change tracking on specific databases and tables so that when changes are made to these tables, it pulls the information in the ManagementReporterDM database. The data mart also has the benefit of additional functionality from Microsoft. After debuting in Management Reporter Rollup 3, the data mart has been the main focus for new features and additional functionality within Management Reporter. Things like multi-currency require the data mart to function correctly in Management Reporter.
As you can read, the data mart sounds great! Why would anyone use the legacy data provider? Early on in Management Reporter, there were some issues with the way that it worked. Now from everything I have heard, most of these bugs that caused issues in the data mart were actually Dynamics GP bugs, places where Dynamics GP did not follow its own design specifications. Specifically within the Analytical Accounting module, there were some issues. That meant that you needed to recreate the data mart fairly often in order to output accurate data. The good news? It seems like a majority of these bugs have been sorted out as of Dynamics GP 2015 and Management Reporter 2012 Cumulative Update 12. From my experience with clients, there are only a few reasons why you may need to recreate the data mart.
What are these few reasons you ask? The big reasons I have found are:
- Restoring a GP database from a backup
- Modifying the account structure for a Dynamics GP company
- Changing segment names
- Bad data or data Corruption
- Your Management Reporter services were stopped for more than three days
Now to me, these causes generally make sense based on what I understand from the data mart. The first is fairly obvious, since the data mart is tracking changes on specific tables, if we copy over those tables with a backup, we lose some of that information, and the data mart might lose track of some information. The second two also make sense to me from a design perspective. How often are you changing the account structure or segment names in Dynamics GP? Not very often in my experience. So with that being said, why would you want to waste resources checking to see if there have been any changes on these tables every minute or so? That is, in my opinion, a waste of resources. So the data mart doesn’t track changes on these tables. That means that in order for it to see any new information that may exist here, you need the data mart to recreate these “facts” within its database, so we need to recreate it. I have also seen some data mart issues when bad data exists in the Dynamics GP tables. Generally this will be in the AA tables in my experience, but Microsoft does provide some scripts to check for bad data in these instances. You can find the relevant KB, Financial Reports from Management Reporter do not match the General Ledger Trial Balance Reports in Microsoft Dynamics GP.
In my experience recently, the data mart has become a bit of a scapegoat for issues in Management Reporter. If anything goes wrong, the answer is always “recreate the data mart.” Honestly, in earlier versions of Management Reporter, that was probably a good first step. On any recent version of Management Reporter though, I would pause before recreating the data mart. My experience has shown that as of Cumulative Update 11 and Cumulative Update 12, it is pretty reliable as long as you are not doing any of the above causes. If you are not on one of the most current releases, please spend the time to get on the most current release! If there are issues in Management Reporter, I implore you to first go into the configuration console, check the logs, and see what is going on. They give so much information and really do a good job of letting you know what is going on in the system. Is it possible you need to recreate the data mart? Absolutely. But in my experience, there are usually other things in play as well, and recreating the data mart may just cause you more issues than you had before.
If you do need to recreate a data mart but are unsure on how to perform that process, looks for my next blog on how to recreate.
RSM is a national Microsoft Dynamics partner with a broad range of services for Microsoft Dynamics. To stay current on other tips, news and updates, sign up for Microsoft Dynamics Community news.
By: Paul Newsom – Colorado Microsoft Dynamics GP partner
