Get data to lab report

I started with series of post, I would like to show how to prepare simple testing environment, you can get the data when you test or compare queries performance for example, and you would like to save results and visualise them.

In this post we create simple table structure and SQL procedure which will be used in next post as Chart dataset.

I would like to save following information’s:

  • Test identification – identification of test – for example testing clustered vs nanocluster index, sorting group vs hash group, etc.
  • Task name – identification of task we made in test – for example task1 = table with clustered index, task2 heap table
  • Counter name (for example number of reads, writes, rows, etc.)
  • Counter value – measured results of tests

As you can see the structure is very simple, I believe we could design much sophisticated solution but for our purposes will be enough. Next time we can extend our solution.

Here is the final structure:

SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
 
CREATE TABLE [dbo].[LabTable](
    [WorkoutName] [sysname] NOT NULL,
    [TaskOwnerName] [sysname] NOT NULL,
    [CounterName] [sysname] NOT NULL,
    [CounterValue] [sql_variant] NULL,
    [InsertDateTime] [datetime] NULL
)
ON [PRIMARY]
GO
 
ALTER
TABLE [dbo].[LabTable] ADD
CONSTRAINT [df_InsertDateTime] DEFAULT (getdate())
FOR [InsertDateTime]
GO

Stored procedure returning data to dataset:

SET
ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[SSRSGetDatset]
@WorkoutName SYSNAME
AS
BEGIN
SELECT WorkoutName,
     TaskOwnerName,
     CounterName,
     TRY_CONVERT(BIGINT,TRY_CONVERT(DATETIME, CounterValue))
AS CounterValue,
     InsertDateTime
FROM dbo.LabTable
WHERE WorkoutName = @WorkoutName
END
GO

In next post we fill structures with tested data and create graph to visualise our results. Stay tuned.

SSRS report caching

There are several techniques to optimize report processing. One of the solutions is to enable report caching. When you enable this feature in SSRS report manager (as described below) the data are cached to the Reporting Services Temporary database (see queries below) Reporting services database. There are few possibilities of how to set the caching strategy, from planned approach to the solution with expiration cache. But, be aware that in case the user changes report parameters, the stored cache will not be used and there will be new data cache created. Keep it in mind when you set the caching strategy.

Go to the report manager.

Go to report Properties click on right arrow right to the report name and select properties form menu.

Report Properties
Picture 1 – Report Properties

Go to Processing Options section. And set Cache a temporary copy of the report. Expire copy of report after a number of minutes: and put time you would like to hold the cache in database.

Processing options
Picture 2 – Processing options

When you choose scheduled strategy. Expire copy of report on the following schedule or Render this report from a report snapshot, schedule has to be set. You have to fill the data on picture bellow. Be aware the SQL agent has to be running to use scheduling feature.

SQL agent settings
Picture 3 – SQL agent settings
Schedule settings
Picture 4 – Schedule settings

After setting property for Report Execution Cache new record is added to execution cache. You can see in database that there is time when cache will expire. After this time the record disappear from the database. (not immediately but after few seconds minutes asynchronous mechanism delete it ).

Cache from database
Picture 5 – Cache from database
SELECT * 
FROM ReportServer.dbo.[Catalog] c
JOIN [ReportServerTempDB].dbo.ExecutionCache ec ON ec.ReportID=c.ItemID

Change report parameter.

Change parameter
Picture 6 – Change parameter

You can see that you record what inserted into Report Execution cache.

Execution cache
Picture 7 – Execution cache

SSRS Report PowerShell

I would like to follow my last post regarding PowerShell interaction with Reporting Services. I prepared few posts, which demonstrate PowerShell manipulation with Reporting Services. I will publish them continuously. In near future you can be looking forward to:

  • Download reports
  • Upload reports
  • Copying reports
  • Report serialization/de-serialization to XML
  • Create linking report
  • Create subscription