New Topic

Community Topic Question Administration

Eric Hagfors

Yellowfin Support Team

Assigned

Category Administration

Product Version 7.3

What is DocumentId in ReportInstance table

Nick shared this question ago

Answered

Hi Support,

Can you explain what the DocumentId column is used for?

As a test, I've run/edited different reports and always found DocumentId to be equal to zero. What are the different use-cases that would make DocumentId a different number other than zero?

Also, I have set 'Version History' to 'Current Version Only' for the Incident Management Content Folder, but DB entries where DocumentId = 0 in ReportInstance are still being created whenever related reports are being run. Copying, editing and then publishing incident management reports do not create any records in the ReportInstance table, however. It only happens when reports are run.

While I am aware that the table can be maintained by manually deleting those entries, is there a way to automatically maintain the ReportInstances table?

Finally, how should I go about deleting over 65 million records from the ReportInstance table efficiently? The concern is the SQL query might take a long time. Is there a more efficient way of maintaining this table Eg. By Date Range?

The same question

Replies (4)

Eric Hagfors ●

Hi Nick,

Thanks for reaching out to support with your question.

The only reference to the DocumentId column I could find was in relation to license installation -

https://wiki.yellowfinbi.com/display/USER71/Report+Audit+and+Tracking

You can keep help maintain the ReportInstance automatically by disabling Version History, or by setting Max age on your content folders -

https://wiki.yellowfinbi.com/display/user80/Content+Folders

Here is info on manually maintaining the ReportInstance table -

https://community.yellowfinbi.com/knowledge-base/article/why-is-my-yellowfin-database-so-large-and-what-can-i-do-about-it

https://community.yellowfinbi.com/topic/what-query-can-i-use-to-manually-clear-cached-reports-from-the-document-data-table

note - When running any queries directly against the Yellowfin Configuration Database it's possible to cause irrevocable data loss. This process isn't directly supported nor recommended unless otherwise advised to do so. Before running any queries against your Yellowfin Configuration Database, please make sure you have full, up-to-date backups of both your Yellowfin Configuration Database and your Yellowfin Installation folder. This way, if something goes wrong you can revert back to your current state.

I may be able to find out more about DocumentId when our Australian counterparts are online. Does this help answer your questions in the meantime?

Thanks,

Eric

Reply URL

Nick ●

Thanks Eric. No, it doesn't answer any of my questions. I've covered all these already which is why I've submitted this topic.

As you will notice in the first post, I've tried disabling version history but that didn't work. Entries were still getting created in the ReportInstance table. Version is 7.3 Build 20171012. Are you aware if there was any defect that was raised regarding this?

Regards,

Nick

URL

Eric Hagfors ●

Hi Nick,

At this time, I can't find a defect relating to the "version history" setting not applying to the Report instance table. There is however a "slow query" defect logged for the reportinstance table, which looks to improve the table's indexing, as well as an enhancement request - "Automatically clear unused/old records from the report instance table." Would you like to be added as an affected client to either of these tasks?

I'll check to verify that reportinstance table should be prevented from adding rows (on documentid=0) when version history is disabled, and attempt to replicate on this side. I'll then create a defect if warranted and I still am not able to find one.

Thanks,

Eric

URL

Eric Hagfors ●

Hi Nick,

Was talking to some colleagues about this, it sounds like there is a misunderstanding on how the report history is controlled through the UI we are looking to clear up with devs and our documentation. We'll determine if this is defective, or a possible enhancement. In the meantime, there is a query you can run that will prevent records being created every time a report is run -

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):

INSERT INTO Configuration (IpOrg, ConfigTypeCode, ConfigCode, ConfigData) VALUES (1, 'SYSTEM', 'CREATEREPORTINSTANCES', 'false');

Is this useful?

Thanks,

Eric

Hi Nick,

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):

INSERT INTO Configuration (IpOrg, ConfigTypeCode, ConfigCode, ConfigData) VALUES (1, 'SYSTEM', 'CREATEREPORTINSTANCES', 'false');

Is this useful?

Thanks,

Eric

URL

Eric Hagfors ●

Hi Nick,

Was able to get some more info, DocumentId's are generated when creating snapshots and creating automated insights:

Does this help clarify things for you?

Thanks,

Eric

Reply URL

Nick ●

Hi Eric,

Thanks for that.

Regards,

Nick

URL

Eric Hagfors ●

Hi Nick,

Just wanted to check in to see if there's anything else you needed here?

Thanks,

Eric

Reply URL

Eric Hagfors ●

Hi Nick,

I'm going to go ahead and mark this as Answered due to inactivity. Feel welcome to re-open with a reply.

Best,

Eric

Reply URL