1.SnapshotData
2.History
3.ExecutionLogStorage
4.ModelDrill
5.ModelPerspective
SnapshotData
Stores the configuration of the snapshot for the report.
History
Has the history of snapshot of reports with the parameters and other details.
ExecutionLogStorage
Has the execution log for each and every report launched in the reporting server. This table data is help full for performance tuning the report.
Some Important Fields| Parameters | Parameters passed for the report |
| TimeStart | Start time |
| TimeEnd | End time |
| TimeDataRetrieval | Total Reporting time = Timeprocessing + TimeRendering |
| TimeProcessing | Time taken to retrieve the data from database |
| TimeRendering | Time taken for rendering the report |
| Status | Status success or failure |
ModelDrill
Stores the details of the custom click through report configured for the model items. This can be set up on ModelsàManage à Click through.
ModelPerspective
Stores the name and the perspectives list created with in the model. A perspective can be created in BIDS by rightclick on Model àNew à Perspective.
This is similar to SSAS perspective to group the attributes.
Sample usage queries
1. Get the details of the history pf the snapshot with report name and schedule used to create the snapshot
SELECT c.name,
h.snapshotdate,
s.DESCRIPTION,
s.effectiveparams,
s.queryparams,
sc.name,
sc.nextruntime
FROM history h
JOIN snapshotdata s
ON h.snapshotdataid = s.snapshotdataid
JOIN catalog c
ON c.itemid = h.reportid
JOIN reportschedule rs
ON rs.reportid = h.reportid
JOIN schedule sc
ON sc.scheduleid = rs.scheduleid
WHERE rs.reportaction = 2 -- Create schedule
2. Get the execution details of a given report with the details like User executing the report , Execution time etc
SELECT c.name,
CASE e.requesttype
WHEN 1 THEN 'Subscription'
WHEN 0 THEN 'Report Launch'
ELSE ''
END,
e.*
FROM executionlog e
JOIN catalog c
ON e.reportid = c.itemid
WHERE c.name = N'@Reportname'
No comments:
Post a Comment