Management Reporter - Table Size

(0) Share

Report

Posted on by Anders Suarez

255

I have a client that is using Management Reporter for financial statements and their Management Report database size is now over 50GB. One particular table (ReportLineTransaction) seems to be part of the problem. Is there a setting, in Management Report, that deletes history or truncate the transaction from tables? It looks like this table holds information for specific report IDs, rowindex.

*This post is locked for comments

I have the same question (0)

All responses (12)

Answers (0)

Community Member on at

Like (0)

Report

It seems the growth of the database to 50GB is contained mostly to the ReportLineTransaction table. Why would data in the ManagementReporter database grow exponentially with each run of a report? Is it keep the transaction details of the report? Can that be cleared? Is there a setting or safe truncation process to run? This report and MR Reports in general that utilize drilldown to the transaction level are unusable at this point because it has to read essentially a table or set of tables with 27GB's of data. The instance of MR was moved to a new server and report objects exported from old and imported to new. This started the ReportLineTransaction table out very small, but within weeks of running reports with large trees against a 10GB GP database, it has grown to 50GB's.

Is this by design to fill ManagementReporter tables with report data? That seems unwise?!!!

Was this reply helpful? Yes No
April Olson on at

Like (0)

Report

Hello Anders and Beratung,

I reached to you and sent my business card and would like to discuss this with you, Let me know if you are available to discuss it early next week.

Thanks,

April

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

April,

Please resend your contact info.

We found the cause of the issue, and it may even explain why users complain that MR is slower than FRx.

Every time you generate a Transaction Detial Level report it writes every transaction line to the ReportLineTransaction table. That table does not truncate those records until you delete the historical instance from the Viewer application. If you don't delete them on a regular basis that table will grow rather quickly every time you generate one of those reports. The problem lies in the fact that the table is indexed and written to frequently. Unless you truncate the table be deleting the instances of the report and/or reindex the table, MR will slowdown. Writing large amounts of data to an indexed table that is large and growing is a heavy operation. It will most likely take exponentially more time to write to the table then it does to read the data to create the report. That may explain the complete around performance of MR. I beleive FRx just wrote each reports data to its own flat file; hence, a huge diference.

Was this reply helpful? Yes No
April Olson on at

Like (0)

Report

Hello Beratung,

I tried sending it again, but just in case its aolson@microsoft.com. I am aware of the issue but would like to discuss it with you.

Thanks,

April

Was this reply helpful? Yes No
Oscar Mayer 520 on at

Like (0)

Report

April,

Can you post here the information being discussed? I as Beratung have MR clients running very large MR databases causing slowness, consumption of disk space and adding significant time to the database backups. If unable to post, send email to rmayer@summitgroupsoftware.com Thanks!

Was this reply helpful? Yes No
April Olson on at

Like (0)

Report

Hello Randal,

We are still working with the issue with Beratung. If you delete the unused versions of reports from the library, and then shrink the database, is it still quite large? I will send you an email as we are gathering some additional information on this.

Thanks,

April

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Anders,

I have the same issue with my DB & log file. What was the solutions ? Please.

Thx

Was this reply helpful? Yes No
Deborah Bergman 1,465 on at

Like (0)

Report

We are having the same issue with a client - it has grown to 10 GB within a couple of weeks. Where, EXACTLY, do we go to remove historical instances and WHY is this issue not in the Techknoeledge database on MS PartnerSource Support any where? If there is a TK hiding somewhere in PartnerSource and you please provide the number? The oline support database has become nearly impossible to find any real help.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

There is no TK to my knowledge. We worked directly with the MSFT product team on this to get some of the issues resolved but you still have to manually clean up your report versions. Go to Report Viewer app of MR and right click on the reports and go to Versions. Delete the versions. If the report is too big it may time out.

Version 2012 with the latest SP fixes some issues with time outs that prevent deleting large report instances.

Was this reply helpful? Yes No
Deborah Bergman 1,465 on at

Like (0)

Report

Why is there not a setting of somekind to automate deletion of reports after a specific time period? The situation is causing many of my multi-company, large transactional customers a nighmare in overall slowdowns not only in MR but in GP as the database goes uncontrollably. I went in and removed some versions and took their db size from 11 gb to 4 gb, but to have to maintain removing these on a manual basis seems arceaic.

Was this reply helpful? Yes No