Download - DWBI Testing by Puneet
-
8/8/2019 DWBI Testing by Puneet
1/27
Testing of a Reporting
Application
Data Centric Testing Approach
By Puneet Maheshwari
-
8/8/2019 DWBI Testing by Puneet
2/27
Topics
Data Warehouse
Data Flow
BI
Report types
DW BI Testing
Report Testing
ETL Testing
-
8/8/2019 DWBI Testing by Puneet
3/27
Data Warehouse
Data warehouse is a repository of an organization'selectronically stored data.
DW contains Subject oriented, Non volatile, Integratedand Time variant Data.
Data warehouses are designed to facilitate reporting andanalysis.
Data mart is a miniature data warehouse designed tomeet Reporting needs of specific department of BusinessFunction.
-
8/8/2019 DWBI Testing by Puneet
4/27
A generalised DW System
-
8/8/2019 DWBI Testing by Puneet
5/27
Generalized Data Flow
csv
Cube
DM
DB
SourceStaging
DW
ETL
ETL
ETL
ETL
ETL
Reports
-
8/8/2019 DWBI Testing by Puneet
6/27
Data Warehouse Data model
DW Database is modeled on Star Schema orSnow flaked Schema.
Schema contain Fact and Dimension Tables.
Dimension Table contain the attributes ofmeasures or facts. This table contain primary(or surrogate keys).
Fact Tables contain Numeric measures andforeign keys to dimension Tables.
-
8/8/2019 DWBI Testing by Puneet
7/27
BI Tool Structure
Tables
Semantic Layer Reports
RequestSQL
Data Formatted Data
-
8/8/2019 DWBI Testing by Puneet
8/27
Semantic layer
This is a layer through which a user accessesdatabase.
Hides the complexity of underlying data sources. Allows data access using common business terms.
Creates Complex and efficient SQL statements.
It contains Metadata, Tables & Columns information,
Joins, Cardinality, Database connection, Users
Semantic layer is provided by BI tool.
-
8/8/2019 DWBI Testing by Puneet
9/27
Types of Reports
Static Reports:Data is displayed in form of tables or charts.The data can be summarized or detailed.These reports have fixed layout and precisely defined
functionality.
User can only select filter values and prompt values.
Dash Boards:Single interface of many data.Data is generally displayed as graphical manner like pie
chart, bar graph, dials etc.
Dash boards are also used to display KPI of the system.
-
8/8/2019 DWBI Testing by Puneet
10/27
Types of Reports
KPI/Score Card/Metric Reports:KPI- Key performance indicators are High level aggregations of data existing
in the system.
These reports show the KPI/Metric values with various colors based onperformance. e.g.: Heat chart report.
Adhoc reports:These reports are made on the fly by users.
These reports are made to analyze scenarios not covered in static reports.
Reports are made in Query Studio.
Cognos packages are exposed for users in query studio.
-
8/8/2019 DWBI Testing by Puneet
11/27
-
8/8/2019 DWBI Testing by Puneet
12/27
BI/Report Testing
-
8/8/2019 DWBI Testing by Puneet
13/27
BI/Report Testing
Format tests:
Header , Footer,
Look and feel, Fonts,
Static Prompts & Dynamic Prompts,
Conditional formatting, Paging. Functional Tests:
Checking all Functionalities of report,
Checking filters and their options,
Requirement conformance .
-
8/8/2019 DWBI Testing by Puneet
14/27
BI/Report Testing
Data Validation: Most critical test in BI testing.
Fixed Reports:
Generate the Report.
SQL query is made as per functionality of report andfired at each data step.
(For cross tab reports if SQL gets very complicated,report can be broken from one dimension into manysub reports.)
Results of SQL query and Report are compared atrecord level.If result set is huge sampling can be performed.
-
8/8/2019 DWBI Testing by Puneet
15/27
BI/Report Testing
Dash Bords/KPIs
Generate the report.
As per KPI Definition SQL Query is made and fired atdatabase.KPI values generated by SQL and Report are compared.
Color displays for Matrices are checked with standards.
Adhoc Reports (Cognos Model Testing):Static check of links between Query objects and Query Itemswith database tables and columns respectively.
Static Check of joins created between query objects.
Static checks are performed at report studio.Checking calculations and aggregations by running adhocreports and corresponding SQL Query in Database.
-
8/8/2019 DWBI Testing by Puneet
16/27
BI/Report Testing
Performance Testing
Report Generation and Refresh duration.
Authentication and Security Testing
Only authorized users should get access toreports.
Scheduled reports should be delivered tocorrect inboxes.
-
8/8/2019 DWBI Testing by Puneet
17/27
Pre Requisites for Report
testing Access rights. Report requirements i.e. Functionality.
Data models of reporting database.
KPI and Metric definitions.
Mapping between reports and databasetables.
Knowledge of SQL.
-
8/8/2019 DWBI Testing by Puneet
18/27
ETL Testing
-
8/8/2019 DWBI Testing by Puneet
19/27
ETL Testing
Data Completeness
To Test all jobs have run successfully and data transfer iscomplete.
Counts between two data holding stages should be equal.
Aggregations on numeric columns should be equal.
-
8/8/2019 DWBI Testing by Puneet
20/27
ETL Testing
Data Correctness
Also called as Transformation Testing
Validation of business rules.
Business rules are documented as mappingdocuments.
A sample Mapping
Source Target
DB Table Coulmn DataType DB Table Coulmn DataType Transformation
Scr TblX A int Tgt TblX P int A + 100
Scr TblX B CHAR Tgt TblX Q Date Date(B)
Scr TblY C int Tgt TblX R int Sum( C )
TblX S int Default 100
-
8/8/2019 DWBI Testing by Puneet
21/27
ETL Testing
Data Correctness Testing
Attribute level transformation validation process.
A select SQL Query, containing key columns + attribute columns, isfired at target table.
ETL is executed.
A SQL query , containing source columns + transformations(sourcecolumn), is fired on source table.
The results are compared.
This comparison is performed for small subset of data.
All attributes should be validated in one or more queries.
-
8/8/2019 DWBI Testing by Puneet
22/27
ETL Testing
Data Correctness Testing
Each Types of transformations needs separate validation.
Aggregations
Direct Maps
Defaults
Lookups
Calculations
DFD,LDM &ERD are required to formulate filter conditions
and joins in the SQL query.
-
8/8/2019 DWBI Testing by Puneet
23/27
ETL Testing
Data Validation
Row to Row comparison between twoconsecutive data holding stages.
A small set of test data is prepared and ETL isexecuted to load it.
SQL query are used to validate complete
data at each stage.
-
8/8/2019 DWBI Testing by Puneet
24/27
ETL Testing
Environment Integration
Testing ETL execution on various OS , DBetc.
Testing Logging mechanism of ETLs.
Execution Logs
Error Logs
Data Rejection logs Testing ETL on schedulers.
-
8/8/2019 DWBI Testing by Puneet
25/27
-
8/8/2019 DWBI Testing by Puneet
26/27
Pre Requisites for ETL Testing
Mappings between each stage.
Data Flow Diagram, Logical data modeland Physical data models.
Database access rights and ETLexecution rights.
Knowledge of SQL, Operating systemcommands and ETL Tool used.
-
8/8/2019 DWBI Testing by Puneet
27/27
Other DW Tests
System Reconciliations.
Comparison of DW Reports with otherreporting applications.
Automation of ETL Testing.
Testing of Landing server files.