I validated simple reports here and there but the work was always short and easy.
The reports were simple, displayed data in a tabular format, with some sorting, filtering and grouping.
The validation consisted in checking that
- the report displays the correct data; I would do this by cross checking the report data with the data from an existing app
- the report's details are correct: filtering, sorting, grouping
A recent reports testing assignment was much different.
The reports were far from simple in this case.
Look, for example, at the Retail Site Openings report from this page:
This is the report content:
This is not a simple report but a dashboard with 4 sub-reports.
Play around with the dashboard a little bit and you will see that clicking parts of every sub-report updates the content in the remaining sub-reports.
The same happens when changing the year or selecting categories.
Even more, multiple selections are possible in the bottom tree map reports.
How do you test this type of report?
Before discussing how to test the report, lets see how the report is built.
It is made with Tableau Desktop and published on a website.
Its data comes from an OLTP database populated through the company's main system.
The OLTP database is first transformed into a simpler database through ETL packages.
The simpler database is then used as the source of an OLAP cube.
The report uses the OLAP cube's data.
The cube does not include the raw OLTP data but data aggregations (sums, counts, etc).
How do you test this type of report?
It is difficult to cross check the report data using the main OLTP system (the main OLTP system has raw data while the OLAP cube has data aggregations).
How else can the reports data be validated?
The answer is through SQL queries executed on the OLTP database.
If the SQL queries' results match the reports data, this means that:
- the relational warehouse was created correctly from the OLTP database
- the OLAP cube was created correctly from the relational warehouse
- the dashboards were created correctly from the OLAP cube
Creating the queries is not complicated as soon as the database schema is understood.
But with so many reports in a dashboard, interactions between the reports and caused by filters, the number of SQL queries can be high.
Running the SQL queries is not easy since the parameters values have to be changed every time and the SQL queries are difficult to maintain.
To solve all these problems (not easy to run, difficult to maintain, high number of queries), the SQL queries can be changed into stored procedures with multiple parameters.
The stored procedures resolve all existing issues:
- simplify the execution since only the stored procedure header (name and parameter values) is needed.
- the maintenance is easy as the code is stored in one place.
- one stored procedure replaces a few SQL queries.
The stored procedures come with performance problems because of the execution plans.
In some situations, running a SQL script returns results in 10 seconds but running the same script as a stored procedure takes 30 mins.
What can be done in this case?
I tried multiple things like dynamic where clauses, parameter sniffing, etc.
Nothing helped.
The only idea that worked was to build the SQL script in each stored procedure based on the values of the parameters and then execute it.
See below a sample stored procedure and the SQL script used for executing it.
ALTER PROCEDURE [dbo].[getResultCountForReport1]
@Code int
, @Layer varchar(50)
, @SubLayer varchar(50)
, @Filter1 varchar(50)
, @Filter2 varchar(3)
, @Filter3 as integer
, @Filter4 as integer
, @ResultValue as integer OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @LayerCode integer, @SubLayerCode integer
IF (@Layer <> 'All')
BEGIN
SET @LayerCode = dbo.getLayerCode(@Code, @Layer)
SET @SubLayerCode = dbo.getSubLayerCode(@Code, @Layer, @SubLayer)
END
DECLARE @SqlQuery nvarchar(4000)
SET @SqlQuery =
' SELECT
SUM(CASE
WHEN xxxxxx = ''abcdef'' THEN 1
WHEN xxxxxx = ''ghijkl'' THEN 2
ELSE 0
END)
FROM xxxxxxxx AS www
LEFT JOIN xxxxx AS www ON xxxxxx = xxxxxxx
LEFT JOIN xxxxx AS www ON xxxxxx = xxxxxxx
LEFT JOIN xxxxx AS www ON xxxxxx = xxxxxxx
LEFT JOIN xxxxx AS www ON xxxxxx = xxxxxxx
LEFT join xxxxx AS www ON xxxxxx = xxxxxxx
WHERE xxxxxxxxxx = ''AAA'' AND '
IF @Code <> 0
BEGIN
IF @Code = 123
BEGIN
SET @SqlQuery = @SqlQuery + ' add WHERE clause '
IF (@Layer <> 'All')
SET @SqlQuery = @SqlQuery + ' add WHERE clause AND '
END
ELSE
IF @Code = 456
BEGIN
SET @SqlQuery = @SqlQuery + ' add WHERE clause AND '
IF (@Layer <> 'All')
SET @SqlQuery = @SqlQuery + ' add WHERE clause AND '
END
END
SET @SqlQuery = @SqlQuery +
' mmmmmmmmmmmmm = ''11111''
AND mmmmmmmmmmmmm IN (1, 2, 3)
AND mmmmmmmmmmmmm = 1
AND mmmmmmmmmmmmm IS NOT NULL
AND xxxxxxxxxx.Date between @Filter3 and @Filter4 '
IF @Filter1 != 'All'
SET @SqlQuery = @SqlQuery + ' AND xxxxxxx = @Filter1 '
IF @Filter2 != 'All'
SET @SqlQuery = @SqlQuery + ' AND xxxxxxx = @Filter2 '
DECLARE @ResultTable TABLE (countvalue int)
INSERT INTO @ResultTable (countvalue)
EXEC sp_executesql @SqlQuery,
N'@Code int,
@Layer varchar(50),
@SubLayer varchar(50),
@Filter1 varchar(50),
@Filter2 varchar(3),
@Filter3 integer,
@Filter4 integer',
@Code,
@Layer,
@SubLayer,
@Filter1,
@Filter2,
@Filter3,
@Filter4
SET @ResultValue = (SELECT countvalue FROM @ResultTable)
END
------------------------------------------------------
DECLARE @Code int = 11
DECLARE @Layer varchar(50) = 'layer1',
@SubLayer varchar(50) = 'layer2'
DECLARE @Filter1 varchar(50) = 'value1',
@Filter2 varchar(3) = 'value2',
@Filter3 integer = 33,
@Filter4 integer = 44
DECLARE @ResultValue integer
EXEC getResultCountForReport1 @Code,
@Layer,
@SubLayer,
@Filter1,
@Filter2,
@Filter3,
@Filter4,
@ResultValue OUTPUT
SELECT @ResultValue
So, to summarize, for effective reports testing, the following are needed:
- better than average SQL scripting knowledge
- good understanding of the OLTP database schema
- SQL scripts for verifying each element of the report
- change the SQL scripts into stored procedures
- build the SQL scripts in the stored procedure and then execute it
Share this
