web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Management Reporter Da...
Microsoft Dynamics GP (Archived)

Management Reporter DataMart Growing rapidly

(0) ShareShare
ReportReport
Posted on by ARand 122

I about a week ago a clients MR Data Mart jumped 40 GB and it continues to jump but we are not sure what is causing the jumps. The MR database is find but DM is not. We can rebuild the DM and shrink the database but we wanted to get a better idea of what can cause the database to jump in size this quickly. Any suggestions?

Thank you,

Angie

*This post is locked for comments

I have the same question (0)
  • ARand Profile Picture
    ARand 122 on at

    Just to wanted to mention they were just updatede to the most current update two months ago and it has been running fine except for the recent issues.

  • Suggested answer
    Redbeard Profile Picture
    Redbeard 12,931 on at

    Typically when a database jumps in size mysteriously the Recovery Model, visible in SQL Server Management Studio under Database Options has been set to Full, and Transaction Log backups are not being performed against the database.  This causes the database to continue to grow as it writes all activity to a Transaction Log, which is meant to enable a restore to the moment of failure.  I have a difficult time justifying a database that can and does routinely get recreated being set to a Full Recovery Model.  You should have your database administrator or GP partner switch the recovery model to Simple, and then rebuild the data mart.  It should stop growing unnecessarily.

  • ARand Profile Picture
    ARand 122 on at

    Sorry should have included that since it was the first thing I checked, it is already set at Simple.

  • Suggested answer
    Redbeard Profile Picture
    Redbeard 12,931 on at

    You would be surprised how often it is the simple solution that is the right one. Bummer! I would check your change tracking settings, it would seem to me that if your change tracking settings got "out of whack" you might keep writing the same changes to the database over an over again.  I haven't witnessed this behavior on a Data Mart database before (without someone having marked it with a Full recovery method, that is).

    Additionally, you should take a look at your Files Property in SQL and find out what your Autogrowth/Maxsize settings are.  If someone set this to a high number, then whenever it hit the threshold it could grow dramatically.

  • ARand Profile Picture
    ARand 122 on at

    Where do I check the change tracking settings?

    Thank you,

    Angie

  • Redbeard Profile Picture
    Redbeard 12,931 on at

    Change tracking is one of the options at the database level, like your recovery option.  When turned on, it defaults to 2 or 3 days of information.

  • ARand Profile Picture
    ARand 122 on at

    Change Tracking is set to False. Do I need to change this or is this OK.

  • Suggested answer
    Redbeard Profile Picture
    Redbeard 12,931 on at

    Interesting. Change Tracking was introduced a few versions back, can't remember exactly when, and it is integral to the datamart understanding what has been synchronized from GP and what needs to be synchronized.  I would flag change tracking on all your GP related databases (Dynamics included) and then rebuild the data mart.  Monitor the situation and see if this resolves the problem.

  • Mike Bufano Profile Picture
    Mike Bufano 1,484 on at

    "One of the main reasons the MR Datamart database grows is that it retains a copy of every report each time that report is run.  So at month end if you have a Balance Sheet and PL, and the staff accct runs it 10 times to get it just right, then 10 copies of that report (with all supporting details) are kept in the MR Datamart.  So as you can imagine, if you have dozens of reports or run each report dozens of time with TRx details over several years and , then the MR Datamart grows quickly. 

    Microsoft has a script (and they are MR version specific), that can be used to remove those reports.  Basically you indicate how many copies of each report you wish to retain for each day.  So if you have 1 report run every day (ten times a day) for 30 days straight, but then run the "script" and indicate you only want to retain 1 report per day.  it will prune the reports from 300 retained to 30 reports.  The script does a little more than I describe but hopefully you get the picture.

    We have set it up for several clients after testing and discussing with them and it makes a tremendous difference.

  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    Hi Mike,

    You're confusing two different things here... The DataMart DB doesn't hold any report definintions.. that's why you can rebuild it any time.. The purpose of the DM introduced mid-way into Management Reporter 2012 (aka MR) was to speed up the reading of financial data out of larger GP setups with dozens of GP companies against which you have to report financial data. The major difference between the "Legacy" & DataMart Connector is that the Legacy is reading the data from each single company one after the other, whereas the DM was meant to provide a single point of access for all the consolidated financial data of all GP companies (that's why you have to 'enable' the financial reporting inside of GP, this triggers the reading of financial data into the DM).

    Unfortunately that whole concept of DataMart, which should have been built by using SSAS (SQL Analysis Cubes Data warehouse) to take advantage of the multi-dimensional capabilities of SSAS), went the path of a regular database, thus pilling even more load into the already loaded GP server.. that's why also it is recommended to setup MR 2012 on a separate SQL instance (and server) to no hurt the performance of the GP SQL database server.

    I for instance still recommend using the Legacy connector, even if Microsoft says that it will be deprecated for many releases, it's still there and isn't likely to go away, as no further dev will be done on MR 2012 anyway. I have to see yet a company that pulls financial reports so often from their GP system that it justifies the setup of the Datamart, which refreshes financial data every 5 min by default (now, before it was even every minute).

    All the definitions (column, rows, trees, etc..) are stored in the regular MR database... not the DM. The script you mentioned, that was written by Microsoft, allows for a targeted deletion of the report history (or generations to be more precise), as they are stored too in the MR DB, not the DM DB.

    If you want to read more about the refresh rate of the MR Datamart, please read on here : https://dyngpbeat.wordpress.com/2014/12/09/the-dreadful-1-minute-refresh-cycle-of-mr2012-datamart/

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans