What I Know About Testing Reports

About half my career has been spent working on reports in one way or another including stints at Actuate corporation and Visa's BI department

I recently wrote a small piece on testing database reports as part of a job application posting it here for future reference:



* Define the Data (etl or static)
* Test The Prompts
* Test The Filters (the data)
* Test the report
* Test The Dimension Tables & cross Validate


Define (create) the data:
The data set needs to contain a broad enough range of data to test the report, this means:

* All attributes values in defined value fields (i.e. reason code, merchant category code) should be in the fact table.
* Metrics need to range from the smallest realistic values to the highest (i.e. if the data will have values in the billions and the thousands both should be represented as it may reveal problems with graphs and rounding errors).
* For fact table All possible hierarchy's have to be represented (i.e. If a Business may have 3 levels of child business those possibilities should be represented)
* All possible relationships should be represented (i.e. there may be more than hierarchical relationships represented- Peers, Vendors, Partners...
* Dimension Tables are updated and correct
* All fact tables are populated so that their likely levels of grouping is represented several times (i.e. if you are testing a report for checking what was bough in a week the fact table should have data for several different weeks, several different suppliers and several different categories of items FOR EACH SUPPLIER)
* There is sufficient data to test all the filters (in terms of what to exclude what to include
* The data can either be generated or created manually but there should be a way to wipe, restore and modify the data
* If the DB has aggregates do the aggregates match the details (i.e. if aggregates are for January data and details are for February that will not work well)


Test The Prompts:

* Do the prompts cover the parameters defined in the spec
* Do the attributes the business wants to be able to filter on appear in the filters
* Do any of the attributes covered in the prompts impact performance (for instance filtering on a "comments attribute" could slow down the report because it can have as many values as there are rows in the fact table).
* Is data security enforced in the prompts, for instance can a supplier view the data of their competitors by typing in a competitor's supplier id.
* Are the expected values in each prompt (if static)
* Are the prompts updated when a dimension table is updated (if dynamic)
* If prompt is based on another prompt, is the relationship enforced
* For report builders are all the expected column options present


Test the Filters(rows):

* Do the built in filters keep out the data they are supposed to keep out (write SQL for what is expected to keep out and compare data sets)
* Do the built in filters let in ALL the data they are supposed to let (write SQL for what is Expected to let in and compare data sets)
* Keep in + Keep out rows should sum to the row count of the fact table
* Does each Prompt work correctly (write SQL for what should be filtered out/in and compare to report)
* Note that filters, effects may not be revealed to QA team in which case these tests need to be derived from the business rules
* Is data security enforced (i.e. if reports are by region, US region users should not see Western Europe data)
* Check for metrics that look incorrect due to rounding errors


Test the report (columns):

* Are non-aggregable metrics treated correctly (counts are notorius for this)
* Are all the expected columns present and in the right order
* Are the grouping levels correct
* do the drill downs drill down to the composition of the parent
* Are the calculated fields calculated correctly (this can also reveal ETL problems where the report just passes through data that is incorrectly processed)
* Are any graphs out of scale (are some graphs useless because because they show widely disparate ranges)
* Is the font/look and feel correct
* Are the copyright/disclaimers correct/present


Test The Dimension Tables & Cross Validate:

* Take the query used for the report, and gradually remove a table at a time from the joins if the amount of records increases it means that there is a value in the fact table that does not exist in the dimension table
* Do the same metrics agree in different reports (i.e. Revenue for Q2 1999 for Business X should be the same in a reports, unless it is defined differently)
* Are aggregates and detail reports consistent