Jose Mendes

Jose Mendes' Blog

How to run a successful testing stage

As we all know, testing is one of the most important stages of an IT project, however, either because the client doesn’t know how to test the solution, because we don’t have sample data we can use to compare against our results or because there is not a general approach we can apply to all projects, testing is sometimes set to failure. On this blog, I will share the approach adopted on the project I have been working.

Step 1 - Build test scenarios

This step can only succeed with the help of the Business Analyst or any other person from the business side.

In this project, we are creating a set of KPIs to be used on Power BI and Excel. Considering the end goal, the BA created a set of scenarios (example below) that we used to test our values.

image

 

Step 2 – Create SQL scripts to query the source data

One of the biggest risks of this approach lies on this step. Here, we want to create a set of SQL scripts that will follow the logic implemented in the cube. If the logic is wrong, the KPI will show incorrect values, even though we managed to match the results from the source data and the cube. This is where the input of the business user is crucial, since only him will be able to look at the numbers and confirm they are accordingly.

Building the test script is very simple. All we should do is set a couple of variables and make sure all the business rules are applied.

USE STAGE

DECLARE @StartDate DATETIME = '20170602'
DECLARE @EndDate DATETIME = '20170603'
DECLARE @OutletUniverse Int

IF OBJECT_ID(N'tempdb..#CallDataAggregated', N'U') IS NOT NULL DROP TABLE #CallDataAggregated;

SELECT
 COUNT(DISTINCT B.VISIT_ID) AS POPCount
INTO #CallDataAggregated
FROM Dms.SalRdCallPerformanceRep A
INNER JOIN Dms.SalVsDailyTimingSum B
	ON B.DIST_CD = A.DIST_CD
		AND B.SLSMAN_CD = A.SLSMAN_CD
		AND B.CUST_CD = A.CUST_CD
		AND B.VISIT_DT = A.VISIT_DT
INNER JOIN Dms.SalSlSalesman C 
		ON C.SLSMAN_CD = A.SLSMAN_CD
		AND C.DIST_CD = A.DIST_CD
WHERE (A.VISIT_DT >= @StartDate AND A.VISIT_DT < @EndDate)
	AND USER_DEFINE1 IN ('DSM',
						'EBSM',
						'HTSR',
						'KAM',
						'OTSR',
						'PTSR',
						'RVR',
						'TMR')
	AND B.VISIT_TYPE IN ('S','E','X')

SELECT @OutletUniverse = 
 MAX(OutletUniverse)
FROM Warehouse.Fct.MarketConfiguration
WHERE MarketKey = 13
 AND (DateKey >= CONVERT(VARCHAR(8),@StartDate,112) AND DateKey < CONVERT(VARCHAR(8),@EndDate,112))

SELECT
  POPCount
 ,@OutletUniverse
 ,(CONVERT(FLOAT,POPCount) / @OutletUniverse) AS Coverage
FROM #CallDataAggregated
 

 

Step 3 – Share the results with the Business Analyst and Testers

Once our testing is complete and the results are approved by the BA, we release the KPIs to UAT. If we are very lucky, we will have a tester that will then carry with his own checks, however, if that is not the case, we will have to make the work for them.

 

Step 4 – Product testing session with the business users

To sign off the KPIs, the business users need to agree with the results that are shown on the cube, however, they don’t always have the time, skills or tools to query the data. To resolve such problem, we created some examples in excel were we compare the source data with the cube.

KPI UAT Cube – In this sheet, we run a query in the cube for a specific scenario

clip_image002[6]

KPI Source – We query the source data ensuring that all the business rules are applied, which is a risky approach as discussed above

KPI Pivot – We create a Pivot table based on the data from the KPI Source sheet

clip_image004[5]

Once the excel scenarios are completed, we arrange a session with the business users and demonstrate that the values from the cube match with the source data. If they agree with the results, the KPIs are signed off and the testing stage is considered a success.

If you have any questions or thoughts, please leave your comment below.

Comments (1) -

  • Dave Stokes

    7/27/2017 10:53:04 AM | Reply

    Great article.  The traditional view would be that the developer who cut the original code, should never be involved with the UAT.   However, it is the norm that the UAT people are not technically skilled at testing BI.  If left to their own devices it can be a disaster, so providing the data interrogation skills and facilitating the client to reconcile and audit the numbers has proven to be a healthy compromise.

Loading