Thursday, August 9, 2018

SSRS Requirement- Maintain a history of what was completed during the day (Historical data every day at 6:30 am in the morning and at 12:00am) – last 30 days

There are various option(s) for implementing this requirement for showing the historical data every day at 06:30 am and at 12:00AM

Option 1: Report Subscription

A report subscription is a report snapshot generated on some defined scheduled time and delivered to the intended audience by the report server. There are two type of delivery mode for the subscription. Subscriptions can be used to schedule and automate the delivery of a report and with a specific set of report parameter values. You can create multiple subscriptions for a single report to vary the subscription options e.g you can create subscription for your Report for any kind of selection parameter. While creating the subscription, we need to feed the values in the parameters so that subscription will generate the report accordingly.

1.     Windows File Share
2.     Email

Advantages of using Report Subscription approach
1.     Report subscription will generate your Report in an excel format and store in the assigned directory in an unattended manner or without any manual intervention.
2.     User has to go to the correct folder/directory to view the report. They don’t need to run the report.
3.     There is no need to change the existing architecture of your reporting application which is most important factor for choosing this option.
4.     This option fulfils the requirement of generating the report at exact point of required time.
5.     Less maintenance cost
6.     Reports can be used for future reference as it will be available in windows folder.
7.     Reports can be send to list of user(s) at scheduled time(if required)
8.     There is performance effects over the ReportServer and application database

Option 2: Report Snapshot

A report snapshot is a report that contains layout information and query results that were retrieved at a specific point in time. Unlike on-demand reports, which get up-to-date query results when you select the report, report snapshots are processed on a schedule and then saved to a report server.
When you select a report snapshot for viewing, the report server retrieves the stored report from the report server database and shows the data and layout that were current for the report at the time the snapshot was created. User can see these reports (report snapshot) by accessing the SSRS URL.

Advantages of using Report Snapshot:

1.       User can see the historical report using SSRS/SharePoint url itself
2.       No Changes in architecture required
3.       Less maintenance as SSRS automates the process of creating new snapshot/expiring the old snapshot(s).

Disadvantages of using Report Snapshot:
1.       There is a chance that size of the Reportserver / ReportservertempDB can grow because these snapshot are stored in these DB(s) only.
2.       Reports performance need to monitor if any degradation found due to increase in size of reporting DB(s).

Option 3: Storing the data for 30 days in database

We can stored the 30 days data in the application database itself or can have separate database named History database in which we can maintain the data for 30 days from current day.

Advantages of this option

1.       User can view the report of any historical data of 30days by selecting the datetime filter.

Disadvantages of this option:

1.       Need to change the architecture of the application/ database
2.       May need to maintain the redundant data
3.       There could be need of separate database which specially maintain the data for 30 days.
4.       Data refresh and data merging will be time consuming & it may affect the performance of ETL as well as reports
5.       Maintaince cost is more
6.       High degree of testing required
7.       Less immune to defects/bugs as it required to test each & every features of the report.

No comments:

Post a Comment