Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Jodie Baxter]

Hi Everyone,

I have had an issue reported whereby three accruals in GP are not showing up in the Management Reporter TB report. 
I have checked both Datamart and Legacy reports (ie. -Curr) and the accurals are not reflected in either. The data is fine in GP.

In Management Reporter configuration console DYNAMICS to ManagementReporterDM integration has a warning:
‘The ERP validation on 27/02/2018 3:00:02 PM encountered errors. Check the data mart integration log for additional details.’
The integration logs have a lot of errors the main constant being ‘Index was outside the bounds of the array.’

I logged a ticket with our partner and they sent me this link (https://support.microsoft.com/en-us/help/3029952/management-reporter-2012-data-mart-integration-error-index-was-out-of)
I did find that there is data is GL40200 table that does not exist in GL00100 for a lot of the databases. 
Partner says to fix this is out of scope and have referred it to a consultant, with an estimate of 2 days work although there is no consultant currently available to do the work so I am stuck…. 🙁 

What I don’t understand is why this is suddenly happening as the accounts in GL40200 have been missing in GL00100 for months (I checked some of the backups) so I am not convinced that the two issues are linked. I am hoping someone can give me some advice in case they have come across either of these issues before? 

FYI – GP server was restarted one day before this issue of the data missing in MR as a user reported an issue when trying to open Design Reporter. The error was: Can’t connect to Management Reporter server. Do you want to specify a different server address?
Our tech support reported that the GP server was unresponsive so he restarted it and the user was able to open Design Reporter again. I wonder if this has contributed to the issue of the missing data in MR? 

I’m relatively new to GP and have never rebuilt the Datamart myself before but it seems like something I need to learn as this will be the 2nd time we will have to pay for it to be done in the last 6 months.  Should I attempt it myself or wait for a consultant to be assigned to the ticket?

——————————
Jodie Baxter
Business Systems Analyst
Lumina Chartered Accountants
Sydney
——————————

[Frank Heslin]

Hi ,

Rebuilding the datamart is not as bad as it sounds.  Here is a link to a post I did a few years ago.  The post says that MR CU13 is the latest; I think CU16 is now the most recent.  Rebuilding the DataMart Post

There is a link in the post to a good step-by-step guide. 

Let us know if you have any other questions.?

——————————
Frank Heslin
Financial Systems Administrator
ExamWorks, Inc.
Atlanta GA

GPUG Advisory Committee Member
Summit Track Leader
GPUG All Star 2016

GP2015 R2
MR CU14
——————————
——————————————-

[Cory Severson]

Hi Jodie,

As Frank mentions rebuilding the Data Mart isn’t too hard.  I’ve seen where transactions are missing from the Data Mart often and usually using the Legacy Provider gives the correct results so I’m surprised that did not work.

When trying with the Legacy and -CURR company, was that change made only to the company in Report Definition?  Do you by chance have a tree on the report that may be pointing back to the Data Mart Company?

Thanks!

Cory

——————————
Cory Severson
Technical Consultant
Stoneridge Software
Fargo ND
——————————
——————————————-

[Jodie Baxter]

Hi

Yes you are right. The report did have a reporting tree on it that I did not change when I checked ‘legacy’ the first time. When I change that and run the report the legacy reports are correct and matching GP as expected. 
Thanks alot for your help.

——————————
Jodie Baxter
Business Systems Analyst
Lumina Chartered Accountants
Sydney
——————————
——————————————-

[Beat Bucher]

Hi ,
This is one of the reasons (aside from others) that made me switch back to the ‘Legacy’ connector after the DataMart came out the first time and our testing showed a  lot of inconsistencies in the generated reports and that we couldn’t trust it.
Aside of that, it was consuming a ton of CPU on my server and didn’t provide any significant benefits in terms of report generation compared to the standard connector (and we’re consolidating a dozen companies for our Financials using Multi-currency).
Read more about this here :
https://dyngpbeat.wordpress.com/2014/12/09/the-dreadful-1-minute-refresh-cycle-of-mr2012-datamart/

The only time you’d be forced to go with the DM is when you’re usin AA (Analytical Accounting), as the Legacy doesn’t support that.

Also have a look at this blog post for some of the issues around the DM :
https://dynamicserppros.com/microsoft-dynamics-gps-management-reporter-data-mart-youd-recreate/
?

——————————
Beat Bucher
Business Analyst, Dynamics GP MVP
Ultra-Electronics Forensic Technology Inc.
Montreal QC/Canada
@GP_Beat http://dyngpbeat.wordpress.com/
Montreal QC GPUG Chapter Leader
GP2013R2 / MR2012 CU14
——————————
——————————————-

[Jodie Baxter]

Thanks Beat for the great insight and helpful links.

Interesting to know that restoring a database can be one of the root causes. We are constantly restoring live databases to the test database because we have some very manual integrations that require performing the integration in test database first (yes I know its not optimal and this is on my CI list).

We do use AA with a couple of companies and these are always the ones we see issues with in MR.  We will be migrating more clients to GP soon and my inclination is to try and avoid AA to make life easier in MR. In your experience, would you say it’s better to use additional segments in the COA instead of using AA?

——————————
Jodie Baxter
Business Systems Analyst
Lumina Chartered Accountants
Sydney
——————————
——————————————-

[Jo deRuiter]

?Hi

For my two cents, I am NOT a fan of AA.  It should be utilized to track useful information that you would NOT use a separate segment to track, such as for, say, Auto Expenses it should track whether it is New Tires, Oil Changes etc.

If AA is setup to replace a segment – i.e. a business line, etc then it is being used wrong.

AA over-complicates GP and the reporting.  For instance, if MR is used and AA is installed MR looks specifically at the AA tables first.

Again, I am neither a fan of MR OR AA :D?

——————————
Regards,

Jo deRuiter
“That GP Red Head”
Senior Financial Systems Consultant
Heartland Business Systems, LLC
Pewaukee WI
770-906-4504 (Work)
770-906-4504 (Cell)
——————————
——————————————-

[Jodie Baxter]

Hi

AA is set up to track 2 things on one of our Companies.
The first is office location – ie City and the other is department/division. I guess you could say it is a business line so it sounds like maybe we should not be using AA at all… happy Friday to me! 
Thanks Jo! 
?

——————————
Jodie Baxter
Business Systems Analyst
Lumina Chartered Accountants
Sydney
——————————
——————————————-

[Jo deRuiter]

?Good Morning

My colleagues on here are correct that you need to rebuild the DataMart.

The rule is that any time data is incorrect in MR when compared to GP the first thing you should do is rebuild the DataMart.

It should only take a few moments and will, generally, correct all of your issues.

The need to rebuild generally stems from things like integrations, but is caused quite frequently be disconnects between SQL and GP – as in a server reboot.

I would follow  ‘s link to rebuild the DM – it really is not a big deal to do that.

Let us know if you have any other questions!???

——————————
Regards,

Jo deRuiter
“That GP Red Head”
Senior Financial Systems Consultant
Heartland Business Systems, LLC
Little Chute WI
920-687-4797 (Work)
770-906-4504 (Cell)
——————————
——————————————-

[Frank Heslin]

Hi ,

I just checked the link to the step-by-step, and it is no longer valid. 

Here is a quick reference guide that I put together that should help.

Let us know if you are able to successfully rebuild your datamart.?

——————————
Frank Heslin
Financial Systems Administrator
ExamWorks, Inc.
Atlanta GA

GPUG Advisory Committee Member
Summit Track Leader
GPUG All Star 2016

GP2015 R2
MR CU14
——————————
——————————————-

[Jo deRuiter]

?Good Morning

A lot of my clients are getting super frustrated with MR, for many of these reasons and are moving to Jet Reports or other Excel Based Reporting tools.  They do have a cost to them, but are much, much, much easier to use and maintain.?

——————————
Regards,

Jo deRuiter
“That GP Red Head”
Senior Financial Systems Consultant
Heartland Business Systems, LLC
Pewaukee WI
770-906-4504 (Work)
770-906-4504 (Cell)
——————————
——————————————-

[Jodie Baxter]

Thanks Community for all your comments and help. You are a great bunch of people.

In case anyone comes across this post in future the issue was resolved by Consultant as follows:
1. Determined that Legacy reports were in fact correct and Datamart incorrect.
2. Took a backup of the related database and restored that backup to a Test Database.
3. Run reconcile GL (Tools > Utilities> Financial > Reconcile) from oldest year to current open year.
4. Found strange/orphan records in MR as per below. Verified that these records pertain to databases which no longer exist. Cleaned up this data in MR. 
5. Configured change tracking in MR configuration console.
6. Run AA validation scripts.
7. Rebuilt Datamart.

Reports are now matching legacy and I am no longer getting ‘Index outside the bounds of the array’ error in config console DM integration logs. Not really sure which of these steps ultimately fixed the problem but I suspect the weird company in MR and the Datamart rebuild were key steps.

——————————
Jodie Baxter
Business Systems Analyst
Lumina Chartered Accountants
Sydney
——————————
——————————————-