How to Perform Data Validation in QlikView

As with any BI tool, developer validity testing needs to be performed prior to any and all releases of an application to the end users. BI tools, like QlikView, allow for the rapid development of an application giving results very quickly. This rapid development is wonderful to help see results and make decisions…. but a developer must be cautious and perform a few accuracy checks on the data model prior to releasing an application. It’s better to have a few checks in place than to have the end users calling to report incorrect values!

A few simple inspections can be performed first as these will require less time and effort to review.

  • Review the connection strings of the databases that are used within script.
  • Review the SET variables to ensure that the proper file locations are being referenced.
  • Review the script to make sure that the proper flat files are being referenced.
    • Best Practice: List connection and flat file locations on the first tab within the Script along with comments to remind one to review prior to release.

Next, check integrity of the data model, making sure that the data is presented accurately for business users.

  • Does the model build without errors? If yes, that is great but there could be a logic error that is still lurking in the dark. An approach to finding logic errors is to compare Set Analysis to SQL queries.
  • Verify the tables by row counts and totals of the measures within the fact tables as compared to the source tables.
  • Key Fields should not be used for any calculations within the UI. If a key is needed within the UI create a copy of the key and rename it.
  • Review composite keys making sure that the data types align.
  • Informational Density less than 100% could mean NULL values are present and will need reviewed.

Once these assessments have been performed, move along to the UI and create a few test cases to spot-check any variables that are in use. Text boxes are a great way to watch variables and how they are affected during the test case.

The goal of data validation is to get the developer thinking of any possible oversights prior to release. The rapid development cycle allows for iterations so it will not be uncommon for an exception to pop up that will require changes to the existing data model or how a table is joined. This is the joy of rapid development within QlikView.

Happy modeling! 🙂

Share this post