Monthly Archives: December 2014

The dreadful 1-minute refresh cycle of MR2012 DataMart

When Microsoft introduced the DataMart connector in Management Reporter 2012 (aka MR2012) back in 2012 with the RU3 release, they probably didn’t put much tought into the fact that some users are running multiple GP companies up to several dozen databases on the same SQL instance… Although at the time one of the benefits that were put in the head lights was the performance enhancements that the DataMart was supposedly provide.

When the RU3 release came out, I installed it on my TEST bed GP server where I had already a copy of MR2012 installed to have a simulation environment for upgrades. We only run about a half-dozen GP companies with financial data and two consolidation companies for the eliminations. Though our CFO never really was impressed by the performance of MR since it took over FRx 6.7 in our company, he wanted to believe that over time the product would improve and become a solid replacement candidate for FRx. Needless to say, it took us about one year of parallel testing for financial reports between MR 2.0 (at the time) and FRx before deciding to dump FRx as the company’s financial reporting tool and make the jump to MR 2012.

With the time the performance of the product improved and it became more stable and less prone to bugs and various errors of unknown nature. So when the DataMart was introduced, I tested it on my GP test system and was expecting the so-promised performance improvement over the “Legacy Connector”, especially when it came to consolidated reports across multiple companies… The first impression was not really ‘impressive’… I would even say that the DataMart was slower then the Legacy connector to complete the generation process and open MR Viewer.. The other drawback was that the initial version of DM didn’t support Multi-Currency reporting, a feature we absolutely needed, given our international business and presence with our various GP companies.

One other drawback I learned the day RU3 was installed on my TEST GP system was the fact that there was now a service from MR that was permanently connected to all my GP databases that were “integrated” with MR 2012 using the DataMart connector. Every time I needed to restore one of my production databases to my GP Test server I had first to stop the MR services that run in the background to allow access to my SQL DB’s.

Another thing I realized also pretty soon as I’m monitoring very closely my various SQL servers in the company and keep an eye on the performance, catching processes that could trigger some phone calls by the end-users, was the unusual activity going on. My SQL server on my GP test server was doing some strange tasks every minute or so, that would use quite a good chunk of CPU from my system… something I wasn’t really comfortable with. The process was hitting about 60-70% of my CPU capacity and last for about 12-15 seconds every minute… My I/O state would also show some disk activity that shows some 5-6 Mb was read and written every minute.. Not that it was taking very long, but it was annoying. In some cases, the CPU would even hit almost 100%.

MR2012_CU10_DataMart_refresh_cycle_1min

Process Explorer shows CPU activity on SQL server

I tried to get in touch with Microsoft about this and after a couple of e-mail exchanges I got a confirmation that this was “by design” for the new DataMart connector to allow for the refresh of the financial data of the GP companies. I must confess that when I first installed the new release with the DM, I was very disappointed as in my mind ‘DataMart’ was sounding like ‘Data Warehouse’ and thought that Microsoft would finally make the jump and build an Analysis Cube for the GP financial data that would be used by MR 2012… Boy was I wrong !.. After the installation of the new connector, I was the proud owner of another new flat database called ‘ManagementReporterDM’.

I discussed the issue with the performance hit on the SQL server with Microsoft and explained that I found that a default refresh value of 1 minute for the GP Financial was quite a little bit overkill, especially when you think that it is most of the time for financial reports about month-end data for the past months… usually historical data between your last month closing and YTD. So I wont really see the benefits of refreshing the data that much often. I have other HR Analysis Cubes on my SQL server that refresh twice a day ! and that’s even more then enough…

My main grip and argument towards Microsoft was the fact that this refresh rate was out of user control, and there was no place you could act on this settings in the MR 2012 configuration console. As a reply to this, Microsoft sent me a SQL script that I could use (with great care of course, and no liability from them ūüôā ) to find out in the MR 2012 configuration database what current settings are active.

/************************************************************************************
This scripts reads out some of the important MR2012 system parameters that are
scheduled to be refreshed on regular intervals.
Pick the trigger ID that needs to be changed and update the value with the second
statement below
(c) April Olson, MS, 2014-04-14
************************************************************************************/

SELECT t.id AS TaskID
, t.name
, ts.StateType
, ts.Progress
, ts.LastRunTime
, ts.NextRunTime
, tr.IsEnabled
, tr.Interval
, tr.UnitOfMeasure
, tr.id AS TriggerID
FROM
scheduling.Task t JOIN scheduling.TaskState ts ON t.id = ts.TaskId
JOIN Scheduling.[trigger] tr ON t.TriggerId = tr.id
WHERE TypeId = ’55D3F71A-2618-4EAE-9AA6-D48767B974D8′;

The result would be something like this :

Scheduled Tasks in MR2012 related to data refresh

Scheduled Tasks in MR2012 related to data refresh

In this case the value we’re looking for is the TriggerID (original field ‘ID’ from the trigger table) for the Task Name that is called ‘GL Transaction to Fact’. You may see several other entries with the value ‘Interval’ set to 1, in my case above I already changed many of the them to a 5 minute interval. The statement you’ll need to execute to change the refresh interval would look like this :

update scheduling.[trigger] set Interval = 5 where ID = ‘A4A77F6F-4310-492F-BA02-953CBB395093’

This will uptdate the trigger schedule table and you’ll almost instantly see the difference on your SQL server.. there is no need to restart your MR services. The UnitOfMeasure value of the table seem to carry different effects, as I’ve seen the dates in the NextRunTime field set apart of 24hrs from the LastRunTime value, and the interval was still = 1. My findings so far are that the UoM = 2 is for Minutes, and UoM = 4 is for Days.. so maybe 3 = Hours…

The new refresh cycle set to 5 minutes interval.

The new refresh cycle set to 5 minutes interval.

Try it out and maybe you’ll be able to find the best settings for your setup. As always, never carry those tweaking on your production system without having tested it out first in a development or test environment. Also, do a full backup of your MR databases in case something goes wrong. I’ve heard from some end-users that have to struggle with some 50-100 GP companies and the 1-minute refresh cycle is definitely not appropriate for situations like this… the whole refresh cycle can’t even run to the end as the next cycle is already triggered by the system..

Read more about the thread in the Dynamics Community here.

I’d warmly suggest to Microsoft to reconsider the possibility in a future release of MR 2012 to make these settings accessible to the MR2012 Configuration Console, so at least the system administrator has a control of it.

Until next post…

 

PS: I’ve created a new ticket on Microsoft Connect that you can vote for here (you need to be registered to vote)

Advertisements
Categories: Dynamics GP, MR2012 - Management Reporter | Tags: , , | 4 Comments

First GPUG local chapter meeting for Montreal QC

Friday Nov. 21st was the first local GPUG chapter meeting held in the brand new offices of Microsoft downtown Montreal. The meeting was scheduled from 8:30am to noon, but I showed up there five minutes before eight to be sure that everything was ready in time. I arrived at the door at the same moment as the nice lady from the reception and we had a short chat about the company I’m working for, which she found very interesting¬† (who’s not fascinated by CSI Miamiūüėä?), before she led me to the room that was reserved for the meeting.

The New Microsoft Offices in Montreal

The New Microsoft Offices in Montreal

 

The new conference rooms are really nice and the setup was quick and easy (kudos to that new control console for the projector in form of a mini touch pad). The first attendees arrived already ahead of time at 8:00 and were promptly headed to the room.. At 8:30 we had 4 attendees in the room, out of the 8 that had registered to the meeting… so we waited a little moment more, starting to chat about GPUG and the recent Summit in St-Louis. Shortly after arrived the catering service with coffee, some pastries and fruits.. Thank You GPUG for this sweet attention.

As no other participants showed up, we decided to start the meeting and went on with the opening of the session and starting the discussion about kicking off a new local chapter in Montréal. The discussion went on well for about 20 minutes to explain what the purpose and the benefits of a local GPUG Chapter are.

After that brief intro, we moved to the actual meat of the morning, an educational session about Disaster Recovery Planning for Dynamics GP, a session that I had presented at the GPUG Summit in St-Louis last month. The PowerPoint material can be found here : GPA01-GPUGSummit2014_SessionDeck_DisasterRecovery.

After the session presentation and a short cofee break, it was time to have a brief review of the previous month’s GPUG Summit 2014 in St-Louis, MO. The video below will provide you a quick glimpse of what the event was like.

Here is also a short presentation of the next GPUG Summit 2015 destination, the beautiful Reno Lake Tahoe in Nevada.

RenoTahoeLogoBadge_CMYK_Orange_VisitRenoTahoe_REV-Outlined-Fonts-w-URL-(1)

 

The meeting went on with the presentation of member’s showcase, as I made a half-hour introduction of Ultra-Electronics Forensic Technology Inc., also known as FTI. I went thru the history of the company and our current position in the market, with a presence in more than 60 countries around the world, and being a leader in the domain of ballistic identification. More about FTI can be found here : http://forensictechnology.com/about/

 

Ultra Electronics acquires Forensic Technology

I think I was able to capture the audience with the presentation of our company, especially when considering that most people associate Forensics with the CSI TV show (see more about this here). However, not many people know that the ballistic identification system that is used in the TV show is really an full working IBIS station that FTI sold to the producer many years ago. For those who want to know more about our company, there is a full load of brochure to download from our site.

As we came to the end of the morning, it was time to wrap the meeting and head back to our respective offices. My feeling is that it was a first step into what I hope will be a long successful local GPUG Chapter. Despite the fact that we were only five people attending this first meeting, I’m confident that we’ll be able to build a strong chapter. From the discussion that I had with one of the present partner, there seems to be way over 300 customers using Dynamics Great Plains in the greater Montreal area, so there is definitely room for more members :-).

For now, there were no nomination to fill the position for the local chapter, but I welcome any member to register and fill for a position.Below is an excerpt from the GPUG Chapter manual :

Local Chapter Leadership Roles
Persons leading Chapters must be Microsoft Dynamics GP Users. The roles defined below, provide guidelines
for what common duties are necessary for a smooth running chapter:
Chair Person

  • Oversees delivery of and facilitates Chapter Meetings
  • Serves as a point person to GPUG and represents the chapter
  • Communicates final agenda, location, schedule and other items needed to execute the meetings
  • Helps find and selects subject matter experts who can help present
  • Oversees survey/feedback/continuous improvement of their chapter
  • Provides feedback to GPUG on topics, presenters, member participation
  • Participates in GPUG Chapter Leadership calls and training when possible

Co-Chair Person

  • Will assist in sharing duties with Chair Person

Vice Chair for Membership

  • Oversees chapter membership roster for accuracy and completeness
  • Welcomes attendees, updates attendance reports and submits to GPUG
  • Generates creative ideas to drive User and Partner attendance
  • Participates in GPUG Chapter Leadership calls and training when possible

Vice Chair for Communication

  • Oversees communication to members at a local level via GPUG Collaborate, social media, etc.
  • Assists in driving attendance by utilizing the GPUG membership directory, partners, ISVs
  • Document activities of meetings, presenters and topics and record Chapter history
  • Submits summary of meeting for GPUG Magazine (photos are great)
  • Participates in GPUG Chapter Leadership calls and training when possible

 

The following openings are available :

  1. Co-Chair Person
  2. Vice Chair for Membership
  3. Vice Chair for Communication

GPUG has a great wealth of material to support the regional chapters and provides also the logistics to help organize the meetings, so there isn’t really a giant amount of prep work, except to plan at least two presentations for each meeting.

The 2015 Meeting Dates have been set and should help everyone to plan accordingly their attendance :

1. Wednesday Feb. 18th, 2015
2. Wednesday April 15th, 2015
3. Wednesday Aug. 19th, 2015
4. Wednesday Nov. 18th, 2015

The meetings for Q1/Q2 will be held in Microsoft’s offices downtown Montr√©al, from 8:30 to Noon again. For the second part of the year, we might be able to have them organized at one of the members corporate office and take the opportunity for a member showcase.

Some of the 2015 Meeting Topics that were suggested:

1. GP 2015 & GP 2013
2. GP Web Client
3. Support Debugging Tool
4. BI & Reporting/Project Accounting

 I want to Thank again here the people from Microsoft Canada in Montreal, especially Stephan Mondoux for inviting us to use their brand new meeting rooms, and of course Whitney Derks & Jennifer Dufresne from GPUG for arranging the catering. Thank you also to the participants that took some time off from their work to attend this event.

The goal for next year is to spread the word and try double the number of attendees we had for this first meeting. Until then, I wish everyone a Merry Christmas and a Happy New Year.

Your truthfully  chapter Chair Person.

PS: Bellow is the link for the regional GPUG Chapter certificate.

Canada-Quebec_GPUG 2014 Chapter Certificate

Categories: Dynamics GP, GPUG | Tags: , , , | Leave a comment

Powered by WordPress.com.