Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The next generation BI tools have changed the IT landscape of enterprises with features such as self-service reporting, ease of development and deployment, cloud and big data support. With these rich features and initiatives to modernize IT, there has been surge in adoption and migration to tools such as Qlik and Tableau. The enterprise tools are now focused on automation and reducing IT development and maintenance overheads. And QA, being an integral and critical part of development process, must have clear automation approach supported by tools. The white paper describes a BI QA Automation framework.
Enterprises use BI tools such as SAP BusinessObjects, SAP Crystal Reports, OBIEE, Qlik and Cognos for catering their reporting needs. One of the key factors for successful BI project implementation is the level of trust on the data depicted in reports and dashboards. Lack of trust in data causes attrition in user adoption and often results in project failure.
BI testing is the process of validating the data, format and performance of the reports, subject areas and security aspects of the governing project. Emphasis on a thorough BI Testing is key for improving the quality of the reports and user adoption. However, testing of the BI projects is different from traditional web application testing since the contents of the report is automatically generated by the BI tool based on the tool metadata. The focus of this article is to propose an automated approach to test the report data and its utilization would be a report migration project.
Need for Report QA Automation
Report Data Testing is an important process of assessing the reliability of information visualized on the report. It is required to sustain the trust of the user on the reports because reports are often used as key basis for management decisions and the user assumes that the information on the report is representative of source data.
A typical approach to verify report quality is to verify calculations on the reports themselves to the data in the source report or data warehouse. Thus the BI testing process becomes a manual one, which increases the workload for testers and making it prone to mistakes. Most BI tools manipulate/transform the source data, which makes it even harder to manually test. Manual testing also is a slow and cumbersome approach which compromises the accuracy of the testing.
It needs to be ensured that report data exactly matches with the expected output, as the reports act as a basis to make critical decisions. Having a manual QA process to validate reports has multiple downsides:
QA Automation Solution Design
As described in the Solution Architecture below the QA automation is done in following steps:
Fig 1: QA Automation Solution Architecture
Generated Code Template
QA Automation Report
Fig 3: Report Output
The above figure is the snapshot of the test engine output where the “Comparison Summary” provides the count of Total mismatched data points and mismatch count by Measure. Also depicts count of data points which are present in either source or target.
The Detailed view highlights the mismatched data points in RED background. The column of interest here is “Table” from which one can identify to which source this data row belongs to i.e. in our case OLTP data means transaction data from SAP Crystal Reports and OLAP means Dimensional data from Qlik.
Benefit of ’s BI QA Automation Approach
As per the Analyst Firm Gartner- “The average organization loses $14.4 million dollars annually through poor data quality.”
Conclusion
Automated BI report Data Quality Analysis approach is a full-proof automated solution to the challenges faced by the Quality Analysts while performing data matches in the environments like report migration from one BI tool to another. It has multiple file format support feature and based on the pre requisite that the data is freeze at same point for both the reporting tools. The observation is that it reduces the testing effort by around 80 percent with reduced number of resources and lesser technically skilled. So it ensures that the trust on the reports is enhanced by reducing the efforts.
10xs)
it will be great if a practical example is given to understand the automation process
Is it available to download?
1. Are all the templates mentioned in framework available to download please?
2. How to develop the automation utility that extracts data from source and also Qlik Sense dashboard?
3. Which Data base source system is used in Code Generator and is it free ware?
4. Any inputs please on how test engine has to be configured
How to automate data validation for SQL Server/Qlik project - seeking recommendations