Acceptance testing of Sql or Reports

Question

I am responsible for the development and maintenance of a project containing several hundred Sql Reports (SSRS). I would really like to add acceptance testing. Is there best practices for Report Acceptance tests??

We are currently using several testing frameworks for .net projects so are comfortable with TDD, BDD - User Stories etc. Are people using similar approaches with report acceptance testing e.g

Given customer 123456
When I run Report "CustomerOrders"
Then I should see 5 rows in the output

Some reports take a long time to run, are people using dedicated acceptance testing databases?

Is anyone using runners like watir with SSRS??

Any insight in this area greatly appreciated!

Answer 1

I worked on developing a report for real time application. I used SSIS/RS. I didn't use SSAS in my case. I used to run SQL query in

• OLTP System (Run queries on OLTP Tables to find number of rows/value that need to be reflected in reports)
• Staging System (Run TSQL Queries - This would help us verify SSIS ETL jobs have pushed data)
• Fact & Dimension tables (DW Databases) - Reports
• Run SQL Queries with filter options provided in the reports

This was a year ago. Recently I found codeplex tools and few posts related to MSBI / SSRS testing

Please refer below posts and tools.

• Reporting Services Unit Testing Framework - http://rsunit.codeplex.com/

• Microsoft Article - Adventures with Testing BI/DW Application:On a crusade to find the Holy Grail - http://msdn.microsoft.com/en-us/library/gg248101.aspx

• Using Visual Studio 2005 to Perform Load Testing on a SQL Server 2005 Reporting Services Report Server - http://msdn.microsoft.com/en-us/library/aa964139(SQL.90).aspx

• Unit Testing Report within Reporting Services: My Theory - http://consultingblogs.emc.com/stevewright/archive/2009/08/27/unit-testing-report-within-reporting-services-my-theory.aspx

Hope it Helps