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
Angie,
The sudden excessive growth of your DataMart DB has nothing to do with the number of generated reports in MR 2012.. however, as Harry suggested, adding new GP companies to the financial data reporting can have an impact. If that's not the case, then you need to look elsewhere..
Did you recently activated AA (Analytical Accounting) in your GP companies ? I've never used it, but heard / read that it can have severe impact on your MR reporting performance and accuracy.
To get back to the 'change tracking' issue, this should normally be enabled in the MR 2012 configuration console, the purpose being to reduce the amount of queries & data transfer from the GP companies, as it would only pull the financial data that has really changed. The change tracking gets enabled on each & every GP company that you have marked as to be used for reporting purposes (this is done in GP starting with 2015 under : Administration >> Setup >> Company >> Company >> Options
There are 2 check boxes at the bottom of the window:
- Enable General Ledger Reporting
- Enable Analytical Accounting Reporting
If you have enabled AA and don't intend to report against it, disable that option. After doing that in each company, I'd rebuild the Datamart from scratch.. There are numerous links in the forum about how to do it, but let me know if you can't find it.
PS: you can quickly check which of your GP companies are enabled for reporting by running the following query in SQL Studio MAnagement :
select InterID, CMPNYNAM, EnableGLReporting, EnableAAReporting from DYNAMICS..SY01500
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/
"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.
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.
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.
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.
#1
André Arnaud de Cal...
292,494
Super User 2025 Season 1
#2
Martin Dráb
231,305
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
