Datamart Sync Issues

(0) Share

Report

Posted on by kmalone43

894

Transactions being posted in GP are taking upwards of 5 minutes to appear in reports generated in Management Reporter. We are setup using a Citrix environment & the transactions appear in the GL20000 table within seconds of being posted by the user. The delay is certainly with the sync between the datamart and the company databases. When looking in the Data Mart Integration logs, I notice that the 'GL Transaction to Fact' task (which I'm guessing handles the transactions) is taking over 120 seconds each time it is firing off regardless of the number of records being written.

A couple quick notes:

- We meet or exceed all minimum system requirements (60gb RAM, 6CPUS, etc.)

- Windows Server 2008R2

- SQL Server 2008 R2

- Never had any issues with FRx

- 23 company databases included in the datamart sync

- Other tasks in the log take between 1-10 seconds (Maintenance task, CompanyProvider to Company, etc.)

Is anyone aware of items that may be causing the datamart sync to drag?

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

KirkLivermont 5,985 on at

Like (0)

Report

RE: Datamart Sync Issues

It sounds like the refresh cycle of datamart is probably triggering before the previous integration is complete. If this is what is causing the issue there are instructions on how to change it on Beat Bucher's blog. https://dyngpbeat.wordpress.com/2014/12/09/the-dreadful-1-minute-refresh-cycle-of-mr2012-datamart/

Good Luck,

Kirk

Was this reply helpful? Yes No
Suggested answer

soma 24,410 on at

Like (0)

Report

RE: Datamart Sync Issues

Normally, DataMart integration will take time to update GL details from GP to MR reports. If you want to see the report with quick update from GP, better you can change your adapter to Legacy (Disable DataMart integration from Management Console and create a new integration with using GP Legacy adapter and check the reports).

Note: Before changing the adapter take a backup of DataMart database from SQL and export all company building blocks from Management Report Designer.

Hope this helps!!!

Was this reply helpful? Yes No
Suggested answer

Bill Campbell 12 on at

Like (0)

Report

RE: Datamart Sync Issues

Something we had to just deal with recently was data not properly displaying after the refresh cycle completed.

We went in and simply restarted the services and as well we reconfigured the change tracking - which simply reset the user password as far as we could tell.

The end result was 1.xxxx second cycles for everything and data appears in the reports almost as fast as the user can regenerate the report.

Not sure if this related to your exact problem, but if it helps - let other know.

Was this reply helpful? Yes No
kmalone43 894 on at

Like (0)

Report

RE: Datamart Sync Issues

Thanks all. I updated the interval to 3 minutes to allow the entire GL Transaction to Post task to fully process before it kicks off again. I noticed after changing the interval that items are no longer coming through in integration logs in the configuration console although they do appear to be running when viewing SQL. I'm going to rebuild the datamart & check the logs and performance tomorrow morning. In the end, by changing the interval to 3 minutes instead of 1 is still going to lead to a 3-6 minute sync time which I'd like to improve.

Unfortunately, I did not see a difference in performance when starting and stopping the services & reconfiguring the change tracking.

In the end, we may have to switch back to the legacy integration...

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

RE: Datamart Sync Issues

That is unfortunate that the changes you tried did not result in a better sync time.

I know you have it well spec'd out - but is there a chance that the disk is not performing up to speed? What else can control that sync - could it be a ram issue?

Just thinking out loud. Hope you find a solution, and post the results here. The community really does use this for solutions to problems like this.

Was this reply helpful? Yes No
Verified answer

kmalone43 894 on at

Like (3)

Report

RE: Datamart Sync Issues

Quick update. After working with Microsoft, it was found that with CU10 an additional script was introduced to the datamart compilation process that was essentially scanning all of our accounts and inserting them into the temp table for certain data checks. Normally this temp table worked just fine and suited the task that it was introduced for. However, with an environment of many companies and large COAs, this new temp table was taking an extended amount of time to compile thus causing our issue. Per Microsoft, this issue will be resolved with CU13 with a modified process of how the datamart compiles.

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

RE: Datamart Sync Issues

Thanks Kyle for posting the refresher here.. That's indeed an interesting news.

Was this reply helpful? Yes No