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