Showing results for 
Search instead for 
Did you mean: 

In Advanced Reporting can I change how many months of data to keep in BigDataInsightUC SQL database?

Community Manager

In Advanced Reporting can I change how many months of data to keep in BigDataInsightUC SQL database?

In Advanced Reporting can I change the retention period of the BigDataInsightUC SQL database to increase or reduce the number of months of data to keep?



Community Manager

Re: In Advanced Reporting can I change how many months of data to keep in BigDataInsightUC SQL datab

Yes you can. The supported way to do this is to run the setup.bat Setup script that was run to deploy the Advanced Reporting solution originally, and in it to select the option 'U' to use the existing database. It should then also prompt you to set how many months of data to keep in the SQL database. 


For example to reduce the retention months from 2 to 1 month of data:


Prognosis Insight Solution Deployment

Prognosis Insight needs information regarding Microsoft SQL Server instance,
to store data. Please enter the following details:

SQL Server hostname/IP address ( Default: SUP-2012R2-MON7 )>

SQL Server instance name on SUP-2012R2-MON7 ( Default:  )>

Checking SQL Server connection ... ok.

Checking SQL Server edition  ..... ok.

Prognosis Insight requires two databases on SQL server.
Please enter names for the datamart database and the staging database:

Datamart database name ( Default: BigDataInsightUC )>

Staging database name ( Default: BigDataInsightUCSource )>

Checking databases details ........... done.

The specified databases already exist on SQL Server.
These databases appear to be compatible with the current Setup.
You can:
[U]se these databases,
[D]elete the databases and create new ones,
[M]odify the database names used as part of this deployment,
or [Q]uit.

[U]se [D]elete [M]odify [Q]uit
(Default: U)>

To prevent filling up the disk, a limit is placed on the amount of data
to be kept for reporting.

How many months of data should be kept for reporting ( Default: 2 )>1

Note that this new retention will take effect on the first day of each month when the ETL will decide whether or not to purge the oldest data according to the set retention period number of months.


Note also that we can check how many months it is supposed to keep by running this SQL query and observing the KeepDataMonths:

use BigDataInsightUC;
select * from dbo.settings




More about the Setup script in Online Help for single server and multi-server deployments:

Webinar: The Journey to Microsoft Teams - Readiness Phase (part 2)

Having looked at the planning phase in session one of this series, we will turn our focus to the readiness phase. The all important technical capabilities assessment, ensuring the network, endpoints and users are adequately prepared for the move.

Hear first hand from IR's Global Head of Information Systems and Technology, Jason Schwendinger, on how he has been tackling these issues.

Join webinar