In a typical business intelligence (BI) architecture, data will flow from various source systems to the exploitation environment. The data passes through a staging (STG) area, possibly an operational data store (ODS), a data warehouse, as well as a data mart (in no particular order - depending on the methodology used) and then gets extracted into user reports.
In many BI implementations today, the only method of testing the end-to-end solution is done by running the new BI report and comparing it to some existing report/figure from the source systems. This is all good, but what happens when those figures do not match? Immediately, the user starts mistrusting the implemented BI application and the success of the project might be in jeopardy. The timelines set for user acceptance testing may very well also be at risk, as the user will immediately get much stricter on test cases and less trusting of the processes used to deliver the new user reports.
Before even attempting user acceptance testing (UAT), basic technical source system verifications as well as the establishment of data acceptance criteria have to be agreed upon upfront with the source system owners. This process ensures the correct structures and data will be extracted from the various source systems before the data gets transformed and loaded into the BI architecture for reporting purposes.
In synch
The source data verification process focuses on the first reason for possible differences within the extraction transformation and loading (ETL) processes. This is around the correct source data extracted into the BI solution not being in synch with the source system data as needed for correct reporting measures, and key performance indicator validations. The data acceptance criteria can also be used to establish a baseline from a data verification perspective that can be used to verify data at certain checkpoints through the data flow architecture.
Mapping of report measures to its actual source will normally be collected during the initial data analysis and information gathering cycle. These also need to be verified by the individual source system owners and be inclusive of data acceptance criteria.
In some cases, consultants from third-party vendors might be required to support this exercise. The purpose of this is to:
* Obtain agreement on the data take-on period for use during the UAT process.
* Obtain verification on the mappings between data sources and all UAT-related reporting measures and key performance indicators.
* Obtain agreement on the source extraction rule. By default, it should be that all available data for a particular source for the defined UAT period is to be extracted.
* Obtain agreement on the source data acceptance criteria that would be needed to verify successful data extraction.
When both parties are satisfied that all the source data is present in the BI solution, UAT with the business representatives can commence.
Ren'e Muiyser is principal consultant at PBT Group.
Once the above verification and associated agreements are in place, the technical verification of the source data loaded into the staging area can be done together with the source system owners. If some business users have the necessary technical knowledge, it would prove beneficial to include them into process.
The following tasks are performed to obtain the results:
* BI team to execute scripts on the staging data to generate values according to the agreed acceptance criteria.
* Source system owners to verify the BI acceptance criteria values on own source system and provide a comparison report listing possible discrepancies.
* In case of discrepancies, the BI team needs to ensure the complete load has taken place.
* Where a source did not get loaded fully to the BI staging area as agreed, it should be re-loaded and re-verified.
Creating assurance
Some source systems may be described as a “black box” due to vendor restrictions. The following must, therefore, be done by the BI team to establish a high confidence in the correctness of this particular source identification:
* Analyse the SQL scripts as per reporting portal provided by the vendor.
* Use the knowledge base from involvement in other similar/identical source systems.
When both parties are satisfied that all the source data is present in the BI solution, UAT with the business representatives can commence. Where differences exist between a source system measures and the similar BI measures on the reporting level, the source system owners could initially be excluded from the problem solving exercise, as the problem is first assumed to exist only between the BI staging and exploitation areas. Business owners need to reconfirm the business logic to be used in these cases and it is compared to the current BI report extraction logic.
The importance of the above verification process can't be over-emphasised, as the BI team and the source system owners have to take the data sources as being correct as far as possible when UAT starts. Undertaking such measures will ensure the complete process of BI is an overall success from the start - proving that BI with its many benefits is a solution that businesses should be investing in.
Share