It looks like two most problematic tables are event and eventarchive.
I also spotted that in reportinstancefilter there are hundreds of thousand of rows for single filterid. I have 1708287 rows for 125 different filter ids. And also reportinstance seems to grow rapidly.
Is it save to clear data from those tables before making dump?

Andy

** addendum to Andy's email above **

we have highlighted four tables which are causing our instance to swell

event, eventarchive, reportinstance and reportinstancefilter

We are not particularly interested in maintaining an audit of activities, rather we have to be able to perform a rapid archive and restore.

Secondly if we don't require audits is there a means of configuring the product to not insert into the tables above, or should we truncate them regularly if required?


Thanks


Ed

Hi Ed,

The event,eventarchive tables are used for auditing purposes.
If you are not interested in tracking events you could clear these tables.

In regards to the reportinstance and reportinstance filter, these tables can get a little big depending on your report configuration.
These tables store historical report data, and is controlled via the report category setting.

By default, the report category settings are all set to 'keep archived result sets' in the database for 5 years (see below).




If you have a report running every minute it will keep a result set for that report each minute for the next 5 years.

Before removing any data I would suggest checking the size of each table and send across the information.
I would also check your archived result set period and decrease the delete period if necessary, or see which reports are running often e.g every minute and decide it this is really necessary.

Regards,
David

I have a question regarding this answer. Does this mean that ReportInstance and ReportInstanceFilter can safely be truncated without affecting reports in Yellowfin?

Thank you.

Hi Justin,

Sorry for the delayed response.
Yes both the ReportInstance and ReportInstanceFilter tables can be truncated.

The ReportInstance & ReportInstanceFilter table will also store a record for each report, each time it is run or edited.
If you run the same report multiple times, you will get multiple rows for that report in this table. This is used for things like ;
-Getting 'average report run time'
-'Remember Filter Values' option, Editing a report (the draft copy is a separate record so you can roll back to the original activated version),
-Results used for KPI reporting.

If you would like to truncate these tables the below queries will assist.

The query below should clear the ReportInstance table and leave all KPI reports & cached reports intact.

[code]
DELETE FROM ReportInstance WHERE DocumentId = 0 OR DocumentId IS NULL OR ReportId not in (SELECT ReportId
FROM ReportHeader WHERE RoleCode = 'KPI')
[/code]

This will stop records being created in ReportInstance and ReportInstanceFilter tables every time a report is run (please remember what this data is for to ensure you want to do this):
[code]
INSERT INTO Configuration (IpOrg, ConfigTypeCode, ConfigCode, ConfigData) VALUES (1, 'SYSTEM',
'CREATEREPORTINSTANCES', 'false');
[/code]

Hope this helps and please let me know if there was anything else you were after.
I believe you may also have a related case open with the support team, so we can use that channel if need be.

Thanks,
David