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.
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.
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;
COUNT(DISTINCT B.VISIT_ID) AS POPCount
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',
AND B.VISIT_TYPE IN ('S','E','X')
SELECT @OutletUniverse =
WHERE MarketKey = 13
AND (DateKey >= CONVERT(VARCHAR(8),@StartDate,112) AND DateKey < CONVERT(VARCHAR(8),@EndDate,112))
,(CONVERT(FLOAT,POPCount) / @OutletUniverse) AS Coverage
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
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
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.